Linux is not Matrix——mysql优化脚本

   最近因为莫名奇妙的原因，我们的访问页面cas经常的会反映缓慢，然而日志里面一片祥和，目前我也没有很好的方案，所以只能说根据经验来找一下原因。

   我估计得原因有两个，一方面是数据库，一方面是tomcat。这篇博客主要写一下对数据库的参数设定方案。

   在1个月之前，我也遇到了这种情况，当时我上网查到的资料有一种说法是在mysql的配置文件中加入一句

   --skip-name-resolve

   这句话的意思是让mysql跳过域名解析，不用DNS查找主机名的方式来允许远程连接。也就是只能使用IP地址来连接，后来我查到了一些具体的解释。

   当新的客户连接mysqld时，mysqld创建一个新的线程来处理请求。该线程先检查是否主机名在主机名缓存中。如果不在，线程试图解析主机名：如果操作系统支持线程安全gethostbyaddr_r ()和gethostbyname_r()调用，线程使用它们来执行主机名解析。如果操作系统不支持线程安全调用，线程锁定一个互斥体并调用gethostbyaddr()和gethostbyname()。在这种情况下，在第1个线程解锁互斥体前，没有其它线程可以解析不在主机名缓存中的主机名。

   这样看来，这个选项对性能的影响取决于DNS服务器的响应速度，当然像我们这种完全使用IP的情况，纯粹是浪费时间。

   大概10天之前又开始出现访问缓慢的问题了，而且我重启了一次mysqld服务后，访问缓慢的现象消失了，所以我才有了对数据库的怀疑。

   基于优化mysql的需求，我就找到了一个挺好的检测脚本，tuning-primer。

   首先下载这个最新版的脚本。

wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh

   下载之后直接授权，执行就可以了。中间会遇到输入用户名密码什么的，大家英文这么好，应该不是问题。我就直接给大家看看运行结果吧。

	-- MYSQL PERFORMANCE TUNING PRIMER --
	     - By: Matthew Montgomery -

MySQL Version 5.1.57-log x86_64

Uptime = 0 days 23 hrs 29 min 50 sec
Avg. qps = 8
Total Questions = 721699
Threads Connected = 81

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 1.000000 sec.
You have 41051 out of 721720 that take longer than 1.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/5.1/en/purge-master-logs.html
Binlog sync is not enabled, you could loose binlog records during a server crash

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 0
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 82
Historic max_used_connections = 179
The number of used connections is 35% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 12 M
Current InnoDB data space = 28 M
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 8 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 2.58 G
Configured Max Per-thread Buffers : 6.05 G
Configured Max Global Buffers : 426 M
Configured Max Memory Limit : 6.47 G
Physical Memory : 62.90 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 21 M
Current key_buffer_size = 384 M
Key cache miss rate is 1 : 134
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 4 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 15.34 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 8 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 366 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 2558 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 1024 tables
Current table_definition_cache = 256 tables
You have a total of 1183 tables
You have 1024 open tables.
Current table_cache hit rate is 12%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 76066 temp tables, 8% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 90 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 725548
Your table locking seems to be fine

   在xshell里面，这个都是有颜色的，绿色表示没什么问题，红色表示可以优化。我根据上次给我的提示扩大了table_cache的大小，然后访问缓慢的问题有大概一周没有出现过了。然而现在又出现了。所以我开始怀疑tomcat本身是否没有配置到最优。