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
