I have an application where speed is probably the #1 priority here. I am
using MySQL 5.0.22. Would SQL be considerably faster than MySQL?
I know this has been discussed before, but the search on the forums is
not working.
Thanks for your help.
On 8/5/06, Ben J. [email protected] wrote:
I have an application where speed is probably the #1 priority here. I am
using MySQL 5.0.22. Would SQL be considerably faster than MySQL?I know this has been discussed before, but the search on the forums is
not working.
Depends entirely on your application, but in 99% of cases I can think
of, no. But remember that sqlite doesn’t have very good concurrency.
It’s good for what it is, but you won’t want to use it for a multi
user web app unless you have other reasons to do so.
Frankly, I think you are choosing the database platform for the wrong
reasons. The difference between say mysql or postgresql in speed for
an average application is going to be measured in milliseconds. There
are differences as you scale up, but if you were even considering
sqlite then I assume scaling wasn’t really a consideration.
On Aug 5, 2006, at 2:52 PM, Ben J. wrote:
I have an application where speed is probably the #1 priority here.
I am
using MySQL 5.0.22. Would SQL be considerably faster than MySQL?I know this has been discussed before, but the search on the forums is
not working.
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.
Robby
–
Robby R.
Founder & Executive Director
PLANET ARGON, LLC
Ruby on Rails Development, Consulting & Hosting
+1 503 445 2457
+1 877 55 ARGON [toll free]
+1 815 642 4068 [fax]
On 06/08/06, Åukasz Piestrzeniewicz [email protected]
wrote:
Ben J. wrote:
I have an application where speed is probably the #1 priority here. I am
using MySQL 5.0.22. Would SQL be considerably faster than MySQL?
Echoing a point made the other day by someone, what would be
considerably
faster is throwing more resources at the problem. It matters not if you
use
MySQL, SQLlite, Derby, or whatever.
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,
Ben J. wrote:
I have an application where speed is probably the #1 priority here. I am
using MySQL 5.0.22. Would SQL be considerably faster than MySQL?I know this has been discussed before, but the search on the forums is
not working.Thanks for your help.
Just my 2 cents:
I have a large suite of tests that use transactional fixtures.
During the development I switched from MySQL (InnoDB tables) to SQlite
to see if I can get tests running faster. But in fact tests were slower
by about 10% in SQlite - and note there is no concurency during tests. I
switched back to MySQL.
I think that SQLite is great when you have a small, self-contained app.
But I wouldn’t recommend it for larger appliances.
Cheers,
Bragi
Greg D. 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.
On Tue, Mar 24, 2009 at 8:00 AM, Fernando P.
[email protected] wrote:
Has anyone had to migrate a live app from sqlite to psql/mysql/etc due
to performance reasons?
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 D.
http://destiney.com/
On Tue, Mar 24, 2009 at 11:36 AM, Robert W.
[email protected] wrote:
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.
–
Greg D.
http://destiney.com/
On Tue, Mar 24, 2009 at 1:48 PM, Robert W.
[email protected] wrote:
I take it you’re not developing on a Mac.
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.
–
Greg D.
http://destiney.com/
On Tue, Mar 24, 2009 at 7:48 PM, Robert W.
[email protected] wrote:
I take it you’re not developing on a Mac.
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.
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.
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.
On Tue, Mar 24, 2009 at 2:06 PM, Fidel V. [email protected]
wrote:
Actually, it isn’t hard to setup on Linux either. I have installed
without any problems.
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.
–
Greg D.
http://destiney.com/
Greg D. wrote:
On Tue, Mar 24, 2009 at 2:06 PM, Fidel V. [email protected]
wrote:
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.
Now you have met that illusory individual. I am a Java developer by day
(not necessarily by choice). I use Rails to create “one-off” prototypes
all the time. Mostly because experimenting in Java is a complete pain in
the a**.
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.
As I said, I’m not disagreeing with you at all. I was merely providing
some insight into the thought process behind making SQLite the default
database as I see it. Zero configuration, on this very common Rails
development platform, is still faster and more convenient than even the
simplest of install/configurations of other databases.
On Tue, Mar 24, 2009 at 3:02 PM, Robert W.
[email protected] wrote:
As I said, I’m not disagreeing with you at all. I was merely providing
some insight into the thought process behind making SQLite the default
database as I see it. Zero configuration, on this very common Rails
development platform, is still faster and more convenient than even the
simplest of install/configurations of other databases.
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.
–
Greg D.
http://destiney.com/
Greg D. wrote:
On Tue, Mar 24, 2009 at 3:02 PM, Robert W.
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.
True, I have not. I’ve been lucky I guess.
So if SQLite was a mistake, then tell me, “What database should Rails
use by default?”
A: SQLite
B: MySQL
C: PostgreSQL
D: None of the above
“Who’s opinion should count?” Maybe having no database as the “default”
would be the right choice. In that case we have the next best thing:
SQLite. 
Greg D. wrote:
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 take it you’re not developing on a Mac. 
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.
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.
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.
On Tue, Mar 24, 2009 at 8:23 PM, Greg D. [email protected] wrote:
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.
On Tue, Mar 24, 2009 at 4:17 PM, Fidel V. [email protected]
wrote:
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.
–
Greg D.
http://destiney.com/
On Tue, Mar 24, 2009 at 2:49 PM, Greg D. [email protected] wrote:
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.
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
On Tue, Mar 24, 2009 at 6:30 PM, Jeremy K. [email protected]
wrote:
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.
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 D.
http://destiney.com/