Summary: insert/delete/update transactions are taking 10-15x the time on CentOS 6.3 compared to MacOSX 10.8.2
I'm using SQLite (3.7.12) from Perl (DBD::SQLite 1.37). My application has a number of places where it does multiple writes (deletes, updates and inserts) within a transaction.
I've been comparing timings between 3 machines:
The transaction is taking roughly 10x to 15x longer on the CentOS server compared to the MBP and MBA. As expected, the MBA is a bit quicker, as it's got an SSD. If I turn pragma synchronous off, it's nice and fast, as expected.
We're running exactly the same test sequence every time, and end up with indentical databases. There's very little else (of note) running on the CentOS box at the time the test is running.
Benchmarking low-level disk write performance, the CentOS machine outperforms the others. Where should I look next?
The issue turned out to be the way the ext3 filesystem was configured in /etc/fstab.
I ended up doing a lot of experiments and performance testing to better understand this, which I wrote up on server fault:
https://serverfault.com/questions/486677/should-we-mount-with-data-writeback-and-barrier-0-on-ext3
In summary, the filesystem was mounted with barrier=1; changing it to barrier=0, combined with data=ordered, gave back the performance we were "missing".