MySQL系统调优及问题查找_MySQL

bitsCN.com

MySQL系统调优及问题查找

性能优化相关状态参数

SHOW STATUS LIKE 'value';

connections 连接数

uptime 启动时间

show_queries 慢查询次数

com_select 查询操作次数

com_insert 插入操作次数

com_update 更新操作次数

com_delete 删除操作次数

分析查询语句

EXPLAIN/DESC SELECT;

禁用/启用索引

ALTER TABLE table DISABLE/ENABLE KEYS;

禁用唯一索引

SET UNIQUE_CHECK=0/1

分析、检查和优化表

ANALYZE TABLE table1 [,table2 ...]

CHECK TABLE table1[,table2 ...]

OPTIMIZE TABLE table1[,table2...]

分析SQL语句

explain select count(*), max(id), min(id) from user/G

通过explain分析SQL语句，获知执行情况

Profiling的使用

打开profiling 参数

>set profiling=1;

执行一些SQL语句后就可以查看query的profile 信息

>show profiles;

>show profiles cpu, blockio for query 6;

#查看query 6所使用的CPU IO资源情况

索引中的限制

1、MyISAM引擎索引长度总和不能超过1000字节

2、BLOB和TEXT类型的列只能创建前缀索引

3、MySql不支持函数索引

4、使用不等于( != 或 ) 的时候无法使用索引

5、过滤字段使用了函数后（如: abs(column)) 无法使用索引

6、Join语句中Join条件字段类型不一致时，无法使用索引

7、使用Like操作的时候如果条件以通配符开始 ( '%abc...')无法使用索引

8、使用非等值查询时，无法使用hash索引

查询效率测试工具 mysqlslap

$ mysqlslap --create-schema=example --query="select * from group_message where user_id=3 AND subject like 'weiurezs%' --iterations=10000

#用于测试query的执行效率，给出平均、最大、最小执行时间。

FORCE INDEX(索引名称) 强制使用索引

EXPLAIN select * from group_message

FORCE INDEX(idx_group_message_author_subject)

where user_id=3 AND author='3' AND subject like 'weiurazs%'/g

性能调优——log设置

Mysql的log项有：错误日志、更新日志、二进制日志、查询日志、慢查询日志

Binlog

>show variables like '%binlog%';

慢查询

>show veriables like 'log_slow%';

>show variables like 'long_query%';

long_query_time最小值为1秒，如果需要进一步缩短慢查询的时间限制，可以使用Percona提供的microslow-path(http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-path-installation-walk-through/)

性能调优——Query Cache

查看Query Cache系统变量

>show variables like '%query_cache%';

了解Query Cache的使用情况

>show status like 'Qcache%';

cache命中率= Qcache_hits / (Qcache_hits + Qcache_inserts)

Qcache_hits / (Qcache_hits + Com_select) 应该更准确些

弊端： 1、Query语句的hash运算和查找资源增加CPU资源的消耗

2、Query Cache失效问题（当表的更新频繁时会造成非常高的失效率

3、Query Cache中缓存的Result Set, 而不是页面，可能造成内存的过度消耗，以及因内存不足造成过多的换入换出导致命中率的下降。

应对措施：

1、对那些经常更新的记录指定SQL_NO_CACHE的SQL Hint，强制MySQL不缓存。

2、对那些大部分时候都是静态的数据指定SQL_CACHE，使用CACHE。

3、对那些Result Set较大的的Query要么使用SQL_NO_CACHE,强制不使用CACHE，或者通过设置query_cache_limit参数来控制query中cache的最大Result Set，系统默认为1M，大于此设定值的Result Set将不会Cache。

Query Cache的限制

1、 5.1.17 之前的版本不能 Cache 帮定变量的 Query ，但是从 5.1.17 版本开始， Query Cache 已经开始支持帮定变量的 Query 了；

2、 所有子查询中的外部查询 SQL 不能被 Cache ；

3、在 Procedure ， Function 以及 Trigger 中的 Query 不能被 Cache ；

4、包含其他很多每次执行可能得到不一样结果的函数的 Query 不能被 Cache 。

性能调优——其他常用优化

max_connections(最大连接数)：一般设置为 500—800左右

max_user_connections(每个用户允许的最大连接数)：一般不做限制

net_buffer_length(网络传输缓存)：默认16KB基本够用

thread_cache_size(Thread Cache池应该存放的连接线程数): 不应该小于应用系统对数据库实际并发请求数，一般50-100之间。对短连接效果很好。

相关系统设置值及状态值

>show variables like 'thread%';

>show status like 'connections';

>show status like '%thread%';

Thread Cache 命中率：（应该保持在90%以上）

Thread_Cache_hit = (Connections - Threads_created) / Connections * 100%;

MyISAM引擎优化

MyISAM引擎适用场景：以读为主的非事务性数据系统，对数据的准确性要求不高时有优异的性能表现。

系统参数：

key_buffer_size: 索引缓存大小

key_buffer_block_size: 索引缓存中的Cache Block Size:

key_cache_pision_limit: LRU链表中的Hot Area和Warm Area的分界值（范围1-100），系统默认100，及只有Warm Cache。

key_cache_aeg_threshold: 控制 Cache Block从Hot Area降到 Warm Area的限制

性能参数：

key_block_not_flushed 已经更改但还未刷新到磁盘的Dirty Cache Block

key_blocks_unused 目前未被使用的Cache Block数目

key_read_requests Cache Block被请求读取的总次数

key_read, 在Cache Block中找不到需要读取的Key信息后到 .MYI 文件中读取的次数

key_write_requests, Cache Block被请求修改的总次数

key_writes 在Cache Block中找不到需要修改的Key信息后到 .MYI 文件中读入再修改的次数

参数合理性判断指标：

Key_buffer_UsageRatio = ( 1- Key_blocks_used / (key_blocks_used +key_blocks_unused ) ) * 100%

(应该在99%以上，如果该值过低，说明key_buffer_size设置过大，MySQL根本用不完)

Key_buffer_read_HitRatio = ( 1- Key_reads / key_read_requests ) * 100%

(应该在99%以上，如果值过低，说明key_buffer_size设置过小，需要增加；也可能是key_cache_age_threshold 和 key_cache_pision_limit 的设置不当，造成 Key Cache cache 失效太快 。一般来说，在实际应用场景中，很少有人调整 key_cache_age_threshold 和 key_cache_pision_limit 这两个参数的值，大都是使用系统的默认值)

Key_buffer_write_HitRatio = ( 1- Key_writes / key_Write_requests ) * 100%

多Cache系统

MySQL 官方建议在比较繁忙的系统上一般可以设置三个 Key Cache ：

一个 Hot Cache 使用 20% 的大小用来存放使用非常频繁且更新很少的表的索引；

一个 Cold Cache 使用 20% 的大小用来存放更新很频繁的表的索引；

一个 Warm Cache 使用剩下的 60% 空间，作为整个系统默认的 Key Cache ；

Key Cache 的 Mutex 问题：目前MySQL在Active线程数量较高时非常容易出现 Cache Block 的锁问题

Key Cache 预加载

在 MySQL 中，为了让系统刚启动之后不至于因为 Cache 中没有任何数据而出现短时间的负载过高或 者

是响应不够及时的问题。 MySQL 提供了 Key Cache 预加载功能，可以通过相关命令（ LOAD INDEX INTO CACHE tb_name_list ... ），将指定表的所有索引都加载到内存中，而且还可以通过相关参数控制是否只 Load 根结点和枝节点还是将页节点也全部 Load 进来，主要是为 Key Cache 的容量考虑。

对于这种启动后立即加载的操作，可以利用 MySQL 的 init_file 参数来设置相关的命令，如下：

mysql@sky:~$ cat /usr/local/mysql/etc/init.sql

SET GLOBAL hot_cache.key_buffer_size=16777216

SET GLOBAL cold_cache.key_buffer_size=16777216

CACHE INDEX example.top_message in hot_cache

CACHE INDEX example.event in cold_cache

LOAD INDEX INTO CACHE example.top_message,example.event IGNORE LEAVES

这里我的 init file 中首先设置了两个 Key Cache （ hot cache 和 cold cache ）各为 16M ，然后分别将 top_message 这个变动很少的表的索引 Cache 到 Hot Cache ，再将 event 这个变动非常频繁的表的索引Cache 到了 Cold Cache 中，最后再通过 LOAD INDEX INTO CACHE 命令预加载了 top_message，groups 这两个表所有索引的所有节点以及 event 和 user 这两个表索引的非叶子节点数据到 Key Cache 中，以提高系统启动之初的响应能力。

其他可以优化的地方

1. 通过 OPTIMIZE 命令来整理 MyISAM 表的文件。这就像我们使用 Windows 操作系统会每过一段时间后都会做一次磁盘碎片整理，让系统中的文件尽量使用连续空间，提高文件的访问速度。 MyISAM 在通过 OPTIMIZE 优化整理的时候，主要也是将因为数据删除和更新造成的碎片空间清理，使整个文件连续在一起。一般来说，在每次做了较大的数据删除操作之后都需要做一次 OPTIMIZE 操作。而且每个季度都应该有一次 OPTIMIZE 的维护操作。

2. 设置 myisam_max_[extra]_sort_file_size 足够大，对 REPAIR TABLE 的效率可能会有较大改善。

3. 在执行 CREATE INDEX 或者 REPAIR TABLE 等需要大的排序操作的之前可以通过调整 session 级别的 myisam_sort_buffer_size 参数值来提高排序操作的效率。

4. 通过打开 delay_key_write 功能，减少 IO 同步的操作，提高写入性能。

5. 通过调整 bulk_insert_buffer_size 来提高 INSERT...SELECT... 这样的 bulk insert 操作的整体性能， LOAD DATA INFILE... 的性能也可以得到改善。当然，在设置此参数的时候，也不应该一味的追求很大，很多时候过渡追求极端反而会影响系统整体性能，毕竟系统性能是从整体来看的，而不能仅仅针对某一个或者某一类操作。

MySql MyISAM、INNODB类型表碎片优化

针对MyISAM表类型采用 OPTIMIZE TABLE table_name SQL语句清理碎片.

InnoDB 使用的 Clustered Index,索引和数据绑定在一起,重排序是不现实的.所以不支持 MyISAM 式的 OPTIMIZE,而是绑定到了ALTER TABLE 命令上面.可以通过执行以下语句来整理碎片,提高索引速度:

ALTER TABLE table_name ENGINE = Innodb;

这其实是一个NULL操作,表面上看什么也不做,实际上重新整理碎片了.当执行优化操作时,实际执行的是一个空的 ALTER 命令,但是这个命令也会起到优化的作用,它会重建整个表,删掉未使用的空白空间.

innodb 存储引擎优化

Innodb 存储引擎和 MyISAM 存储引擎最大区别主要有四点，第一点是缓存机制，第二点是事务支持 ，第三点是锁定实现，最后一点就是数据存储方式的差异。在整体性能表现方面， Innodb 和 MyISAM 两个存储引擎在不同的场景下差异比较大，主要原因也正是因为上面这四个主要区别所造成的。

Innodb_buffer_pool_size

假设是一台单独给 MySQL 使用的主机，物理内存总大小为 8G ， MySQL 最大连接数为 500 ，同时还使用了 MyISAM 存储引擎，这时候我们的整体内存该如何分配呢？

内存分配为如下几大部分：

1、系统使用，假设预留 800M ；

2、线程独享，约 2GB = 500 * (1MB + 1MB + 1MB + 512KB + 512KB) ，组成大概如下 :

sort_buffer_size ： 1MB

join_buffer_size ： 1MB

read_buffer_size ： 1MB

read_rnd_buffer_size ： 512KB

thread_statck ： 512KB

3、MyISAM Key Cache ，假设大概为 1.5GB ；

4、Innodb Buffer Pool 最大可用量： 8GB - 800MB - 2GB - 1.5GB = 3.7GB

通过Buffer Pool 的实时状态信息来确定InnoDB的Buffer Pool的使用是否高效：

>show status like 'Innodb_buffer_pool_%';

Innodb_Buffer_pool_HitRatio = ( Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads ) / Innodb_buffer_pool_read_requests * 100%

buffer pool 使用率 = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%

innodb_log_buffer_size 参数的使用

顾名思义，这个参数就是用来设置 Innodb 的 Log Buffer 大小的，系统默认值为 1MB 。 Log Buffer的主要作用就是缓冲 Log 数据，提高写 Log 的 IO 性能。一般来说，如果你的系统不是写负载非常高且以大事务居多的话， 8MB 以内的大小就完全足够了。

>show status like 'innodb_log%'; (查看innodb_log_buffer_size 设置是否合理)

Innodb 存储引擎的物理存储结构：

最小单位： page（默认16KB）——＞extent（64连续的page）——>segment（一个或多个extent）——>tablespace（最大的物理结构单位，由多个segment组成）

InnoDB 性能监控

>show innodb status/G

持续获取状态信息的方法： create table innodb_monitor(a int) engine=innodb;

创建一个innodb_monitor空表后，InnoDB就会每隔15秒输出一次信息并记录到Error Log中，通过删除该表停止监控

除此之外，我们还可以通过相同的方式打开和关闭 innodb_tablespace_monitor, innodb_lock_monitor, innodb_table_monitor 这三种监控功能

MySQL 高可用性方案

1、MySQL Replication

2、MySQL Cluster

3、DRDB

bitsCN.com