Hello all,
Please read the first couple of paragraphs. It's background for my questions, which are host-related.
I have been experimenting with MySQL and PHP for the last couple of months. I'm creating a database that will be the core of an interactive educational web archive.
Lately, I've come across quite a few articles at places like OpenACS (http://openacs.org/philosophy/why-not-mysql.html) and PHP Builder (sorry, can't find the specific URL now) that claim MySQL isn't ready for primetime. Specifically, they say that it doesn't scale well, can't support many concurrent users, and doesn't have good recovery for failed SQL operations. This concerns me, as the db will _be_ my business, and it will pretty much require large numbers of concurrent users.

My questions:

1. What are your experiences with high-traffic MySQL sites? Should I be worried about this?

2. If I switch to PostgreSQL, can anyone recommend some good ISP's who support it?

Thanks in advance,
Chris McCormick
Lead developer
The Athenaeum

Chris,

I think the answer is "it depends". Can you tell us more about your application/site? Do people mostly read from the database and you update or will they be updating also?

Frank

Ok, here is the story:

I am a programmer with about 3-4 years experience in HTML, Javascript, Access DB programming, and ASP. In the last few months, I've moved over to PHP/MySQL.

The database (which I am currently designing, along with its web interface) will be a community-based, user-built history and art resource.

Users will be given accounts based on their expertise, and then they can add (historical) people to the database. For each person, they will be able to add events, art works, text of publications, images, and links to other people or events in the database. After a while, this will hopefully create kind of a vast, user moderated encyclopedia. Sort of like a mix between Encarta and Slashdot. (Shh! Don't tell anyone! ;) )

Each person will also have a profile with their own most recent actions, notes on any of the nodes, saved db searches, etc.

In other words, I expect the vast majority of the activity on this db to be actual additions, changes, and updates. The evolution itself is what will make the site useful.

Who knows where it will go. I have a lot to learn. But if it should turn into the kind of thing I envision, it would have to be rock solid under heavy amounts of db changes, not just read-access.

Also, I will have to learn more about *nix based systems. Currently, I'm testing the scripts on NT/IIS, but I am aware that an eventual large-scale solution would probably sit on Linux, Cobalt, or Free-BSD.

Thanks again. It's tough to find info on this stuff.

- Chris

mySQL is okay, but it doesn't have table locking. So if you do a series of steps to the database and one of them fails it will still write the rest of the tables, which isn't good for certian things. I would definitely NOT use it with anything involving credit cards. Most hosts who store credit cards in database or use billing software, don't have their mySQL locked down right...

Well first thing, I would suggest you look at a database abstraction like ADODB (http://php.weblogs.com/adodb). With abstraction will will make switching RDBMS's a bit easier (if you need to).

In the environment you described, there are two problems with MySQL:

With the current release version of MySQL when you insert/update a table, the whole table is locked. This can cause a lot of slowdowns (problems) on a large active database. I've read stuff about MySQL with row level locking, but I do not believe that is available yet.

MySQL does not have transactions. A transaction is a database "thingy" that allows multiple inserts/updates from one user to all either be written or backed out of the database.

Having said (and prob. scared you) this forum and 1000's of other forums like it run on MySQL. I have not heard of people having lots of problems with MySQL until they get 1000's of concurrent users etc.

Hopefully this help a little :)

Frank

fracture, in your situation I think using either will not make much of a difference. It all pretty much would boil down to which ever one you find easier or more availible to use.

Also I know that Can-Host (http://www.can-host.com) supports both mySQL and PostgreSQL.

but it doesn't have table locking. So if you do a series of steps to the database and one of them fails it will still write the rest of the tables

Yes, it does have table locking. But I don't think that's really what you mean. If you're doing a sequence of steps and one has an error you get an error return code and you can stop performing the rest of the steps. I still don't think that's really what you mean though. You're probably thinking of transactions.

MySQL does not have transactions

Yes it does. It didn't before but it does now.

I do believe you are correct that MySQL only does table level locking and not row level locking. That could cause performance problems with a lot of concurrent users. It wouldn't be too hard to architect around this however. You could stage all the inserts into a different table and do a nightly update of the live data.

fracture - you might want to take a look at the site in my profile. It's a lot like what you're describing though it's for a different content area. I don't know what you consider a lot of users. Currently I'm doing 50 - 90K page views a day. I have over 750 editor and close to 10K registered users. I think you overestimate the amount of update traffic. I'd estimate I have over 250K montly uniques. That means only 1 in 25 visitors is even registered and able to do updates and less than 10% of those are editors who make do most of the updates.

I don't think I have more than 10 users updating the site an any given time. With web stuff that means I rarely if ever have multiple updates to the database at the same time. Usually when I check it's at most 5 active queries of all types (including updates).

We use MySQL as the back end for DreamBook.com, which has about 550,000 registered users. The management area and guestbook signing scripts are in mod_perl and connect to two different dbs on two different servers. It's pretty fast really, and there are a lot of inserts (everytime somebody signs a book). We did break the entries table into 36 different tables based on the first letter of their username though. You can do things like that with your code to get around the limitations of MySQL.

There have been a few problems with tables getting corrupted, but if you keep backups you should be able to restore them when it happens. Overall the system isn't too complicated, and doesn't have really sensitive data, being a free service. So it's not really the end of the world if some info gets out of sync. Really it doesn't though.

We've started adding features and recoding stuff in the last few months, but other than that we've left the system virtually untouched for over 2 years and haven't really had any trouble with the steady growth of the database..

There has been an interesting thread about the lacks of MySQL on vbulletin.com forums : http://www.vbulletin.com/forum/showthread.php?s=&threadid=21049