Since you have a development team that's built complex software on top of sqlite, why not instrument it to see how many seeks it's doing per query operation?
This can be done relatively easily by writing a custom vfs shim that can collect stats for the read/write seeks. This stat collected over a period can give you an idea of fragmentation if the seeks are increasing as the db gets older. If you are on newer Linux kernels, you can use blktrace to diagnose latency issues. I work on a very large scale distributed datastore that uses sqlite as the on-disk format and we store huge amounts of data in it. But we try to keep the size of each individual db file less than a few tens of GBs for ease of management. But we have multiple dbs per server that are attached and accessed together. Thanks, Vinay On Tue, Oct 30, 2012 at 10:45 PM, David Barrett <dbarr...@expensify.com> wrote: > On Mon, Oct 29, 2012 at 8:28 PM, Richard Hipp <d...@sqlite.org> wrote: > >> On Mon, Oct 29, 2012 at 5:58 AM, David Barrett <dbarr...@expensify.com >> >wrote: >> > So what specifically do you feel is the problem with sqlite at scale? >> > >> > And to be clear, I'd like to pre-empt "well it doesn't do X, which you'll >> > probably also want to do" -- all those X's are already covered and >> working >> > great. > > > Generally the argument in favor of client/server databases versus SQLite >> comes down to (1) Concurrency, (2) Replication, and (3) Network access. >> The size of the database file shouldn't really be a factor. > > > Yes, thank you, I'm sorry I didn't make it clear -- concurrency, > replication, and network access are *not* problems for me. Specifically: > > 1) Our server is single-threaded (all writes are serialized), so there are > no concurrency issues. > 2) We have our own replication layer (which is better than MySQL and > Postgres replication) > 3) We provide our own network access (on top of the replication layer) > 4) The backup API works fine > 5) Everything is on the local disk > > So I agree entirely -- if you need something sqlite doesn't provide, then > sqlite isn't the right choice. I'm just saying sqlite provides everything > I need, and does it incredibly well. Similarly, I'm encouraged to hear > that there aren't any particular size restrictions or concerns. We're > currently at 40GB and it's working great. > > But back to the original question -- can anybody point me in a direction to > learn more about MySQL's handling of fragmentation and how it differs from > sqlite? This isn't an obsession, it doesn't even really affect us in light > of our moving to SSDs. It's just a gap in my knowledge I'm looking to > fill. Thanks! > > -david > _______________________________________________ > 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
