In the next weeks I’ll move my webproject from a rather small server to a big one. The specs:
AMD Athlon 64 X2 6000+ Dual Core
8 GB DDR2 RAM
2 x 750 GB SATA II (Hardware RAID1)
1 GBit OnBoard Network
I read some tutorials and blog posts about configuring mysql, especially innodb, but most of them are focused on MySQL-only servers (e.g. “use 80% of your memory for innodb_buffer_size” and so on). I’ve to run MySQL 5, Apache, PHP 5 etc. on the same server, so these recommendations doesn’t really fit to my new server.
So what do you suggest are good values for the most important mysql configuration settings? It’s for a community website with heavy database use and a lot of traffic. The main databases are running with InnoDB, only databases for third-party-scripts (like a blog system) use MyISAM.
I would say your best bet is to look at your current server, analyze the load, find your points of contention, and work from there.
Take a look at your current memory utilization, decide which resources are starving for memory, and then increase your settings accordingly. If it looks like apache and php aren’t starving for RAM, then it’s a good bet you can add most of your extra RAM to the InnoDB buffer pool. Of course, if you’re current buffer pool is under utilized, the extra RAM isn’t going to be needed there either.
If you have the luxury, you can build your new box up and load test it (maybe use apache bench) and tweak your settings before you move it into production.
Oh, and if you can change your config at this point, it’d probably be better to get 6 x 250GB SATA IIs and put them in RAID10.