I have an FB 2.1.5 Classic server running on a Windows 2003 server, with a single hard drive for the operating system, and a 3 disk raid 5 array for the database. We have one database on this machine, which is a dialect 1 database that was started on IB6.0 many years ago, currently at 90GB. We have sweep disabled, and each night run gbak, gfix sweep, as well as reindex all tables via a script.
The database has very little OLTP, and is mostly used for reporting and serving web pages to internal business users. We do alot of ETL starting very early each morning, and create a mix of scheduled reports as well as allowing users to specify parameters to create pre-designed reports in an ad-hoc manner. Once or twice per month, the system slows down tremendously. One ETL process typically runs at a pace of about 1000 records per 10 seconds. During these slow periods, the same ETL will run 1000 transactions per 60-80 seconds. When processing a file with 1mil+ records, this slow down costs us hours. I have not been able to determine the reason for these slow periods. They do not coincide with higher cpu or disk usage most of the time I'm seeing very little usage of anything disk/cpu/network/memory. I do see more connections to the database during these periods typically we have 10 to 15 connections, and the number may double during the problem times. This is due to the fact that the reports that users are requesting are taking longer to run, and our pooled-connection application server or web server creates more connections to satisfy new user requests. I do see more queries running by the time I am notified of the problem. Again though, this is a coincidence of the slowdown and not the cause queries are taking longer to run, and therefore we have more chance of overlapping user requests than usual. Most of the time we need to reboot once or even twice to fix the problem. That is not a viable long-term solution though, and I'm looking for more ideas to determine what may be happening. Any ideas would be most helpful. I have included gstat h output of the database as it is suffering from the issue below: Database header page information: Flags 0 Checksum 12345 Generation 43136192 Page size 8192 ODS version 11.1 Oldest transaction 40789582 Oldest active 41467442 Oldest snapshot 41467442 Next transaction 42431040 Bumped transaction 1 Sequence number 0 Next attachment ID 705070 Implementation ID 16 Shadow count 0 Page buffers 2048 Next header page 0 Database dialect 1 Creation date May 2, 2009 22:22:39 Attributes force write, no reserve Variable header data: Sweep interval: 0 *END* Thank you for your assistance, Bob M.. [Non-text portions of this message have been removed]