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

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

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

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

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

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

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

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

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

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

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

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

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

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,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

> 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

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

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

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

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

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

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

[sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

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