Optimize database cache utilization to improve database performance

<< Garbage collectors | Database technology articles | Selecting the right datatype to improve database performance >>

Optimize database cache utilization to improve database performance

By Holger Klemt

Do you ever think about possibilities to improve your database performance? Sure, a database system such as Firebird or InterBase® is able to speed up typical operations internally but, in a lot of cases, there are very easy but powerful methods to improve performance.

Here is a first example:

When the first user connects to a database, the database cache is empty and all database and index pages must be read from the hard disk. The Superserver architecture will use the cache for all connected users for this database, but when the users are disconnected again, the cache is cleared and everything starts over again.

This is not only important for typical Delphi/C++/.net/Java client applications, but also for web server applications using PHP or ASP.

How to improve the database open performance?

  1. Use available memory as cache. The cache setting for a specific database can be changed in the IBExpert menu item Tools / Database Properties / Buffers / Pages. Maximum values depend on the used Firebird/InterBase® server version, but Firebird 2.0 supports up to 128k (131072) pages here.
  2. Use a large page size. Firebird 2.0 can be used with a 16k page size, so 131072 pages cache means about 2 GB ram is used as cache. When using an 8k page size, the maximum ram is 1 GB etc. To change the page size, just perform a backup and then restore with the changed page size.
  3. Important: Do not set this combination higher than the free available physical memory on your database server. It should also not be much higher than the database file size.
  4. How to fill the cache? When daily work starts, for example at 8:00am, it might be helpful to have the cache already filled before the employees start their work. For this reason, we create a simple stored procedure:
    CREATE PROCEDURE FILLCACHE
    AS
    declare variable SQL VARCHAR(200);
    declare variable cnt integer;
    BEGIN
       /* Fillcache Procedure (c) IBExpert Team*/
       FOR
         select rdb$relation_name sql from rdb$relations
         INTO :SQL
       DO
       BEGIN
         sql='select count(*) from '||sql;
         execute statement sql into cnt;
       END
END

This procedure is compatible with Firebird >=1.5, but it can be also altered to be implemented with InterBase® or older Firebird versions. Since it counts all data in all tables, all data pages are copied from the hard disk to the cache. When there is enough free memory, all cache pages remain in the memory until the last connection disconnects.

This script should be executed, for example, the first time every morning at 7:30 am. Write a batch file and create a job in the Windows Task Manager or Linux cron: 

 connect 'localhost:C:\db1.fdb' user 'sysdba' password 'masterkey';
 execute procedure fillcache;
 commit;
 shell sleep 3600000
 execute procedure fillcache;
 commit;
 shell sleep 3600000
 execute procedure fillcache;
 commit;
 shell sleep 3600000
 ......

 exit;

This script connects to the database, executes the fillcache procedure, commits the transaction and sleeps for one hour before it runs again. The operation is repeated as often as desired and the connections remain active until the command exit is executed. For example when executed hourly 12 times, it fills the cache for twelve hours and stops after that time. On the next day, the script starts again automatically.

  1. Additional advantages: this script also starts the garbage collector when it finds outdated records in the database, but this will only happen as long as there is no older active transaction (OAT) blocking the garbage collector.

Résumé

Feel free to implement these operations in your database server to improve the performance. We have a number of customers who have used this and reported very satisfactory improvements.

back to top of page
<< Garbage collectors | Database technology articles | Selecting the right datatype to improve database performance >>