mysql vs sqlite3... which is better for production?

Hi Everybody!

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?

Thanks for your input! DC

Since you mentioned your project's going to be using around 2,000 data fields, wouldn't it be better if you use PosgreSQL?

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.

Ryan Bigg wrote:

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

Peace, Phillip

I'm currently indexing all of the #rubyonrails logs from circa April 2004 in MySQL. I'm using it because thinking_sphinx "Just Works" (tm) on it.

  It really depends on what you feel more comfortable with.

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.