Dinsmore, Jeff
2005-11-16 14:49:10 UTC
I have a little engine doing HL7 messaging using SQLite3 from Tcl
(Windows Server 2003 with a local attached RAID 5 for database).
I'm looking for some speed improvements, so yesterday I experimented
with PRAGMA synchronous = OFF. Holy Cats - it's WAAAY faster - like
night and day.
So, I'd like to use synchronous = OFF, but I'd like to understand a
little more about the possible side effects.
I understand that if the OS crashes or if I lose power to the server
it's possible that the SQLite database could be corrupted assuming that
the failure happens at a time after SQLite has handed off a database
write to the OS but before the OS has committed the write to disk.
My server is UPS'd and the server has dual power supplies, so power
should be pretty stable. The Windows OS also seems to be pretty stable
and the RAID has battery protected cache. So I'm fairly comfortable with
my OS, hardware and power reliability.
The Big Question: What evidence will I see from SQLite that a database
file is corrupt? Will it fail to open and return an error to that
effect? Or, will it (possibly) open the database and then fail somewhere
down the line when it hits the corrupted area of the database?
I have never experienced any SQLite database corruption. Does anyone
have a feel for how common database corruption is in the real world?
Thanks,
Jeff Dinsmore
IT - Interfaces
Ridgeview Medical Center
jeff.dinsmore-a/B9otNJ57NCiBvDG4KOnGD2FQJk+8+***@public.gmane.org
952.442.2191 x6592
Ridgeview Medical Center Confidentiality Notice: This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
(Windows Server 2003 with a local attached RAID 5 for database).
I'm looking for some speed improvements, so yesterday I experimented
with PRAGMA synchronous = OFF. Holy Cats - it's WAAAY faster - like
night and day.
So, I'd like to use synchronous = OFF, but I'd like to understand a
little more about the possible side effects.
I understand that if the OS crashes or if I lose power to the server
it's possible that the SQLite database could be corrupted assuming that
the failure happens at a time after SQLite has handed off a database
write to the OS but before the OS has committed the write to disk.
My server is UPS'd and the server has dual power supplies, so power
should be pretty stable. The Windows OS also seems to be pretty stable
and the RAID has battery protected cache. So I'm fairly comfortable with
my OS, hardware and power reliability.
The Big Question: What evidence will I see from SQLite that a database
file is corrupt? Will it fail to open and return an error to that
effect? Or, will it (possibly) open the database and then fail somewhere
down the line when it hits the corrupted area of the database?
I have never experienced any SQLite database corruption. Does anyone
have a feel for how common database corruption is in the real world?
Thanks,
Jeff Dinsmore
IT - Interfaces
Ridgeview Medical Center
jeff.dinsmore-a/B9otNJ57NCiBvDG4KOnGD2FQJk+8+***@public.gmane.org
952.442.2191 x6592
Ridgeview Medical Center Confidentiality Notice: This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.