On 2015-03-24 05:43 PM, Rob van der Stel wrote:
> 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.
I might simply be obtuse, but is the second test based upon anything
regarding SQLite at all? If it is, how? If it isn't, then you have a
clear indicator that normal file writes have slowed down significantly,
so SQLite will simply be an also-affected, not an anomaly.
That said, I'm with Simon, having done a quick and dirty test writing
106 random bytes to a file 1000 times flushing buffers in between (so
1000 flushes) I get a good 1500ms on my PCI-E M.2 drive that usually
shifts >1GB per sec, and around 2200 ms on the 520MB/s SATA SSD all
using Win 8.1 but not in RAID 1.
These figures can be understood from repeated flushing overhead and
isn't surprising (again, unless I'm misunderstanding your tests).
Conclusion is that I think your Windows XP+Driver+Setup combination lied
to you somewhere and it wasn't actually committing all of those writes
into non-volatile NAND gates before returning - aka not safe (though it
would have taken a miraculously well-placed power failure to actually
break it, something that probably by luck never happened).
> 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.
Ok, but are you allowed to use WAL mode which will offer some
significant performance improvements (especially with lots of writes),
remain ACID and is very safe?
https://www.sqlite.org/wal.html
Regards,
Ryan
