Strange behavior here. On my dev machine code works, but on the
production machine I get the dreaded:
SQLite3::BusyException: database is locked INSERT INTO "comments" [...]
What kind of bug could yield such behavior? It's impossible the database
times out, there are no concurrent requests coming in, and the db is
only 150kB. Is there a possible race condition? Caching conflict?
In development mode, the DB time is less than 10ms and total time less
than 300ms with a timeout of 5000. The server is much faster than my dev
machine and loading a page on average takes less than 100ms.
The incredible thing is that this app uses the same code as another app.
On the other one it works without any problem, and this one just decided
to give me hell. I guess it's a pretty stupid option hidden somewhere in
my code that's messing up everything, the thing is I don't know where to
look for
If your app has very low concurrency and traffic, and if it is low in
transaction intensity and you don’t have any security constraints,
SQLite may be it is fit for production.
But… with SQLite you have poor support for concurrency, it’s no
scalable, you can’t secure it more than set permissions in your
directory, and since it is text-based, content is almost available to
anyone (by default).
Unfortunately my apps have low traffic, so postgresql was really
overkill.
Postgres isn't overkill. Juan Pablo is absolutely right: SQLite is
inappropriate for production. If you've been getting away with it till
now, consider yourself lucky -- and don't push your luck. Use a real
multiuser DB for production.
I'll report on this list if I run into any scaling problems, but I guess
I be fine with sqlite for a good time.
But... with SQLite you have poor support for concurrency, it's no
scalable, you can't secure it more than set permissions in your
directory, and since it is text-based, content is almost available to
anyone (by default).
I just remembered that MySQL uses MyISAM tables by default, and they
only support table locking. So it's basically the same concurrency
support as sqlite isn't it? So why would MyISAM be cool and sqlite not?
But... with SQLite you have poor support for concurrency, it's no
scalable, you can't secure it more than set permissions in your
directory, and since it is text-based, content is almost available to
anyone (by default).
I just remembered that MySQL uses MyISAM tables by default, and they
only support table locking. So it's basically the same concurrency
support as sqlite isn't it? So why would MyISAM be cool and sqlite not?
Who the hell uses MyISAM, except for special cases? Sure it's the
default, but even Rails' migrations build InnoDB tables unless otherwise
specified -- which, with MySQL, is the right thing to do.
But I'd really encourage you to use Postgres instead. At the cost of
*very slightly* more difficult setup, you get a *hugely* better DBMS.
But... with SQLite you have poor support for concurrency, it's no
scalable, you can't secure it more than set permissions in your
directory, and since it is text-based, content is almost available to
anyone (by default).
I just remembered that MySQL uses MyISAM tables by default, and they
only support table locking. So it's basically the same concurrency
support as sqlite isn't it? So why would MyISAM be cool and sqlite not?
Hey, one other bit to that answer. MyISAM locks the whole *table*. I
believe SQLite locks the whole *database*. SQLite is really, really not
meant for multiuser situations.
Well, for starters, that is why MySQL has two modes.
MyISAM is only for very special cases and it is not the Rails default
for MySQL.
Also, MyISAM locks a table, SQLite locks the whole file (hence, the
whole database).
InnoDB is the way to go if you’ll use MySQL.
Postgre is another fantastic choice.
And if you’re feeling adventurous, try something like CouchDB (although
is a DB for a very different target than the traditional RDBMS)
Don’t get me wrong, SQLite is awesome for development. I use it
everyday for my side projects and even I’ll start embedding an SQLite
database for a Gnome2-ruby application I’ll be developing soon, but
only for config data and stuff like that.
But, in case of Web Applications, I would rather go with something much
more elaborated than SQLite.
