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

Re: [sqlite] Sqlite versus mySQL in PHP

[Radu Lodina]

One (and first) shoot: - try to use :

BEGIN TRANSACTION



COMMIT TRANSACTION




- Original Message - 
From: "Anton Rifco" 
To: 
Sent: Friday, March 27, 2009 11:28 AM
Subject: [sqlite] Sqlite versus mySQL in PHP


> Hi guys,
>
> I would like to ask a question about sqlite in php. I don't know if I am
> sending this message to the right place, but I don't know where else to 
> ask.
>
> Lately, I read in http://www.sqlite.org/speed.html, that sqlite is a bit
> faster than mysql and postgreSQL. But, when I perform a little testing yo
> compare them in php, I got a contrast result.
>
> For both testing, I use this table :
> CREATE TABLE test (a varchar(20) PRIMARY KEY, b varchar(20), c 
> varchar(20),
>  d varchar(20), e varchar(20),  f varchar(20),
>  g varchar(20), h varchar(20),  i varchar(20), j
> varchar(20))
>
> for that table, I perform 5000 insert for both sqlite and mysql :
> here's for sqlite code :
> === sqlite-test.php ===
> $time_start = microtime(true);
> $db = sqlite_open('pegawai.db', 0666, $sqliteerror);
> for($i = 0; $i < 5000; $i++)
>{
>$sql = "INSERT INTO test VALUES
> ('a$i','b$i','c$i','d$i','e$i','b$i','b$i','b$i','b$i','b$i')";
>$q = sqlite_query($db, $sql);
>}
> sqlite_close($db);
> $time_end = microtime(true);
> $time = $time_end - $time_start;
> echo "executed in $time seconds\n";
> =
>
> and here's for mysql code :
> = mysql-test.php 
> .
> $time_start = microtime(true);
> for($i = 0; $i < 5000; $i++)
>{
>$sql = "INSERT INTO test VALUES
> ('a$i','b$i','c$i','d$i','e$i','b$i','b$i','b$i','b$i','b$i')";
>$q = mysql_query($sql);
>}
> $time_end = microtime(true);
> $time = $time_end - $time_start;
> echo "executed in $time seconds\n";
> ..
> ===
>
> when I run those 2 script, the first one (sqlite) run for 43.4918169975
> seconds, while the second (mysql) only take 0.52137298584 seconds.
>
> It means that sqlite run 80x longer than mysql did (very contrast with the
> one I read in http://www.sqlite.org/speed.html).
>
> Please tell me where do I did wrong. Thanks very much.
>
> Regards,
>
> --
> Anton Rifco S
>
> Student of Informatics Department
> School of Electrical Engineering and Informatics
> Institute Technology of Bandung
> ___
> 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 in PHP

[Martin Engelschalk]

Hi,

wrap your inserts in a transaction.
Place
 $q = sqlite_query("begin");
before your loop, and
 $q = sqlite_query("commit");
after your loop of inserts.

Martin

Anton Rifco wrote:
> Hi guys,
>
> I would like to ask a question about sqlite in php. I don't know if I am
> sending this message to the right place, but I don't know where else to ask.
>
> Lately, I read in http://www.sqlite.org/speed.html, that sqlite is a bit
> faster than mysql and postgreSQL. But, when I perform a little testing yo
> compare them in php, I got a contrast result.
>
> For both testing, I use this table :
> CREATE TABLE test (a varchar(20) PRIMARY KEY, b varchar(20), c varchar(20),
>   d varchar(20), e varchar(20),  f varchar(20),
>   g varchar(20), h varchar(20),  i varchar(20), j
> varchar(20))
>
> for that table, I perform 5000 insert for both sqlite and mysql :
> here's for sqlite code :
> === sqlite-test.php ===
> $time_start = microtime(true);
> $db = sqlite_open('pegawai.db', 0666, $sqliteerror);
> for($i = 0; $i < 5000; $i++)
> {
> $sql = "INSERT INTO test VALUES
> ('a$i','b$i','c$i','d$i','e$i','b$i','b$i','b$i','b$i','b$i')";
> $q = sqlite_query($db, $sql);
> }
> sqlite_close($db);
> $time_end = microtime(true);
> $time = $time_end - $time_start;
> echo "executed in $time seconds\n";
> =
>
> and here's for mysql code :
> = mysql-test.php 
> .
> $time_start = microtime(true);
> for($i = 0; $i < 5000; $i++)
> {
> $sql = "INSERT INTO test VALUES
> ('a$i','b$i','c$i','d$i','e$i','b$i','b$i','b$i','b$i','b$i')";
> $q = mysql_query($sql);
> }
> $time_end = microtime(true);
> $time = $time_end - $time_start;
> echo "executed in $time seconds\n";
> ..
> ===
>
> when I run those 2 script, the first one (sqlite) run for 43.4918169975
> seconds, while the second (mysql) only take 0.52137298584 seconds.
>
> It means that sqlite run 80x longer than mysql did (very contrast with the
> one I read in http://www.sqlite.org/speed.html).
>
> Please tell me where do I did wrong. Thanks very much.
>
> Regards,
>
> --
> Anton Rifco S
>
> Student of Informatics Department
> School of Electrical Engineering and Informatics
> Institute Technology of Bandung
> ___
> 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 inside MySQL as one of the pluggable "storage mechanisms" ?

[John Stanton]

Jim C. Nasby wrote:

On Sun, Apr 23, 2006 at 07:30:21AM +1000, John Stanton wrote:

I have actually done that and it works well for a particular class of 
applications, ones with a relatively small number of simultaneous users. 
For large numbers we switch to PostgreSQL  The basic architecture of 
Sqlite, and why it is "Lite", is that it uses a single file and file 
locks for synchronization.  That is very well adapted to myriads of 
applications, but does not make it a competitor to DB2, Oracle or 
PostgreSQL in the enterprise area.  The single file makes it a dream to 
maintain, but there is a price involved for that simplicity.


Dr Hipp put it succinctly when he pointed out that Sqlite is a 
replacement for fopen(), not Oracle.


In our server we do not rely on the file locks in Sqlite but instead 
serialize the transactions using the mutex/condition/event capabilities 
which become available in such a framework.


As for your idea of pluggable storage methods, I consider that a better 
approach for an embedded tool like Sqlite is to embed the other 
functionality alongside it.  Both would be linked into the specific 
application.  For example the people who are eager to have more BLOB 
features could do well to have a second file which just stores BLOBs in 
their chosen style and links to Sqlite via a key.



On a semi-related note, Sean Chittenden (of FreeBSD fame) created an API
from PostgreSQL into memcached
(http://pgfoundry.org/projects/pgmemcache/), mainly to allow the
database to invalidate objects from memcached that had been updated. I
think it would be very interesting to see something similar that would
allow using SQLite from within PostgreSQL, since there's a few
applications that are difficult to get good performance out of with
PostgreSQL. Website session tables that are constantly updated are one
example.

Food for thought.

Re: [sqlite] Sqlite inside MySQL as one of the pluggable "storage mechanisms" ?

[Jim C. Nasby]

On Sun, Apr 23, 2006 at 07:30:21AM +1000, John Stanton wrote:
> I have actually done that and it works well for a particular class of 
> applications, ones with a relatively small number of simultaneous users. 
>  For large numbers we switch to PostgreSQL  The basic architecture of 
> Sqlite, and why it is "Lite", is that it uses a single file and file 
> locks for synchronization.  That is very well adapted to myriads of 
> applications, but does not make it a competitor to DB2, Oracle or 
> PostgreSQL in the enterprise area.  The single file makes it a dream to 
> maintain, but there is a price involved for that simplicity.
> 
> Dr Hipp put it succinctly when he pointed out that Sqlite is a 
> replacement for fopen(), not Oracle.
> 
> In our server we do not rely on the file locks in Sqlite but instead 
> serialize the transactions using the mutex/condition/event capabilities 
> which become available in such a framework.
> 
> As for your idea of pluggable storage methods, I consider that a better 
> approach for an embedded tool like Sqlite is to embed the other 
> functionality alongside it.  Both would be linked into the specific 
> application.  For example the people who are eager to have more BLOB 
> features could do well to have a second file which just stores BLOBs in 
> their chosen style and links to Sqlite via a key.

On a semi-related note, Sean Chittenden (of FreeBSD fame) created an API
from PostgreSQL into memcached
(http://pgfoundry.org/projects/pgmemcache/), mainly to allow the
database to invalidate objects from memcached that had been updated. I
think it would be very interesting to see something similar that would
allow using SQLite from within PostgreSQL, since there's a few
applications that are difficult to get good performance out of with
PostgreSQL. Website session tables that are constantly updated are one
example.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

Re: [sqlite] Sqlite inside MySQL as one of the pluggable "storage mechanisms" ?

[John Stanton]

I have actually done that and it works well for a particular class of 
applications, ones with a relatively small number of simultaneous users. 
 For large numbers we switch to PostgreSQL  The basic architecture of 
Sqlite, and why it is "Lite", is that it uses a single file and file 
locks for synchronization.  That is very well adapted to myriads of 
applications, but does not make it a competitor to DB2, Oracle or 
PostgreSQL in the enterprise area.  The single file makes it a dream to 
maintain, but there is a price involved for that simplicity.


Dr Hipp put it succinctly when he pointed out that Sqlite is a 
replacement for fopen(), not Oracle.


In our server we do not rely on the file locks in Sqlite but instead 
serialize the transactions using the mutex/condition/event capabilities 
which become available in such a framework.


As for your idea of pluggable storage methods, I consider that a better 
approach for an embedded tool like Sqlite is to embed the other 
functionality alongside it.  Both would be linked into the specific 
application.  For example the people who are eager to have more BLOB 
features could do well to have a second file which just stores BLOBs in 
their chosen style and links to Sqlite via a key.

JS

Russell Leighton wrote:


I was afraid of that...it would be cool if someone created a sqlite 
server which
handled the networking and serialization...I would take a crack at it 
myself but

right now I don't have time.

Dan Kennedy wrote:


I had a musing while reading:

   
http://news.com.com/2100-7344_3-6063599.html?part=rss=6063599=news 




where it reminded me of one of MySQL's features:

   MySQL's database is built so that it can use a range of different
   storage mechanisms, tuned for different purposes, such as
   transactions or indexing large amounts of text.

   "Rather than have one perfect engine, it's better to have a
   pluggable architecture," Urlocker said. "The idea is you can mix and
   match within a single application because data will be used in
   different ways."


Perhaps adding the appropriate bindings to MySQL to register sqlite 
as a storage mechanism would
allow the use of Sqlite in a network environment with out bloating 
the core Sqlite code?


Maybe the above makes no sense, but I find the notion appealing that 
Sqlite could remain as tight little sql engine but could use the 
"network server"

of MySQL should ever such a thing be needed.

Would that be possible?
  



It's possible. But the mysql interface is looking for something more
like the sqlite btree layer - you would be effectively bypassing the 
sqlite schema layer entirely and just using sqlite as a simple 
key-value database (like berkeley db). I think it would be extremely 
tricky to make the sqlite schema visible to the mysql client.





__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com  

Re: [sqlite] Sqlite inside MySQL as one of the pluggable "storage mechanisms" ?

[Russell Leighton]

I was afraid of that...it would be cool if someone created a sqlite 
server which
handled the networking and serialization...I would take a crack at it 
myself but

right now I don't have time.

Dan Kennedy wrote:


I had a musing while reading:

   http://news.com.com/2100-7344_3-6063599.html?part=rss=6063599=news


where it reminded me of one of MySQL's features:

   MySQL's database is built so that it can use a range of different
   storage mechanisms, tuned for different purposes, such as
   transactions or indexing large amounts of text.

   "Rather than have one perfect engine, it's better to have a
   pluggable architecture," Urlocker said. "The idea is you can mix and
   match within a single application because data will be used in
   different ways."


Perhaps adding the appropriate bindings to MySQL to register sqlite as a 
storage mechanism would
allow the use of Sqlite in a network environment with out bloating the 
core Sqlite code?


Maybe the above makes no sense, but I find the notion appealing that 
Sqlite could remain as tight little sql engine but could use the 
"network server"

of MySQL should ever such a thing be needed.

Would that be possible?
   



It's possible. But the mysql interface is looking for something more
like the sqlite btree layer - you would be effectively bypassing the 
sqlite schema layer entirely and just using sqlite as a simple 
key-value database (like berkeley db). I think it would be extremely 
tricky to make the sqlite schema visible to the mysql client.





__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
 

Re: [sqlite] Sqlite inside MySQL as one of the pluggable "storage mechanisms" ?

[Dan Kennedy]

> 
> I had a musing while reading:
> 
> 
> http://news.com.com/2100-7344_3-6063599.html?part=rss=6063599=news
> 
> 
> where it reminded me of one of MySQL's features:
> 
> MySQL's database is built so that it can use a range of different
> storage mechanisms, tuned for different purposes, such as
> transactions or indexing large amounts of text.
> 
> "Rather than have one perfect engine, it's better to have a
> pluggable architecture," Urlocker said. "The idea is you can mix and
> match within a single application because data will be used in
> different ways."
> 
> 
> Perhaps adding the appropriate bindings to MySQL to register sqlite as a 
> storage mechanism would
> allow the use of Sqlite in a network environment with out bloating the 
> core Sqlite code?
> 
> Maybe the above makes no sense, but I find the notion appealing that 
> Sqlite could remain as tight little sql engine but could use the 
> "network server"
> of MySQL should ever such a thing be needed.
> 
> Would that be possible?

It's possible. But the mysql interface is looking for something more
like the sqlite btree layer - you would be effectively bypassing the 
sqlite schema layer entirely and just using sqlite as a simple 
key-value database (like berkeley db). I think it would be extremely 
tricky to make the sqlite schema visible to the mysql client.




__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] SQLite to MySQL

[m christensen]

First, WHY move off sqlite.
Hosting companies are a dime-a-dozen.
sqlite is a library, it's part of your code, you are not asking the 
provider to set up

or maintain another RDBMS engine.
Option one in my opinion is to find a cooperative hosting company and 
move you

app unmodified.

IF your app is currently running on your PC and IF it's currently 
running sqlite

and IF your design and SQL code are reasonably optimized I highly doubt
you'll find anything that cane come close speed-wise.

IF you do have a poor design and are forced to fix it in order to 
migrate it may be

faster once you are done, but that doesn't count. ;-).

I'm an Oracle DBA I make good money doing it, but I'm not beyond using a 
more

appropriate database for a given situation.

Just to muddy the waters a bit more...
Oracle has a free version for windows and linux that is good for a 1 GB 
or smaller database

if I recall.
DB2 has just been released for free on windows and linux for machines 
with up to 2 dual Core

processors and 4 Gig RAM. No limits of database size.

Oracle just changed the way they license multi-core processors but List 
price for the

enterprise edition for a 2 CPU dual core machine it still $100,000 list.

I currently support about 20 Oracle instances driving 5 commercial 
applications which cost

about 150 million dollars to develop.
From what I've seen so far this free version of DB2 running on good 
linux hardware would

outrun the sun/oracle systems for about 1/5th the cost.





Jim C. Nasby wrote:


See also http://sql-info.de/mysql/gotchas.html.

About the only downsides I can think of with PostgreSQL is that it's
out-of-the-box configuration is meant for like a 486 and that not quite
as many hosting providers offer it. That url has about 100 downsides to
MySQL (many of which are rather serious). PostgreSQL is also strives to
stay as close to ANSI SQL as possible and makes it nearly impossible to
configure your database in such a way that it's only a matter of time
and luck before you end up with corrupted data.

Granted, MySQL added a lot of features in 5.0, but they still focus much
less on doing things the right way than PostgreSQL does.

And remember: Feb 31st isn't a date. :)

On Thu, Feb 09, 2006 at 07:47:47AM +1100, John Stanton wrote:
 

PostgreSQL implements standard SQL as well as the features of an 
enterprise DBMS.  On that basis if you are changing it makes sense to 
change to the fuller-featured product, one in the same class as Oracle 
and DB2.  In the short term Mysql could be as good as PostgreSQL.


Fanda Vacek wrote:
   

I'm not sure, if Postgres is better choice than MySQL. I have used both 
of  them to find out which is the better one. Both of them can do 
almost  anything. The choice is a matter of taste and person. We are 
free to  choose:)) I'm talking about MySQL 5 and PostgreSQL 8.1.


Sorry for writing this to SQLite mail-list.

Fanda

On Tue, 07 Feb 2006 22:35:09 +0100, John Stanton <[EMAIL PROTECTED]>  
wrote:


 


Jim C. Nasby wrote:

   


On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote:

 


Hi there,

I use SQLite on my website for 2 years now. I do like SQLite a lot and
will use it for a lot of new web projects but, because I got more and
more traffic, I consider to move this one to MySQL in order to reduce
the over load of my computer (I host it @ home).

Do you know if there is a way to convert easily an SQLite database
into a MySQL one ?
(I'm especially thinking about the escapestring and other "create
table [myTable] {" issues...)
   

FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it 
is  to

MySQL, so it might me easier to migrate that direction...
 

If you are migrating to an enterprise level DBMS, PostgreSQL is a 
better  choice than Mysql.  It is fully featured with all the 
qualities of DB2  and Oracle but without the expense.


Note that you will need considerably more machine resources to run a  
"heavier" DBMS than Sqlite.  For example on our 166MHz, 256MB RS/6000  
PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series 
it  is lightning fast.  Sqlite runs fast on the 166MHz machine.


   

 



 

Re: [sqlite] SQLite to MySQL

[Jim C. Nasby]

On Fri, Feb 10, 2006 at 04:44:30PM +0100, Fanda Vacek wrote:
> You can choose what to use. An Elephant or a Dolphin :))

Or you could use both! :P
http://commandprompt.com/images/mammoth_versus_dolphin_500.jpg

Re: [sqlite] SQLite to MySQL

[Fanda Vacek]

You can choose what to use. An Elephant or a Dolphin :))

Fanda

On Thu, 09 Feb 2006 02:43:13 +0100, Jim C. Nasby <[EMAIL PROTECTED]>  
wrote:



See also http://sql-info.de/mysql/gotchas.html.

About the only downsides I can think of with PostgreSQL is that it's
out-of-the-box configuration is meant for like a 486 and that not quite
as many hosting providers offer it. That url has about 100 downsides to
MySQL (many of which are rather serious). PostgreSQL is also strives to
stay as close to ANSI SQL as possible and makes it nearly impossible to
configure your database in such a way that it's only a matter of time
and luck before you end up with corrupted data.

Granted, MySQL added a lot of features in 5.0, but they still focus much
less on doing things the right way than PostgreSQL does.

And remember: Feb 31st isn't a date. :)

On Thu, Feb 09, 2006 at 07:47:47AM +1100, John Stanton wrote:

PostgreSQL implements standard SQL as well as the features of an
enterprise DBMS.  On that basis if you are changing it makes sense to
change to the fuller-featured product, one in the same class as Oracle
and DB2.  In the short term Mysql could be as good as PostgreSQL.

Fanda Vacek wrote:
>I'm not sure, if Postgres is better choice than MySQL. I have used both
>of  them to find out which is the better one. Both of them can do
>almost  anything. The choice is a matter of taste and person. We are
>free to  choose:)) I'm talking about MySQL 5 and PostgreSQL 8.1.
>
>Sorry for writing this to SQLite mail-list.
>
>Fanda
>
>On Tue, 07 Feb 2006 22:35:09 +0100, John Stanton <[EMAIL PROTECTED]>
>wrote:
>
>>Jim C. Nasby wrote:
>>
>>>On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote:
>>>
Hi there,

I use SQLite on my website for 2 years now. I do like SQLite a lot  
and
will use it for a lot of new web projects but, because I got more  
and
more traffic, I consider to move this one to MySQL in order to  
reduce

the over load of my computer (I host it @ home).

Do you know if there is a way to convert easily an SQLite database
into a MySQL one ?
(I'm especially thinking about the escapestring and other "create
table [myTable] {" issues...)
>>>
>>>  FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it
>>>is  to
>>>MySQL, so it might me easier to migrate that direction...
>>
>>
>>If you are migrating to an enterprise level DBMS, PostgreSQL is a
>>better  choice than Mysql.  It is fully featured with all the
>>qualities of DB2  and Oracle but without the expense.
>>
>>Note that you will need considerably more machine resources to run a
>>"heavier" DBMS than Sqlite.  For example on our 166MHz, 256MB RS/6000
>>PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series
>>it  is lightning fast.  Sqlite runs fast on the 166MHz machine.
>>
>
>

Re: [sqlite] SQLite to MySQL

[Jim C. Nasby]

See also http://sql-info.de/mysql/gotchas.html.

About the only downsides I can think of with PostgreSQL is that it's
out-of-the-box configuration is meant for like a 486 and that not quite
as many hosting providers offer it. That url has about 100 downsides to
MySQL (many of which are rather serious). PostgreSQL is also strives to
stay as close to ANSI SQL as possible and makes it nearly impossible to
configure your database in such a way that it's only a matter of time
and luck before you end up with corrupted data.

Granted, MySQL added a lot of features in 5.0, but they still focus much
less on doing things the right way than PostgreSQL does.

And remember: Feb 31st isn't a date. :)

On Thu, Feb 09, 2006 at 07:47:47AM +1100, John Stanton wrote:
> PostgreSQL implements standard SQL as well as the features of an 
> enterprise DBMS.  On that basis if you are changing it makes sense to 
> change to the fuller-featured product, one in the same class as Oracle 
> and DB2.  In the short term Mysql could be as good as PostgreSQL.
> 
> Fanda Vacek wrote:
> >I'm not sure, if Postgres is better choice than MySQL. I have used both 
> >of  them to find out which is the better one. Both of them can do 
> >almost  anything. The choice is a matter of taste and person. We are 
> >free to  choose:)) I'm talking about MySQL 5 and PostgreSQL 8.1.
> >
> >Sorry for writing this to SQLite mail-list.
> >
> >Fanda
> >
> >On Tue, 07 Feb 2006 22:35:09 +0100, John Stanton <[EMAIL PROTECTED]>  
> >wrote:
> >
> >>Jim C. Nasby wrote:
> >>
> >>>On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote:
> >>>
> Hi there,
> 
> I use SQLite on my website for 2 years now. I do like SQLite a lot and
> will use it for a lot of new web projects but, because I got more and
> more traffic, I consider to move this one to MySQL in order to reduce
> the over load of my computer (I host it @ home).
> 
> Do you know if there is a way to convert easily an SQLite database
> into a MySQL one ?
> (I'm especially thinking about the escapestring and other "create
> table [myTable] {" issues...)
> >>>
> >>>  FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it 
> >>>is  to
> >>>MySQL, so it might me easier to migrate that direction...
> >>
> >>
> >>If you are migrating to an enterprise level DBMS, PostgreSQL is a 
> >>better  choice than Mysql.  It is fully featured with all the 
> >>qualities of DB2  and Oracle but without the expense.
> >>
> >>Note that you will need considerably more machine resources to run a  
> >>"heavier" DBMS than Sqlite.  For example on our 166MHz, 256MB RS/6000  
> >>PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series 
> >>it  is lightning fast.  Sqlite runs fast on the 166MHz machine.
> >>
> >
> >
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

Re: [sqlite] SQLite to MySQL

[John Stanton]

PostgreSQL implements standard SQL as well as the features of an 
enterprise DBMS.  On that basis if you are changing it makes sense to 
change to the fuller-featured product, one in the same class as Oracle 
and DB2.  In the short term Mysql could be as good as PostgreSQL.


Fanda Vacek wrote:
I'm not sure, if Postgres is better choice than MySQL. I have used both 
of  them to find out which is the better one. Both of them can do 
almost  anything. The choice is a matter of taste and person. We are 
free to  choose:)) I'm talking about MySQL 5 and PostgreSQL 8.1.


Sorry for writing this to SQLite mail-list.

Fanda

On Tue, 07 Feb 2006 22:35:09 +0100, John Stanton <[EMAIL PROTECTED]>  
wrote:



Jim C. Nasby wrote:


On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote:


Hi there,

I use SQLite on my website for 2 years now. I do like SQLite a lot and
will use it for a lot of new web projects but, because I got more and
more traffic, I consider to move this one to MySQL in order to reduce
the over load of my computer (I host it @ home).

Do you know if there is a way to convert easily an SQLite database
into a MySQL one ?
(I'm especially thinking about the escapestring and other "create
table [myTable] {" issues...)


  FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it 
is  to

MySQL, so it might me easier to migrate that direction...



If you are migrating to an enterprise level DBMS, PostgreSQL is a 
better  choice than Mysql.  It is fully featured with all the 
qualities of DB2  and Oracle but without the expense.


Note that you will need considerably more machine resources to run a  
"heavier" DBMS than Sqlite.  For example on our 166MHz, 256MB RS/6000  
PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series 
it  is lightning fast.  Sqlite runs fast on the 166MHz machine.

Re: [sqlite] SQLite to MySQL

[Fanda Vacek]

I'm not sure, if Postgres is better choice than MySQL. I have used both of  
them to find out which is the better one. Both of them can do almost  
anything. The choice is a matter of taste and person. We are free to  
choose:)) I'm talking about MySQL 5 and PostgreSQL 8.1.


Sorry for writing this to SQLite mail-list.

Fanda

On Tue, 07 Feb 2006 22:35:09 +0100, John Stanton <[EMAIL PROTECTED]>  
wrote:



Jim C. Nasby wrote:

On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote:


Hi there,

I use SQLite on my website for 2 years now. I do like SQLite a lot and
will use it for a lot of new web projects but, because I got more and
more traffic, I consider to move this one to MySQL in order to reduce
the over load of my computer (I host it @ home).

Do you know if there is a way to convert easily an SQLite database
into a MySQL one ?
(I'm especially thinking about the escapestring and other "create
table [myTable] {" issues...)
  FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it is  
to

MySQL, so it might me easier to migrate that direction...


If you are migrating to an enterprise level DBMS, PostgreSQL is a better  
choice than Mysql.  It is fully featured with all the qualities of DB2  
and Oracle but without the expense.


Note that you will need considerably more machine resources to run a  
"heavier" DBMS than Sqlite.  For example on our 166MHz, 256MB RS/6000  
PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series it  
is lightning fast.  Sqlite runs fast on the 166MHz machine.

Re: [sqlite] SQLite to MySQL

[John Stanton]

Jim C. Nasby wrote:

On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote:


Hi there,

I use SQLite on my website for 2 years now. I do like SQLite a lot and
will use it for a lot of new web projects but, because I got more and
more traffic, I consider to move this one to MySQL in order to reduce
the over load of my computer (I host it @ home).

Do you know if there is a way to convert easily an SQLite database
into a MySQL one ?
(I'm especially thinking about the escapestring and other "create
table [myTable] {" issues...)



FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it is to
MySQL, so it might me easier to migrate that direction...


If you are migrating to an enterprise level DBMS, PostgreSQL is a better 
choice than Mysql.  It is fully featured with all the qualities of DB2 
and Oracle but without the expense.


Note that you will need considerably more machine resources to run a 
"heavier" DBMS than Sqlite.  For example on our 166MHz, 256MB RS/6000 
PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series it 
is lightning fast.  Sqlite runs fast on the 166MHz machine.

Re: [sqlite] SQLite to MySQL

[Jim C. Nasby]

On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote:
> Hi there,
> 
> I use SQLite on my website for 2 years now. I do like SQLite a lot and
> will use it for a lot of new web projects but, because I got more and
> more traffic, I consider to move this one to MySQL in order to reduce
> the over load of my computer (I host it @ home).
> 
> Do you know if there is a way to convert easily an SQLite database
> into a MySQL one ?
> (I'm especially thinking about the escapestring and other "create
> table [myTable] {" issues...)

FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it is to
MySQL, so it might me easier to migrate that direction...
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

RE: [sqlite] SQLite to MySQL

[Paul Severson]

HELP!!

I have a problem with a SqLite install.  I've been charged with
installing PolicyPage,  a web site compliance checking tool with uses
PHP4 and Sqlite.  I've got the web/php content installed such that
Apache will serve it.  I have verified that PHP is working on the
machine (FC4).

I used pear to install SqLite as far as I can tell the install worked.

When I hit a page that attempts to tap the database,  I am graced with
the following error:

Unable to load dynamic library /usr/lib/php4/sqlite.so

Cannot open shared object file:  Permission denied on Line 0...

Any help anyone could give would be great.

Thanks
PCS

Re: [sqlite] SQLite to MySQL

[Jay Sprenkle]

On 2/6/06, Laurent Goussard <[EMAIL PROTECTED]> wrote:
> I don't know, I suppose my queries are not as optimized as I thought
> (even if this optimization was my leitmotiv for all the development
> part), or perhaps it's an apache2+php5 issue on my windows computer...
>
> But the fact is since the database has grown (like my traffic : 6000
> visitors/day and 22Mb db file), I've got more and more "maximum
> execution time" errors at the peak hours. I've monitored them, and it
> seems a lot of simultaneous queries are freezing the server and
> finally generates this error.
>
> The interresting point is the same queries sent a testing mysql db
> while the sqlite part is not responding anymore are working very well,
> So that's the reason why I consider to switch on a mysql solution for
> this website.
>
> Do you got clues concerning conversion ?

Herbert posted a good link to a converter.
That will get your data, but I don't know how well the queries will
translate though. I would think debugging your existing code
would be much less work than a rewrite to a new database.
If there's a flaw in your design a new database won't fix that.

RE: [sqlite] SQLite to MySQL

[nbiggs]

Are you using SQLite that is built into PHP5?  That SQLite version is
2.8.17, or at least it is on my pc.  Anyway, you might want to consider
switching to a newer version of SQLite and using PHP's PDO functions
which will allow you to connect to a SQLite3 database.

In my application, I experienced a dramatic increase in speed by
switching to version 3.2.8.

-Original Message-
From: Laurent Goussard [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 06, 2006 12:05 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite to MySQL

I don't know, I suppose my queries are not as optimized as I thought
(even if this optimization was my leitmotiv for all the development
part), or perhaps it's an apache2+php5 issue on my windows computer...

But the fact is since the database has grown (like my traffic : 6000
visitors/day and 22Mb db file), I've got more and more "maximum
execution time" errors at the peak hours. I've monitored them, and it
seems a lot of simultaneous queries are freezing the server and
finally generates this error.
The interresting point is the same queries sent a testing mysql db
while the sqlite part is not responding anymore are working very well,
So that's the reason why I consider to switch on a mysql solution for
this website.

Do you got clues concerning conversion ?


2006/2/6, Jay Sprenkle <[EMAIL PROTECTED]>:
> > Hi there,
> >
> > I use SQLite on my website for 2 years now. I do like SQLite a lot
and
> > will use it for a lot of new web projects but, because I got more
and
> > more traffic, I consider to move this one to MySQL in order to
reduce
> > the over load of my computer (I host it @ home).
>
> How is this going to reduce load?
>
> sqlite = mysql - server code
>
> You're adding server code. More code = More load.
>

Re: [sqlite] SQLite to MySQL

[Marian Olteanu]

There might be two possible causes for this to happen:
- query optimization - for example, complex queries are better optimized 
by MS SQL Server. I don't know about MySql. Could you post some 
problematic queries?
- concurency. SQLite is not that great about concurency. But... there was 
before the issue of writer starvation and it was fixed.  I don't know in 
which version. Maybe you should give a try to SQLite 3.3.3 and see if the 
problems persist


On Mon, 6 Feb 2006, Laurent Goussard wrote:


I don't know, I suppose my queries are not as optimized as I thought
(even if this optimization was my leitmotiv for all the development
part), or perhaps it's an apache2+php5 issue on my windows computer...

But the fact is since the database has grown (like my traffic : 6000
visitors/day and 22Mb db file), I've got more and more "maximum
execution time" errors at the peak hours. I've monitored them, and it
seems a lot of simultaneous queries are freezing the server and
finally generates this error.
The interresting point is the same queries sent a testing mysql db
while the sqlite part is not responding anymore are working very well,
So that's the reason why I consider to switch on a mysql solution for
this website.

Do you got clues concerning conversion ?


2006/2/6, Jay Sprenkle <[EMAIL PROTECTED]>:

Hi there,

I use SQLite on my website for 2 years now. I do like SQLite a lot and
will use it for a lot of new web projects but, because I got more and
more traffic, I consider to move this one to MySQL in order to reduce
the over load of my computer (I host it @ home).


How is this going to reduce load?

sqlite = mysql - server code

You're adding server code. More code = More load.

Re: [sqlite] SQLite to MySQL

[Paul Tomblin]

Quoting Jay Sprenkle ([EMAIL PROTECTED]):
> > Hi there,
> >
> > I use SQLite on my website for 2 years now. I do like SQLite a lot and
> > will use it for a lot of new web projects but, because I got more and
> > more traffic, I consider to move this one to MySQL in order to reduce
> > the over load of my computer (I host it @ home).
> 
> How is this going to reduce load?
> 
> sqlite = mysql - server code
> 
> You're adding server code. More code = More load.

For applications that are updating and querying at the same time, the
locking on sqlite can impose horrible delays.  I've got a database that's
currently on mysql.  There are three main types of interaction:
- interactive web applications that both query and update (hundreds per
  day)
- long running "batch" applications that do queries lasting 10 minutes or
  more (dozens per day)
- very long running batch update applications that can take hours to run,
  but only have to be done once a month.
In MySQL, all three types of applications happen simultaneously.  In
SQLIte, the way locking is implemented means that the interactive web
applications can't run in a reasonable time because the batch applications
won't allow the updater to commit until the batch job is finished.

-- 
Paul Tomblin <[EMAIL PROTECTED]> http://xcski.com/blogs/pt/
OTOH, the general theme is that lusers should not be allowed to have
computers, cars, guns or genitalia.
  -- Anthony DeBoer

Re: [sqlite] SQLite to MySQL

[Laurent Goussard]

Ho, thank you Herbet !

But, hmmm, is there a chance you know an opensource or free solution
instead of this expensive one ?

2006/2/6, Laurent Goussard <[EMAIL PROTECTED]>:
> I don't know, I suppose my queries are not as optimized as I thought
> (even if this optimization was my leitmotiv for all the development
> part), or perhaps it's an apache2+php5 issue on my windows computer...
>
> But the fact is since the database has grown (like my traffic : 6000
> visitors/day and 22Mb db file), I've got more and more "maximum
> execution time" errors at the peak hours. I've monitored them, and it
> seems a lot of simultaneous queries are freezing the server and
> finally generates this error.
> The interresting point is the same queries sent a testing mysql db
> while the sqlite part is not responding anymore are working very well,
> So that's the reason why I consider to switch on a mysql solution for
> this website.
>
> Do you got clues concerning conversion ?
>
>
> 2006/2/6, Jay Sprenkle <[EMAIL PROTECTED]>:
> > > Hi there,
> > >
> > > I use SQLite on my website for 2 years now. I do like SQLite a lot and
> > > will use it for a lot of new web projects but, because I got more and
> > > more traffic, I consider to move this one to MySQL in order to reduce
> > > the over load of my computer (I host it @ home).
> >
> > How is this going to reduce load?
> >
> > sqlite = mysql - server code
> >
> > You're adding server code. More code = More load.
> >
>

Re: [sqlite] SQLite to MySQL

[Laurent Goussard]

I don't know, I suppose my queries are not as optimized as I thought
(even if this optimization was my leitmotiv for all the development
part), or perhaps it's an apache2+php5 issue on my windows computer...

But the fact is since the database has grown (like my traffic : 6000
visitors/day and 22Mb db file), I've got more and more "maximum
execution time" errors at the peak hours. I've monitored them, and it
seems a lot of simultaneous queries are freezing the server and
finally generates this error.
The interresting point is the same queries sent a testing mysql db
while the sqlite part is not responding anymore are working very well,
So that's the reason why I consider to switch on a mysql solution for
this website.

Do you got clues concerning conversion ?


2006/2/6, Jay Sprenkle <[EMAIL PROTECTED]>:
> > Hi there,
> >
> > I use SQLite on my website for 2 years now. I do like SQLite a lot and
> > will use it for a lot of new web projects but, because I got more and
> > more traffic, I consider to move this one to MySQL in order to reduce
> > the over load of my computer (I host it @ home).
>
> How is this going to reduce load?
>
> sqlite = mysql - server code
>
> You're adding server code. More code = More load.
>

Re: [sqlite] SQLite to MySQL

[Herbert Slesiona]

Hi,
with dbCopy you can copy the SQLite-tables directly to MySQL.
"w_w_w.dbcopy.de"

Laurent Goussard schrieb:

Hi there,

I use SQLite on my website for 2 years now. I do like SQLite a lot and
will use it for a lot of new web projects but, because I got more and
more traffic, I consider to move this one to MySQL in order to reduce
the over load of my computer (I host it @ home).

Do you know if there is a way to convert easily an SQLite database
into a MySQL one ?
(I'm especially thinking about the escapestring and other "create
table [myTable] {" issues...)

Thanks,

Laurent

Re: [sqlite] SQLite to MySQL

[Jay Sprenkle]

> Hi there,
>
> I use SQLite on my website for 2 years now. I do like SQLite a lot and
> will use it for a lot of new web projects but, because I got more and
> more traffic, I consider to move this one to MySQL in order to reduce
> the over load of my computer (I host it @ home).

How is this going to reduce load?

sqlite = mysql - server code

You're adding server code. More code = More load.

Re: [sqlite] SQLite vs MySQL

[Paolo Vernazza]

Richard Nagle wrote:
Have a few question regarding sqlite ;
1. what standard is the current sqlite using: "92" or SQL:2003 ?
http://www.sqlite.org/omitted.html
2. how does sqlite compare to mysql in the pro's and con's,
another words, what major benefits would we get using sqlite over mysql?
http://www.sqlite.org/cvstrac/wiki?p=WhenToUseSqlite
3. part of #2. what are the speed advantages?
You can't compare a car with a bus...
BTW
http://www.sqlite.org/cvstrac/wiki?p=SqliteCompetitors
http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations
4. part of #2. import text or tab delimited files? spreadsheet files ?
What do you mean? CSV tables (or similar) or SQL commands?
5. are there any MAC gui interface apps, that would interface with 
sqlite ?
Check at
http://www.sqlite.org/contrib
and
http://www.sqlite.org/cvstrac/wiki?p=SqliteTools
Please if there a website, with all this information or a white paper,
please provide the link.
did you tried www.sqlite.org?
Usually it contains some useful info about sqlite.
Paolo