【环境介绍】
系统环境：Linux + mysql 5.7.18 + 主从复制架构
【背景描述】
客户反映用在mysql数据库上truncate一个innode引擎的list分区100G左右表时，耗时时间非常久，执行命令期间数据库运行非常缓慢，新连接无法登录，连旧连接都是卡主。
【问题处理】
数据库后台日志信息：
2018-04-17T07:01:50.963763Z 0 [Warning] InnoDB: A long semaphore wait:
--Thread 139922526992128 has waited at btr0sea.ic line 90 for 241.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0xa49e6a08 created in file btr0sea line 195
a writer (thread id 139922526992128) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr0sea.ic line 128
Last time write locked in file /export/home/pb2/build/sb_0-22759043-1489826356.96/mysql-5.7.18/storage/innobase/btr/btr0cur line 3874
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0
---BUFFER POOL 1
……
---BUFFER POOL 7
Buffer pool size 786336
Free buffers 240156
Database pages 546149
Old database pages 201625
Modified db pages 2
Pending reads 0
Pending writes: LRU 0, flush list 2, single page 0
Pages made young 116, not young 8649813
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1978853, created 5, written 1217
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 546149, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=32294, Main thread ID=139922602555136, state: making checkpoint
Number of rows inserted 19058, updated 51, deleted 15, read 767102669
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
从日志报错看跟innodb引擎的buffer pool相关
mysql> show VARIABLES like '%pool%' ;
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 103079215104 |
从日志跟参数可以看出数据库innodb_buffer_pool_size96G，该参数相对较大，怀疑是有其他业务操作该表，但是客户反映该数据库为历史数据库，使用人很少。
| innodb_buffer_pool_size | 103079215104 |是96G，
---BUFFER POOL 7 Buffer pool size 786336 786336*16/1024/1024*8 是96G
truncate表清理数据是非常快的，从Old database pages上面信息推断数据在dirty_pages较多，扫描链表时间过长。于是查看dirty_pages_pct参数。
mysql> show variables like '%dirty%';
+--------------------------------+-----------+
| Variable_name | Value |
+--------------------------------+-----------+
| innodb_max_dirty_pages_pct | 90.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
+--------------------------------+-----------+
InnoDB tries to flush data from the buffer pool so that the percentage of dirty pages does not exceed
this value. The default value is 75.
从官方文档看出，innodb_max_dirty_pages_pct默认值为75%，但是当前数据库为90%，可以说是很高的了。咨询该参数修改情况，为之前模板复制过来直接运用。
【解决办法】
根据当前数据库为历史数据库，应用连接数据库操作非常少，建议innodb_max_dirty_pages_pct参数调为50%。调整该参数不需要重启数据库，注意修改参数文件，防止下次重启数据库后还是原来的参数。
innodb_max_dirty_pages_pct参数调为50%后，业务删除数据为1分钟之内。
mysql> truncate table nm_xxxf_xxxt_xx_201803;
Query OK, 815420 rows affected (44.32 sec)
当前数据库内存较高，暂时不建议对innodb_buffer_pool_size 调小，可根据后续调整该参数。
Mariadb/MySQL数据库单表查询基本操作及DML语句
Mariadb/MySQL数据库单表查询基本操作及DML语句 作者:尹正杰 版权声明:原创作品,谢绝转载!否则将追究法律责任. 一数据库及表相关概述 1>.数据库操作 创建数据库: CREATE ...
MySQL数据库分表的3种方法
更多推荐
mysql truncate很慢_mysql数据库truncate表时间长处理
发布评论