MySQL kill使用案例

最新推荐文章于 2024-05-30 15:06:37 发布

只是甲

最新推荐文章于 2024-05-30 15:06:37 发布

阅读量829

点赞数

分类专栏： # MySQL案例 MySQL从小工到专家之路文章标签： mysql kill 杀进程清理表空间

本文链接：https://blog.csdn.net/u010520724/article/details/108592682

版权

MySQL从小工到专家之路同时被 2 个专栏收录

181 篇文章 23 订阅

订阅专栏

MySQL案例

28 篇文章 1 订阅

订阅专栏

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

只是甲

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL kill使用案例

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 w
复制链接

扫一扫

专栏目录