>> 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.
>>
>> 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.
>
> We have taken the database offline and did a full
> backup/restore/replace, removing "no reserve", and setting the page
> size to 16384. The hardware is raid 5 running on 3 scsi hard drives
> using NTFS 4k cluster size on Windows 2003 32bit with 4GB ram. Using
> IB Analyzer on the original database we found that there were a number
> of indexes with a depth of 4, and a number of tables showing
> fragmentation because of record size or blobs (not sure what that is
> about).

Fragmentation also as a reason for the no reserve stuff, I guess.

The worst thing I've ever seen regarding index depth was 6, but this was
because the guy used a page size of 1K "per accident". ;-)

You also should have a look on "useless" indices, basically large
indices with only 1 unique value as the worst scenario.

> In this setup, should using a page size of 16384 with the same 2048
> page buffers do a better job than the previous 8192 page size?

You pretty much double max. RAM usage for the page cache and AFAIR you
are using Classic, thus, this means PER connection. Have an eye on that
that these settings don't exceed the available RAM getting into a swap
scenario, especially with other concurrent RAM-intensive processes.

> The
> ondisk size of the db is now 93.4GB vs the old one at 91.2GB, which I
> suppose is the 80% page fill and the larger pages.

Correct.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/