| Subject | R: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2 |
|---|---|
| Author | Costantino Molinari |
| Post date | 2014-10-01T12:32:44Z |
Hi Karol,
My DB Page Size is 16384
Page Buffers 20000
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.
49999 inserts, 950001 updates, 0 deletes, 950004 index, 112 seq.
Delta memory: 51432 bytes.
TEST: 49999 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.
49999 inserts, 950001 updates, 0 deletes, 950021 index, 112 seq.
Delta memory: 115040 bytes.
TEST: 49999 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.
49999 inserts, 950001 updates, 0 deletes, 950016 index, 2 seq.
Delta memory: 82968 bytes.
TEST: 49999 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.
49999 inserts, 950001 updates, 0 deletes, 950016 index, 4 seq.
Delta memory: 53512 bytes.
TEST2: 49999 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.molinari@... [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|**|-~