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 512K102.1 R 244.6 W 4K1.280 R 14.62 W 4K QD32 13.85 R14.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 512K2787R 2756W 4K109R 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
Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Hi Costantino, I did some experimenting before one year and I found that Firebird is much faster when you use page size = cluster size on the file system. So if your file system is with 4K cluster I suggest to use page size of 4K. This is very helpful when you have Forced Write = ON. Performance gain with insert only scenario is more then 10-15% from 16K page on Windows 7 with RAID 10. another thing to look for is to try to minimize the number of transactions you create. Try to put as many as possible statements into single transaction. So for this check do you use autocommit on every statement or you wrap all statements executed while processing single file in one transaction. Also when you process your lines in the input file try to group as many as possible selects into single select. for example: select field1, filed2, filed3, field4 from table1 where field1 = ? and field2 = ? into : select field1, filed2, filed3, field4 from table1 where (field1 = ? and field2 = ?) or (field1 = ? and field2 = ?) or (field1 = ? and field2 = ?) .. this way you will check for multiple values at once and that means less selects to execute on the database. If you do your query on single field then you can use IN instead of = Check also you have proper index setup on the tables. Usually execution that is IO heavy does not get much better performance by just changing the hardware. If you move from HDD to SSD this can speed up much more but HDD performance is not very different in the last 10 years. Also another thing to note is that for DB scenarios I prefer to use Read Caching and no Write caching. This gives me better guarantee that I will not end with broken database in case of power failure. Have a nice day. -- Doychin Bondzhev dSoft-Bulgaria Ltd. PowerPro - billing provisioning solution for Service providers PowerStor - Warehouse POS http://www.dsoft-bg.com/ Mobile: +359888243116 [Non-text portions of this message have been removed]
Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Do not change to a SSD! Corruption will occur. Em 27/09/2014 11:16, Doychin Bondzhev doyc...@dsoft-bg.com [firebird-support] firebird-support@yahoogroups.com escreveu: Hi Costantino, I did some experimenting before one year and I found that Firebird is much faster when you use page size = cluster size on the file system. So if your file system is with 4K cluster I suggest to use page size of 4K. This is very helpful when you have Forced Write = ON. Performance gain with insert only scenario is more then 10-15% from 16K page on Windows 7 with RAID 10. another thing to look for is to try to minimize the number of transactions you create. Try to put as many as possible statements into single transaction. So for this check do you use autocommit on every statement or you wrap all statements executed while processing single file in one transaction. Also when you process your lines in the input file try to group as many as possible selects into single select. for example: select field1, filed2, filed3, field4 from table1 where field1 = ? and field2 = ? into : select field1, filed2, filed3, field4 from table1 where (field1 = ? and field2 = ?) or (field1 = ? and field2 = ?) or (field1 = ? and field2 = ?) .. this way you will check for multiple values at once and that means less selects to execute on the database. If you do your query on single field then you can use IN instead of = Check also you have proper index setup on the tables. Usually execution that is IO heavy does not get much better performance by just changing the hardware. If you move from HDD to SSD this can speed up much more but HDD performance is not very different in the last 10 years. Also another thing to note is that for DB scenarios I prefer to use Read Caching and no Write caching. This gives me better guarantee that I will not end with broken database in case of power failure. Have a nice day. -- Doychin Bondzhev dSoft-Bulgaria Ltd. PowerPro - billing provisioning solution for Service providers PowerStor - Warehouse POS http://www.dsoft-bg.com/ Mobile: +359888243116 [Non-text portions of this message have been removed]
[firebird-support] page type N lock denied(216) or lock conversion denied(215)
Is bug http://tracker.firebirdsql.org/browse/CORE-2848 http://tracker.firebirdsql.org/browse/CORE-2848 fixed in FB 2.5.3? We have had this bug in two FB 2.5.2 classic server installs and it still appears sometimes after updating to 2.5.3. Page number in error message indicates that page belong to tables with moderate UPDATE load (30-60 updates per second, each record is updated in 10-30 seconds). All updates performed by one connection and updating transaction is committed and started anew each 15 seconds. Rate of error had grown significally after deployment of new application server which uses JDBC (Jaybird) Firebird connection and constantly (each 10-15 seconds) executes SELECT queries, including above-mentioned tables. Both servers use Windows x64 (2008 and 2012) and 32-bit FB builds (32-bit udfs are used, so we cannot switch to 64-bit FB for now).
Odp: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Hi, If you use default fb config then i suppose your cache settings is very very small. Change DefaultDbCachePages to value greater then your db size in pages. Then restart server and run test again. You should have fastest response time. I recommend you also 16k db page size for better index support (smaller index deep) Regards, Karol Bieniaszewski - Reply message - Od: apos;Costantino Molinariapos; c.molin...@tiscali.it [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2 Data: pt., wrz 26, 2014 12:44 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 3754W512K2787R 2756W4K109R 106W4K QD32 440W 338W The problem is that i see long time of execution in read/write to a very little FB database (about 100 MB, it’s a new one, just for tests before production)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 thi nk I can really obtain better performances.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. Thank in advance to anyone who will try to help me.Costantino
