Scaling up with MySQL

rkcth

We are currently in the design phase for our web site. We will be using a system of dynamic web pages, and we will be running an pop-up ad campaign (I know, i hate them too.) So far, we are thinking of using a SAN and a bunch of load balanced database servers. I am aware that Oracle will allow me to use this configuration (we need speed, and expandability, thus we need to be able to add machines to increase total back end capabilities.) I, however, am a fan of MySQL, all of our code is written for MySQL, and lastly Oracle costs a lot. We have been trying and trying to figure out a way to use MySQL. I suggested using an extremely powerful Sun system with 12 processors and 16 gigs of ram. The only problem is, I don't know how well MySQL runs on Solaris, AND i flat out don't know if we need that much power. I am a bit of a newbie when it comes to guesstimating the amount of power our servers will need. If there is any information you can give me on how to create a load balancing cluster w/ MySQL, or how ram/processor speed/number of processors/number of HD's in disk array will effect the performance, or any information on how well MySQL runs on solaris, I would greatly appreciate it. I hope i Have been clear, the problem is complex, and I am not very good w/ terminology, so I hope you will be able to help me. Thank you very much for your time.
Sincerely Robert Hostetter

[deleted]

a 12 cpu machine with 16 gigs of ram? Are you planning to take over the world? :-)

First off, get yourself a decent estimate of the popularity of your website.
It's good to think of scalability, and you shouldn't underestimate the power you are going to need, but you should also not underestimate the power that a 'simple' machine can supply.

A 12 CPU sun with 16G of ram (that's just over 1GB per CPU, not a lot for a database) will set you back more than you make in a year, and the only advantage is that SUN hardware hardly ever fails and SUN has a very good support contract capability.

If you want something scalable, you might consider using Intel based linux servers. They cost a fraction of what that SUN machine would cost, and you can add more machines as required. Those machines (penguin power for ex) come with 1-2-4 CPU's, several Gigs of ram, scsi/ide disks, and are 1u-2u high.
Perfect for creating a rackmount serverpool/cluster.
So you can start with a dead-cheap single-server, and add servers as you go.
If it turns out that 4 cpu's was all you need, you don't have to buy more.
ONce you bought that SUN, you're stuck with it. 🙂

What kind of database operations will you be doing on each page?
If they are only simple selects, you'll get away with mysql on a low spec machine.
If you plan to do a lot of inserts too, then choose postgres. MySQL's horrible table-level-locking will do you in. (every insert locks the whole table, everything stops for that table)
If you need real power, you'll have to fork some dough and get Informix. Informix is expensive, but cheaper than oracle. (and IBM did not buy Informix for nothing, they knew Informix can do things DB2 would never be able to do)
Informix also supports database fragmentation, CPU affinity for the connection server and the engine itself (seperate dedicated CPU's for connectionbuilding and for the engine itself) Logical and physical logs, automated backups, incremental or full, and a complete set of SQL commands.

And if you are planning to grow big fast, you'll need data-consistency, the word that MySQL's creators fear most. (there's even a article in the MySQL manual that says that foreign keys are a bad thing giggle)

Buttttt... the fact that your site is dynamic does not mean that everything must be generated realtime. You can get huge performance boosts by 'rendering' (or caching) those parts of the pages that don't change much. For example, a list of news headlines only changes when a new headline arrives. So it's much faster to generate a static piece of HTML to represent the headlines, and generate a new piece of 'headlines' HTML every time a new headline arrives.
That saves you hundreds of database queries.

rkcth

If i would use a cluster of linux machines, using postgre or informix, and a NAS, would that get me into trouble? I know oracle can do it w/ real application clustering. I just got back a quote from some guy who wants to set up the hardware for us. He wants to set us up w/ a 110,00 dollar SUN 4500 machine w/ 4 CPUs. Then he says to fork out 480,000 dollars for the database. That is ridiculous. I'd prefer to buy 5 suns and use a free database🙂 Anyway, if I used informix or MySQL and store the info on a NAS, would I be able to use a cluster? I wish to avoid oracle like the plague. I hate software that is so expensive, AND i will have to learn how to use it. I love open source, and if there is any way to use it, I want to. Thanks for your time.
Robert Hostetter

[deleted]

Be Very carefull and picky about licencing. Orcale for example has a pricing policy that is based on the MHz of the CPU that you want to run it on. The faster your CPU, the more expensive the licence. Talk about retarded.

Anyway, I think you should really spend some more time finding out just how much power you really need. guestimating will not get you the right answers.
I'd advise you to talk to some Informix people, tell them what you want to do and how they can help, you'll soon get a clear picture.

BTW, I don't think you can use NAS here, because Postgres uses locked files, and Informix prefers to use raw-devices.

rkcth

Thats the problem, I don't know HOW to estimate it out. I can estimate MAX and avg queries per second, but honestly, I have no idea how much hardware it takes to do a Medium amount of work. We are going to be running an advertising system off of this, so we are looking off at STARTING w/ 20,000,000 impressions a day, which is prolly almost 200,000,000 queries. The database Hardware will need to be powerful, but I REALLY want to avoid oracle/Anyone elses, high licenses. It just isn't fiscally responsible. Thank you for your time.
Robert