欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL系统调优及问题查找_MySQL

程序员文章站 2022-06-16 20:51:02
...
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
相关标签: 系统