Subject FB 2.1.3 Classic on Debian - performance problem
Author jczyzkowski
I have the following problem with my database:
Server: IBM 4xSixCore, 24 GB RAM
Linux Debian 2.6.30-2-amd64
Firebird ClassicServer
About 100 attachments in the moment,
Clients: PHP4, IBX (VCL - Windows), .NET FirebirdClient, Python

After restarting all machine everything go fine.

But after a couple of hours server slow down.

It seems that after some users disconnect (logout), properly or not properly (ex. killing client process), server slows down.

When I look at htop I see, that max 60-70 % of processor power is used (at start there are moment where 100 % is). Even if I run a big procedure - it's going for couple of minutes, but process takes pnly 60% of processor power. There is no waits on disks, no I/O interrupts, no memory swap, server machine is almost "idle" - but queries runs slower and slower ...
Insert or update statements get the same time, but select time can grows a couple of times.

There is not older transactions that 3-5 minutes. I have 1000-2000 active transactions in the same time.
Sweep is set to 20 000,00, and works fine and fast.

Where could be a problem ? As i said, I can observe slowing down after some connections are closed.

What is strange - the are a lot of MON$STATEMENTS record, that doesn't have transactions (transaction is closed), and they leaves almost all connection time. There are in Prepared state, but they are not closed when transaction is. Could it have the influence for slowing down ?

To get server faster, I must stop xinet, kill all fb_inet_server, wait about 20 sec, and then start. If not wait, just kill all fb_inet_server, problem stays.

Another issue: I had a problem with connection loosing by mobile devices, when WIFI connection is lost. Mobile client was detecting connection lost, but on server side this connection (and it's transaction) was still visible (in MON$TRANSACTIONS). I was thinking, that this lost connections could slow down server, so I set ConnectionTimeOut for 30 sec in firebird.conf. But there is comments, that it shouldn't be used with Windws Clients.

Any ideas, where could be a problem ? In firebird.conf, or maybe in some linux settings ?

I have one problem - client on PHP4 connects by driver from FB 1.5. Can it have so influence for performance ?

I really need a help, I spend almost 2 weeks and know I'm thinking to "downgrade" to Firebird 2.0 ...

Database "/srv/firebird/database/esystem.fdb"
Database header page information:
Flags 0
Checksum 12345
Generation 4248353
Page size 16384
ODS version 11.1
Oldest transaction 3812274
Oldest active 3812275
Oldest snapshot 3801134
Next transaction 3812419
Bumped transaction 1
Sequence number 0
Next attachment ID 435894
Implementation ID 24
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Jan 14, 2010 22:14:34

Variable header data:
Sweep interval: 20000