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
total used free shared buffers cached
Mem: 7680 7557 123 0 728 864
-/+ buffers/cache: 5964 1715
Swap: 30725 0 30725
$ 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)
