服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - mysql性能优化脚本mysqltuner.pl使用介绍

mysql性能优化脚本mysqltuner.pl使用介绍

2019-12-19 14:21MYSQL教程网 Mysql

无意中发现了,major哥们开发的一个性能分析脚本,很有意思,可以通过这个脚本学学他的思想

MySQLTuner 下载地址:http://github.com/rackerhacker/MySQLTuner-perl

MySQLTuner 使用方法: 
Downloading and using MySQLTuner is actually a very simple process:

wget mysqltuner.pl 
perl mysqltuner.pl

If you'd rather not invoke perl every time, just make it executable:

chmod u+x mysqltuner.pl 
./mysqltuner.pl

» Latest development version 
If you want to get the bleeding edge commits, you can check out the latest code with git:

git clone git://github.com/rackerhacker/MySQLTuner-perl.git

看看我的运行结果

 

复制代码代码如下:


>> MySQLTuner 1.1.1 - Major Hayden 
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/ 
>> Run with '--help' for additional options and output filtering

 

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

-------- Storage Engine Statistics ------------------------------------------- 
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 889M (Tables: 479) 
[--] Data in MEMORY tables: 1M (Tables: 4) 
[!!] Total fragmented tables: 29

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

-------- Performance Metrics ------------------------------------------------- 
[--] Up for: 12h 55m 27s (4M q [105.230 qps], 295K conn, TX: 9B, RX: 546M) 
[--] Reads / Writes: 82% / 18% 
[--] Total buffers: 432.0M global + 6.3M per thread (500 max threads) 
[!!] Maximum possible memory usage: 3.5G (175% of installed RAM) 
[OK] Slow queries: 0% (48K/4M) 
[OK] Highest usage of available connections: 3% (16/500) 
[OK] Key buffer size / total MyISAM indexes: 384.0M/332.6M 
[OK] Key buffer hit rate: 100.0% (2B cached / 205K reads) 
[OK] Query cache efficiency: 49.3% (1M cached / 3M selects) 
[!!] Query cache prunes per day: 268042 
[OK] Sorts requiring temporary tables: 0% (10 temp sorts / 122K sorts) 
[OK] Temporary tables created on disk: 19% (4K on disk / 22K total) 
[OK] Thread cache hit rate: 99% (16 created / 295K connections) 
[!!] Table cache hit rate: 18% (256 open / 1K opened) 
[OK] Open file limit used: 19% (497/2K) 
[!!] Table locks acquired immediately: 88%

-------- Recommendations ----------------------------------------------------- 
General recommendations: 
Run OPTIMIZE TABLE to defragment tables for better performance 
MySQL started within last 24 hours - recommendations may be inaccurate 
Reduce your overall MySQL memory footprint for system stability 
Increase table_cache gradually to avoid file descriptor limits 
Optimize queries and/or use InnoDB to reduce lock wait 
Variables to adjust: 
*** MySQL's maximum memory usage is dangerously high *** 
*** Add RAM before increasing MySQL buffer variables *** 
query_cache_size (> 32M) 
table_cache (> 256)

 

延伸 · 阅读

精彩推荐