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: [email protected] [mailto:[email protected]]
Inviato: venerdì 26 settembre 2014 22:10
A: [email protected]
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
