|Subject||R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2|
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.
Da: email@example.com [mailto:firstname.lastname@example.org]
Inviato: venerdì 26 settembre 2014 22:10
Oggetto: RE: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
> Hello,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
> I have installed Firebird 2.5.3 SuperServer x64 on a Windows server 2012 R2
> 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
> The application reads from a text file, checks in FB table if the record exists,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.
> 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.
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 CacheSystem 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)
> 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.
Please provide details on the database page size (using gstat to extract) and page cache settings (from the Firebird config file).