Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

perrin (4270)

perrin
  (email not shown publicly)
Perrin is a contributor to various Perl-related projects like mod_perl, Template Toolkit, and Class::DBI. He is a frequent speaker at OSCON, YAPC, and ApacheCon, and a contributor to several perl-related books.

Journal of perrin (4270)

Wednesday March 16, 2005
04:23 PM

DBD::SQLite tuning help?

[ #23685 ]
If anyone out there knows any good performance tweaks for DBD::SQLite, please take a look at my post on PerlMonks about it.

List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

DBD::SQLite tuning help? 10 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • There's no reason to use explicit transactions for just one individual statement. Just turn it off. It's still transactionally safe - the DB just does the commit for you internally instead of having to cross back to the SQL compiler.
    • I did try it, but it had no significant effect on the performance numbers. The only thing that helps is leaving off transactions entirely, and that won't work for sharing the data between processes.
      • What do you mean by leaving off transactions entirely?
        • I mean if I turn off AutoCommit and never do any commits it runs three times as fast, but obviously that isn't going to work.
          • Also what is your test platform? Be careful of Mac OSX - the "commit" there uses a special mac fcntl which guarantees that data is on the platter even on IDE. BerkeleyDB and MySQL don't do that - they just do an fsync. You can disable this - email me if that's the platform you're testing on.

            My recommendation is to test on Linux with SCSI disks where the fsync doesn't lie.

            I'll take a look at the rest of your code now that you say you've tried my suggestion. But this may just be one area where SQLite isn't
            • I tested on Fedora Core 3 with a pretty standard consumer-grade IDE drive.

              It may be a bad application for SQLite. I just wanted to check it out because I heard SQLite 3 had made some big speed gains. The use case that I'm ultimately looking at here is storage for cache data between mod_perl processes.

              • Wouldn't memcached be a better way?

                The speed gains in SQLite3 seem to be more related to multiple concurrent readers, rather than raw performance. Your code (at least for me) runs faster on DBD::SQLite2.
                • Memcached is a lot slower than Cache::FastMmap, BerkeleyDB, or even a local MySQL in this benchmark. It should scale better, and it works for a cluster while some of those others don't, but the raw performance it gives is about the same as SQLite. Anyway, my plan is to provide a cache module that can switch between these so that people can use what works best for their situation.
                  • Do you find the concurrent access to be OK with BDB? I've had real problems with it in the past, and for some applications had to switch to SQLite just to get working (albeit slower) concurrency.
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.