mysql进阶1
关于mysql调优通常有三种方法：
1.替换有问题的硬件。
2.对mysqld进程调优。
3.对查询进行优化。
我们本章只对mysql进程调优。
一.记录慢速查询：
- /etc/my.cnf增加如下内容:
(注意对应目录的权限与db用户,
建议:
[root@user61 lib]# chown -R root:root mysql
[root@user61 lib]# chown -R 777 mysql
log_slow_queries =ON
#log-slow-queries = /var/lib/mysql/slow-queries.log
log-slow_queries = /var/lib/mysql/slow-queries.log
long_query_time = 3
log-queries-not-using-indexes
log-slow-admin-statements
log-error=/var/lib/mysql/error.log
log=/var/lib/mysql/mysql.log
可以用多种工具：mysqldumpslow, myprofi,mysqlsla
二.查询进行缓存
query_cache_size=32M 添加到/etc/my.conf中可以启用32MB查询缓存。
一下解释：Qcache_free_blocks: 缓存中相邻内存的个数。数目大说明可能有碎片。
qcache_free_memory:缓存中的空闲内存。
qcache_hits:每次查询在缓存中命中时就增大。
qcache_inserts:每次插入一个时就增大。上表缓存命中为：0，命中次数除以插入次数就是不命中比率，1-这个值就是命中率
qcache_lowmem_prunes:缓存出现内存不足并且必须清理以便为更多查询提供空间的次数，如果这个数字在不断增长，可能碎片非常严重,或内存很少（free_blocks,free_memory可以判断）
qcache_not_cached:不适合进行缓存的查询的数量（通常不是select语句）
qcache_queries_in_cache:当前缓存的查询（和响应）的数量；
qcache_total_blocks:缓存中块的数量。
注意：如果flush query cache占用了很长时间，就说明缓存大了。
三. 强制限制
在my.conf中添加：
set-variable=max_connections=500 (1)
set-variable=wait_time=10 (2)
max_connect_errors=100 (3)
(1)服务器允许客户端的连接数,可以查询目前建立过最大连接数为：show status like 'max_used_connections'
(2)意味着mysqld终止所有空闲超过10秒的连接。
(3)如果clinet连接mysql100次不成功，mysql将锁定clinet直到flush hosts之后才运行。
四.缓存
4.1缓存表：
mysql客队表进行缓存： /etc/mysqld.conf中的table_cache指定
mysql> show status like 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 8 |
| Opened_tables | 0 |
+---------------+-------+
2 rows in set (0.00 sec)
说明：目前已打开8张表，有0张需要打开。如果show status命令快速增加，说明缓存命中率不够,如果open_tables比table_cache设置小很多，就说明值太大了。
4.2缓存线程
mysql> show status like 'threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 2 | (1)
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
(1)说明每次mysqld需要创建一个新线程，此值都会增加，show status 命令发现次值快速增加，
可作如下操作： my.cnf中使用thread_cache=40来实现。
4.2磁盘请求
mysql> show status like '%key_read%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Key_read_requests | 0 | (1)
| Key_reads | 0 | (2)
+-------------------+-------+
2 rows in set (0.00 sec)
说明：
(2) 代表命中磁盘的请求个数。
(1)磁盘的请求个数的总数。
公式1=(2)/(1)为不命中录。
公式2=(1-公式1)为命中录。
如果每1000个请求中命中磁盘的数目超过1个，则应增大缓冲了，如：key_buffer=38MB
4.3 临时表的使用：
mysql> show status like 'created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 | (1)
| Created_tmp_files | 0 | (2)
| Created_tmp_tables | 0 | (3)
+-------------------------+-------+
3 rows in set (0.00 sec)
说明：
(3)每次使用临时表都会增大created_tmp_disk_tables
(2)基于磁盘的表也会增大created_tmp_disk_tables
可以控制: my.cnf中对两值进行设置
4.4 显示排序统计信息： (统计排序时的磁盘活动)
mysql> show status like 'sort%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 | (1)
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
+-------------------+-------+
4 rows in set (0.00 sec)
说明：
(1)如果sort_merge_passes状态变量很大，指示了磁盘的活动情况。须注意sort_buffer_size,my.cnf可设置
4.5表扫描比录:
mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 0 | (1)
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'handler_read_rnd_next';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_rnd_next | 0 | (2)
+-----------------------+-------+
1 row in set (0.00 sec)
说明：
表扫描比录=(1)/(2)
如果值超过4000设置：read_buffer_size,如：
read_buffer_size=4M,如果超过8MB,进行调优在my.cnf中
四.工具介绍：
1.mytop
%E7%9B%91%E6%8E%A7%E5%B7%A5%E5%85%B7-mytop.html
2.mysqlard
3.mysqlreport
http://www.chedong.com/blog/archives/001451.html
