mysql truncate很慢_mysql数据库truncate表时间长处理

【环境介绍】

系统环境：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 fileline 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/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 调小，可根据后续调整该参数。