<commit_before>---
title: MySQL -- 问题排查
date: 2019-02-13 14:02:09
categories:
    - Storage
    - MySQL
tags:
    - Storage
    - MySQL
---

## 表初始化
```sql
CREATE TABLE `t` (
    `id` INT(11) NOT NULL,
    `c` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DELIMITER ;;
CREATE PROCEDURE idata()
BEGIN
    DECLARE i INT;
    SET i=1;
    WHILE (i<=100000) DO
        INSERT INTO t VALUES (i,i);
        SET i=i+1;
    END WHILE;
END;;
DELIMITER ;

CALL idata();
```

<!-- more -->

## 查询长时间等待
大概率是表`t`被锁住了，通过`SHOW PROCESSLIST;`查看语句处于什么状态
```sql
SELECT * FROM t WHERE id=1;
```

### 等MDL

#### 执行时序
| session A | session B |
| ---- | ---- |
| LOCK TABLE t WRITE; | |
| | SELECT * FROM t WHERE id=1; |

1. session A通过`LOCK TABLE`命令持有表`t`的**MDL写锁**
2. session B执行DML，需要先持有表`t`的**MDL读锁**，从而进入阻塞状态

#### 语句状态
session B的线程ID为33，状态为等待MDL：`Waiting for table metadata lock`
```sql
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------+
| Id | User            | Host      | db   | Command | Time  | State                           | Info                       |
+----+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 21668 | Waiting on empty queue          | NULL                       |
| 30 | root            | localhost | test | Query   |    33 | Waiting for table metadata lock | SELECT * FROM t WHERE id=1 |
| 31 | root            | localhost | test | Sleep   |    41 |                                 | NULL                       |
| 33 | root            | localhost | NULL | Query   |     0 | starting                        | SHOW PROCESSLIST           |
+----+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------+

-- 设置成`ON`，会有10%左右的性能损失
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
```

#### 锁信息
`blocking_pid=31`阻塞`waiting_pid=30`，应该采用`KILL 31`
```sql
mysql> SELECT * FROM sys.schema_table_lock_waits\G;
*************************** 1. row ***************************
               object_schema: test
                 object_name: t
           waiting_thread_id: 69
                 waiting_pid: 30
             waiting_account: root@localhost
           waiting_lock_type: SHARED_READ
       waiting_lock_duration: TRANSACTION
               waiting_query: SELECT * FROM t WHERE id=1
          waiting_query_secs: 404
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 70
                blocking_pid: 31
            blocking_account: root@localhost
          blocking_lock_type: SHARED_NO_READ_WRITE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 31
sql_kill_blocking_connection: KILL 31
```

### 等flush

#### flush tables
```sql
-- Closes all open tables, forces all tables in use to be closed, and flushes the prepared statement cache.
FLUSH TABLES t WITH READ LOCK;
FLUSH TABLES WITH READ LOCK;
```

#### 执行时序
| 时刻 | session A | session B | session C | session D |
| ---- | ---- | ---- | ---- |
| T1 | SELECT SLEEP(1) FROM t; | | | |
| T2 | | | | SHOW PROCESSLIST; |
| T3 | | FLUSH TABLES t; | | |
| T4 | | | | SHOW PROCESSLIST; |
| T5 | | | SELECT * FROM t WEHERE id=1; | |
| T6 | | | | SHOW PROCESSLIST; |

1. session A：执行10W秒，在这期间表`t`会被session A**一直打开**
2. session B：需要等待session A的查询结束后才能执行flush命令，被阻塞
3. session C：在session B中的flush命令还未执行完成时发起查询操作，会**被flush命令阻塞**

#### 语句状态
```sql
-- T2时刻
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------------+
| Id | User            | Host      | db   | Command | Time  | State                  | Info                   |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 26199 | Waiting on empty queue | NULL                   |
| 30 | root            | localhost | test | Sleep   |   434 |                        | NULL                   |
| 33 | root            | localhost | test | Sleep   |   430 |                        | NULL                   |
| 34 | root            | localhost | test | Query   |     6 | User sleep             | SELECT SLEEP(1) FROM t |
| 35 | root            | localhost | test | Query   |     0 | starting               | SHOW PROCESSLIST       |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------------+
<commit_msg>update mysql-troubleshoot<commit_after>---
title: MySQL -- 问题排查
date: 2019-02-13 14:02:09
categories:
    - Storage
    - MySQL
tags:
    - Storage
    - MySQL
---

## 表初始化
```sql
CREATE TABLE `t` (
    `id` INT(11) NOT NULL,
    `c` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DELIMITER ;;
CREATE PROCEDURE idata()
BEGIN
    DECLARE i INT;
    SET i=1;
    WHILE (i<=100000) DO
        INSERT INTO t VALUES (i,i);
        SET i=i+1;
    END WHILE;
END;;
DELIMITER ;

CALL idata();
```

<!-- more -->

## 查询长时间等待
大概率是表`t`被锁住了，通过`SHOW PROCESSLIST;`查看语句处于什么状态
```sql
SELECT * FROM t WHERE id=1;
```

### 等MDL

#### 执行时序
| session A | session B |
| ---- | ---- |
| LOCK TABLE t WRITE; | |
| | SELECT * FROM t WHERE id=1; |

1. session A通过`LOCK TABLE`命令持有表`t`的**MDL写锁**
2. session B执行DML，需要先持有表`t`的**MDL读锁**，从而进入阻塞状态

#### 语句状态
session B的线程ID为33，状态为等待MDL：`Waiting for table metadata lock`
```sql
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------+
| Id | User            | Host      | db   | Command | Time  | State                           | Info                       |
+----+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 21668 | Waiting on empty queue          | NULL                       |
| 30 | root            | localhost | test | Query   |    33 | Waiting for table metadata lock | SELECT * FROM t WHERE id=1 |
| 31 | root            | localhost | test | Sleep   |    41 |                                 | NULL                       |
| 33 | root            | localhost | NULL | Query   |     0 | starting                        | SHOW PROCESSLIST           |
+----+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------+

-- 设置成`ON`，会有10%左右的性能损失
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
```

#### 锁信息
`blocking_pid=31`阻塞`waiting_pid=30`，应该采用`KILL 31`
```sql
mysql> SELECT * FROM sys.schema_table_lock_waits\G;
*************************** 1. row ***************************
               object_schema: test
                 object_name: t
           waiting_thread_id: 69
                 waiting_pid: 30
             waiting_account: <EMAIL>
           waiting_lock_type: SHARED_READ
       waiting_lock_duration: TRANSACTION
               waiting_query: SELECT * FROM t WHERE id=1
          waiting_query_secs: 404
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 70
                blocking_pid: 31
            blocking_account: <EMAIL>
          blocking_lock_type: SHARED_NO_READ_WRITE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 31
sql_kill_blocking_connection: KILL 31
```

### 等flush

#### flush tables
```sql
-- Closes all open tables, forces all tables in use to be closed, and flushes the prepared statement cache.
FLUSH TABLES t WITH READ LOCK;
FLUSH TABLES WITH READ LOCK;
```

#### 执行时序

| 时刻 | session A | session B | session C | session D |
| --- | --- | --- | --- | --- |
| T1 | SELECT SLEEP(1) FROM t; | | | |
| T2 | | | | SHOW PROCESSLIST; |
| T3 | | FLUSH TABLES t; | | |
| T4 | | | | SHOW PROCESSLIST; |
| T5 | | | SELECT * FROM t WEHERE id=1; | |
| T6 | | | | SHOW PROCESSLIST; |

1. session A：执行10W秒，在这期间表`t`会被session A**一直打开**
2. session B：需要等待session A的查询结束后才能执行flush命令，被阻塞
3. session C：在session B中的flush命令还未执行完成时发起查询操作，会**被flush命令阻塞**

#### 语句状态
```sql
-- T2时刻
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------------+
| Id | User            | Host      | db   | Command | Time  | State                  | Info                   |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 26199 | Waiting on empty queue | NULL                   |
| 30 | root            | localhost | test | Sleep   |   434 |                        | NULL                   |
| 33 | root            | localhost | test | Sleep   |   430 |                        | NULL                   |
| 34 | root            | localhost | test | Query   |     6 | User sleep             | SELECT SLEEP(1) FROM t |
| 35 | root            | localhost | test | Query   |     0 | starting               | SHOW PROCESSLIST       |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------------+

{'fraction_non_alphanumeric': 0.27354447580223684, 'fraction_numerical': 0.02383192223267482, 'mean_word_length': 1.2678359800900687, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 14, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1540138', 'n_tokens_mistral': 3195, 'n_tokens_neox': 3044, 'n_words': 705}