Well I was gonna do a huge commercial project with nearly 2,000 data
fields in PHP5 but the recent alliance between Zend Framework and Dojo
means the planned integration won't be available for a few months and
documentation is non-existent about how to use them together... So
guess what? I'm learning Rails on a 2 foot screen iMac running OSX
10.5 and it's a nice environment to code in :o) Anyways...
1. Does anyone have experience running mysql and sqlite3 in high
traffic production environments and know from experience which is more
dependable?
2. The advantages to using sqlite3 because it is the default
environment are many, but aside from that, is one any better or more
dependable than the other?
DC, you've opened up a can of worms. A lot of people will argue
MySQL's better than PostgreSQL and vice-versa. Prepare for a long,
daunting flamewar.
Hm...let's see if we can nip it in the bud...come on y'all, let's be
like Barney Fife!
Anyway.
SQLite is a decent database for embedded applications and development.
_Possibly_ for production if your traffic is low and your storage needs
are minimal. But if you plan to have a lot of data and/or a lot of
traffic, look elsewhere. Both PostgreSQL and MySQL are fine databases.
They can both be tuned to run at about the same level of performance.
Which one is better is, for all intents and purposes here, a matter of
opinion. There might be some technical reasons why one is better than
the other in this or that area, but unless you are a database engine
geek, it probably won't matter to you. Shoot, you don't have to choose
either one of those if you don't want to. There was a post just the
other day about another Rails database adapter for MonetDB (or something
like that), and if the numbers on the web site are accurate, it beats
MySQL and PostgreSQL both, hands down. Didn't Oracle release an
"express" version of their database a year or two ago? You might even be
able to use that as I believe there is an oracle adapter somewhere. The
point is that all of these databases are going to be sufficient. Which
one you choose depends on what you are doing. I use PostgreSQL, mainly
because my primary client uses it and I don't want two database engines
running at the same time. So I do everything on PostgreSQL. Could I use
MySQL? Sure. As of right now, I'm not making use of anything Postgres
specific (I don't think).
Well sqlite3 is really meant for development. For production your best
off with either postgresql or mysql. The only real benefit from
postgresql is the fact that it comes with a gui, where as mysql
database have to be created through command line unless your using
netbeans or have a gui for mysql.
SQLite is definitely suitable for production servers.
The practical constraint is the amount of concurrency for updates.
Problem with traffic is that SQLite stores everything in a single file
and writing needs a lock.