[sqlite] Very poor SQLite performance when using Win8.1 + Intel RAID1
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 msecSystem 2: 1 - 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
[sqlite] Problem: you can delete a virtual table with open statements and then SEGV
On 3/24/15, Roger Binns wrote: > On 03/23/2015 04:33 PM, Roger Binns wrote: >> SQLite really should prevent it from happening by disallowing the >> deletion of virtual tables with open statements. > > Thanks for fixing this. I can confirm that the latest SQLite dev code > now gives the expected database table is locked error with the prior > test code. Tnx for pointing out the problem and confirming the fix. -- D. Richard Hipp drh at sqlite.org
[sqlite] Very poor SQLite performance when using Win8.1 + Intel RAID1
Seems about right to me. If I do 1000 inserts on an SQLite database backed on
spinning disk with a 32GB Flash Cache it takes 3 seconds. I would expect that
(assuming your SSD are using a Connor interface) you would see at least twice
that time used for RAID 1.
Is your new Intel RAID driver the IRST? If so, you can configure it to
"dangerous" mode under the performance options so that it will lie to you about
disk flushes just like the driver on XP did.
You may also need to look under the devmgmt.msc options for the volume -- the
IRST driver claims the device cache and "turn off windows control of cache
flushing" is located in the IRST management app, but it isn't (since it is
correct, I have no volatile volumes) -- those options are set against the
volume as they were in the old days -- directly at the windows volume level).
In my case, setting the "enable device cache" means that writes (from the
windows volume cache) return complete when the write to flash is complete but
before the data is committed to spinning disk, and the "turn off windows
write-cache flushing" means that forced-flush operations do not force the flash
to write to spinning disk (or, on a normal cached disk it would disable forcing
a flush from the drive RAM cache to the media), since the data is already
preserved in the non-volatile cache and will be preserved in case of a power
failure.
I don't know what sort of device cache your devices have, or whether it is
volatile or not. You would have to check with the manufacturer to see how
these settings (assuming they are available) affect the actual writing to the
non-volatile storage.
---
Theory is when you know everything but nothing works. Practice is when
everything works but no one knows why. Sometimes theory and practice are
combined: nothing works and no one knows why.
>-Original Message-
>From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>bounces at mailinglists.sqlite.org] On Behalf Of Rob van der Stel
>Sent: Tuesday, 24 March, 2015 09: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 msecSystem 2: 1 - 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
[sqlite] Problem: you can delete a virtual table with open statements and then SEGV
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/23/2015 04:33 PM, Roger Binns wrote: > SQLite really should prevent it from happening by disallowing the > deletion of virtual tables with open statements. Thanks for fixing this. I can confirm that the latest SQLite dev code now gives the expected database table is locked error with the prior test code. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlUR/w4ACgkQmOOfHg372QTFegCff/6Vl7NrJPRrFuJNgB8SLWk4 ol0AoJ9qYTMuP7xhEMyKkEYt4euC/CHj =awey -END PGP SIGNATURE-
[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 msecSystem 2: 1 - 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] Very poor SQLite performance when using Win8.1 + Intel RAID1
On 24 Mar 2015, at 3:43pm, Rob van der Stel wrote:
> Samsung 128 GB SSD
Are those Samsung 830s ? Those things are insanely fast.
FlushFileBuffers() does all the hard work in disk access. Other calls may end
up changing only cached information whereas FlushFileBuffers() has to go mess
with the real storage device. If your device driver is properly configured
then that one call can end up doing a whole lot of work and a lot of device
access. Given your times ...
> --- SQLite 1000 x append in new database *)
> System 1: 1000 - 1100 msec
> System 2: 1 - 11000 msec
> --- Binary file 1000 x {append + FlushFileBuffers} *)
> System 1: 220 - 230 msec
> System 2: 2600 - 2700 msec
1000 append transactions in SQLite can amount to 6000 simple file operations
(assuming a normal journaling mode and no dodgy PRAGMAs). The difference in
the figures you show here doesn't look too out of place: in both cases SQLite
is about 4 times binary.
Looking at the above numbers I am wondering whether the System 1 device driver
and OS was actually giving you true ACID operations, with write-in-order and
write barriers enforced correctly. System 1 figures do seem to be very low,
whereas the System 2 figures seem to be about what I'd expect for a fast SSD.
Simon.
[sqlite] Very poor SQLite performance when using Win8.1 + Intel RAID1
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. I don't know anything about SQLite, but faced with what you're seeing I think I'd want to be sure how much of the problem is SQLite... Have you done any I/O benchmarks using other tools? Have you checked if there's known problems with the SSDs and/or RAID drivers you're using? >The following information is obtained to comparing two hardware identical systems one running Win XP the other running Win 8.1. Is W8.1 expected to run as well as XP on similar hardware, or does it need (for example) much more RAM? If you install a RAM disk on each system and place your SQLite test database on that, do you see the same speed problems? -- Jeremy C B Nicoll - my opinions are my own.
[sqlite] Query times vary between 0.2 s and 30 s for very
The temporary table is creates as CREATE TEMPORARY TABLE _tempNNN (oid INTEGER PRIMARY KEY) So the optimizer must know that the values are unique.
