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.
- CPU Model and number of cores
- RAM amount
- 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)
- 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?
- 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?
- What is the version of Linux? 32/64 bit?
- What are the versions of Linux kernel and glibc?
- 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.
- 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).
- 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?)