Thank you. This is what I wanted to hear. And as you already saw from responses, fragmentation is far from your main problem. I'd like to point to one particular issue:
> However, we're starting to see problems. There is so much activity on some > servers that there is never a chance for our checkpointing thread to do its > thing, so our WAL file often ballons up to 30GB or more. This makes query > times plummet. Looking at this problem alone I'd say SQLite is not the right tool for you. At least at the scale you are working now. And I don't know all your arguments but I hope you are arguing not just because you are a fan of SQLite and don't want to move away from it. Pavel On Sun, Oct 28, 2012 at 7:48 AM, David Barrett <dbarr...@expensify.com> wrote: > Wow, I didn't realize this was such a controversial question. > > I'm a huge sqlite fan. Expensify is built on sqlite. We have a 40GB > database, replicated using our custom distributed transaction layer across > 5 severs in three different datacenters.[1] It's been powering all of > Expensify (including our direct deposit reimbursement engine and credit > card import layer -- both of which contain incredibly sensitive > information, with mistakes causing millions of dollars to move in the wrong > direction). On the back of sqlite, we've grown to over million users, > processing millions of dollars in expense reports every day. > > However, we're starting to see problems. There is so much activity on some > servers that there is never a chance for our checkpointing thread to do its > thing, so our WAL file often ballons up to 30GB or more. This makes query > times plummet. We regularly checkpoint manually, and often vacuum, all in > an effort to keep queries moving quick. We also do things to trick out our > indexes in order to ensure proper disk ordering, pay particular attention > to block and cache amounts, etc. This isn't premature optimization for the > sake of having fun, these are in response to real performance problems > affecting our product. > > In light of that, there is a contingent pushing to drop sqlite in favor of > MySQL. There are a wide range of reasons -- it has its own replication, > better write concurrency, clustered indexes, and better edge-case data > integrity (because we use 2 DBs and WAL, ATTACH doesn't provide atomic > commit advantages). And for each I have a corresponding answer -- MySQL's > replication isn't as good as ours, concurrency doesn't matter because we > serialize writes and have a single threaded server anyway, clustered > indexes would be nice but we can get close enough with custom ROWIDs, and > the extremely rare situation where there's a cross-database integrity > problem, we can detect and recover from any of the other slaves. And I > also add in that sqlite can never crash because it's built into the server; > its performance is fantastic because it runs in the same process; in years > of operation we've never once seen it corrupt data; it's so easy to use; > etc etc. > > But there's an argument I've heard come up to which I don't have a > response: MySQL handles fragmentation better, and by extension would give > us better performance on the same hardware. I'd like to know more about > it, which is why I've asked. Thanks! > > -david > > [1] http://permalink.gmane.org/gmane.comp.db.sqlite.general/71868 > > > On Sun, Oct 28, 2012 at 2:12 PM, Pavel Ivanov <paiva...@gmail.com> wrote: > >> OK. Curiosity is a good thing in certain situations. But could you >> kindly tell me what will you do with this information (assuming it's >> possible to obtain it of course)? >> >> Pavel >> >> On Sat, Oct 27, 2012 at 11:54 PM, David Barrett <dbarr...@expensify.com> >> wrote: >> > I completely understand the wide and varied differences. I'm just *also* >> > interested in this very specific issue. >> > >> > -david >> > >> > On Sun, Oct 28, 2012 at 12:38 PM, Pavel Ivanov <paiva...@gmail.com> >> wrote: >> > >> >> > That said, I'd still welcome any quick summary of the differences >> between >> >> > sqlite and mysql when it comes to fragmentation. >> >> >> >> This is far from main differences between sqlite and mysql that you >> >> should consider if you want to choose between them unless of course >> >> your question is just about an academic interest. As you are talking >> >> about employees I guess you are not in some purely academic exercise. >> >> In this case think more about in-process code vs pumping through >> >> TCP/IP stack, designed mostly to be accessible from machine-local >> >> processes only vs accessible to anyone on the network, plain access to >> >> everything vs versatile and complicated authorization and >> >> authentication mechanisms, and so on and so forth. Database format is >> >> never a part of the decision which DBMS you want to use. >> >> >> >> Pavel >> >> >> >> >> >> On Sat, Oct 27, 2012 at 9:32 PM, David Barrett <dbarr...@expensify.com> >> >> wrote: >> >> > Thanks, this is really helpful! (And I lecture my employees about the >> >> > evils of premature optimization all the time. In fact, I'll lecture >> >> anyone >> >> > in earshot, so frequently that it's the butt of jokes.) >> >> > >> >> > That said, I'd still welcome any quick summary of the differences >> between >> >> > sqlite and mysql when it comes to fragmentation. I often get in >> debates >> >> > about sqlite versus other datbases, and I'm always eager to be >> informed. >> >> > Thanks! >> >> > >> >> > -david >> >> > >> >> > On Sat, Oct 27, 2012 at 11:42 PM, Simon Slavin <slav...@bigfraud.org> >> >> wrote: >> >> > >> >> >> >> >> >> On 27 Oct 2012, at 11:38am, David Barrett <dbarr...@expensify.com> >> >> wrote: >> >> >> >> >> >> > I'm trying to learn more about MySQL versus sqllite when it comes >> to >> >> >> > vacuuming and fragmentation, especially as it relates to SSD >> storage. >> >> >> >> >> >> >> >> >> Rather than answer your questions point-by-point, I'm going to give >> you >> >> >> the current state of play. Your understanding of how the various >> DBMSes >> >> >> work is right, but your excellent question "in a world with SSDs and >> a >> >> >> tremendous amount of RAM, does vacuuming matter nearly as much as on >> a >> >> >> spinning disk with constrained RAM?" cuts to the heart of all your >> other >> >> >> points. The following involves a little simplification and >> handwaving >> >> >> because otherwise it would be two chapters long and you'd have to do >> >> >> homework. >> >> >> >> >> >> Fragmentation made a big difference to apps running on Windows, but >> very >> >> >> little on any other platform. This is because Windows does something >> >> >> called 'read-ahead caching' which assumes that if you read block B, >> >> you're >> >> >> soon going to want to read block B+1, so at quite a low level it >> >> helpfully >> >> >> pre-reads it for you. Other operating systems don't make this >> >> assumption. >> >> >> This is why Windows users talk about defragmentation so much, but >> Unix >> >> >> users don't care about it. >> >> >> >> >> >> SSDs negate the whole point of defragmentation. On a rotational disk >> >> it's >> >> >> faster to read blocks B, B+1, B+2, B+4, B+5 than it is to read five >> >> random >> >> >> blocks from the disk, because the read heads are already positioned >> in >> >> the >> >> >> right place, and the disk is going to rotate to show those five >> blocks >> >> in >> >> >> order. SSDs are just like RAM: they're Random Access Memory. >> Reading >> >> any >> >> >> five blocks in any order takes roughly the same time. So nobody >> cares >> >> >> about fragmentation on an SSD. Read whatever blocks you want in >> >> whatever >> >> >> order you want. >> >> >> >> >> >> As to clever management of disk block alignment with respect to rows >> and >> >> >> columns, this is rarely worth attention these days. The amount of >> >> >> programming and debugging time it takes to get this right, and the >> >> amount >> >> >> of extra processing and disk access you need to do, give you less >> >> return on >> >> >> investment than if you spent the same money on buying a faster hard >> >> disk. >> >> >> It's "premature optimization" (look up the term) except for two >> cases: >> >> >> overnight runs and realtime 3D graphics. If your overnight run takes >> >> more >> >> >> than one night, you have a problem. If you're programming realtime >> 3D >> >> >> graphics and they're jerky, your users won't enjoy your simulation. >> But >> >> >> you wouldn't be using a SQL engine for 3D graphics anyway. >> >> >> >> >> >> The matters you mentioned were all worth attention back in the 1980s >> >> when >> >> >> storage and bandwidth were expensive. As you pointed out near the >> end >> >> of >> >> >> your post, these things matter less now. >> >> >> >> >> >> Simon. >> >> >> >> >> >> _______________________________________________ >> >> >> sqlite-users mailing list >> >> >> sqlite-users@sqlite.org >> >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> >> > _______________________________________________ >> >> > sqlite-users mailing list >> >> > sqlite-users@sqlite.org >> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> _______________________________________________ >> >> sqlite-users mailing list >> >> sqlite-users@sqlite.org >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> > _______________________________________________ >> > sqlite-users mailing list >> > sqlite-users@sqlite.org >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
