[root@etch171 ~]# mysqltuner.pl --host 10.XXX --port XXX --user xxx --pass xxx --forcemem >> MySQLTuner 1.2. - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Performing tests on 10.xxx:38xxx
[OK] Logged in using credentials passed on the command line
[--] Assuming MB of physical memory
[!!] Assuming MB of swap space (use --forceswap to specify) -------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.-log -------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 31M (Tables: )
[--] Data in InnoDB tables: 913G (Tables: )
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: )
[!!] Total fragmented tables: -------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned -------- Performance Metrics -------------------------------------------------
[--] Up for: 14d 1h 24m 13s (114M q [94.201 qps], 28M conn, TX: 441B, RX: 36B)
[--] Reads / Writes: % / %
[--] Total buffers: .1G global + 128.2M per thread ( max threads)
[!!] Maximum possible memory usage: .9G (% of installed RAM)
[OK] Slow queries: % (28K/114M)
[OK] Highest usage of available connections: % (/)
[OK] Key buffer size / total MyISAM indexes: 256.0M/10.6M
[OK] Key buffer hit rate: 100.0% (443K cached / reads)
[!!] Query cache efficiency: 8.3% (3M cached / 42M selects)
[!!] Query cache prunes per day:
[OK] Sorts requiring temporary tables: % ( temp sorts / 120K sorts)
[OK] Temporary tables created on disk: % ( on disk / 239K total)
[OK] Thread cache hit rate: % (2K created / 28M connections)
[OK] Table cache hit rate: % ( open / opened)
[OK] Open file limit used: % (/204K)
[OK] Table locks acquired immediately: % (43M immediate / 43M locks)
[!!] InnoDB data size / buffer pool: .8G/.0G -------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 2M, or use smaller result sets)
query_cache_size (> 124M)
innodb_buffer_pool_size (>= 913G)
本机
[root@typhoeus79 MySQLTuner-perl-master]# perl mysqltuner.pl --user root --pass c0BsZjR57MgAGOk6IWZAMarVVg0 --socket /data1/guosong/mysql_5580/tmp/mysql.sock >> MySQLTuner 1.2. - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Logged in using credentials passed on the command line -------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.-log
[OK] Operating on -bit architecture -------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 116K (Tables: )
[--] Data in InnoDB tables: 1G (Tables: )
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: )
[!!] Total fragmented tables: -------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned -------- Performance Metrics -------------------------------------------------
[--] Up for: 56d 14h 18m 18s (20K q [0.004 qps], 1K conn, TX: 886M, RX: 294M)
[--] Reads / Writes: % / %
[--] Total buffers: 696.0M global + 34.2M per thread ( max threads)
[OK] Maximum possible memory usage: .0G (% of installed RAM)
[OK] Slow queries: % (/20K)
[OK] Highest usage of available connections: % (/)
[OK] Key buffer size / total MyISAM indexes: 32.0M/.0K
[OK] Key buffer hit rate: 99.5% (4K cached / reads)
[!!] Query cache efficiency: 1.4% ( cached / 2K selects)
[OK] Query cache prunes per day:
[OK] Sorts requiring temporary tables: % ( temp sorts / sorts)
[OK] Temporary tables created on disk: % ( on disk / total)
[OK] Thread cache hit rate: % ( created / 1K connections)
[OK] Table cache hit rate: % ( open / opened)
[OK] Open file limit used: % (/8K)
[OK] Table locks acquired immediately: % (4K immediate / 4K locks)
[!!] InnoDB data size / buffer pool: .3G/512.0M -------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Variables to adjust:
query_cache_limit (> 2M, or use smaller result sets)
innodb_buffer_pool_size (>= 1G)
pt-variable-advisor
•# WARN innodb_flush_log_at_trx_commit-: InnoDB is not configured in strictly ACID mode.
•# NOTE innodb_max_dirty_pages_pct: The innodb_max_dirty_pages_pct is lower than the default.
•# NOTE log_warnings-: Log_warnings must be set greater than to log unusual events such as
aborted connections.
•# NOTE max_connect_errors: max_connect_errors should probably be set as large as your
platform allows.
•# WARN sync_binlog: Binary logging is enabled, but sync_binlog isn't configured so that every transaction is flushed to the binary log for durability.
根据需求自动生成配置