Mysql数据库管理-Innodb 内存优化分析

MySql数据库内存分析优化

1 Innodb重做日志

redo log是Innodb保障事务ACID属性的重要机制。工作原理图如下：

2 增加缓冲池数量，减少内部对缓冲池结构争用。

mysql内部线程对innodb缓存池的访问在某个阶段是互斥的，这种内部竞争也会产生性能问题，尤其是在高并发和buffer pool较大的情况下，为了解决这个问题，innodb的缓存系统引入参数。

参数：

innodb_buffer_pool_instances,

对于较大的缓冲池，适当的增加此参数值，可以降低并发导致内部缓存访问冲突，改善性能，innodb_buffer_pool_size指定大小的缓存平分为innodb_buffer_pool_instances个buffer pool

3 控制innodb buffer刷新，延长数据缓存时间，减缓磁盘IO

在innodb找不到干净的可用缓存页或者检查点被触发的情况下，innodb的后台线程开始吧“脏的缓存页回写到磁盘中”这个过程叫做缓存刷新。
我们通常希望buffer pool中的数据在缓存中保留的时间尽可能长，以备重用，从而减少IO的次数。延缓缓存刷新来减少IO压力。

Innodb buffer pool的刷新快慢主要取决于2个参数

innodb_max_dirty_pages_pct

innodb_io_capacity

02:45:  [(none)]> show variables like '%innodb_max_dirty%';
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| innodb_max_dirty_pages_pct     | 90.000000 |
| innodb_max_dirty_pages_pct_lwm | 10.000000 |
+--------------------------------+-----------+
2 rows in set (0.01 sec)

02:45:  [(none)]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.34    |
+-----------+
1 row in set (0.00 sec)

02:46:  [(none)]>

如果状态变量innodb_buffer_pool_wait_free的值增长较快，则说明Innodb经常在等待空闲缓冲页，如果无法增大缓冲池，那么应该将 innodb_max_dirty_pages_pct  调整小或者增加innodb_io_capacity值。

02:47:  [(none)]> show status like '%innodb_buffer_pool_wait_free%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0     |
+------------------------------+-------+
1 row in set (0.00 sec)

02:49:  [(none)]> 

4 调整用户服务线程排序缓存区

       如果通过 show global status 看到 sort_merge_passes 的值很大，可以考虑通过调整参数sort_buffer_size的值来增大排序缓存区，以改善带有 order by子句或group子句SQL的性能。

       对于无法通过索引进行连接操作的查询，可以尝试通过增大，join_buffer_size的值来改善性能。不过需要注意的是，sort buffer和 join buffer都是面向客户服务线程分配的，如果设置过大可能造成内存浪费，甚至导致内存交换。尤其是 join buffer，如果是多表关联的复杂查询，还可能会分配多个 join buffer，因此最好的策略是设置较小的全局 join_buffer_size，而对需要做复杂连接操作的session单独设置较大的join_buffer_size。

set global join_buffer_size=1000

set session join_buffer_size=100000

2 优化mysql并发

1 max_connections

\s

如果状态变量connection_errors_max_connections不为0，并且一直在增长就说明不断有连接请求因为数据库连接数达到上限而失败,每个连接都有自己独立的sort_buffer_size和join_buffer_size值。

show status like '%max_connection%';