Hi,

Here is more informations :

- I don't use UDFs at all
- My tables aren't external
- I don't use blob but big varchar(8192)
- Primary key are not generated automatically so no trigger/generator at
all.
- Firebird version is 2.1.1.17910 is running in Superserver mode.
- Client application (written in delphi) connect to server via ODBC
(1.2.0.69)
- Database is running on Windows 2003 Server 32 bit (in Vmware ESX
server environment)
- Each transaction don't make many insert or delete (3 or 4 queries max
by transaction)

Here is the stats I could get :

Database header page information:
Flags 0
Checksum 12345
Generation 2133722
Page size 8192
ODS version 11.1
Oldest transaction 2133718
Oldest active 2133719
Oldest snapshot 2133719
Next transaction 2133720
Bumped transaction 1
Sequence number 0
Next attachment ID 18
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Oct 13, 2008 14:35:02
Attributes force write

Variable header data:
*END*


Database file sequence:
File C:\Galaxie\Customs\DataBases\SCHV2.fdb is the only file

Analyzing database pages ...
TEXPERIMENT (128)
Primary pointer page: 150, Index root page: 151
Data pages: 184, data page slots: 1450, average fill: 18%
Fill distribution:
0 - 19% = 98
20 - 39% = 83
40 - 59% = 3
60 - 79% = 0
80 - 99% = 0

Index RDB$PRIMARY1 (0)
Depth: 2, leaf buckets: 3, nodes: 1198
Average data length: 1.84, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 2
60 - 79% = 0
80 - 99% = 0

Index RUNQUEUE (1)
Depth: 2, leaf buckets: 5, nodes: 1198
Average data length: 5.73, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 2
40 - 59% = 2
60 - 79% = 0
80 - 99% = 0

TNOTIFICATION (131)
Primary pointer page: 162, Index root page: 163
Data pages: 1, data page slots: 1, average fill: 1%
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0

TOPTION (130)
Primary pointer page: 159, Index root page: 160
Data pages: 27, data page slots: 367, average fill: 78%
Fill distribution:
0 - 19% = 1
20 - 39% = 1
40 - 59% = 2
60 - 79% = 0
80 - 99% = 23

Index RDB$PRIMARY3 (0)
Depth: 2, leaf buckets: 6, nodes: 1198
Average data length: 11.16, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 1
40 - 59% = 3
60 - 79% = 1
80 - 99% = 0

TTASK (129)
Primary pointer page: 155, Index root page: 156
Data pages: 683, data page slots: 1768, average fill: 71%
Fill distribution:
0 - 19% = 9
20 - 39% = 60
40 - 59% = 57
60 - 79% = 331
80 - 99% = 226

Index INDEXSYSTEM (1)
Depth: 2, leaf buckets: 2, nodes: 1199
Average data length: 0.10, total dup: 1169, max dup: 40
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 1
60 - 79% = 0
80 - 99% = 0

Index RDB$PRIMARY2 (0)
Depth: 2, leaf buckets: 11, nodes: 1199
Average data length: 28.83, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 3
40 - 59% = 5
60 - 79% = 2
80 - 99% = 0


Thanks for your time !


Svein Erling Tysvær a écrit :

>
> I take it that you don't have any UDFs either, that your tables aren't
> external tables and that there's nothing else that can be of
> importance either (e.g. do you use Blobs?). You haven't mentioned any
> keys in the tables, I take it that you do use primary keys and
> possibly generators (ah, looking at your description you don't have
> any triggers, so that means no triggers for assigning primary keys)?
>
> What's the database statistics like (that's a more reliable way to
> deem that there's no transaction that keeps things from moving on)?
> Which Firebird version are you using (both server and client), how do
> you access Firebird (IBO, IBX, something completely different) and
> what operating system?
>
> 160000 inserts and deletes per day doesn't sound too much, do each of
> them happen in separate transactions or are there several
> inserts/deletes per transaction?
>
> It is normal that the file size increases at first, but after a while
> it should start reusing empty space. So if the file size keeps
> increasing day by day, you have no long running transaction and empty
> the database each day, then I'd say there's something that isn't right.
>
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>
> [mailto:firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>] On Behalf Of Johanny Bergeron
> Sent: 15. oktober 2008 09:15
> To: firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>
> Subject: [firebird-support] Memory consumption / Database file size
>
> Hello,
>
> First, I must say that I don't do a typical usage of the database.
> The database is used to store some data for 1 day maximum (really simple
> db : 4 tables, 0 procedures, 0 triggers, some indexs), and the number of
> records by table never gets really high (2000 max). But where Firebird
> must be stressed is I am doing many insert and delete (around 40000 by
> day for each table) + all the select queries. The client application
> stay connected to the database server 24 hour a day.
>
> The database is working good except 2 things that prevent me to have an
> application stable for production (server or Firebird service must not
> be rebooted !!)
>
> -) The memory consumption of the Firebird process is increasing day
> after day. My initial thought was I had forgotten to release some
> transactions or queries in my application. However, even if all client
> application disconnect, the memory don't go back to its initial state so
> I think the problem don't come from client application.
>
> -) The size of the database file keeps growing, even if the number of
> records in the database don't grow (but many are deleted and new ones
> inserted). Is it normal?
>
> If someone could help, I would be very happy , Thanks :)
>
> Johanny
>
>

--

Johanny Bergeron
Instrument Control & Hardware R&D , Developement Engineer
Analytical Instruments
Varian Data System S.A.S
1 rue Hector Berlioz
Fontaine, 38600
France


johanny.bergeron@... <mailto:johanny.bergeron@...>
www.varianinc.com

<http://www.varianinc.com>/Inspiring Excellence/ ^TM



[Non-text portions of this message have been removed]