--- In firebird-support@yahoogroups.com, Vlad Horsun <hvlad@...> wrote:

>
> > > > > How many databases active at this moment ?
> > > >
> > > >
> > > > 2 active databases.
> > > > 1 is very small.
> > > > Contains 1 table with 38 records inside.
> > > > During a day, this DB has some 200 reads and 50 writes.
> > > > No insert or deletes.
> > > >
> > > > Second DB - Its around 8Gb in size.
> > > > It has between 50 and 150 users connected.
> > > > I think I earlier on said some 200+, but as I have been

monitoring the

> > > > last days its "only" 50 to 150 users.
> > >
> > > Any chance that many of them run some queries with SORT in plan
> > > simultaneously ?
> >
> > If you be this means, that users do a
> > select f1, f2, etc
> > from table
> > Inner join....
> > where
> > Conditions...
> > Order by ....
> >
> > Then yes - There are times a day when users will do that on the same
> > time.
>
> What value of SortMemBlockSize and SortMemUpperLimit in your

firebird.conf ?

#SortMemBlockSize = 1048576
#SortMemUpperLimit = 67108864

So this is whatever default value FB uses.

>
> > > > Does some of them have
> > > > > fixed cache setting ?
> > > >
> > > > I don't know what this is.
> > >
> > > Show us the output of gstat -h for each database, please
> >
> > Is this the information you would like to have ?
>
> Yes
> ...
> > Page buffers 0
>
> I need to know value of "Page buffers" for _each_ database to

which there are attachments


DB1
Database "E:\DB\Kaufmann\OCCEASYPOS.FDB"

Database header page information:
Flags 0
Checksum 12345
Generation 13883537
Page size 4096
ODS version 10.1
Oldest transaction 13828972
Oldest active 13828973
Oldest snapshot 13823496
Next transaction 13883429
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 1
Creation date Jun 14, 2006 13:37:55
Attributes force write

Variable header data:
Sweep interval: 20000
*END*


DB2

Database "E:\DB\Kaufmann\OCCLICENS.FDB"

Database header page information:
Flags 0
Checksum 12345
Generation 33269
Page size 4096
ODS version 10.1
Oldest transaction 33260
Oldest active 33261
Oldest snapshot 33261
Next transaction 33263
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 1
Creation date Dec 9, 2006 23:36:40

Variable header data:
Sweep interval: 20000
*END*

> Also look at "DefaultDbCachePages" setting in firebird.conf

#DefaultDbCachePages = 2048
Again - The default valule.

>
> I can think about 3 possible ways to Firebird to reserve big

amount of virtual memory and not use it:

>
> a) Large unused page cache
>
> Firebird reserved virtual memory of size of page buffer setting

(in database header or in firebird.conf)

> when first attachment to database is made. But Firebird can't use

more pages than database file

> have - so it reserved one amount of memory and used another if page

buffer is greater than database

> file itself

Hmm - I didn't really understand all of it to the full.
But is there a way to change some settings in order to have do it
diffrent ?

>
> b) many simultaneous sortings
>
> Firebird reserved memory for sorting by SortMemBlockSize chunks

(no more than SortMemUpperLimit)

> of course it can use less memory then SortMemBlockSize

They do a lot of sorting.
Especially some of the rutines during the day creates 3 - 4 reports
that selects some 500 - 2000 records from a table containing some 8
mill. records.
I have looked at the sql, and it does use an index to retrieve the
records. And the index has just recently had a set statistics (besides
- There are only insert and updates (updates are not in index fields)
in this table. No delete).

The last few days I have monitored the memory use, and tried to see
what people where doing at the time when memory cunsumption suddenly
increased. If what I suspect is rigth it seems as if the memory
increases when creating some reports (which users often do at the same
time). But I'm not certain yet....


Can I in some way tweak these as well ?

>
> c) some memory leak
>
> It is possible you faced with some memory leak in Firebird but

much more probably in some UDF.

> But i can't imagine memory leak when physical memory is unused...

We don't use any UDF.

Regards and thx.

Michael

>
> Regards,
> Vlad
>