Subject FB 1.5 RC6 linear slowdown
Author Rajesh Punjabi
Hi Folks,

Our environment is WinXP, 2.4 GHZ P4, 512 MB RAM with FB 1.5 RC6. Our
application runs on JBoss 3.2.1 with JDK 1.3.1 and Jaybird 1.0. We have
stress tested our application with Oracle 8i and MySQL 4 with InnoDB
databases. We are now trying to port our application to Firebird.

All our connections to the database use J2EE datasources and Read
Committed transaction isolation level with wait and record version. We
have tried various settings on the database inlcuding

1) Forced writes enabled/disabled using gfix -write
2) Increased and decreased Buffer sizes in a 0-10000 range
3) Changed sweep intervals using gfix -housekeeping. We also tried
disabling sweep altogether.
4) Increased and decreased page cache sizes in a 1024-8192 range
5) Backed up the database and restored regularly

We use only primary key and foreign key indexes. All our queries are
prepared statements with simple selects and the joins are done at the
session bean level. We do not use any Stored procedures or triggers.
There is no memory leak and the memory used by JBoss as well as FB is
almost constant over a period of time. As the number of records
increases in the database, FB seems to visibly slow down. The slowdown
is apparent even when the database is only 10 MB in size. Also, our
application runs 2X slower on FB as compared to MySQL InnoDB and 1.5X
slower as compared to Oracle.

We monitered locks and after every request our application processed all
the locks were being properly released and the number of open
connections to the database was also constant (application server
pooling). The slowdown in request processing is exhibited by our
application only when we use FB. I am now trying to create test cases
that use Java-JDBC and EJB (Stateless Session Bean + Entity Bean) to
check if this is a problem with our application, Jaybird driver or the
application server JBoss (or our cache settings in JBoss).

Are we doing anything glaringly wrong? Has anyone else seen this kind of
behavior in FB 1.5 RC6? I know it is difficult to solve any problem
without an accompanying test case so I will send out an email with a
test case. Is there any obvious tuning trick that we have missed? I did
not find much information on how to read the information shown by GStat
and fb_lock_print. I read through Interbase 6.0 documentation and
IBPhoenix knowledge base and could not find any information on how to
interpret the stats printed by Gstat and lock print.

Any help is truly appreciated.