Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
results

EXPLAIN

mysql> explain SELECT * FROM `submissions` WHERE `submissions`.`id` = 5334;
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | submissions | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)

free -m

$ free -m
             total       used       free     shared    buffers     cached
Mem:          7680       7557        123          0        728        864
-/+ buffers/cache:       5964       1715
Swap:        30725          0      30725

mysqltuner

$ perl mysqltuner.pl 

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: <redacted>
Please enter your MySQL administrative password: 

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in InnoDB tables: 122M (Tables: 69)
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 154d 13h 15m 19s (269M q [20.158 qps], 941K conn, TX: 1055B, RX: 28B)
[--] Reads / Writes: 89% / 11%
[--] Total buffers: 5.6G global + 4.4M per thread (300 max threads)
[!!] Maximum possible memory usage: 6.9G (91% of installed RAM)
[OK] Slow queries: 0% (7K/269M)
[OK] Highest usage of available connections: 15% (46/300)
[OK] Key buffer size / total MyISAM indexes: 32.0M/21.0K
[OK] Key buffer hit rate: 99.7% (202M cached / 643K reads)
[OK] Query cache efficiency: 86.1% (202M cached / 234M selects)
[!!] Query cache prunes per day: 43833
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 4M sorts)
[!!] Joins performed without indexes: 368331
[!!] Temporary tables created on disk: 42% (7M on disk / 17M total)
[OK] Thread cache hit rate: 99% (46 created / 941K connections)
[!!] Table cache hit rate: 0% (89 open / 46K opened)
[OK] Open file limit used: 0% (15/2K)
[OK] Table locks acquired immediately: 99% (40M immediate / 40M locks)
[OK] InnoDB data size / buffer pool: 122.6M/5.5G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    long_query_time (<= 10)
    query_cache_size (> 4M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    table_cache (> 1024)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment