Asinine MySQL configuration

[guero61]

Odd question that some of you may enjoy answering about a system destined to provide a *very* large database server.

I've recently acquired control of a very... *large* server. I'll withhold details for the moment, but suffice it to say that things are pretty unlimited - the typically supplied "my-huge.cnf' doesn't even come close to scratching the surface. Although I'm quite familiar with kernel & hardware tuning, I'm interested in seeing what the beast can do with MySQL. My question is this: what bits of the MySQL configuration scale linearly, and which don't? Are there any pitfalls or undocumented ceilings?

[guero61]

Okay - specs:
- 8x P-III Xeon 900/100/2
- 24GB RAM
- All the SCSI/AoE storage I can throw at it

[Corona688]

I've been helping prod a pretty big MySQL database/server into cooperating, though not THAT big. The database, a fairly ginormous forum, was about 1GB in size, and the fulltext index another gig or two. Two gigs of ram, dual Opteron processors, SCSI disks.

Beware queries with "GROUP BY" or "ORDER BY" clauses in them. The database has to get all the results, store them in a temporary table, sort them, THEN give them to you. LIMIT 10 or whatever does nothing since it can't know what the first 10 are before sorting. You never notice the overhead on small tables, but when the result set gets big, WHAMN.

Writes can back up behind slow selects. The worst bit is that if there are writes in the queue, MySQL will wait for the writes to finish before doing more reads even if the writes aren't writing yet, and even though the reads could be happening simultaneously with that big select. --low-priority-updates will prevent writes from corking the queue like that, but then, endless reads could prevent writes from happening.
_________________
Petition for Better 64-bit ATI Drivers - Sign Here
http://www.petitiononline.com/atipet/petition.html