|Subject||Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2|
Constantino, how many simultaneous users you have?
One thing that boosts database writes on large tables (tables not databases) is changing database buffers.
Try increase to 20000, restart your server or close and open all connections and try again.
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 intelrullz@... [firebird-support]" <email@example.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 fabianoaspro@... [firebird-support] <firstname.lastname@example.org>:
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 louis@... [firebird-support]" <email@example.com> escreveu:Why will corruption occur?
Sent from my iPad
On 27 Sep 2014, at 19:03, "fabianoaspro@... [firebird-support]" <firstname.lastname@example.org> wrote:
Do not change to a SSD! Corruption will occur.Em 27/09/2014 11:16, "Doychin Bondzhev doychin@... [firebird-support]" <email@example.com> escreveu:
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.
select field1, filed2, filed3, field4 from table1 where field1 = ? and
field2 = ?
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
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.
PowerPro - billing & provisioning solution for Service providers
PowerStor - Warehouse & POS
[Non-text portions of this message have been removed]