Have you checked the page/block/record size of the file systems and RAID
systems, vs sqlite's page_size (which you can check or set with the
relevant pragma: https://www.sqlite.org/pragma.html)?
If sqlite has a smaller or different page size than the underlying storage,
a single sqlite write can result in a disk read, modify and write. That'll
cost you a lot of resources, and I wouldn't be surprised if that showed up
in FlushFileBuffers() .
Cheers, Ketil
On 24 Mar 2015 16:44, "Rob van der Stel" <RvanderStel at benelux.tokheim.com>
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.
>
> 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
>
