I was configuring our new master DB server today, to try and tweak it as much as I can for MySQL performance. Now obviously the more powerful hardware helps, but I think these settings I found are pure gold ;p
The machines:
DB1 (new one) = Quad-Xeon 3.2 Ghz w/ 4G ram
DB2 (demo one) = Dual-Xeon 1.8 Ghz w/ 3G ram
Checkout some of these results:
1 - Very complex join query, using subqueries and whatnot through 15-20 tables each containing 1-2 million records
DB1 = 0.57 seconds
DB2 = 38.02 seconds
2 - OPTIMIZE TABLE command on each table in the database (140 tables)
DB1 = 29 mins
DB2 = 5 hours, 16 mins
3 - Complete import of 10 gig database
DB1 = 17 mins
DB2 = 2 hours 34 mins
I've tried many other types of queries, counts on huge tables, etc...and have found the new server handles things 20,50 or even 100x faster !!!!!!!!!
I can only imagine a nice Cluster of identical machines like this....that should scale to 100 million users (well by which time something better should be out heh)
