MySQL vs SQLite

Speed in which direction is your priority? SELECTS, INSERTS? Both?

MySQL is fast out of the box... but it has been my experience that in
larger databases (hundreds of GB to TB...) PostgreSQL wins hands down.

SQLite is nice for a small application with a small database.

Up to what extent is sqlite suitable for an online application? Above
what DB size and req/s does sqlite start to lose ground against
psql/mysql?

Has anyone had to migrate a live app from sqlite to psql/mysql/etc due
to performance reasons?

Best regards,

Yes. At some point, as your traffic/requests increase, your sqlite db
will start to experience file lock errors. When depends on your code
and your hardware.

Greg Donald wrote:

Yes. At some point, as your traffic/requests increase, your sqlite db
will start to experience file lock errors. When depends on your code
and your hardware.

This is just my opinion, but I don't consider SQLite to be a production
level database solution. That said I do use SQLite to begin most of my
Rails projects, because it's just so convenient to work with. Later on
when the application starts to get large, or I find tests taking too
long, then I would probably switch to another database solution. But, I
would probably never choose to run my application in production under
SQLite. I just don't really see the point when nearly all deployment
environment are going to be running MySQL or PostgreSQL anyway.

As for MySQL vs Database X. I do have some opinions that I've gained
though my own experiences. It is my impression that "out-of-the-box"
MySQL has horrible INSERT/UPDATE performance compared to other database
solutions. As a disclaimer, "I am not an expert DBA and maybe tuning
would help." Unfortunately, I don't have experience comparing MySQL with
PostgreSQL, But, on one project me and a partner decided MySQL wasn't
cutting it for our needs so we switched to OpenBase and (again
out-of-the-box) were able to handle over 5x the number of
inserts/updates per second on a ~= 250GB database.

I fully understand that other people's experience may vary, but from my
perspective switching to OpenBase solved a problem that MySQL just
wasn't able to handle. I'm not promoting OpenBase, and I've only used it
in production for this one special case. I use MySQL for most of my
applications and it typically works great.

In my opinion it was a mistake in making sqlite the default db for
Rails. Any serious app will eventually have a test suite where you
will need local test/development performance to be high. The sqlite
db constantly being locked and unlocked only hurts performance. The
modern CPU is moving towards more threads and more cores, meanwhile
Rails development heads in the exact opposite direction.

And look at the mail archives for all the beginners struggling to
figure out how to get sqlite, and ruby-sqlite running on their
systems, it's not like replacing mysql with sqlite gave them less to
install or less issues overall. Seems to me they were trying to solve
non-problems with the sqlite move. Any serious developer will already
have a favorite db up and running, spec'd by his phb or otherwise, and
I'd say it's never sqlite by default.

I mean I understand why they did it, to try and bring in more new
Rails users faster, more book sales, more everything. And to that I'd
say why not ease the issues of the existing Rails users first, like
how about some Oracle love for those of us working in research? Hmm?
Just a thought.

I take it you're not developing on a Mac. :slight_smile:

No, I do actually.

http://static.destiney.com/cats/cloey_slumber.jpg

I don't entirely disagree with you on this point, but the fact remains
that a huge percentage of Rails developers are on the Mac.

Is this just your opinion or do you have some quantification?

There are 12 web developers in my research department currently using
Rails. Two use macs, one uses windoze, and the rest of us use Linux.
All of us use Oracle. None of us use SQLite.

On the Mac
SQLite is installed and ready to use, along with Rails, and ruby-sqlite.
SQLite is used in several other areas on the Mac; 1: Persistent store
for Core Data, 2: HTML 5 local storage used by WebKit, etc.

What I'm saying is that from this perspective, on this platform,

Well that's great if you only care about Mac users and the Mac OS.
Knowing what a small percentage of the desktop market Mac OS currently
possesses, I'd say that only caring about the Mac users is a
completely moronic business decision.

If Rails is to ever take any enterprise market-share for it's own it
needs more attention to the things enterprise developers use, and
sqlite is at the bottom of that list.

I take it you're not developing on a Mac. :slight_smile:

Yes. He is not alone.....

I don't entirely disagree with you on this point, but the fact remains
that a huge percentage of Rails developers are on the Mac. On the Mac
SQLite is installed and ready to use, along with Rails, and ruby-sqlite.
SQLite is used in several other areas on the Mac; 1: Persistent store
for Core Data, 2: HTML 5 local storage used by WebKit, etc.

Actually, it isn't hard to setup on Linux either. I have installed
without any problems.

What I'm saying is that from this perspective, on this platform, it does
actually make sense to use SQLite as the default. It's the easy choice
to make it the default, because there's no bickering between whether the
default should be MySQL or PostreSQL for instance.

The other advantage of having SQLite as the default is that there is
zero configuration necessary when creating a new Rails app. This is
great for quick prototypes.

rails my_proj
cd my_proj
./script/generate model MyModel name:string
rake db:migrate
./script/server

Zero database configuration. No username/password setup, no rake
db:create. It just works.

That is one great thing that I like about rails and sqlite. you don't
have to mess with the database.yml file.

I don't believe anyone expects SQLite to be use as the production
database, including the Rails core team who chose to use SQLite as the
default.

It's dead simple to skip the whole SQLite thing:

rails my_proj --database=mysql

It makes sense to specify what "other" database server you want when
creating a new project. If you specify it, chances are that you've done
the work to install and configure it for use.

I was just going to say that.

SQLite is a good choice if you want a quick prototype. It is pretty
straight forward.

Once you have the test db working, then you can switch to a production
RDBMS (be it MySQL, Oracle, Postgresql, etc..)

Fidel.

I'm not saying setting up sqlite is hard in general, I'm saying for a
beginner who has no experience with setting up mysql or sqlite the
difference in difficulty is nearly the same.

I don't usually write lots of throw-away Rails apps (nor have I ever
met this illusory individual who does), so being able to quickly set
up a new Rails app means nothing to me.

I work on large apps with lots of tests typically.. so using sqlite
isn't a fast option.

Configuring a database.yml file is a one-time task, so spending 30-60
seconds configuring it seems a very small investment considering how
fast my development environment will go from that point on because I
didn't choose sqlite.

The 30-60 seconds of time saved not configuring the database.yml file
for sqlite is in no way worth the amount of time one loses by using
sqlite. Perhaps you just haven't ever experienced the
SQLite3::BusyException.

I'm not saying setting up sqlite is hard in general, I'm saying for a
beginner who has no experience with setting up mysql or sqlite the
difference in difficulty is nearly the same.

In other words, either one will be ok for a beginner.

The purpose of rails is not for you to learn how to develop quick apps
with oracle, mysql or have you. It is meant to abstract you from all
of that. So, from a beginner point of view, it doesn't really matter
if you use sqlite or mysql, because you are learning about the
framework, and not the database technology.

I don't usually write lots of throw-away Rails apps (nor have I ever
met this illusory individual who does), so being able to quickly set
up a new Rails app means nothing to me.

I don't really know about the research world, but I do know that in
the commercial world, it is sometimes necessary to show a quick
prototype in order to convince a potential client. And that does not
mean that you are going to throw away the application. That is not
what I meant.

I work on large apps with lots of tests typically.. so using sqlite
isn't a fast option.

That may be your case, but not everyone working in research. Remember
that rails was born in the commercial world to meet commercial needs.

Generally speaking, it doesn't really matter if I use mysql or sqlite
to write a prototype to show to a potential client. I am not defending
that sqlite should be the de facto for rails, but it doesn't really
affect me or any other rails developers, because you do have an
option: add -d mysql which takes you less then 30-60 seconds.

Configuring a database.yml file is a one-time task, so spending 30-60
seconds configuring it seems a very small investment considering how
fast my development environment will go from that point on because I
didn't choose sqlite.

I never said that it takes a lot of time, but for a lazy person like
me, 30-60 seconds is saving time anyway. ;D

There is, though, one thing that I happen to agree with you on one of
your previous posts: that we should have better support for other
RDBMSs. That would be really nice.

To conclude, if I am to debate about the topic of this thread, I will
definitely say that MySQL would be the winner, which wasn't what I
meant in my previous post. I was just trying to say that to a
beginner, it does not really matter which RDBMS you use, because, like
you said, the time to setup either one is the same. And let's not
forget that Rails is not about database technology, but about writing
database aware apps as easy as possible.

I hope I have made myself clear this time.

Take care!

Fidel.

In other words, either one will be ok for a beginner.

That's my point exactly. But the switch to sqlite was touted as
"SQLite3 is simply an easier out of the box experience than MySQL".

http://weblog.rubyonrails.org/2007/12/17/rails-2-0-2-some-new-defaults-and-a-few-fixes

Sadly, the statement actually carries weight _if_ you wrongly assume
all new Rails interest will come from Mac owners.

I don't really know about the research world, but I do know that in
the commercial world, it is sometimes necessary to show a quick
prototype in order to convince a potential client.

Convince them of what? That pencil and paper or a simple wireframe
app isn't good enough?

It's been my experience clients want to see your past work and talk to
your references. They care not for how fast you can scaffold an app
together at Starbucks.

There is, though, one thing that I happen to agree with you on one of
your previous posts: that we should have better support for other
RDBMSs. That would be really nice.

Yup. Assuming everyone uses the same thing is an obvious mistake to
anyone giving it more than a moment's thought.

I can only imagine how many times "Works on MySQL, ship it!" has
probably been declared at 37signals.

Convince them of what? That pencil and paper or a simple wireframe
app isn't good enough?

It may be in first world countries, but not exactly in countries that
have experienced 30 years of civil war.

It's been my experience clients want to see your past work and talk to
your references. They care not for how fast you can scaffold an app
together at Starbucks.

Well, it has been my experience that in first world countries that
seems to be the case. Not quite in third world countries. There are
quite a lot of cases where the client does not care to know about what
you have done before, but about what you are going to develop for
them. I have had several cases where they were not that impressed with
what we had done before, but when we showed them a quick prototype
with a quick touch of graphical user interface, then they were
actually all excited about it. It's funny, but it does happen. We just
have to learn to adapt.

Yup. Assuming everyone uses the same thing is an obvious mistake to
anyone giving it more than a moment's thought.

I can only imagine how many times "Works on MySQL, ship it!" has
probably been declared at 37signals.

If you think about it, MySQL is a sort of de facto standard for web
applications.

All the best,

Fidel.

In other words, either one will be ok for a beginner.

That's my point exactly. But the switch to sqlite was touted as
"SQLite3 is simply an easier out of the box experience than MySQL".

http://weblog.rubyonrails.org/2007/12/17/rails-2-0-2-some-new-defaults-and-a-few-fixes

Sadly, the statement actually carries weight _if_ you wrongly assume
all new Rails interest will come from Mac owners.

In Rails 2.3, you can generate an app from any template you like.
Create one that you think captures the Rails interest that's going
unserved and evangelize it.

I don't really know about the research world, but I do know that in
the commercial world, it is sometimes necessary to show a quick
prototype in order to convince a potential client.

Convince them of what? That pencil and paper or a simple wireframe
app isn't good enough?

It's been my experience clients want to see your past work and talk to
your references. They care not for how fast you can scaffold an app
together at Starbucks.

$ rails -d mysql myapp

This handful of characters is worth backhandedly demeaning Fidel? Not cool.

There is, though, one thing that I happen to agree with you on one of
your previous posts: that we should have better support for other
RDBMSs. That would be really nice.

Yup. Assuming everyone uses the same thing is an obvious mistake to
anyone giving it more than a moment's thought.

I can only imagine how many times "Works on MySQL, ship it!" has
probably been declared at 37signals.

Imagination is right. This is ridiculous. Please take it off-list.

Regards,
jeremy

Rails templates seems a fairly useless feature to me personally. I
don't write very many throw away apps so when I weigh the time it
takes me to configure a new Rails app versus the time it'd take me to
paste all those files into a template.. I really feel like I'd come
out ahead by just not doing it.

And do you really expect anyone to think the Rails template API will
remain unchanged for more than the current Rails release? Even if I
did make a template I bet I'd have to upgrade it three times before
the next time I'd go to use it.

Greg Donald wrote:
[...]

Rails templates seems a fairly useless feature to me personally. I
don't write very many throw away apps

I haven't played with templates much yet, but my impression is that
they're not only about throwaway apps. I have about 6 plugins and gems
that I throw into almost every app I create, and I'd love to have an
easy command to do that for me. I gather I'm not alone in this; witness
the existence of things like Blank and Bort.

I agree with you that throwaway apps are a waste of time -- if I want to
impress a client, I'll put together a prototype using the same
technology I plan to use on the final product. Anything else is
wasteful and feels dishonest to me.

so when I weigh the time it
takes me to configure a new Rails app versus the time it'd take me to
paste all those files into a template.. I really feel like I'd come
out ahead by just not doing it.

Huh? Have you actually created more than one Rails app? Wasn't it a
pain to put in the same stuff again each time? Or did you create your
own app template or fork a repo?

And do you really expect anyone to think the Rails template API will
remain unchanged for more than the current Rails release?

No, and that's a good point. But it doesn't make templates useless.

Even if I
did make a template I bet I'd have to upgrade it three times before
the next time I'd go to use it.

Why 3 times? Just upgrade as necessary when you need to -- i.e., the
next time you use the template.

--
Greg Donald
http://destiney.com/

Best,

Huh? Have you actually created more than one Rails app?

Heh.. Yeah.. I've created way more than one Rails app.

Wasn't it a
pain to put in the same stuff again each time?

Copying some files from one Rails app to another is not difficult.

Why 3 times? Just upgrade as necessary when you need to -- i.e., the
next time you use the template.

It's just a number I threw out there. But yeah, it might very well be
4 or 5 times, especially now that I think about that one Rails app I
upgraded from 1.0 to 2.2 last month.