本文为墨天轮数据库管理服务团队原创内容,如需转载请联系小墨(VX:modb666)并注明来源。
作者:杨明翰
墨天轮数据库管理服务团队总监
专注于MySQL性能优化和功能改进,擅长MogDB(openGauss)运维与优化,所服务的行业涉及电商、金融和政企等,致力于将积累的IT技术与行业实践分享给业界的客户及同仁,帮助到更多的企业和用户。
https://bugs.mysql.com/bug.php?id=113812; https://bugs.mysql.com/bug.php?id=115608; https://perconadev.atlassian.net/browse/PS-9144; https://perconadev.atlassian.net/browse/PS-9214
https://github.com/percona/percona-server/commit/e331f9f2a28af52e3e25050ad3d8c99c6b408595;https://github.com/percona/percona-server/commit/d9d50adab52b453dd7504d784060bd593d6af812
h:localhost u:root :db(none) [16:59:59]>select version();
+-----------+
| version() |
+-----------+
| 8.0.38 |
+-----------+
1 row in set (0.00 sec)
h:localhost u:root :db(none) [17:00:04]>show create table test.his_schedule_job_run;
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| his_schedule_job_run | CREATE TABLE `his_schedule_job_run` (
`id` varchar(36) NOT NULL,
`job_id` varchar(36) DEFAULT NULL,
`job_type` varchar(2) DEFAULT NULL,
`job_group` varchar(45) DEFAULT NULL,
`job_obiect` varchar(256) DEFAULT NULL,
`shell_path` varchar(256) DEFAULT NULL,
`cron_expression` varchar(128) DEFAULT NULL,
`run_start_time` datetime DEFAULT NULL,
`run_end_time` datetime DEFAULT NULL,
`run_result` varchar(2) DEFAULT NULL,
`run_message` varchar(4000) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `fk_reference_02` (`job_id`) USING BTREE,
KEY `fk_reference_03` (`run_start_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='logtable' |
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#!/bin/bash
>/tmp/his_schedule_job_run.log
for i in {1..50}
do
echo "call test.his_schedule_job_run $i time" >> /tmp/his_schedule_job_run.log
/root/mysql_random_data_load -uroot -pXXXXXXXX -P3306 --max-threads=2 test his_schedule_job_run 2000
/usr/local/mysql/bin/mysql -uroot -pXXXXXXXX -S /tmp/mysql.sock -e "select count(*) from test.his_schedule_job_run;" >> /tmp/his_schedule_job_run.log
/usr/local/mysql/bin/mysql -uroot -pXXXXXXXX -S /tmp/mysql.sock -e "delete from test.his_schedule_job_run where run_start_time < curdate() - interval 7 day;"
echo "call $i time alter before" >> /tmp/his_schedule_job_run.log
/usr/local/mysql/bin/mysql -uroot -pXXXXXXXX -S /tmp/mysql.sock -e "select count(*) from test.his_schedule_job_run;" >> /tmp/his_schedule_job_run.log
/usr/local/mysql/bin/mysql -uroot -pXXXXXXXX -S /tmp/mysql.sock -e "alter table test.his_schedule_job_run engine = innodb;"
echo "call $i time alter after" >> /tmp/his_schedule_job_run.log
/usr/local/mysql/bin/mysql -uroot -pXXXXXXXX -S /tmp/mysql.sock -e "select count(*) from test.his_schedule_job_run;" >> /tmp/his_schedule_job_run.log
echo "call test.his_schedule_job_run $i time" >> /tmp/his_schedule_job_run.log
done
查看日志/tmp/his_schedule_job_run.log,发现存在触发bug的现象
.........
call test.his_schedule_job_run 42 time
count(*)
3823
call 42 time alter before
count(*)
1869
call 42 time alter after
count(*)
1868
call test.his_schedule_job_run 42 time
call test.his_schedule_job_run 43 time
count(*)
3868
call 43 time alter before
count(*)
1913
call 43 time alter after
count(*)
1911
call test.his_schedule_job_run 43 time
...............
call test.his_schedule_job_run 45 time
count(*)
3955
call 45 time alter before
count(*)
1999
call 45 time alter after
count(*)
1997
call test.his_schedule_job_run 45 time
..............
call test.his_schedule_job_run 48 time
count(*)
4090
call 48 time alter before
count(*)
2115
call 48 time alter after
count(*)
2114
call test.his_schedule_job_run 48 time
call test.his_schedule_job_run 49 time
count(*)
4114
call 49 time alter before
count(*)
2149
call 49 time alter after
count(*)
2148
call test.his_schedule_job_run 49 time
...........
>SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'dbname/tablename'
;
查看。COPY执行期间会阻塞对表的修改。
参考文档:
https://www.percona.com/blog/who-ate-my-mysql-table-rows/
THE END

墨天轮从乐知乐享的数据库技术社区蓄势出发,全面升级,提供多类型数据库管理服务。墨天轮数据库管理服务旨在为用户构建信赖可托付的数据库环境,并为数据库厂商提供中立的生态支持。
服务官网:https://www.modb.pro/service

点击进入作者个人主页
本文分享自微信公众号 - 墨天轮(enmocs)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。