Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Hi, i test this on virtual machine with Windows 2008R2 and Xeon E5-2680 v2 and result was: Executing statement... Statement executed (elapsed time: 0.000s). 10899160 fetches, 1051082 marks, 0 reads, 69 writes. 4 inserts, 950001 updates, 0 deletes, 950016 index, 2 seq. Delta memory: 82968 bytes. TEST: 4 inserts. 950001 updates. Total execution time: 9.548s Script execution finished. Executing statement... Statement executed (elapsed time: 0.000s). 10899152 fetches, 1051082 marks, 0 reads, 69 writes. 4 inserts, 950001 updates, 0 deletes, 950016 index, 4 seq. Delta memory: 53512 bytes. TEST2: 4 inserts. 950001 updates. Total execution time: 9.297s Script execution finished. Commiting transaction... Transaction committed (elapsed time: 0.109s). I do not know if this is because good CPU - or somethink else in raid config I do not know exactly what is your configuration for this test i put DefaultDbCachePages = 65536 in Firebird config and DB 16KB pagesize Firebird 2.5.3 64 bit regards, Karol Bieniaszewski W dniu 2014-09-29 14:35:21 użytkownik 'Costantino Molinari' c.molin...@tiscali.it [firebird-support] firebird-support@yahoogroups.com napisał: To Karol: Original page size was 16K, than I changed page size to 4K just as my NTFS cluster size and time incredibly doubled !! Really confused, but maybe it has to do with the 64K stripe size of RAID 10…. To Set: TERM SET^ suggestion worked fine. Time of execution is 11.0 seconds average (tried 3 different executions, every time with empty table) Don’t know what is the difference between my system (2 x 12Core Xeon Proc./32GB DDR3 ram/6x1.2TB 15K SAS RAID 10/1GB Controller Cache) and yours (16.5 secs), but mine took less time as expected. Maybe not too less as I have whised, but now I’m going to change some settings in firebird.conf and retry your simple but very useful test. Thanks a lot I’ll let everyone know if I have news Costa Da: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Inviato: lunedì 29 settembre 2014 13:48 A: firebird-support@yahoogroups.com Oggetto: Re: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2 Obviously the logic of my application is not the best, but it needs to pass through a special interface, that transforms classic cobol read/write routines, in sql statements. This brings to the need to have a single commit after a single insert command of a single record. I know this is not good, but knowing this and knowing that I cannot change this, I only want to know if I can get the best for my Firebird Installation of 2.5.3 SuperServer onto Windows Server 2012 R2 x64. OK, commit after every single insert will slow things down considerably and may be the reason for your time trouble (it is similar to having trouble with a car being slower than a bike, and the reason being that you have an additional requirement to start and stop every 5 meters). I'm not certain (because I've never had a similar requirement and know very little about CommitRetaining), but maybe changing from Commit to CommitRetaining might make it possible for a prepared statement to survive the CommitRetaining (?) and then only do a proper Commit occationally. It could at least be worth a try if feasible. I'm trying several settings, some others have suggested in this thread. About your test (thanks for that), I have created the table, than copied and pasted the SQL loop, but I get this error: Preparing statement: execute block returns (i integer) as declare variable i2 integer Error: *** IBPP::SQLException *** Context: Statement::Prepare( execute block returns (i integer) as declare variable i2 integer ) Unexpected end of command - line 2, column 21 This is probably due to IBPP believing the ; to be the end of the statement. Try to 'SET TERM ^^ ;' before execute block, change the final end of the execute block to end^^ and then do 'SET TERM ; ^^' at the end. Sorry for not being able to help more, Set ~-|**|PrettyHtmlStart|**|-~
R: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Hi Karol, My DB Page Size is 16384 Page Buffers 2 Test 1 with firebird.conf as default (no changes at all from installation) Executing statement... Statement executed (elapsed time: 0.000s). 11328970 fetches, 1192039 marks, 0 reads, 214 writes. 4 inserts, 950001 updates, 0 deletes, 950004 index, 112 seq. Delta memory: 51432 bytes. TEST: 4 inserts. 950001 updates. Total execution time: 11.947s Script execution finished. Test 2 with DefaultDbCachePages = 65536 in Firebird config Executing statement... Statement executed (elapsed time: 0.000s). 10902466 fetches, 1051080 marks, 17 reads, 69 writes. 4 inserts, 950001 updates, 0 deletes, 950021 index, 112 seq. Delta memory: 115040 bytes. TEST: 4 inserts. 950001 updates. Total execution time: 11.581s Script execution finished. As you can see, no changes in time of execution, but I think that is a good time compared to yours What are your raid and controller settings? My Processor is Intel Xeon E5-2630v2 6C/12T 2.60Ghz 15MB Raid Controller LSI Megaraid 6G 1GB ram – 6 x 1.2TB SAS 10K configured as RAID 10 Da: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Inviato: mercoledì 1 ottobre 2014 11:29 A: firebird-support@yahoogroups.com Oggetto: Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2 Hi, i test this on virtual machine with Windows 2008R2 and Xeon E5-2680 v2 and result was: Executing statement... Statement executed (elapsed time: 0.000s). 10899160 fetches, 1051082 marks, 0 reads, 69 writes. 4 inserts, 950001 updates, 0 deletes, 950016 index, 2 seq. Delta memory: 82968 bytes. TEST: 4 inserts. 950001 updates. Total execution time: 9.548s Script execution finished. Executing statement... Statement executed (elapsed time: 0.000s). 10899152 fetches, 1051082 marks, 0 reads, 69 writes. 4 inserts, 950001 updates, 0 deletes, 950016 index, 4 seq. Delta memory: 53512 bytes. TEST2: 4 inserts. 950001 updates. Total execution time: 9.297s Script execution finished. Commiting transaction... Transaction committed (elapsed time: 0.109s). I do not know if this is because good CPU - or somethink else in raid config I do not know exactly what is your configuration for this test i put DefaultDbCachePages = 65536 in Firebird config and DB 16KB pagesize Firebird 2.5.3 64 bit regards, Karol Bieniaszewski W dniu 2014-09-29 14:35:21 użytkownik 'Costantino Molinari' c.molin...@tiscali.it [firebird-support] firebird-support@yahoogroups.com napisał: To Karol: Original page size was 16K, than I changed page size to 4K just as my NTFS cluster size and time incredibly doubled !! Really confused, but maybe it has to do with the 64K stripe size of RAID 10…. To Set: TERM SET^ suggestion worked fine. Time of execution is 11.0 seconds average (tried 3 different executions, every time with empty table) Don’t know what is the difference between my system (2 x 12Core Xeon Proc./32GB DDR3 ram/6x1.2TB 15K SAS RAID 10/1GB Controller Cache) and yours (16.5 secs), but mine took less time as expected. Maybe not too less as I have whised, but now I’m going to change some settings in firebird.conf and retry your simple but very useful test. Thanks a lot I’ll let everyone know if I have news Costa Da: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Inviato: lunedì 29 settembre 2014 13:48 A: firebird-support@yahoogroups.com Oggetto: Re: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2 Obviously the logic of my application is not the best, but it needs to pass through a special interface, that transforms classic cobol read/write routines, in sql statements. This brings to the need to have a single commit after a single insert command of a single record. I know this is not good, but knowing this and knowing that I cannot change this, I only want to know if I can get the best for my Firebird Installation of 2.5.3 SuperServer onto Windows Server 2012 R2 x64. OK, commit after every single insert will slow things down considerably and may be the reason for your time trouble (it is similar to having trouble with a car being slower than a bike, and the reason being that you have an additional requirement to start and stop every 5 meters). I'm not certain (because I've never had a similar requirement and know very little about CommitRetaining), but maybe changing from Commit to CommitRetaining might make it possible for a prepared statement to survive the CommitRetaining (?) and then only do a proper Commit occationally. It could at least be worth a try if feasible. I'm trying several settings, some others have suggested in this thread. About your test (thanks for that), I have created the table, than copied and pasted the SQL loop, but I get
R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Obviously the logic of my application is not the best, but it needs to pass through a special interface, that transforms classic cobol read/write routines, in sql statements. This brings to the need to have a single commit after a single insert command of a single record. I know this is not good, but knowing this and knowing that I cannot change this, I only want to know if I can get the best for my Firebird Installation of 2.5.3 SuperServer onto Windows Server 2012 R2 x64. I'm trying several settings, some others have suggested in this thread. About your test (thanks for that), I have created the table, than copied and pasted the SQL loop, but I get this error: Starting transaction... Preparing statement: SELECT r.ID, r.MYINT FROM TEST r Statement prepared (elapsed time: 0.001s). Field #01: TEST.ID Alias:ID Type:INTEGER Field #02: TEST.MYINT Alias:MYINT Type:INTEGER PLAN (R NATURAL) Executing statement... Statement executed (elapsed time: 0.000s). 47 fetches, 0 marks, 0 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 11 index, 0 seq. Delta memory: 12064 bytes. Total execution time: 0.021s Script execution finished. Preparing statement: execute block returns (i integer) as declare variable i2 integer Error: *** IBPP::SQLException *** Context: Statement::Prepare( execute block returns (i integer) as declare variable i2 integer ) Message: isc_dsql_prepare failed SQL Message : -104 Invalid token Engine Code: 335544569 Engine Message : Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 2, column 21 -Messaggio originale- Da: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Inviato: domenica 28 settembre 2014 16:54 A: firebird-support@yahoogroups.com Oggetto: Re: [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) I'm not surprised, I think Firebird SuperServer will only use one core. I created a table: CREATE TABLE TEST ( ID INTEGER NOT NULL, MYINT INTEGER, CONSTRAINT PK_TEST PRIMARY KEY (ID) --for this test an important primary key ); Then I executed the following query on the empty table: execute block returns (i integer) as declare variable i2 integer; begin i = 0; i2 = 0; while (i 100) do begin i = i+1; i2 = i2+i; while (i2 10) do i2 = i2 - 1; update or insert into test(id, myint) values (:i2, :i); end suspend; end 16.5 seconds later the query had looped and inserted or updated 1 millon times, 4 rows where inserted, the rest of the times things were updated. Maybe you should add half a second for the commit afterwards. This on a computer that is a few years old and nothing special. The database is Firebird 2.5, don't remember whether it is 2.5.1 or 2.5.2. Now, this is very different from your import from a text file. Still, I hope it is enough to show that 9 minutes to check and insert 35000 records is more than what is normally neccessary. You could either continue to try to improve the environment and maybe get the import to finish within 5 or 7 minutes after some further optimization. Or you could try to discover and fix the real problem through telling us more about what is actually going on (what does your SQL look like, what PLANs are used, which indexes are used, do you use prepared statements or create 35000 separate statements and how many transactions?) and hopefully get the import to finish in less than one minute. HTH, Set ++ Visit
R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
To Karol: Original page size was 16K, than I changed page size to 4K just as my NTFS cluster size and time incredibly doubled !! Really confused, but maybe it has to do with the 64K stripe size of RAID 10 . To Set: TERM SET^ suggestion worked fine. Time of execution is 11.0 seconds average (tried 3 different executions, every time with empty table) Dont know what is the difference between my system (2 x 12Core Xeon Proc./32GB DDR3 ram/6x1.2TB 15K SAS RAID 10/1GB Controller Cache) and yours (16.5 secs), but mine took less time as expected. Maybe not too less as I have whised, but now Im going to change some settings in firebird.conf and retry your simple but very useful test. Thanks a lot Ill let everyone know if I have news Costa Da: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Inviato: lunedì 29 settembre 2014 13:48 A: firebird-support@yahoogroups.com Oggetto: Re: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2 Obviously the logic of my application is not the best, but it needs to pass through a special interface, that transforms classic cobol read/write routines, in sql statements. This brings to the need to have a single commit after a single insert command of a single record. I know this is not good, but knowing this and knowing that I cannot change this, I only want to know if I can get the best for my Firebird Installation of 2.5.3 SuperServer onto Windows Server 2012 R2 x64. OK, commit after every single insert will slow things down considerably and may be the reason for your time trouble (it is similar to having trouble with a car being slower than a bike, and the reason being that you have an additional requirement to start and stop every 5 meters). I'm not certain (because I've never had a similar requirement and know very little about CommitRetaining), but maybe changing from Commit to CommitRetaining might make it possible for a prepared statement to survive the CommitRetaining (?) and then only do a proper Commit occationally. It could at least be worth a try if feasible. I'm trying several settings, some others have suggested in this thread. About your test (thanks for that), I have created the table, than copied and pasted the SQL loop, but I get this error: Preparing statement: execute block returns (i integer) as declare variable i2 integer Error: *** IBPP::SQLException *** Context: Statement::Prepare( execute block returns (i integer) as declare variable i2 integer ) Unexpected end of command - line 2, column 21 This is probably due to IBPP believing the ; to be the end of the statement. Try to 'SET TERM ^^ ;' before execute block, change the final end of the execute block to end^^ and then do 'SET TERM ; ^^' at the end. Sorry for not being able to help more, Set
Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Guten Tag 'Costantino Molinari' c.molin...@tiscali.it [firebird-support], To Karol: Original page size was 16K, than I changed page size to 4K just as my NTFS cluster size and time incredibly doubled !! Really confused, but maybe it has to do with the 64K stripe size of RAID 10…. Not surprisiing at all, Stripe Set is the amount of Data a RAID moves for every read/write so if you have 16 KB or 4KB you still move 64KB each time but with much less usable Payload per transfer go for a Stripsetsize of 16 KB and a Pagesize of 16KB for best match -- Mit freundlichen Grüssen Hannes Streichermailto:hstreic...@gmx.de ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Costantino Mi recomendacion para realizar insert de gran cantidad de datos, es utilizar tablas externas. Estas son extremadamente rapidas.Para esto 1 tenes que definir un archivo de texto con los datos. 2 crear la tabla externa en firebird 3 realizar el insert a la tabla definitiba de los registros de tu interes Saludos Google translate My recommendation for insert large amount of data, is to use external tables. These are extremely fast. for this One got to define a text file with the data. 2 create the external table in firebird 3 to perform the insert table definitiba records of your interest El Lunes, 29 de septiembre, 2014 9:52:42, Hannes Streicher hstreic...@gmx.de [firebird-support] firebird-support@yahoogroups.com escribió: Guten Tag 'Costantino Molinari' c.molin...@tiscali.it [firebird-support], To Karol: Original page size was 16K, than I changed page size to 4K just as my NTFS cluster size and time incredibly doubled !! Really confused, but maybe it has to do with the 64K stripe size of RAID 10…. Not surprisiing at all, Stripe Set is the amount of Data a RAID moves for every read/write so if you have 16 KB or 4KB you still move 64KB each time but with much less usable Payload per transfer go for a Stripsetsize of 16 KB and a Pagesize of 16KB for best match -- Mit freundlichen Grüssen Hannes Streicher mailto:hstreic...@gmx.de ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links
R: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Dear Hannes, good suggestion, but changing the stripe size is not for me now, if I don't want to install all again, if I'll be able to obtain an acceptable speed with other settings. -Messaggio originale- Da: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Inviato: lunedì 29 settembre 2014 14:52 A: 'Costantino Molinari' c.molin...@tiscali.it [firebird-support] Oggetto: Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2 Guten Tag 'Costantino Molinari' c.molin...@tiscali.it [firebird-support], To Karol: Original page size was 16K, than I changed page size to 4K just as my NTFS cluster size and time incredibly doubled !! Really confused, but maybe it has to do with the 64K stripe size of RAID 10 . Not surprisiing at all, Stripe Set is the amount of Data a RAID moves for every read/write so if you have 16 KB or 4KB you still move 64KB each time but with much less usable Payload per transfer go for a Stripsetsize of 16 KB and a Pagesize of 16KB for best match -- Mit freundlichen Grüssen Hannes Streichermailto:hstreic...@gmx.de ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links
Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Why will corruption occur? Sent from my iPad On 27 Sep 2014, at 19:03, fabianoas...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: 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]
Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Number of guaranteed writes is much lower on SSD. when FB tries to write some write operations will fail and database will be corrupted. Flash disks as pen drives and memory cards also. Em 28/09/2014 04:53, Louis van Alphen lo...@nucleo.co.za [firebird-support] firebird-support@yahoogroups.com escreveu: Why will corruption occur? Sent from my iPad On 27 Sep 2014, at 19:03, fabianoas...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: 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]
Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
I`ve been using SSDs for quite some time - had major issues with a Kingston model a while back, so I`ve kept away from them. Most my servers are with Corsair Force 3 drives and doing fine (they are UPS protected, so power outages don`t happen all that often) - haven`t had a corruption during normal operation (meaning no firebird errors at runtime and no improper shutdowns during heavy I/O). I also have a couple of Samsung 830 drives and Intel 330 - yes, they aren`t really the freshest of models but they are proof you can be reasonably safe with SSDs. Even when I do get a corrupted page or two, I haven`t had data loss - a b/r cycle and everything goes back to normal. PS Of course, regular backups on a classic HDD are something you should never ever consider skipping :) 2014-09-28 13:49 GMT+03:00 fabianoas...@gmail.com [firebird-support] firebird-support@yahoogroups.com: Number of guaranteed writes is much lower on SSD. when FB tries to write some write operations will fail and database will be corrupted. Flash disks as pen drives and memory cards also. Em 28/09/2014 04:53, Louis van Alphen lo...@nucleo.co.za [firebird-support] firebird-support@yahoogroups.com escreveu: Why will corruption occur? Sent from my iPad On 27 Sep 2014, at 19:03, fabianoas...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: 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]
Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
We just deployed a new server RAID10 with 8 SSDs and want to make sure we dont run into issues. I have scheduled a gbak every night, but that needs to be to copied to other storage Sent from my iPad On 28 Sep 2014, at 12:57, Ivan Arabadzhiev intelru...@yahoo.com [firebird-support] firebird-support@yahoogroups.com wrote: I`ve been using SSDs for quite some time - had major issues with a Kingston model a while back, so I`ve kept away from them. Most my servers are with Corsair Force 3 drives and doing fine (they are UPS protected, so power outages don`t happen all that often) - haven`t had a corruption during normal operation (meaning no firebird errors at runtime and no improper shutdowns during heavy I/O). I also have a couple of Samsung 830 drives and Intel 330 - yes, they aren`t really the freshest of models but they are proof you can be reasonably safe with SSDs. Even when I do get a corrupted page or two, I haven`t had data loss - a b/r cycle and everything goes back to normal. PS Of course, regular backups on a classic HDD are something you should never ever consider skipping :) 2014-09-28 13:49 GMT+03:00 fabianoas...@gmail.com [firebird-support] firebird-support@yahoogroups.com: Number of guaranteed writes is much lower on SSD. when FB tries to write some write operations will fail and database will be corrupted. Flash disks as pen drives and memory cards also. Em 28/09/2014 04:53, Louis van Alphen lo...@nucleo.co.za [firebird-support] firebird-support@yahoogroups.com escreveu: Why will corruption occur? Sent from my iPad On 27 Sep 2014, at 19:03, fabianoas...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: 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]
RE: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Fabiano, Number of guaranteed writes is much lower on SSD. **Not true** for all SSD/flash based devices. Certainly true for low end/consumer Flash/SATA devices. when FB tries to write some write operations will fail and database will be corrupted. Again, not true, as a broad statement. **Enterprise SSD devices** with power lost protection, are designed to provide reliability beyond anything which almost any application can throw at it. ( (protection is key, most SSDs have on-board flash RAM disk write buffer -- without they can lose writes == database corruption). Drives like the Intel DC S3700 series provide 75,000 read and 36,000 write 4K IOPS, and have endurance for 10 *full disk* writes every day for 5 years. So, for a 200GB drive, you would need to *write* 1TB every day for 5 years, before endurance became an issue. Further, unlike consumer devices, enterprise SSDs have controllers which are designed to provide consistent responsiveness, they sacrifice raw/benchmark performance for consistency, since that it what enterprise applications need. Sean
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]
