November 26th, 2013, 08: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?