Re: [libsql] sqlite 80x faster on transactions

Hi,

I should have read sqlite's documentation better. Setting pragma=20
synchronous to OFF does about the same (and thus speeding up=20
transactions dramastically). Sorry for any confusion.

Rene



Ren=E9 Tegel wrote:

> Hi,
>
> Inspired by cross-platform usage of sqlite vs transaction and=20
> threading issues, i've been looking why sqlite is so slow with=20
> transactions on my system (XPsp2, amd2500+, 250GB HD, NTFS). A=20
> transaction takes about 80ms on my system, with lot of disk head=20
> movements involved.
> Since i need sqlite especially to be fast for one of my projects=20
> (speed is more important than possible data loss in case of an=20
> operating system crash) i've been investigating how to speed up=20
> sqlite. To accomplish that goal, i've recompiled sqlite3.dll and added=20
> an extra pragma to sqlite that disables disk flushing on each=20
> transaction.
>
> If you want to test (please do not use for production at the moment!),=20
> you can download a modified sqlite dll from sourceforge:
> http://sourceforge.net/project/showfiles.php?group_id=3D103463&package_=
id=3D131328=20
>
>
> to disable disk flush, set the pragma win32_flush_buffers to off (it=20
> defaults to on):
> PRAGMA win32_flush_buffers=3Doff
>
> below the readme of the zip, including a simple benchmark.
>
> regards,
>
> rene
>
> -------------------------------------
>
> Important information on this library
> This is a custom build of SQLite 3 dynamic link library (dll) for=20
> windows.
>
> WARNING: THIS BUILD CANNOT GUARANTEE SQLite ACID behaviour in case of=20
> OS crash!
>
> Version: Sqlite 3.27 (modified)
> Compiler: dev-c++ 4.9.9.2 (minGW)
> Modifications: Added pragma for disabling file buffer flush on win32=20
> (Rene Tegel)
>
> Usage:
> Set the sqlite pragma win32_flush_buffers to on or off (defaults to on=20
> (safe)):
> PRAGMA win32_flush_buffers=3Doff
>
> Motivation:
> SQLite is very slow and disk-intensive when completing a transaction.=20
> This is mainly causes by calling the windows api FlushFileBuffers=20
> which does, probably, a lot more than intended (flushing file buffer=20
> to disk).
> In case of an application crash, there should be no difference in=20
> ACID. In case of an operating system crash or power failure, the=20
> effects may be noticable.
> However, by disabling buffer flush a transaction is handled about 80=20
> times faster on my system! Benchmarks below.
>
> Recommended use:
> NTFS file systems
> Data logging applications
> Other applications that involve numerous transactions but need=20
> perfarmance.
> Make a backup of your database if you can't risk total data loss but=20
> still need the performance
>
> Important notice:
> I am not resposible for data loss. What is more: Richard Hipp (SQLite=20
> main developer) is not responsible for data loss or any modifications=20
> in this library. Do NOT contach him and/or the sqlite mailing list for=20
> issues caused by flushbuffers switched to off. The rest of the library=20
> should be identical.
>
> Future compilations:
> I will try to patch, compile and release newer versions. After=20
> testing, i may contact the sqlite mailing list and ask Richard Hipp to=20
> include the patch.
>
>
>
> Benchmarks:
> AS tested with sqlite 3.2.7 compiled with dev-c++ on win32
> platform: win XP AMD 2500+
>
>
>
> sqlite327 standard, non-optimized:
>
> inserting 1 records
> Seperate transactions 79 ms
> Single transaction 78 ms
>
> inserting 10 records
> Seperate transactions 672 ms
> Single transaction 79 ms
>
> inserting 100 records
> Seperate transactions 6719 ms
> Single transaction 78 ms
>
> inserting 1000 records
> Seperate transactions 77359 ms
> Single transaction 156 ms
>
> inserting 10000 records
> Seperate transactions 789453 ms
> Single transaction 781 ms
>
>
>
> sqlite3.27 optimized, disabled buffer flush:
>
> inserting 1 records
> Seperate transactions 0 ms
> Single transaction 0 ms
>
> inserting 10 records
> Seperate transactions 16 ms
> Single transaction 0 ms
>
> inserting 100 records
> Seperate transactions 156 ms
> Single transaction 0 ms
>
> inserting 1000 records
> Seperate transactions 969 ms
> Single transaction 78 ms
>
> inserting 10000 records
> Seperate transactions 9828 ms
> Single transaction 594 ms
>
>
>
>
>
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Power Architecture Resource Center: Free content, downloads, discussion=
s,
> and more. http://solutions.newsforge.com/ibmarch.tmpl
> _______________________________________________
> libsql-discussion mailing list
> lib...@li...
> https://lists.sourceforge.net/lists/listinfo/libsql-discussion