If anyone out there knows any good performance tweaks for DBD::SQLite, please take a look at my post on PerlMonks about it.
Re:Turn on AutoCommit
perrin on 2005-03-17T03:53:21
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.Re:Turn on AutoCommit
Matts on 2005-03-17T14:25:47
What do you mean by leaving off transactions entirely?Re:Turn on AutoCommit
perrin on 2005-03-17T15:50:45
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.Re:Turn on AutoCommit
Matts on 2005-03-17T18:16:24
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 the right choice.Re:Turn on AutoCommit
perrin on 2005-03-17T18:34:35
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.
Re:Turn on AutoCommit
Matts on 2005-03-17T19:29:04
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.Re:Turn on AutoCommit
perrin on 2005-03-17T19:38:00
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.Re:Turn on AutoCommit
Matts on 2005-03-17T21:05:36
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.Re:Turn on AutoCommit
perrin on 2005-03-17T22:46:17
I found it to be quite good if I use database-level locks. Using page-level locks seems to be a real pain because of deadlock issues.
