Subject R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Author Costantino Molinari
Post date 2014-09-27T11:18:40Z

Hi Sean, thanks for the contribution.

 

Some answers to your requests:

1)      Logic of application.

It is a Microfocus Cobol legacy application, with latest (2014) x64 runtime. The long time is obviously not relative to a simple sql command, but to the overall execution. We have developed a dedicated interface (a Delphi x64 service and dll), cobol uses to read from vision indexed cobol files, read into Firebird table if record exist and than insert the record. It is to populate new tables, in order to use a different application with firebird database. The same application runs on windows server 2003 32bit, windows server 2008 64 and SLES 11 SP1 x64. The interface and dll’s are the same, just like the cobol program.

The problem is related to the fact that I expected a real big difference between old or very old hardware in RAID 1 configuration, and this brand new hardware, instead it gained only 20/30 %. Consider this: another application, that runs only in cobol environment, without database, has passed from 2 hrs to 15 minutes !!

Just to say, that surely we can improve our legacy application or the Delphi interface. But if I compare the identical application, with very different machines, I see a little improvement dispite the big difference in hardware (Firebird is always 2.5)

 

2)      The IOPS values with CristalDiskMark  ( 5 x 4000MB test, no bigger size possible) are:

Seq                  627.9 R           529.4 W

512K                102.1 R           244.6 W

4K                    1.280 R           14.62 W

4K QD32         13.85 R            14.75 W

 

One thing to note: the controller cache is used only in write (Write Back) and not in read (Read Ahead disabled)

 

3)      “System File Cache is a real problem with 64 bit Windows systems”

Wow !!! this database will get bigger and bigger. From a rapid calculation, it will grow about 50 Mln of records per year, and we are going to populate initial database with 3 years data (2012-2014). Should I be worried ?

 

            Page size is 16384      firebird.conf  is not edited now (this is because I ask some help), it is as freshly installed.

 

Thanks

Costantino

 

 

Da: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Inviato: venerdì 26 settembre 2014 22:10
A: firebird-support@yahoogroups.com
Oggetto: RE: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2

 

 



> 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).


Sean