mysql优化杂记

一.mysqladmin的使用
#mysqladmin extended-status -u root -i 2 -c 2 -p | grep connect
查看mysql的状态中带有connect字符的变量,每两秒统计一次,共统计2次

#mysqladmin extended-status -u root -r -i 2 -p | grep connect
查看2秒内的增量输出,该项不起作用

二.mysql服务器状态变量中的重要部分

1.Aborted_clients
如果这个变量随时间增加,就要确认是否优雅的关闭了连接.如果不是就要检查网络性能,并且检查max_allowed_packet的配置变量,超过了max_allowed_packet的查询会被强制中断

2.Aborted_connection
这个值应该接近0.不是的话,可能是网络问题.当然如果有攻击或定义了无效的数据库,该值会增加.

3.Binlog_cache_disk和Binlog_cache_use
如果Binlog_cache_disk和Binlog_cache_use之间的比率很大,那么就应该增加binlog_cache_size的值.大部分事物都应该落在二进制日志缓存中.
没有好办法可以减少二进制日志缓存未命中,只能增加binlog_cache_size来观察.

4.Bytes_received和Bytes_sent
这两个值可以帮助你考察问题是由接收数据过多引起的,还是发送数据过多引起

5.Com_*
应该注意不要让Com_rollback这样不常见的变量超过预期

6.Connections
这个值表示意图连接到服务器的数量.如果该值快速增加,例如每秒几百,那么应该检查连接次数或调整操作系统的网络堆栈

7.Created_tmp_disk_tables
如果这个值比较高,有两件事发生了错误
a.查询在BLOB或TEXT列的时候创建了临时表
b.tmp_table_size和max_heap_table_size可能不够大

8.Created_tmp_tables
该值较高唯一处理办法是优化查询

9.Handler_read_rnd_next
Handler_read_rnd_next/Handler_read_rnd 显示了全表扫描的大致平均值.如果该值较大,那么应该优化架构、索引和查询

10.Key_blocks_used
如果
Key_blocks_used * key_cache_block_size(参数,不是变量)的值远远小于已经充分热身的服务器上的key_buffer_size,那么久意味这key_buffer_size值太大了,内存被浪费了.

11.Key_reads(从磁盘读取索引的请求次数)
要注意观察每秒发生的读取次数,并且这个值和I/O系统进行匹配.如果该值在单位时间内过大,则需要看磁盘是否能与此匹配.

12.Max_used_connections
如果该值和max_connections相同,那么可能是max_connections设置的过低或系统最大负载超过了服务器上限了.很多情形下单纯的增加max_connections并不能解决问题.查看程序行为是否正常,服务器调优是否正确,服务器架构是否良好.

13.Open_files
注意不要和open_files_limit值接近,如果接近了,就要增加open_files_limit

14.Open_tables和opened_tables
open_tables:是当前在缓存中打开表的数量。(flush tables将对该项起作用)
opened_tables:是mysql自启动起，打开表的数量。
应该将这两个值和table_cache对照,如果每秒有太多的opened_tables,那么说明table_cache还不够大.表缓存没有被利用上.显式的建立临时表(CREATE TEMPORARY TABLE table_name)也回导致opened_tables的增加.

15.Qcache_*
查询缓存

16.Select_full_join
全联接是无索引联接.这是真正的性能杀手,最好能避免,即便一分钟一次也太多了.

17.Select_full_range_join
如果该变量过高,那么说明运行了许多使用了范围查询联接表.范围查询比较慢,同时也是个性能优化点.

18.Select_range_check
该变量记录了在联接时,对每一行数据重新检查索引的查询数量.这项性能开销很大,如果该值较高或正在增加,那么意味着一些查询没有找到好索引.

19.Slow_launch_threads
该变量说明了某些因素正在延迟联接的新线程.通常表示系统过载.

20.Sort_merge_passes
该变量较大,应该增加sort_buffer_size.

21.Table_locks_waited
该变量显示了有多少表被锁住,并且导致了服务器级的锁等待.innodb的行锁并不会使这个值增加.如果该值较高并且正在增加,说明遇到了严重的并发瓶颈.这时候应该考虑使用innodb存储引擎,手动对大表分区,或者使用mysql的分区机制,优化查询,启用并发插入或者对锁定设置进行优化.

22.Threads_created
如果该值变量较大或正在增加,也许应该增加thread_cache_size的值.可以通过检查threads_cache知道有多少线程在缓存中了.

三.每连接设置调优

1.sort_buffer_size
控制用于文件排序的缓存大小.即使需要排序的数据量很小,mysql也会按照设置一次分配全部的空间

2.read_buffer_size(针对myisam)
对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

3.read_rnd_buffer_size(针对myisam)
如果你认为连续扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能
增加order by查询效率

4.tmp_table_size
临时表大小

5.myisam_sort_buffer_size
用于修复表