mysql负载高排查_mysql负载突然增大，网站无法访问，排查处理方案

一、开启慢查询日志记录

1、vi /etc/my.cnf

#slow_query

slow_query_log

=

on

long_query_time

=

1

slow_query_log_file

= /data/mysql_query/mysql-slow.log

注意事项：配置需要写到[mysqld_safe]上面，日志文件需要有读写权限

service mysqld restart

重启数据库才会生效

开启慢日志查询，非常有利于优化mysql的性能。

二、针对执行花费时间较多的语句，进行优化。

1、查询较多的字段开启索引，将大大提升查询的效率

2、开发程序时可以将数据库的计算，放到应用层可以大大减少数据库的压力。

三、进入mysql后，使用show

processlist;

使用此命令可以看到花费时间较多的语句，并针对性的进行优化，例如添加索引。

四、如果开启了缓存，需要及时清理缓存碎片，

查询缓存使用情况

mysql> show status like

‘%qcache%’;

+————————-+———-+

| Variable_name | Value |

+————————-+———-+

| Qcache_free_blocks | 1041 |

| Qcache_free_memory | 83003648 |

| Qcache_hits | 76534835 |

| Qcache_inserts | 7677916 |

| Qcache_lowmem_prunes | 1516232 |

| Qcache_not_cached | 330027 |

| Qcache_queries_in_cache | 173376 |

| Qcache_total_blocks | 347796 |

+————————-+———-+

8 rows in set (0.00 sec)

Qcache_free_blocks

目前还处于空闲状态的Query

cache内存block的数目 这个值比较大，意味着内存碎片比较多，可以使用flush query

cache清理内存碎片

Qcache_free_blocks

Qcache_free_memory

目前还处于空闲状态的Query Cache内存总量

Qcache_hits查询缓存区的命中个数，也就是直接从查询缓存区作出响应处理的查询个数

Qcache_inserts

向Query Cache中插入新的Query Cache的次数，也就是没有命中的次数

Qcache_lowmem_prunes

其代表的意义为：查询缓存去因内存不足而不得不从查询缓存区删除的查询缓存信息，删除算法为LRU； 当query内存容量不足，需要从中删除老的Query

Cache已给新的Cache对象使用的次数 该参数值对于检测查询缓存区的内存大小设置是否，有非常关键性的作用

Qcache_not_cached

没有被Cache的SQL数，包括无法被Cache的SQL以及由于Query_cache_type设置的不会被Cache的sql

Qcache_queries_in_cache

目前在Query Cache中的SQL数量，当前缓存的查询(和响应)的数量

Qcache_total_blocks

Query Cache中block的总数量

以下有几个概念：

碎片率

查询缓存内存碎片率：Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过20%，

1、可以用flush query

cache;可以清理缓存碎片

2、或者试试减小query_cache_min_res_unit，

如果你的查询都是小数据量的话，这样就可以降低Qcache_free_block。

mysql>SHOW VARIABLES

LIKE '%query_cache%';

( query_cache_size -

Qcache_free_memory )除以 Qcache_queries_in_cache

就是 (134217728 -

119423544)/13205=1120.34

所以在设置的的时候

query_cache_min_res_unit

可以设置成2k 在my.cnf中

query_cache_min_res_unit=

2k

命中率

查询缓存命中率：Qcache_hits/(Qcache_hits +

Qcache_inserts) * 100%

缓存利用率

查询缓存内存使用率：(query_cache_size –

Qcache_free_memory) / query_cache_size *

100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes

> 50的话说明query_cache_size可能有点小，要不就是碎片太多。

四、MySQL调优之innodb_buffer_pool_size大小设置

相关查看命令

sql> show global variables like 'innodb_buffer_pool_size';

sql> show global status

like 'Innodb_buffer_pool_pages_data';

sql> show global status

like 'Innodb_page_size';

或

sql> use mysql;

sql> select

@@innodb_buffer_pool_size;

....

MariaDB [(none)]> show

global variables like 'innodb_buffer_pool_size';

+-------------------------+-----------+

| Variable_name

|

Value

|

+-------------------------+-----------+

| innodb_buffer_pool_size | 268435456 |

+-------------------------+-----------+

1 row in set (0.00 sec)

MariaDB [(none)]> show global status like

'Innodb_buffer_pool_pages_data';

+-------------------------------+-------+

| Variable_name

|

Value |

+-------------------------------+-------+

| Innodb_buffer_pool_pages_data | 6082  |

+-------------------------------+-------+

1 row in set (0.00 sec)

MariaDB [(none)]> show global status like

'Innodb_buffer_pool_pages_total';

+--------------------------------+-------+

| Variable_name

|

Value |

+--------------------------------+-------+

| Innodb_buffer_pool_pages_total | 16383 |

+--------------------------------+-------+

1 row in set (0.00 sec)

MariaDB [(none)]> show global status like

'Innodb_page_size';

+------------------+-------+

| Variable_name

| Value

|

+------------------+-------+

| Innodb_page_size | 16384 |

+------------------+-------+

1 row in set (0.00 sec)

MariaDB [(none)]> 官方对这个几个参数的解释：

Innodb_buffer_pool_pages_data

The number of pages in the InnoDB buffer pool containing data. The

number includes both dirty and

clean pages.

Innodb_buffer_pool_pages_total

The total size of the InnoDB buffer pool, in pages.

Innodb_page_size

InnoDB page size (default 16KB). Many values are counted in pages;

the page size enables them to be

easily converted to bytes

调优参考计算方法：

val = Innodb_buffer_pool_pages_data /

Innodb_buffer_pool_pages_total * 100%

val > 95% 则考虑增大

innodb_buffer_pool_size， 建议使用物理内存的75%

val < 95% 则考虑减小

innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data

* Innodb_page_size * 1.05 / (1024*1024*1024)

设置命令：set global innodb_buffer_pool_size =

2097152; //缓冲池字节大小，单位kb，如果不设置，默认为128M

五、如果不同的系统都在访问一个数据库，最好使用不同的账号来访问数据库，方便排查那个系统访问导致数据库卡顿了。