[DBD-SQLite] Re: test DBD::SQLite 1.30_04 - write-ahead logging

[Toby Corkindale]

On 26/08/10 06:31, Darren Duncan wrote:
> All,
>
> I am pleased to announce that DBD::SQLite (Self Contained RDBMS in a
> Perl DBI
> Driver) version 1.30_04 has been released on CPAN (by Adam Kennedy).
>
> http://search.cpan.org/~adamk/DBD-SQLite-1.30_04/
>
> This developer release bundles the brand-new SQLite version 3.7.2, which
> (since 3.7.0) adds support for write-ahead logging (WAL). See
> http://sqlite.org/wal.html for the details of the WAL support that
> SQLite now has.
 >
> TESTING NEEDED!

Hi all,
So I've done a little testing on the upcoming dbd-sqlite release.
Generally it's performed around the same as the previous version, in 
terms of performance and memory usage.

However in the new write-ahead-logging mode, I initially had results 
that were 20-25% faster!
However I was running the tests in an environment with relatively slow 
disk i/o. After I re-ran the tests with faster I/O available, the 
performance lead narrowed.. but is still slightly faster with WAL enabled.
It's worth noting that this was still the case with fsyncs disabled, 
which surprised me. Seems like you can get good performance benefits out 
of the WAL, in certain conditions (loaded disk i/o) even in single-user 
mode.

In read-only performance tests, 1.30 with or without WAL did not provide 
any difference in performance.

I haven't had time to perform any serious multi-user cases against WAL 
yet. We have tried to avoid multi-user use as it tended to break older 
SQLite versions due to the lock timeouts.


Cheers,
Toby