MySQL 系统调优及问题查找

jopen 12年前
性能优化相关状态参数
    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_division_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_division_limit 的设置不当，造成 Key Cache cache 失效太快 。一般来说，在实际应用场景中，很少有人调整 key_cache_age_threshold 和 key_cache_division_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