提升mysql语句执行速度 之 innodb缓存大小调整

背景:table A 有将近40万条数据,下面这个语句要执行:15  28  78 * 55 = 1801800 次,(where条件中的参数每次都会变化)

select avg(col_5), avg(col_10), avg(col_20), avg(col_30), avg(col_40), count(*)
FROM test_db.table_A
where di = 'M'
and (r_60 >= -2 and r_60 <= 10)
and (r_61 >= 4 and r_61 <= 12)
and (r_62 >= 24 and r_62 <= 40);

即使是在索引有生效的情况下,每次查询花费的时间在 1.2秒左右
1801800 * 1.2 / 3600 = 600 小时,约为25天

很明显,要执行完一个批次的分析就要花费将近1个月的时间,这个时间成本是无法接受的。虽然有按参数范围手工分了4个进程来跑,理论上也要执行将近7天才能把所有的参数全部跑完!

mysql数据库是搭建在win10上,任务管理器里查看mysqld.exe的进程,发现该进程只用了54.4M内存,而我的电脑总共有16G的内存,系统层面的还有将近8G的空闲内存,很自然的怀疑是否可以通过给mysql加大内存来提升查询速度。

事实证明,当下面这条语句执行完之后,马上就有了立杆见影的效果。
语句:SET GLOBAL innodb_buffer_pool_size = 3221225472;
变化:原来 innodb_buffer_pool_size 的值是:8388608 (8M), 调整后的值是: 3221225472 (3G)
效果:任务管理器中mysqld.exe进程占用的内存马上变成:350M左右,此时SQL查询速度得到了非常明显的提升,而且原来100M/s的读硬盘的操作消失了。

当把innodb_buffer_pool_size的值修改至6G后,同样的查询执行时间为: 0.36秒, 比起原来8M时的1.2秒有了很大的提升。这个进程mysqld.exe占用的内存将近1G。

文档主要参考了:如何在MySQL中分配innodb_buffer_pool_size

当前配置的innodb_buffer_pool_size是否合适,可以通过分析InnoDB缓冲池的性能来验证。
show status like ‘innodb_buffer_pool_read%’;
可以使用以下公式计算InnoDB缓冲池性能:
Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100
innodb_buffer_pool_reads:表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
innodb_buffer_pool_read_requests:表示从内存中读取逻辑的请求数。

操作过程记录:
show variables like '%innodb_buffer_pool_size%';
show variables like 'innodb_buffer_pool%';
-- innodb_buffer_pool_instances 1
-- innodb_buffer_pool_chunk_size 8388608
-- innodb_buffer_pool_size 8388608 (缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数)

-- 在线调整 innodb_buffer_pool_size, 无需重启mysql
SET GLOBAL innodb_buffer_pool_size = 3221225472‬; -- 这个参数调整为3G之后,US分析的速度明显变快 3221225472,4294967296‬
-- 调整进度跟踪
SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

show status like 'innodb_buffer_pool_read%'; -- '112702592' / '3003766565' = 0.0375204229626945 = 3.75%
-- InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100
-- = '3003766565'/('3003766565' + '112702592') = 3003766565/3116469157‬ = 96.38%, InnoDB buffer pool 命中率小于 99%, 可以考虑增加innodb_buffer_pool_size

Continue reading 提升mysql语句执行速度 之 innodb缓存大小调整