Hi all,



I'm trying to optimize firebird as a data warehouse for some reporting
queries, however memory use wasn't that great. The machine it's running
on currently is not state of the art (PIII 1.4 Ghz, 512 Mb RAM, running
win XP professional) but it is dedicated so how do I convince firebird
to use all the resources it has available?

So far I have increased the cache buffer size to 40k pages and set the
database to read only.

However I don't think I got the memory use settings quite right. It
seems to me that firebird always releases memory as soon as the
transactions/connections close, which means that for every query it has
to read everything from disk again. That is during execution VM size
goes to 220 Mb and Memory use crawls up to 100-150 Mb, but after closing
the transaction/connection it falls back to 47 Mb VM size and 4 Mb
memory use. At the same time I/O reads/writes are very high: about an
hour of moderate testing (one user) gave I/O reads of 14 Gb and writes
of 4.6 Gb (these values are from task manager, yes I am aware that is
not too trustworthy, but it gives an indication). This must be nearly
all disk activity as the recordsets returned over the network are always
small (about 20-100 records)



Database information:

-------------------------------

Two heavily indexed tables, one with ~500k records the other with ~1M
records.

Database file size: ~300 Mb

Database backup file size: ~230 Mb

Firebird version: 2.0 RC1



Database header page information:

Flags 0

Checksum 12345

Generation 28

Page size 4096

ODS version 11.0

Oldest transaction 1

Oldest active 2

Oldest snapshot 2

Next transaction 18

Bumped transaction 1

Sequence number 0

Next attachment ID 0

Implementation ID 16

Shadow count 0

Page buffers 40960

Next header page 0

Database dialect 3

Creation date Apr 28, 2006 15:00:47

Attributes force write, read only



Variable header data:

Sweep interval: 20000

*END*



An additional problem I'm having is filling the warehouse and performing
database-wide updates (obviously I turn read-only off for this). For
instance it's almost impossible to do a simple UPDATE Sales SET outQTY =
outQTY*-1; (updates 500k records, outQTY is not indexed) (I gave up
after it hadn't returned after 20 minutes).



Regards,



MD





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