November 26th, 2013, 07:54 AM
Does big trancastions affect's performance
I have lot of problems with Firebird performance with database from about 3GB up to 12GB. From time to time (al least once a month, but sometimes even once in a day) server performance slow down rapidly, and without any intevnentions after one-two hours performance is ok. Automatic sweep is turned off. During slow performance in Windows Resource Monitor Disk activity od fbserver.exe is high, Read about 1MB/sec and Wtihe 1MB/sec. Servers are Windows 2003, 2008 R2 and 2012, Firebird 32bit and 64bit, version 2.1.5.
At this moment I suspect that problem is in big transaction (which changes lots of records in single transaction, this transactoins are relativly short, from 1 to 5 minutes) which where executed maybe few hours or minutes before and made lots of updates. My guess is that FB server spends lots of time when trying to use old record versions which are made in a single transaction.
Could my guess be correct, or maybe there is some other couse of this performance problem?
April 28th, 2014, 04:46 AM
There is a limit to the transaction counts in both Firebird and Interbase. You want to limit the record count to (dependent on record size) 10,000 to 500 as Firebird keeps the version of each record it is modifying in memory.
As with all Windows machines, if you run out of physical memory it switches to virtual memory which once again increases your disk usage.
Your disk usage due to slowness of data write and also head placement and availability (for reading and writing) can drastically hamper performance if it is a big transaction.