Subject Re: [firebird-support] Small Database with very very bad performance on LAN
Author Thomas Steinmaurer

> Hallo,
> I have a small database (less 100MB) that works on Firebird 2.5.5
> SS64bit over a machine with Win7 64bit with 4GB Ram, CPU Pentium G620.
> I seems to work very well in local but with the 3 clients on LAN works
> very bad. It is very very slow to open and navigate a simple table with
> less of 5.000 rows!
> What i can do?

You have to sort out where your current bottleneck is.

Can you pin-point it to a particular query or is performance in general
bad? With Firebird 2.5, you have quite some monitoring amory, e.g.
monitoring tables (tables starting with MON$...) or the Trace API.

Try to get a big picture with that tooling to see what's going,
statement execution plans etc..., I/O statistics, e.g. is data being
fetched from disk or served from the Firebird cache, which I doubt with
your page buffers value below.

> I very novice in Firebird.
> The result of gstat is:
> Database header page information:
> Flags 0
> Checksum 12345
> Generation 26613
> Page size 4096
> ODS version 11.2
> Oldest transaction 26603
> Oldest active 26604
> Oldest snapshot 26604
> Next transaction 26605
> Bumped transaction 1
> Sequence number 0
> Next attachment ID 139
> Implementation ID 26
> Shadow count 0
> Page buffers 0
> Next header page 0
> Database dialect 3
> Creation date Jan 4, 2016 15:44:10
> Attributes force write
> Variable header data:
> Sweep interval: 20000
> *END*

With SuperServer and a page size of 4096 and a page buffers set of 0,
which gets overriden with a default value of 2048 from firebird.conf (if
not specified otherwise), you end up with a Firebird page cache of 8MB
(!) only for your database.

Increase "Page buffers" to e.g. 10000 (e.g. with gfix command-line
tool), then re-connect your client application and see if this helps.

There is other tuning stuff, but as a first step increasing the Firebird
page cache + getting a big picture on your slow statements, their
execution plan probably pointing to missing indexes ... is a good start.

Good luck.
With regards,
Thomas Steinmaurer

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.