FireBird OPTIMIZATION

FireBird OPTIMIZATION. ..

AGENDA. ● Hardware optimization ● Firebird server optimization ● Programming optimization ● SQL optimization ● Windows Server optimization.

Hardware optimization. ..

Put your database on SSD. Put your database on SSD ● SSD drive provides much better random IO than traditional drives. ● Random IO is critical for reading and writing data distributed through big database file - the majority of database operations require intensive parallel random IO..

RAID 10. Use RAID 10 ○ If you use RAID1 or RAID5, consider RAID10 ○ RAID10 it is 15-25% faster.

Backup Battery Unit. ● Use BBU ● If you are using RAID controller, check that it has Backup Battery Unit (BBU) installed and operational – some vendors do not provide BBU by default. ● Without BBU, the controller disables the cache, and RAID works very slow, even slower than usual SATA drives. ● Usually, you can check BBU status in the RAID configuration tool..

Low level check disk. ● Check disk subsystem ○ Check your drives for bad blocks and other hardware problems (including overheating). ○ Hardware problems can significantly decrease IO performance and lead to database corruptions..

Firebird 2.5. ● Use SuperClassic or Classic in Firebird ○ If you use Firebird 2.5 SuperServer with many connections, try to use SuperClassic or Classic, they can scale better by using all cores of CPU..

Firebird 3.x. ● Use SuperServer 3.0 ○ If you use Classic or SuperClassic in 2.5, consider migration to Firebird 3.0 SuperServer ○ Now it can use multiple cores and combine it with the advantages of the shared cache.

FIREBIRD SERVER OPTIMIZATION. ..

Increase page buffers cache. ● Increase the size of page buffers cache (parameter DefaultDBCachePages ) from the default values. ● For 2.5 SuperServer we recommend 10000 pages, ● For 3.0 SuperServer – 50000 pages ● For Classic and SuperClassic – from 256 to 2048 pages. However , don't set page buffers cache value too high – cache synchronization has its cost, and the idea to put all database into RAM by tuning this value will not work..

Backup location. ● Store backups on another drive ○ Store database backups on the dedicated physical drive (RAID). ○ It will separate read and write IO during backup, and increase backup speed and decrease load for the main drive. ○ It is especially important when backups are taken while users are working with the database..

Clean Firebird temporary files. ● Temporary files are stored in the following locations : ○ Windows C:\ProgramData\firebird , ○ Linux / tmp /firebird ● Normally these files should be cleaned automatically, however, sometimes it does not happen (for example, in the case of server reboot). ● Check these folders periodically and clean old files – there could be many GBs of outdated files fb_NNN.

Programming optimization. ..

Bulk insert. ● Deactivate indices for bulk inserts ○ If you insert or update many records (more than 25% of the table), deactivate indices for the table where records are inserted and reactivate them after insert or update. ○ The index rebuild operation can be faster than many updates of the index..

No more indexes. ● Avoid unnecessary indices ○ Use fewer indices for tables with intensive inserts and updates. ○ Each index adds significant overhead for insert, update, delete, and garbage collection operations ○ There could be 3-4 additional page reads and writes when the single record is being inserted, updated, deleted, cleaned for each index..

Don’t use UDF. ● Replace UDFs with embedded functions calls ● Many embedded functions were added in the recent versions of Firebird, which offer functionality previously available only in UDF libraries. ● Replace such functions where possible, since embedded functions work up to 3 times faster than UDFs..

Long record chains. ● Avoid situations when one record has many record versions ● Firebird works much slower with long record chains. (to see how many record versions some tables has, and what is the longest record chain you can use HQbird IBAnalyst tool, tab Tables, sort on "Max Version"). ● Use the combination of inserts and scheduled delete of old records instead of multiple updates of the same record..

Queries tuning. PLAN If there is a NATURAL Plan – ensure usage of index in related fields (if index exists then just rebuild) Avoid maximum use of UDFs Don’t use ORDER BY clause with procedure selection statement Move it inside the procedure Use Nested query with GROUP BY instead of usage of DISTINCT.

Queries tuning. Reduce use of LIKE, OR, CONTAINING, STARTING WITH in queries Use ROWS instead of FIRST Do not use ‘SELECT * FROM’ in existence checking Use ‘SELECT FIRST 1 < PK_Field > ‘ or ‘SELECT PKFIELD FROM <Table Name> WHERE <Condition> ROWS 1’.

Queries tuning. JOIN Join necessary tables only Join tables with fields in index created order itself Select values from main table first in nested left join query Check conditions of main table first in where clause Reduce case statements.

Primary Key - BIGINT. ● Use BIGINT type for ○ auto-incremented primary ○ unique keys ○ identifiers of all types. ● Operations with BIGINT are the fastest ● BIGINT has enough capacity to store almost all data ranges..

Primary key - VARCHAR. ● Don't use VARCHARs for keys ○ Don't use VARCHAR for identifiers unless it is really necessary ○ Operations with them are far less effective than with integer columns . Avoid unnecessary sorting in stored procedure.

MON$. ● Avoid often queries to monitoring tables ○ Don't run queries to Firebird monitoring tables (MON$) too often such queries consume significant resources and can greatly decrease the performance of the main business logic. Recommend running MON$ queries not often than once per minute..

Windows server optimization. ..

Interact with desktop. ● Firebird with Classic architecture on Windows Enable «Interact with desktop» ● Without this setting, the resource «desktop heap» is limited by Windows Interact with desktop ● Firebird cannot open more than 250-300 connections (depends on the metadata of the database and related memory consumption) – there will Out Of Memory error..

Beware of Domain Controller. ● Windows with Domain Controller role disables the write cache on the disk with Active Directory database. ● We have significantly worse performance than on the servers without Active Directory roles. ● Please note, that this problem affects such popular Windows version as Windows Small Business Server 2011, as well as other versions with DC..

THANK YOU. ..