Robert martin schrieb am 21.03.2012 um 15:18 (+1300): > We have noticed performance issues on a machine running a web service > connected to a FB 2.5 database. On a brand new machine (to rule out > computer problems) everything worked fine for the first few hours, > however after deleting a large number of records our performance > issues surfaced. Instead of a process starting for each connection, > running for a few seconds, then completing (closing) the transactions > open and sit idle for a long period of time before processing. > > Alter doing a backup and restore the database changed from 1.9GB to > 560MB. Restarting the web server and everything is working great > again.
Just a guess, but you could read these replies from the archives and see if that might be the problem you're facing, long-running transactions preventing garbage from being collected, slowing down operations in the process. FB performance - Helen Borrie 06.01.06 http://groups.yahoo.com/group/firebird-support/message/70793 Then look closely at how transactions are being committed. The IBX default for AutoCommit is to use CommitRetaining. This is handy for Delphi client apps and disastrous for the server. It causes garbage to build up steadily. You are seeing better performance on a clean database and degrading performance as garbage builds up and doesn't get cleared. Check the database header statistics over this period of degradation (gstat -h). If you see a widening gap between the Oldest Transaction and the Oldest Active then you'll know that poor database hygiene is a significant part of your problem. Gbak never end... - Svein Erling Tysvær 12.02.09 http://tech.groups.yahoo.com/group/firebird-support/message/100241 One very important part of Firebird is transactions. They should never be running for a long time (well, 'never' is a strong word - read-only, read-committed transactions may be kept running, as can all transactions on read-only databases, however, your transactions probably do not fall in these two categories when you have the kind of symptoms you describe). There may of course be other reasons for your problem, but it is typical for us Delphi developers (particularly if we have desktop database background) to not take proper care of our transactions and observe that our programs become slower and slower until we finally decide to stop the database. Then everything works fine for a while again until a new slow-down cycle starts. If this fits your observations, then you need to take a look at the transactions. If your problem is not gradually worsening, but that the program seems to work perfectly until suddenly coming to a halt and that there is no large gap in the statistics, I'd say there is probably some other reason for your problem. fb 2.1 on Windows: how to use more RAM? - Svein Erling Tysvær 28.08.10 http://tech.groups.yahoo.com/group/firebird-support/message/109741 Databases that respond quick when they're started, and then gradually slow down until almost coming to a halt, are a typical sign of poor transaction handling in one or more applications. The gap between oldest transaction and next transaction is often the 'proof'. Multi-Version Consistent Read Question - Ann Harrison 13.12.11 http://tech.groups.yahoo.com/group/firebird-support/message/116063 Firebird keeps information about each running transaction, including the oldest transaction that was running when the transaction started. When choosing record versions to remove, Firebird compares the transaction identifier in the record version header with the "oldest of the oldest" - i.e. the oldest transaction that was running when the oldest transaction now running started - and keeps one version older than that. If the oldest transaction running is 200 and the oldest transaction running when it started was 175 and the chain of record versions goes 199, 176, 175, 174, 173, 140, 123, Firebird can remove the versions created by 173, 140, and 123. The newer versions will all stay until transaction 200 exits and the next "oldest of the oldest" is higher than 175. If that's not your problem, then I'd say it's a nice collection of useful quotes nonetheless. :) Michael > My suspicion is that this is related to garbage collection. Does this > sound likely? > > We are using the FB 2.5, superclasic with default garbage collection > settings. What changes would people recommend? > > I am considering turning off garbage collection and scheduling it to be > manually run late at night. This would work fine for this system but > may not suit other users. > > Machine is Win7-64 Quad core I7 with 8GB RAM running Apache.
