MySQL数据库版本 8.0.17
前言
业务有需求，将一张大表 1000w+数据，需要从Oracle数据库全量同步到MySQL
前DBA用python写的脚本，每天全量同步一次，先delete再insert
现在表的空间已经差不多2G了，需要进行清理
一.查询表大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema='load_data' and table_name='tab_test';
查看表大小
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema='load_data' and table_name='tab_test';
+-----------+
| data |
+-----------+
| 2050.02MB |
+-----------+
1 row in set (0.04 sec)
查看表有多少数据量
mysql> select count(*) from tab_test;
+----------+
| count(*) |
+----------+
| 5177608 |
+----------+
1 row in set (16.78 sec)
500多w数据，居然占了2G的空间
二.碎片整理
alter table tab_test engine=innodb;
居然遇到表锁的问题了
-- 超过2个小时都没有响应
mysql> alter table tab_test engine=innodb;
-- 查看进程
mysql> show processlist;
+--------+-----------------+----------------------+-----------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-----------------+----------------------+-----------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 5327 | Waiting for next activation | NULL |
| 174386 | recording_user | 101.132.78.154:51368 | load_data | Query | 55669 | executing | select count(*) from tab_test |
| 177066 | recording_user | 101.132.78.154:37752 | load_data | Query | 47438 | Waiting for table metadata lock | alter table tab_test engine=innodb |
| 177364 | recording_user | 101.132.78.154:41700 | load_data | Query | 7310 | Waiting for table metadata lock | DELETE FROM load_data.tab_test |
| 177391 | recording_user | 218.17.184.91:2686 | load_data | Query | 1682 | Waiting for table metadata lock | SELECT * FROM `load_data`.`tab_test` LIMIT 0, 1000 |
| 177411 | root | 39.108.126.72:58836 | mysql | Query | 1121 | Waiting for table metadata lock | select
I.dt as 日期,
II.HRCODE AS 员工工号,
II.NAME AS 姓名,
I.content_cnt AS 催记量,
II |
| 177442 | recording_user | 218.17.184.91:3793 | load_data | Query | 774 | Waiting for table metadata lock | SELECT * FROM `load_data`.`tab_test` LIMIT 0, 1000 |
| 177444 | recording_user | 218.17.184.91:3794 | load_data | Sleep | 778 | | NULL |
| 177467 | recording_user | 101.132.78.154:46796 | NULL | Sleep | 602 | | NULL |
| 177490 | root | localhost | load_data | Query | 383 | Waiting for table metadata lock | select count(*) from tab_test |
| 177538 | root | localhost | load_data | Query | 56 | Waiting for table metadata lock | select * from tab_test limit 10 |
| 177546 | root | localhost | NULL | Query | 0 | starting | show processlist |
+--------+-----------------+----------------------+-----------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)
mysql> kill 4;
ERROR 1094 (HY000): Unknown thread id: 4
mysql>
mysql> kill 174386;
Query OK, 0 rows affected (0.00 sec)
mysql> kill 177066;
Query OK, 0 rows affected (0.00 sec)
mysql> kill 177364;
Query OK, 0 rows affected (0.00 sec)
mysql> kill 177391;
Query OK, 0 rows affected (0.00 sec)
mysql> kill 177442;
Query OK, 0 rows affected (0.00 sec)
mysql> kill 177444;
Query OK, 0 rows affected (0.04 sec)
mysql> kill 177467;
Query OK, 0 rows affected (0.00 sec)
mysql> kill 177490;
Query OK, 0 rows affected (0.00 sec)
mysql> kill 177538;
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql>
mysql> show processlist;
+--------+-----------------+----------------------+-----------+---------+-------+-----------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-----------------+----------------------+-----------+---------+-------+-----------------------------+------------------------------------------------------------------------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 5529 | Waiting for next activation | NULL |
| 174386 | recording_user | 101.132.78.154:51368 | load_data | Killed | 55871 | executing | select count(*) from tab_test |
| 177411 | root | 39.108.126.72:58836 | mysql | Query | 1323 | executing | select
I.dt as 日期,
II.HRCODE AS 员工工号,
II.NAME AS 姓名,
I.content_cnt AS 催记量,
II |
| 177546 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 177571 | recording_user | 218.17.184.91:4505 | load_data | Sleep | 60 | | NULL |
| 177572 | recording_user | 218.17.184.91:4523 | load_data | Sleep | 61 | | NULL |
| 177575 | root | localhost | load_data | Sleep | 53 | | NULL |
+--------+-----------------+----------------------+-----------+---------+-------+-----------------------------+------------------------------------------------------------------------------------------------------+
7 rows in set (0.01 sec)
居然有一个进程被描述为killed 此时重新执行依旧卡住,显示在等元数据锁
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 421158938794160
trx_state: RUNNING
trx_started: 2020-08-19 09:30:23
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 177411
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 421158938789840
trx_state: RUNNING
trx_started: 2020-08-18 18:00:58
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 174386
trx_query: select count(*) from tab_test
trx_operation_state: counting records
trx_tables_in_use: 1
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 1
trx_autocommit_non_locking: 1
2 rows in set (0.00 sec)
-- 也可以这样拼接select 语句
mysql> select concat('KILL ',id,';') from information_schema.processlist where user='recording_user' and db = 'load_data';
+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 174386; |
| KILL 177571; |
| KILL 177572; |
+------------------------+
3 rows in set (0.02 sec)
找了一圈也没有找到回滚的，只能申请晚上重启mysql实例了
难道mysql 8.0版本修复了这个问题，delete+insert的居然没用
mysql> alter table tab_test engine innodb;
Query OK, 0 rows affected (1 min 36.81 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema='load_data' and table_name='tab_test';
+-----------+
| data |
+-----------+
| 2204.02MB |
+-----------+
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.17 |
+-----------+
1 row in set (0.00 sec)
参考文献:
1.https://blog.csdn.net/m0_37827567/article/details/82979767?utm_source=blogxgwz5
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
