Library

Required information for Firebird database performance optimization

Sergey Nikitin, 31-March-2014 
To get an expert answer about Firebird performance optimization, please answer the following questions in this online form.

A questionnaire for those who have performance problems with Firebird databases. These questions cover most popular performance problems with Firebird or InterBase databases. If you cannot answer some questions, skip them. 

Hardware

  • CPU Model and number of cores
  • RAM amount
  • HDDs
  • Model of HDDs (SAS, SATA, SSD) and their parameters (size, cache size, rpm) or exact HDD model
  • Is there RAID?
  • If yes, what is RAID Controller (model)? If it’s software RAID, model of motherboard
  • How many drives and RAID type (0,1,5,6,10, etc)?
  • Is Battery Backup Unit installed in RAID (if needed)?
  • What is RAID cache mode (Write Through, WriteBack)? What is the size of the stripe set?
  • How the system is placed at HDDs: where is System location, where is the database, where are temp files (if changed)? What is the block/cluster size of the partition with the databases?
  • Any specific details about hardware you can provide (like vendor, model of the server, etc)

Virtualization

  • Is OS running as Virtual Machine instance? If so, what kind of VM (ie: VMWare, VirtualBox, etc), and what are parameters of this instance (# of CPU cores, RAM, HDDs).
  • How many other Virtual Machines are running on the same host with Firebird server?

Operation System

For Windows

  • What is Windows version, is it 32 or 64 bits and service packs installed (like Windows 7 Pro 32 bit, Windows Server 2008 R2 64 bit, Windows 8 64 bit, etc)
  • If it is a Windows server, is it a domain controller?

For Linux

 

  • What is the version of Linux? 32/64 bit?
  • What are the versions of Linux kernel and glibc?

Firebird

  • What is the exact Firebird version (on Windows you can right-click on Firebird executable, tab Version or Details, on both Windows and Linux run isql -z)?
  • What is Firebird architecture – Embedded, SuperServer, Classic or Super Server (check what is running - fbserver.exe is for SuperServer, multiple fb_inet_server is for Classic, single fb_inet_server - SuperClassic)?
  • Where is Firebird installation folder?
  • Please send firebird.conf
  • Please send firebird.log (zipped)
  • Please run fb_lock_print –d 3 times during peak time (for example, 11-00, 14-00 and 16-00), zip results and send to us too.

Database

  • What is the database size?
  • How many databases are on the same server?
  • Please send the output of gstat –h from the peak load (around noon), and no load – early morning or late evening.
  • Please get output from peak time with this command
    gstat –a –r > stat.txt
    and send it to us (zipped).

    Application

    • Is it client/server (when each user runs separate instance of application “per connection”, and usually each user runs on the separate computer) or middleware (when there is the single instance (or a few instances) which works in multi-tier mode, for example, Java web-application in Apache/Tomcat)?
    • Is it 32 or 64 bit?
    • What is the platform (Windows, Linux, MacOS X)?
    • How many client connections do you have during normal load and at peak times? (if you are using FB 2.1 or greater, run SELECT count(*) from MON$ATTACHMENTS )
    • What data access method are you using (ex: JDBC, ODBC, etc… for Delphi apps: BDE, IBX, IBO, dbExpress, etc. )
    • What is the type of the application? For example – CRM, ERP, accounting, etc?

    Describe the performance problem

  • Background of the problem
  • Were there changes recently in hardware/OS/applications?
  • Is there random peaks, or problems occur during specific moments (for example, after lunch, around 16-00, etc)?
  • Any other details about the problem (What users say about a problem?)