Subject RE: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Author Leyne, Sean
> Hello,
> I have installed Firebird 2.5.3 SuperServer x64 on a Windows server 2012 R2
> (x64).
> At the moment I have done no changes in firebird.conf
> Hardware resources are:
> 2 x 12core Intel Xeon, 32 GB DDR3 Ram, 6 x 1.2TB SAS RAID 10, 1 GB Ram on
> 6Gbps RAID controller with Flash Backup and Battery Backup on it.
> The IOPS values with CristalDiskMark  ( 5 x 100MB test) are:
> Seq                  3805R 3754W
> 512K                2787R 2756W
> 4K                    109R   106W
> 4K QD32         440W  338W

Please re-execute the CDM test using 5 x 4000MB (or largest run size) settings. Running too small a test settings can actually have the controller Flash+Battery caching all the disk writes, skewing the results

> The application reads from a text file, checks in FB table if the record exists,
> than writes the record in the same table. This is for about 35000 records.
> The application takes about 9 minutes to end.
> Now, with same application, same DB, Same Firebird version, but on an old
> 2003 server monoprocessor, old raid 1 controller, it takes about 12’ minutes
> to end.
> So my new W2012 is faster, but only 3 minutes less, I think I can really obtain
> better performances.

The problem would seems to be with the logic used by the application/within the database. 9 mins seems like a long time to process 35000 rows.

14+ years ago, I wrote an application to load records from text files and insert them into a database, I was able to get performance on the order of 1000 rows/sec for the import. So, to think that an app would need 8.5 min to process same, seems very unlikely if the process is properly designed/implemented.

So, you need to provide details on the exactly database interactions/operations which are being performed and what SQL statements (and the PLANs) are being used.

> I have read lots of documentation about File System Cache  or DB Cache
> Pages, but honestly I need some good indication from anyone of you,
> because I’m very new with Firebird and I think there are several settings to
> obtain the best from this brand new and “speedy” hardware.

System File Cache is a real problem with 64 bit Windows systems. But, your database would need to be much larger for that to have an impact (database would need to be larger than RAM)

Please provide details on the database page size (using gstat to extract) and page cache settings (from the Firebird config file).