The 5 Biggest Ways to Boost MySQL Scalability

Sean Hall with some really good MySQL advice: 

1. Tune those queries. Biggest bang for your buck. Enable the slow query log and watch it. Once you've found a heavy resource intensive query, optimize it! Tune what receives real-world traffic. 
2. Employ Master-Master Replication. Immediately have a read-only slave for your application to hit as well.
3. Use Your Memory. Set innodb_buffer_pool_size, key_buffer_size and other key options.
4. RAID Your Disk I/O. Use RAID 10 mirroring and striping. On EC2 striping across a number of EBS volumes using the Linux md software raid.
5. Tune Key Parameters. speeds up inserts & updates with innodb_flush_log_at_trx_commit=2; create a tablespace and underlying datafile for each table with innodb_file_per_table.

Full details at the original article.  

Comments

[Kiril Varbanov]

I would add innodb_thread_concurrency to this list - very important one, the default I believe is set to 8, however, on a reasonably powerful machine you can just set it to 0, and leave MySQL to take care of the optimization internally.

[Lennaert Ekelmans]

I can also recommend an analyze tool https://launchpad.net/mysql-tuning-primer

This tool will take a look at your current configuration and gives recommendations.

[Andy]

It's not responsible to advice using innodb_flush_log_at_trx_commit=2 without the warning that you WILL LOSE YOUR DATA if the server crash!

If you don't want to lose any data, do NOT use innodb_flush_log_at_trx_commit=2

[Doron Levari]

Disclaimer: I work for Scalebase.
We are talking MySQL scalability here...

Too many data, too many users... After all optimizations, performance will still be going down... Time to scale.

Scale up is 1 thing. By more CPU, RAM, SSD, (or any other 3 letters... ;) ). Eventually it'll get you so far... Then you'll eventually have to scale out.

Scale out? No easy way... Replication and query offload to the replica slaves will scale your reads but the only way to scale 100% of the app, reads and writes is sharding. Splitting the data, the users, everything. Real scale out. There are solutions to automatically split your data, and then stand in front and re-glue it for the application, for management, for maintenance. Great performance, real scale-out, 0 effort, totally transparent. Scalebase is one of those solutions, try us out.