MySQL vs SQLite

rubyonrails-talk
1

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,

2

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.

3

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.

4

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.

5

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.

6

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.

7

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.

8

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.

9

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.

10

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".

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.

11

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.

12

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".

Ruby on Rails — 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

13

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.

14

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,

15

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.