Hello, I received several useful remarks and suggestions upon my post regarding the SQLite performance with Win8.1 + Intel RAID1. Especially the reminder that my fast System 1 (WinXP) was probably too fast for actual ACID made me look further into several cache options that windows and the RAID controller driver provided. It now appears that it all boils down to a misunderstanding of the DevMgtm.msc: Volume properties 'policies tab'. The naming in WinXP and Win81 is such that a mistake is easily made, the corresponding options are namely:
a1) WinXP: 'Optimize for for performance' -- this corresponds to a2) Win81: 'Enable write cache on the device' b1) WinXP: 'Enable write cache on the disk' -- this corresponds to b2) Win81: 'Turn off Windows write-cache buffer flushing on the device' I wrongfully thought that b1 and a2 represented the same option. In my System 1 WinXP both options were 'checked' (a1 & b1). In my System 2 Win81 only option 'a2' was checked, assuming that it resembled 'b1' and that option 'b2' was a new feature in Win81. By checking b2 'on' the performance of the Win81 system improved significantly to even slightly better numbers than the WinXP system. I am aware that this also means that my initial interpretation of the data integrity retention level was not correct. When both systems need to be fast then 'automatic write-cache flushing' must be turned-off (even though the policy naming is different in WinXP and Win81). I have to re-evaluate whether this is the way that our systems can operate safely, taking into account that a UPS is always used in the systems' configuration. One open issue for me remains however. Can I assume that the FlushFileBuffers API that SQLite uses still causes cache to be written to disk even though the 'automatic write-cache flushing' of Windows is turned off ? Thanks to everyone for their useful remarks and suggestions. Regards, Rob -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Rob van der Stel Sent: dinsdag 24 maart 2015 16:44 To: sqlite-users at mailinglists.sqlite.org Subject: [sqlite] Very poor SQLite performance when using Win8.1 + Intel RAID1 Hello, Currently I am investigating a SQLite performance problem that started to occur when we switched from using Windows XP to Windows 8.1 for our applications. The following information is obtained to comparing two hardware identical systems one running Win XP the other running Win 8.1. *** HW specification System 1 and System 2 --- HP RP5 Retail PC 5810 --- Intel Celeron G1820 at 2.7 GHz --- 4096 MB DDR3 / 1333 MHz --- Intel ICH8R onboard RAID1 with 2 x Samsung 128 GB SSD *** SW specification System 1 --- Windows XP --- Intel RAID driver 9.5.0.1037 02/10/2009 *** SW specification System 2 --- Windows 8.1 --- Intel RAID driver 12.5.0.1066 18/03/2013 =>> When running our applications the performance of SQLite write-access on System2 (Win 8.1) is at least 10 times slower than on System1 (Win XP). =>> Investigations have already shown that the Windows API FlushFileBuffers() plays an important part regarding this issue. The following describes a few comparison tests --- SQLite 1000 x append in new database *) System 1: 1000 - 1100 msec System 2: 10000 - 11000 msec --- Binary file 1000 x {append + FlushFileBuffers} *) System 1: 220 - 230 msec System 2: 2600 - 2700 msec *) The data size of each write is comparable (106 bytes). Final file sizes also match. Has anyone noticed such a dramatic SQLite performance deterioration under comparable circumstances? If so were you able to find the root cause of it and tackle the problem by making improvements such that both systems perform equally well again (other driver software ? other driver parameters ? ...) It is important that we retain the data integrity provisions that are required for our system (RAID1 and SQLite using FlushFileBuffers). Using the "SQLITE_NO_SYNC compile option" or the "PRAGMA synchronous = 0" is therefore no alternative. Regards, Rob This e-mail and any attachments contain material that is confidential for the sole use of the intended recipient. Any review, reliance or distribution by others or forwarding without express permission is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. This message has been scanned for malware by Websense. www.websense.com _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This e-mail and any attachments contain material that is confidential for the sole use of the intended recipient. Any review, reliance or distribution by others or forwarding without express permission is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies.
