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_64Uptime = 0 days 23 hrs 29 min 50 secAvg. qps = 8Total Questions = 721699Threads Connected = 81Warning: Server has not been running for at least 48hrs.It may not be safe to use these recommendationsTo find out more information on how each of theseruntime variables effects performance visit:http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.htmlVisit http://www.mysql.com/products/enterprise/advisors.htmlfor info about MySQL's Enterprise Monitoring and Advisory ServiceSLOW QUERIESThe 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 completeYour long_query_time seems to be fineBINARY UPDATE LOGThe binary update log is enabledThe expire_logs_days is not set.The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manuallySetting expire_logs_days will allow you to remove old binary logs automaticallySee http://dev.mysql.com/doc/refman/5.1/en/purge-master-logs.htmlBinlog sync is not enabled, you could loose binlog records during a server crashWORKER THREADSCurrent thread_cache_size = 8Current threads_cached = 0Current threads_per_sec = 0Historic threads_per_sec = 0Your thread_cache_size is fineMAX CONNECTIONSCurrent max_connections = 500Current threads_connected = 82Historic max_used_connections = 179The number of used connections is 35% of the configured maximum.Your max_connections variable seems to be fine.INNODB STATUSCurrent InnoDB index space = 12 MCurrent InnoDB data space = 28 MCurrent InnoDB buffer pool free = 0 %Current innodb_buffer_pool_size = 8 MDepending on how much space your innodb indexes take up it may be safeto increase this value to up to 2 / 3 of total system memoryMEMORY USAGEMax Memory Ever Allocated : 2.58 GConfigured Max Per-thread Buffers : 6.05 GConfigured Max Global Buffers : 426 MConfigured Max Memory Limit : 6.47 GPhysical Memory : 62.90 GMax memory limit seem to be within acceptable normsKEY BUFFERCurrent MyISAM index space = 21 MCurrent key_buffer_size = 384 MKey cache miss rate is 1 : 134Key buffer free ratio = 81 %Your key_buffer_size seems to be fineQUERY CACHEQuery cache is enabledCurrent query_cache_size = 32 MCurrent query_cache_used = 4 MCurrent query_cache_limit = 1 MCurrent Query cache Memory fill ratio = 15.34 %Current query_cache_min_res_unit = 4 KYour query_cache_size seems to be too high.Perhaps you can use these resources elsewhereMySQL won't cache query results that are larger than query_cache_limit in sizeSORT OPERATIONSCurrent sort_buffer_size = 2 MCurrent read_rnd_buffer_size = 8 MSort buffer seems to be fineJOINSCurrent join_buffer_size = 132.00 KYou have had 366 queries where a join could not use an index properlyYou 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 yourjoin_buffer_size to accommodate larger joins in one pass.Note! This script will still suggest raising the join_buffer_size whenANY joins not using indexes are found.OPEN FILES LIMITCurrent open_files_limit = 2558 filesThe open_files_limit should typically be set to at least 2x-3xthat of table_cache if you have heavy MyISAM usage.Your open_files_limit value seems to be fineTABLE CACHECurrent table_open_cache = 1024 tablesCurrent table_definition_cache = 256 tablesYou have a total of 1183 tablesYou have 1024 open tables.Current table_cache hit rate is 12%, while 100% of your table cache is in useYou should probably increase your table_cacheYou should probably increase your table_definition_cache value.TEMP TABLESCurrent max_heap_table_size = 16 MCurrent tmp_table_size = 16 MOf 76066 temp tables, 8% were created on diskCreated disk tmp tables ratio seems fineTABLE SCANSCurrent read_buffer_size = 2 MCurrent table scan ratio = 90 : 1read_buffer_size seems to be fineTABLE LOCKINGCurrent Lock Wait ratio = 0 : 725548Your table locking seems to be fine

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