drop大表对数据库的影响

最新推荐文章于 2024-07-21 19:06:05 发布

进击的CJR

最新推荐文章于 2024-07-21 19:06:05 发布

阅读量221

点赞数 6

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/DBA_ChenJR/article/details/136836570

版权

mysql 专栏收录该内容

45 篇文章 0 订阅

订阅专栏

表24G，buffer pool1.5G

-rw-r----- 1 mysql mysql          65 Mar 13 16:38 db.opt
[root@hecs-161929 test]# du -sh *
40K     bigtable.frm
24G     bigtable.ibd
40K     cm_ccf_taskins_ipf.frm
2.5G    cm_ccf_taskins_ipf.ibd
4.0K    db.opt

buffer pool使用情况

(root@localhost) [test]  select CONCAT(FORMAT(A.num *100 /B.num,2),"%") BufferPoolFullPct from (select variable_value num from performance_schema.global_status where variable_name='Innodb_buffer_pool_pages_data') A ,(select variable_value num from performance_schema.global_status where variable_name ='Innodb_buffer_pool_pages_total')B;
+-------------------+
| BufferPoolFullPct |
+-------------------+
| 99.96%            |
+-------------------+
1 row in set (0.00 sec)

开始drop

(root@localhost) [test] drop table bigtable;
Query OK, 0 rows affected (0.48 sec)

TPS/QPS 降为0

[root@hecs-161929 test]# sysbench  oltp_read_write --mysql-host=116.63.145.178 --mysql-port=3306 --mysql-user=cjr --mysql-password=cjr --mysql-db=sbtest --tables=5 --table-size=10000  --threads=64 --time=60 --report-interval=10 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 64
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 64 tps: 3.10 qps: 143.27 (r/w/o: 117.88/12.80/12.60) lat (ms,95%): 7086.63 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 64 tps: 3.50 qps: 71.00 (r/w/o: 49.80/14.30/6.90) lat (ms,95%): 11115.87 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 64 tps: 3.40 qps: 69.60 (r/w/o: 49.00/13.70/6.90) lat (ms,95%): 15934.78 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 64 tps: 3.50 qps: 69.00 (r/w/o: 48.50/13.50/7.00) lat (ms,95%): 6026.41 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 64 tps: 3.50 qps: 67.20 (r/w/o: 45.80/14.40/7.00) lat (ms,95%): 14827.42 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 64 tps: 2.80 qps: 57.90 (r/w/o: 40.80/11.50/5.60) lat (ms,95%): 26382.13 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 64 tps: 2.40 qps: 18.80 (r/w/o: 7.80/8.60/2.40) lat (ms,95%): 67291.00 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 64 tps: 0.10 qps: 0.90 (r/w/o: 0.40/0.40/0.10) lat (ms,95%): 63752.41 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 63 tps: 0.50 qps: 3.40 (r/w/o: 0.90/2.00/0.50) lat (ms,95%): 85039.12 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 63 tps: 0.50 qps: 3.40 (r/w/o: 0.90/2.00/0.50) lat (ms,95%): 96462.77 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 15 tps: 2.70 qps: 18.30 (r/w/o: 4.80/10.80/2.70) lat (ms,95%): 100000.00 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 4 tps: 0.10 qps: 0.50 (r/w/o: 0.00/0.40/0.10) lat (ms,95%): 100000.00 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            3668
        write:                           1048
        other:                           524
        total:                           5240
    transactions:                        262    (1.20 per sec.)
    queries:                             5240   (23.92 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          219.0740s
    total number of events:              262

Latency (ms):
         min:                                   27.98
         avg:                                21588.57
         max:                               219073.01
         95th percentile:                   100000.00
         sum:                              5656204.56

Threads fairness:
    events (avg/stddev):           4.0938/6.46
    execution time (avg/stddev):   88.3782/25.09

[root@hecs-161929 test]#

关闭AHI再次测试
drop 表同时开始压测

[root@hecs-161929 ~]# sysbench  oltp_read_write --mysql-host=116.63.145.178 --mysql-port=3306 --mysql-user=cjr --mysql-password=cjr --mysql-db=sbtest --tables=10 --table-size=1000  --threads=5 --time=120 --report-interval=5 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 5
Report intermediate results every 5 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 5s ] thds: 5 tps: 5.80 qps: 127.15 (r/w/o: 90.77/23.79/12.60) lat (ms,95%): 1869.60 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 5 tps: 3.20 qps: 65.81 (r/w/o: 46.60/12.80/6.40) lat (ms,95%): 2320.55 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 5 tps: 3.80 qps: 73.20 (r/w/o: 50.20/15.40/7.60) lat (ms,95%): 2045.74 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 5 tps: 3.20 qps: 63.80 (r/w/o: 45.40/12.00/6.40) lat (ms,95%): 2238.47 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 5 tps: 2.80 qps: 59.60 (r/w/o: 42.60/11.40/5.60) lat (ms,95%): 1836.24 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 5 tps: 3.20 qps: 60.80 (r/w/o: 41.80/12.60/6.40) lat (ms,95%): 2449.36 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 5 tps: 2.80 qps: 59.60 (r/w/o: 42.60/11.40/5.60) lat (ms,95%): 2405.65 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 5 tps: 2.60 qps: 52.80 (r/w/o: 36.60/11.00/5.20) lat (ms,95%): 2045.74 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 5 tps: 3.20 qps: 62.00 (r/w/o: 43.40/12.20/6.40) lat (ms,95%): 1836.24 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 5 tps: 3.20 qps: 62.80 (r/w/o: 43.80/12.60/6.40) lat (ms,95%): 1427.08 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 5 tps: 3.00 qps: 59.20 (r/w/o: 41.20/12.00/6.00) lat (ms,95%): 1836.24 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 5 tps: 2.80 qps: 57.00 (r/w/o: 40.20/11.20/5.60) lat (ms,95%): 1376.60 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 5 tps: 3.60 qps: 69.40 (r/w/o: 48.00/14.40/7.00) lat (ms,95%): 1648.20 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 5 tps: 2.40 qps: 53.00 (r/w/o: 37.60/10.40/5.00) lat (ms,95%): 2045.74 err/s: 0.00 reconn/s: 0.00
[ 75s ] thds: 5 tps: 3.00 qps: 58.60 (r/w/o: 41.40/11.20/6.00) lat (ms,95%): 1648.20 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 5 tps: 3.00 qps: 58.40 (r/w/o: 40.40/12.00/6.00) lat (ms,95%): 1235.62 err/s: 0.00 reconn/s: 0.00
[ 85s ] thds: 5 tps: 3.20 qps: 65.00 (r/w/o: 45.80/12.80/6.40) lat (ms,95%): 1258.08 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 5 tps: 3.00 qps: 59.80 (r/w/o: 41.80/12.00/6.00) lat (ms,95%): 1427.08 err/s: 0.00 reconn/s: 0.00
[ 95s ] thds: 5 tps: 3.20 qps: 64.20 (r/w/o: 45.00/12.80/6.40) lat (ms,95%): 1836.24 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 5 tps: 3.00 qps: 59.40 (r/w/o: 41.40/12.00/6.00) lat (ms,95%): 2045.74 err/s: 0.00 reconn/s: 0.00
[ 105s ] thds: 5 tps: 2.80 qps: 57.40 (r/w/o: 40.60/11.20/5.60) lat (ms,95%): 1258.08 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 5 tps: 3.20 qps: 63.60 (r/w/o: 44.40/12.80/6.40) lat (ms,95%): 1648.20 err/s: 0.00 reconn/s: 0.00
[ 115s ] thds: 5 tps: 2.80 qps: 56.20 (r/w/o: 39.40/11.20/5.60) lat (ms,95%): 2449.36 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 5 tps: 3.00 qps: 57.80 (r/w/o: 39.40/12.40/6.00) lat (ms,95%): 1561.52 err/s: 0.00 reconn/s: 0.00

总结：
drop table 的过程
（1）遍历lru，删除该表的脏页
（2）清理AHI的内容
（3）文件系统的删除、
大的buffer pool会遍历时间过长，通过hash运算找AHI对应的位置并删除，这个时间也是比较长的，此阶段持有内部latch锁不释放，影响其他查询。
在mysql8.0.23修复版本中，对于脏页采用惰性删除方式，在关闭AHI的时候，是瞬间完成的，当开启AHI的时候比历史版本还要长，与其他版本的区别是不影响业务。