Mysql notes
From Federal Burro of Information
Advice
Sean Hall with some really good MySQL advice:
- 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.
- Employ Master-Master Replication. Immediately have a read-only slave for your application to hit as well.
- Use Your Memory. Set innodb_buffer_pool_size, key_buffer_size and other key options.
- 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.
- 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.
More mysql advice: http://mysql.rjweb.org/doc.php/memory
Disk usage / Indexes v total
mysql> SELECT -> CONCAT(sum(ROUND(data_length / (1024 * 1024 * 1024), 2)), 'G') DATA, -> CONCAT(sum(ROUND(index_length / (1024 * 1024 * 1024),2)), 'G') INDEXES, -> CONCAT(sum(ROUND((data_length + index_length) / (1024 * 1024 * 1024), 2)), 'G') -> 'TOTAL SIZE' -> FROM information_schema.TABLES -> ORDER BY data_length + index_length; +-------+---------+------------+ | DATA | INDEXES | TOTAL SIZE | +-------+---------+------------+ | 1.22G | 0.66G | 1.87G | +-------+---------+------------+ 1 row in set (14.14 sec) mysql>