Mysql notes: Difference between revisions

From Federal Burro of Information
Jump to navigationJump to search
No edit summary
No edit summary
Line 1: Line 1:
=== Advice ===
Sean Hall with some really good MySQL advice:  
Sean Hall with some really good MySQL advice:  


Line 8: Line 11:


More mysql advice: http://mysql.rjweb.org/doc.php/memory
More mysql advice: http://mysql.rjweb.org/doc.php/memory
=== Disk usage / Indexes v total ===
<pre>
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>
</pre>

Revision as of 23:43, 28 November 2014

Advice

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.

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>