Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Vinay Y S]

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  wrote:
> On Mon, Oct 29, 2012 at 8:28 PM, Richard Hipp  wrote:
>
>> On Mon, Oct 29, 2012 at 5:58 AM, David Barrett > >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

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Chris Peachment]

In my scenario there is just one master and most transactions are
non-overlapping, but I can foresee a need for resolution of the
occasional collision.

You mention "other states". Can you explain further?

Chris

On Wed, 2012-10-31 at 11:40 +0700, David Barrett wrote:
> Ah, to clarify, there is only one "master" at any point in time.  So this
> isn't a "multi-master" scenario where each node keeps committing locally
> and then somehow merging the results later.  Rather, each node knows if
> it's the master or slave (or a variety of other states).  If it's a master,
> it organizes the two-phase distributed commit.  If it's a slave, it
> escalates to the master.  And if it's something else, then it just holds on
> to the request and waits until it's either a slave or a master.
> 
> -david
> 
> 
> On Wed, Oct 31, 2012 at 2:09 AM, Chris Peachment  wrote:
> 
> > On Wed, 2012-10-31 at 00:49 +0700, David Barrett wrote:
> > > Thanks Alek!  Yes, we're definitely planning on it, just trying to
> > > find the right time.  We don't want to go through the work to open
> > > source it only to be greeted with silence.  Might you be interested in
> > > using it in an actual deployed environment, or just studying it?
> > >
> > >
> > Your proposal to open source the replication method used by Expensify
> > has me interested. My application of interest is much smaller than
> > yours, just a handful of remote clients that risk loss of connectivity
> > but wish to continue with database updates during the downtime.
> >
> > Aside from the details of protocol usage and statement packaging, the
> > concern for collisions during merge is a particular issue of interest.
> >
> > Chris
> >
> >
> > ___
> > 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

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[David Barrett]

Ah, to clarify, there is only one "master" at any point in time.  So this
isn't a "multi-master" scenario where each node keeps committing locally
and then somehow merging the results later.  Rather, each node knows if
it's the master or slave (or a variety of other states).  If it's a master,
it organizes the two-phase distributed commit.  If it's a slave, it
escalates to the master.  And if it's something else, then it just holds on
to the request and waits until it's either a slave or a master.

-david


On Wed, Oct 31, 2012 at 2:09 AM, Chris Peachment  wrote:

> On Wed, 2012-10-31 at 00:49 +0700, David Barrett wrote:
> > Thanks Alek!  Yes, we're definitely planning on it, just trying to
> > find the right time.  We don't want to go through the work to open
> > source it only to be greeted with silence.  Might you be interested in
> > using it in an actual deployed environment, or just studying it?
> >
> >
> Your proposal to open source the replication method used by Expensify
> has me interested. My application of interest is much smaller than
> yours, just a handful of remote clients that risk loss of connectivity
> but wish to continue with database updates during the downtime.
>
> Aside from the details of protocol usage and statement packaging, the
> concern for collisions during merge is a particular issue of interest.
>
> Chris
>
>
> ___
> 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

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Chris Peachment]

On Wed, 2012-10-31 at 00:49 +0700, David Barrett wrote:
> Thanks Alek!  Yes, we're definitely planning on it, just trying to
> find the right time.  We don't want to go through the work to open
> source it only to be greeted with silence.  Might you be interested in
> using it in an actual deployed environment, or just studying it?
> 
> 
Your proposal to open source the replication method used by Expensify
has me interested. My application of interest is much smaller than
yours, just a handful of remote clients that risk loss of connectivity
but wish to continue with database updates during the downtime.

Aside from the details of protocol usage and statement packaging, the
concern for collisions during merge is a particular issue of interest.

Chris


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Simon Slavin]

On 30 Oct 2012, at 5:15pm, David Barrett  wrote:

> I'm encouraged to hear
> that there aren't any particular size restrictions or concerns.



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[David Barrett]

On Tue, Oct 30, 2012 at 1:00 AM, Alek Paunov  wrote:

> On 29.10.2012 11:58, David Barrett wrote:
>
>> Because in practice, as someone actually doing it (as opposed to
>> theorizing
>> about it), it works great.  The MySQL portions of our service are always
>> in
>> a semi-constant state of emergency, while our sqlite portions just hum
>> along   And given that we're switching to SSDs, I expect they will hum
>> even
>> better.  What problems would you expect me to be seeing that I can happily
>> report I'm not, or what problems have I not yet encountered but will -- at
>> 100GB, or 1TB?
>>
>
> In your previous thread (2012-02), you have mentioned that you are about
> to open-source your replication method based on SQL statement distribution.
> Probably your work would be of interest for a huge number of sites managing
> data volumes around or bellow your current level, even if you switch to
> PostgreSQL at this point.
>
> IMHO, there might be a future for your replication model, because I think
> that SQLite, can more easily (relative to other proven DB technologies e.g.
> PostgreSQL) be turned to DB engine for more query languages than SQL
> (thanks to his clever VM design).
>
> Furthermore, AFAIK, PostgreSQL replicates at WAL distribution level, most
> NoSQL databases at keys distribution level, whereas your method seems more
> efficient as bandwidth.
>

Thanks Alek!  Yes, we're definitely planning on it, just trying to find the
right time.  We don't want to go through the work to open source it only to
be greeted with silence.  Might you be interested in using it in an actual
deployed environment, or just studying it?

As for the size this works up to, I should emphasize that Expensify uses
this for our main database -- and we have over a *million* users on it.
 That's not to say a million users is the biggest thing ever, but it's a
lot bigger than most websites (with far more complicated data structures),
and it works great.  Furthermore, we're in the process of upgrading all our
hardware and we feel that alone will get us at *least* an order of
magnitude improvement in capacity -- wiithout any algorithmic changes.  And
we've got plenty of ideas how to improve the basic technology and/or
restructure our database to get even more capacity, should we need it.

The upshot is I don't see a specific reason why it couldn't scale up to a
5M, 10M, or larger service.  And if it starts to break down after that?
 Well that's a problem we should all love to have.

Additionally, I think people get so excited about big data that they
overlook the importance of *available* data.  With this technology,
everything is replicated offsite in realtime, ensuring that service can
continue uninterrupted even when a whole datacenter goes underwater (as is
happening to many datacenters at this very moment in NYC) or falls off the
map (as happens to various AWS zones with surprising regularity).  Our
technology seamlessly fails over when any node (even the master) disappears
(or reappears), without dropping a single transaction -- the web layer
doesn't even know if it's talking to a master or slave, or it was a slave
that became master mid-transaction.

This total confidence in the data layer is what allows us to sleep soundly
even when servers crash: similar to how Google only fixes broken servers
every quarter, any business in this day and age that stresses out when a
server dies is doing it wrong.  Indeed, i'm writing this from a hotel in
Bangkok because every year we take the whole company overseas for a month
to work from the beach -- something that would be inconceivable to an
organization that puts all its eggs in one datacenter.

As for SQL versus binary replication, it has its pros and cons -- it's
generally (though not always) more bandwidth efficient, but at a higher CPU
cost: slaves need to redo all the work as the master.  But it's
fantastically simple, and I feel a simple design brings the most important
efficiency of all: easy to understand, easy to debug, easy to verify.

As for Postgre, MySQL, or any other database back end -- yes, it'd designed
to be a layer above the database.  We're in the midst of making it
optionally backed by a MySQL store, but yes, it should be easy to put
anything behind it.

Finally, that's interesting about using this to replicate non-SQL languages
-- yes, it's definitely language agnostic.  Anything that has the notion of
an atomic transaction with ROLLBACK and COMMIT should work fine with it.

Thanks for the interest!

-david
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[David Barrett]

On Mon, Oct 29, 2012 at 8:28 PM, Richard Hipp  wrote:

> On Mon, Oct 29, 2012 at 5:58 AM, David Barrett  >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

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Alek Paunov]

Hi David,

On 29.10.2012 11:58, David Barrett wrote:

Because in practice, as someone actually doing it (as opposed to theorizing
about it), it works great.  The MySQL portions of our service are always in
a semi-constant state of emergency, while our sqlite portions just hum
along   And given that we're switching to SSDs, I expect they will hum even
better.  What problems would you expect me to be seeing that I can happily
report I'm not, or what problems have I not yet encountered but will -- at
100GB, or 1TB?


In your previous thread (2012-02), you have mentioned that you are about 
to open-source your replication method based on SQL statement 
distribution. Probably your work would be of interest for a huge number 
of sites managing data volumes around or bellow your current level, even 
if you switch to PostgreSQL at this point.


IMHO, there might be a future for your replication model, because I 
think that SQLite, can more easily (relative to other proven DB 
technologies e.g. PostgreSQL) be turned to DB engine for more query 
languages than SQL (thanks to his clever VM design).


Furthermore, AFAIK, PostgreSQL replicates at WAL distribution level, 
most NoSQL databases at keys distribution level, whereas your method 
seems more efficient as bandwidth.


Kind Regards,
Alek

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Richard Hipp]

On Mon, Oct 29, 2012 at 5:58 AM, David Barrett wrote:

> On Mon, Oct 29, 2012 at 2:16 AM, Richard Hipp  wrote:
>
> > It sounds like you are pushing SQLite well beyond what it was intended to
> > do.  Remember the motto:  SQLite is not intended to replace Oracle, it is
> > intended to replace fopen().  SQLite does a great job for roles such as
> > data storage for a desktop application, or for databases in cellphones or
> > other gadgets, or as a local cache to a enterprise network database.  But
> > SQLite was never designed or intended to replace an enterprise-level
> > RDBMS.  And I think you probably need an enterprise-level RDBMS at this
> > point.
> >
>
> 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.  I just mean, what in particular do you feel about sqlite works
> great for a 50MB database, but doesn't work at a 50GB database?  I'm very
> open to being convinced -- and you'd be the person to convince me.  But I
> don't like to make decisions based on vague fears.  "Best practices" often
> aren't.
>
> Because in practice, as someone actually doing it (as opposed to theorizing
> about it), it works great.  The MySQL portions of our service are always in
> a semi-constant state of emergency, while our sqlite portions just hum
> along   And given that we're switching to SSDs, I expect they will hum even
> better.  What problems would you expect me to be seeing that I can happily
> report I'm not, or what problems have I not yet encountered but will -- at
> 100GB, or 1TB?
>

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.  Or it least it
hasn't as far as we know.  On the other hand, we haven't heard from people
pushing terabyte databases into SQLite before

SQLite supports some concurrency (multiple readers, one writer) but not as
much as typical client/server databases.  SQLite is optimized more for the
single-user case.

SQLite has its backup API, which provides some basic replication
capabilities.  But it isn't the same thing has having a full-blown
real-time replicator like you find in most client/server installations.

And finally, SQLite, really really prefers to have its content on the local
disk.  Any application that uses a database can be conceptually divided
into three pieces:  (A) the application (B) the database engine and (C) the
content on disk.  If you have to cross a network to go between A and C (if
the content is on a different machine from where the application is
running) then it is better to cross that network at the A-B junction rather
than at the B-C junction because the A-B junction requires less bandwidth.
Client/server database engines use the A-B junction whereas SQLite on a
network filesystem uses the B-C junction.


>
> Thanks!
>
> -david
>
> PS: Also, if anybody does know anything about MySQL fragmentation, I'd
> still love some pointers.  I'm not sure how my casual request became
> interpreted as an obsession, but either way, I'd still love the benefit of
> your knowledge.
>
>
>
>
>
> >
> > MySQL is a good choice.  But here is another data point to consider:
>  When
> > we were writing the SqlLogicTest test suite for SQLite, we ran the test
> > vectors on a wide variety of server-class database engines in addition to
> > SQLite.  And in every case (including SQLite) we found cases that would
> > crash the server.  Every case, that is, except one.  We were never able
> to
> > crash PostgreSQL, nor find a case where PostgreSQL gave the wrong answer.
> >
> > Furthermore, whenever there is a question about what the behavior of some
> > obscure SQL construct ought to be and whether or not SQLite is doing it
> > right, usually the first thing we check is how PostgreSQL responds to the
> > same query.  When in doubt, we try to get SQLite to do the same thing as
> > PostgreSQL.
> >
> > Far be it from me to recommend one client/server database engine over
> > another.  But in my experience.  well, you can fill in the rest,
> > probably...
> >
> > On Sun, Oct 28, 2012 at 10:48 AM, David Barrett  > >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 

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Simon Slavin]

On 29 Oct 2012, at 9:58am, David Barrett  wrote:

> So what specifically do you feel is the problem with sqlite at scale?

I think it might help if you asked that question with particular reference to 
the points in



particularly the points in the section _Situations Where Another RDBMS May Work 
Better_.  Or perhaps something on that page might answer your question.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[David Barrett]

On Mon, Oct 29, 2012 at 2:16 AM, Richard Hipp  wrote:

> It sounds like you are pushing SQLite well beyond what it was intended to
> do.  Remember the motto:  SQLite is not intended to replace Oracle, it is
> intended to replace fopen().  SQLite does a great job for roles such as
> data storage for a desktop application, or for databases in cellphones or
> other gadgets, or as a local cache to a enterprise network database.  But
> SQLite was never designed or intended to replace an enterprise-level
> RDBMS.  And I think you probably need an enterprise-level RDBMS at this
> point.
>

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.  I just mean, what in particular do you feel about sqlite works
great for a 50MB database, but doesn't work at a 50GB database?  I'm very
open to being convinced -- and you'd be the person to convince me.  But I
don't like to make decisions based on vague fears.  "Best practices" often
aren't.

Because in practice, as someone actually doing it (as opposed to theorizing
about it), it works great.  The MySQL portions of our service are always in
a semi-constant state of emergency, while our sqlite portions just hum
along   And given that we're switching to SSDs, I expect they will hum even
better.  What problems would you expect me to be seeing that I can happily
report I'm not, or what problems have I not yet encountered but will -- at
100GB, or 1TB?

Thanks!

-david

PS: Also, if anybody does know anything about MySQL fragmentation, I'd
still love some pointers.  I'm not sure how my casual request became
interpreted as an obsession, but either way, I'd still love the benefit of
your knowledge.





>
> MySQL is a good choice.  But here is another data point to consider:  When
> we were writing the SqlLogicTest test suite for SQLite, we ran the test
> vectors on a wide variety of server-class database engines in addition to
> SQLite.  And in every case (including SQLite) we found cases that would
> crash the server.  Every case, that is, except one.  We were never able to
> crash PostgreSQL, nor find a case where PostgreSQL gave the wrong answer.
>
> Furthermore, whenever there is a question about what the behavior of some
> obscure SQL construct ought to be and whether or not SQLite is doing it
> right, usually the first thing we check is how PostgreSQL responds to the
> same query.  When in doubt, we try to get SQLite to do the same thing as
> PostgreSQL.
>
> Far be it from me to recommend one client/server database engine over
> another.  But in my experience.  well, you can fill in the rest,
> probably...
>
> On Sun, Oct 28, 2012 at 10:48 AM, David Barrett  >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 

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Richard Hipp]

It sounds like you are pushing SQLite well beyond what it was intended to
do.  Remember the motto:  SQLite is not intended to replace Oracle, it is
intended to replace fopen().  SQLite does a great job for roles such as
data storage for a desktop application, or for databases in cellphones or
other gadgets, or as a local cache to a enterprise network database.  But
SQLite was never designed or intended to replace an enterprise-level
RDBMS.  And I think you probably need an enterprise-level RDBMS at this
point.

MySQL is a good choice.  But here is another data point to consider:  When
we were writing the SqlLogicTest test suite for SQLite, we ran the test
vectors on a wide variety of server-class database engines in addition to
SQLite.  And in every case (including SQLite) we found cases that would
crash the server.  Every case, that is, except one.  We were never able to
crash PostgreSQL, nor find a case where PostgreSQL gave the wrong answer.

Furthermore, whenever there is a question about what the behavior of some
obscure SQL construct ought to be and whether or not SQLite is doing it
right, usually the first thing we check is how PostgreSQL responds to the
same query.  When in doubt, we try to get SQLite to do the same thing as
PostgreSQL.

Far be it from me to recommend one client/server database engine over
another.  But in my experience.  well, you can fill in the rest,
probably...

On Sun, Oct 28, 2012 at 10:48 AM, David Barrett 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  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 
> > 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 
> > 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 

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Pavel Ivanov]

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  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  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 
>> 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 
>> 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 
>> >> wrote:
>> >> > Thanks, this is 

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Bob Cochran]

On 10/28/12 10:58 AM, Simon Slavin wrote:

On 28 Oct 2012, at 2:48pm, David Barrett  wrote:


Wow, I didn't realize this was such a controversial question.

Not particularly controversial, just complicated, and not subject to a good 
explanation other than reading lots of documentation about both engines.

Your description of your setup suggests two thing: first you're obsessed with 
fragmentation when it has only a minor part to play in your problems, and 
second that you should move to a database engine with server/client 
organisation rather than trying to use SQLite in multi-user mode.  Any 
description which includes 'often vacuum' suggests you're using the wrong tool 
for the job.  Whether you'd be best with MySQL or ProgreSQL is another matter.

Simon.

I agree with Simon. I don't see that fragmentation is the issue here.

Bob

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Simon Slavin]

On 28 Oct 2012, at 2:48pm, David Barrett  wrote:

> Wow, I didn't realize this was such a controversial question.

Not particularly controversial, just complicated, and not subject to a good 
explanation other than reading lots of documentation about both engines.

Your description of your setup suggests two thing: first you're obsessed with 
fragmentation when it has only a minor part to play in your problems, and 
second that you should move to a database engine with server/client 
organisation rather than trying to use SQLite in multi-user mode.  Any 
description which includes 'often vacuum' suggests you're using the wrong tool 
for the job.  Whether you'd be best with MySQL or ProgreSQL is another matter.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[David Barrett]

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  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 
> 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 
> 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 
> >> 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 
> >> wrote:
> >> >
> >> >>
> >> >> On 27 Oct 2012, at 11:38am, David Barrett 
> >> 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 

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Pavel Ivanov]

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  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  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 
>> 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 
>> wrote:
>> >
>> >>
>> >> On 27 Oct 2012, at 11:38am, David Barrett 
>> 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.
>> >>
>> >> 

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[David Barrett]

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  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 
> 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 
> wrote:
> >
> >>
> >> On 27 Oct 2012, at 11:38am, David Barrett 
> 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
> 

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Pavel Ivanov]

> 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  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  wrote:
>
>>
>> On 27 Oct 2012, at 11:38am, David Barrett  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

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[David Barrett]

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  wrote:

>
> On 27 Oct 2012, at 11:38am, David Barrett  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

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Richard Hipp]

On Sat, Oct 27, 2012 at 6:38 AM, David Barrett 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.  Can
> anybody summarize for me the differences?
>
> 1) My understanding is sqlite, in general, has no automatic
> defragmentation: there is no process to gradually and incrementally
> reposition data on disk in index order.  (Even auto-VACUUM just reclaims
> deleted data by inserting new transactions in the holes left behind by
> previous deletes, which the documentation says can actually worsen
> fragmentation.)  The only defragmentation option is a full VACUUM, which
> rebuilds the entire database from scratch in index order.  During this
> period, the whole database is locked and cannot be used.  Is this roughly
> right?
>

Roughly.  SQLite does make some effort to keep things in index order as
changes are made.  If SQLite needs a new database page because of new data
being inserted, it looks for a free page in approximately the right spot.
But SQLite never moves pages around in order to put them all in the right
order (apart from VACUUM) since that involves extra I/O and slows down the
INSERT.

In practice, though, fragmentation can still occur.  Depending on your
workload.

In WAL mode, a VACUUM can be ongoing while there are other readers.  This
will lead to a *-wal file that is as big or bigger than the original
database, however.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Simon Slavin]

On 27 Oct 2012, at 11:38am, David Barrett  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] sqlite versus MySQL automatic defragmentation on SSDs?

[David Barrett]

I'm trying to learn more about MySQL versus sqllite when it comes to
vacuuming and fragmentation, especially as it relates to SSD storage.  Can
anybody summarize for me the differences?

1) My understanding is sqlite, in general, has no automatic
defragmentation: there is no process to gradually and incrementally
reposition data on disk in index order.  (Even auto-VACUUM just reclaims
deleted data by inserting new transactions in the holes left behind by
previous deletes, which the documentation says can actually worsen
fragmentation.)  The only defragmentation option is a full VACUUM, which
rebuilds the entire database from scratch in index order.  During this
period, the whole database is locked and cannot be used.  Is this roughly
right?

2) My understanding of MySQL is that the equivalent function is to OPTIMIZE
TABLE.  Unlike sqlite, which works on a full database, this works on a
single table, but is otherwise the same -- rebuilds the entire table (and
thus needs 2x the disk space), locks the table during the operation, is not
incremental (either works fully or not at all).  Is this about right?

3) I've heard mention that in some configurations (perhaps some database
engines?) MySQL doesn't need vacuuming for some reason -- somehow it just
doesn't fragment, or resolves fragmentation as it goes -- but I can't track
down an explanation of why.  Any ideas?

4) Does MySQL (or sqlite?) have any fancy defragmentation capabilities,
such as (for example) using a clustered primary index where no two
secondary rows are put into the same block?  (For example, imagine a table
containing historical credit card transactions, where transactions are
grouped first by "cardID" and then each card has a sequential list of rows
identified by "transactionID".  Every night, new transactions are added to
each card, meaning new data would ideally be inserted throughout the entire
table, as opposed to just adding at the very end. I could imagine a
clustered index ordering all transactions for a single card back-to-back on
disk, except skipping to the next full database block before adding
transactions for the next card.  This would intentionally leave space free
in the database for additional transactions to be added to each card in
disk-sequential-order, explicitly avoiding transactions from multiple cards
being stored in the same block.  This wouldn't be a complete solution --
the database blocks for a particular card wouldn't necessarily be
sequential -- but the transactions inside each block *would* be sequential,
and no two blocks would contain transactions from different cards.  Does
this make sens?  Does anything like this exist?)

4) My understanding is disk order matters a lot for spinning disks, as it
reduces seek times when dealing with data in neighboring primary keys.
 However, am I correct in assuming that the necessity for this is
dramatically reduced by SSDs, which allow random access?  Granted,
vacuuming should always help to a degree: if your rows are smaller than the
block size, then it's better to have neighboring rows in the same block (so
you needn't read multiple blocks to get the same data).  And it's always
good to ensure your database block size is the same as the disk block size
(whether SSD or spinning) so you only need to read a single disk block per
database block.  But 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?

Thanks, I really appreciate your thoughts and links to reading material!

-david
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users