Some MySQL Questions

nigeledge

I'm working on a new project. I've used mysql in the past for some small projects, but nothing like what I'm currently doing.

I'm building a directory service which already has 30K members. It will grow to a little over 2 million users within 6 months when the company is merged with another company.

We're talking some decent load here.

Is mySQL the best db to use for this? I've never seen mySQL handle load like that. My gut tells me to go with Oracle.

Could someone point me to some benchmark tests or maybe provide some some advice based on their own experiences with mySQL and this type of load?

Also, if you think mySQL can hack it, do you have any performance tuning tips? (I know I can look at the manual... but I'm looking for some real world experience here).

Suggestions/comments?

Thanks in advance.

jerdo

I don't know if I personally would use MySQL to handle 2 million users. If each of these users will have their own information in the db, you might start hitting MySQL table limits. Oracle could probably handle it but it's a bit costly. For that many PostgreSQL is probably a better choice than MySQL and it's still free. Of course you will have to take in to effect memory and cpu usage for connections (don't try and run this db on a Pentium 2 with 256 megs of RAM).

Sxooter

It's not just about how many records you're gonna have, it's about your mix of read versus writes, and how many reads or writes per second you'll need to sustain.

2 million records is a walk in the park for any database, as long as their fairly small records.

You just need to tell us more of what you're trying to do, but I'd recommend Postgresql over MySQL for high parallel load, especially if you'll be mixing in a lot of writes with your reads.

nigeledge

The records will be fairly small. And I'm definitely looking at a lot more reads than writes. The data will initally be loaded into the db via a script. After that, I'm looking at about 100K - 200K queries per day.

The data is owned by the organization, so writes will only be performed at scheduled intervals using a batch update script, likely during non-peak traffic times.

Sxooter

Then MySQL should be a good match for what you're doing, as long as it can handle the queries your users will need to throw at it (i.e. if you need fancy sql you'll need to look elsewhere.)y