執行SQL查詢導致磁碟耗盡故障演示

(root@localhost mysql3306.sock)[zlm]08:46:36>create table test(
    -> id int unsigned not null,  --Notice,there is no primary key and any other key here.
    -> name char(50) not null default ''
    -> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)

(root@localhost mysql3306.sock)[zlm]08:46:45>delimiter $$
(root@localhost mysql3306.sock)[zlm]08:46:54>create procedure pro_test()
    -> begin
    -> declare i int;
    -> set i=10000000;
    -> while i>0 do
    -> insert into test(id,name) values (i,'aaron8219');
    -> set i=i-1;
    -> end while;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

(root@localhost mysql3306.sock)[zlm]08:46:54>delimiter ;
(root@localhost mysql3306.sock)[zlm]08:47:13>call pro_test;
^C^C -- query aborted  --I'm afraid the disk space will be insufficient,so i cancel the procedure by Ctrl+C.
Terminated

    it's the incremental change result of the command "df -h" output below at the begining to the point that i cancel insertation:

 1 [root@zlm3 08:47:20 /data/mysql/mysql3306]
 2 #df -h
 3 Filesystem               Size  Used Avail Use% Mounted on
 4 /dev/mapper/centos-root  8.4G  5.1G  3.4G  60% /
 5 devtmpfs                 488M     0  488M   0% /dev
 6 tmpfs                    497M     0  497M   0% /dev/shm
 7 tmpfs                    497M  6.6M  491M   2% /run
 8 tmpfs                    497M     0  497M   0% /sys/fs/cgroup
 9 /dev/sda1                497M  118M  379M  24% /boot
10 none                      87G   76G   11G  88% /vagrant
11 
12 [root@zlm3 08:47:22 /data/mysql/mysql3306]
13 #df -h
14 Filesystem               Size  Used Avail Use% Mounted on
15 /dev/mapper/centos-root  8.4G  5.8G  2.6G  70% /
16 devtmpfs                 488M     0  488M   0% /dev
17 tmpfs                    497M     0  497M   0% /dev/shm
18 tmpfs                    497M  6.6M  491M   2% /run
19 tmpfs                    497M     0  497M   0% /sys/fs/cgroup
20 /dev/sda1                497M  118M  379M  24% /boot
21 none                      87G   76G   11G  88% /vagrant
22 
23 [root@zlm3 08:51:27 /data/mysql/mysql3306]
24 #df -h
25 Filesystem               Size  Used Avail Use% Mounted on
26 /dev/mapper/centos-root  8.4G  6.2G  2.3G  74% /
27 devtmpfs                 488M     0  488M   0% /dev
28 tmpfs                    497M     0  497M   0% /dev/shm
29 tmpfs                    497M  6.6M  491M   2% /run
30 tmpfs                    497M     0  497M   0% /sys/fs/cgroup
31 /dev/sda1                497M  118M  379M  24% /boot
32 none                      87G   76G   11G  88% /vagrant
33 
34 [root@zlm3 08:53:06 /data/mysql/mysql3306]
35 #df -h
36 Filesystem               Size  Used Avail Use% Mounted on
37 /dev/mapper/centos-root  8.4G  7.0G  1.4G  84% /  -- The free disk space became 16% (maybe less) when i cancel the operation.
38 devtmpfs                 488M     0  488M   0% /dev
39 tmpfs                    497M     0  497M   0% /dev/shm
40 tmpfs                    497M  6.6M  491M   2% /run
41 tmpfs                    497M     0  497M   0% /sys/fs/cgroup
42 /dev/sda1                497M  118M  379M  24% /boot
43 none                      87G   77G  9.6G  89% /vagrant
44 
45 [root@zlm3 08:57:30 /data/mysql/mysql3306]
46 #ps -ef|grep mysql
47 mysql     6031     1 12 08:00 ?        00:07:05 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
48 root      6182  6157  0 08:46 pts/0    00:00:00 mysql
49 root      6389  6085  0 08:58 pts/1    00:00:00 grep --color=auto mysql
50 
51 [root@zlm3 08:58:13 /data/mysql/mysql3306]
52 #kill 6182  -- After cancel the operation,i kill the mysql process in this session.
53 
54 [root@zlm3 08:58:22 /data/mysql/mysql3306]
55 #ps -ef|grep mysql
56 mysql     6031     1 12 08:00 ?        00:07:17 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
57 root      6403  6085  0 08:58 pts/1    00:00:00 grep --color=auto mysql
58 
59 [root@zlm3 08:58:29 /data/mysql/mysql3306]
60 #

    in the first session,relogin with mysql client,check the status of the table test:

 1 #mysql
 2 Welcome to the MySQL monitor.  Commands end with ; or \g.
 3 Your MySQL connection id is 7
 4 Server version: 5.7.21-log MySQL Community Server (GPL)
 5 
 6 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 7 
 8 Oracle is a registered trademark of Oracle Corporation and/or its
 9 affiliates. Other names may be trademarks of their respective
10 owners.
11 
12 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
13 
14 (root@localhost mysql3306.sock)[(none)]08:58:35>use zlm;
15 Reading table information for completion of table and column names
16 You can turn off this feature to get a quicker startup with -A
17 
18 Database changed
19 (root@localhost mysql3306.sock)[zlm]08:59:08>show tables;
20 +---------------+
21 | Tables_in_zlm |
22 +---------------+
23 | t1            |
24 | t2            |
25 | t3            |
26 | test          |
27 +---------------+
28 4 rows in set (0.00 sec)
29 
30 (root@localhost mysql3306.sock)[zlm]09:00:49>select table_schema,concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
31     -> concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
32     -> from information_schema.tables
33     -> where table_schema='zlm' and table_name='test';
34 +--------------+-----------+------------+
35 | table_schema | data_size | index_size |
36 +--------------+-----------+------------+
37 | zlm          | 656.00MB  | 0.00MB     |  --Now the table "test" has alread been a big table,the size turned into 656Mb.
38 +--------------+-----------+------------+
39 1 row in set (0.10 sec)
40 
41 (root@localhost mysql3306.sock)[zlm]09:01:34>select count(*) from test;
42 +----------+
43 | count(*) |
44 +----------+
45 |  9070823 | --Almost 1000W records around.
46 +----------+
47 1 row in set (19.16 sec)
48 
49 (root@localhost mysql3306.sock)[zlm]09:02:29>show master status;
50 +------------------+-----------+--------------+------------------+-------------------------------------------------+
51 | File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
52 +------------------+-----------+--------------+------------------+-------------------------------------------------+
53 | mysql-bin.000057 | 244082592 |              |                  | 5c77c31b-4add-11e8-81e2-080027de0e0e:1-13527727 |
54 +------------------+-----------+--------------+------------------+-------------------------------------------------+
55 1 row in set (0.06 sec)
56 
57 (root@localhost mysql3306.sock)[zlm]09:12:58>

    let's check the binlog file,these files really ocuppied a huge amount disk space:

[root@zlm3 08:58:42 /data/mysql/mysql3306/logs]
#ls -l
total 2384300
-rw-r----- 1 mysql mysql      3831 May 28 03:20 mysql-bin.000019
-rw-r----- 1 mysql mysql      9564 May 28 11:37 mysql-bin.000020
-rw-r----- 1 mysql mysql      4761 May 29 11:27 mysql-bin.000021
-rw-r----- 1 mysql mysql       217 May 30 11:29 mysql-bin.000022
-rw-r----- 1 mysql mysql       217 May 31 03:20 mysql-bin.000023
-rw-r----- 1 mysql mysql       613 May 31 03:29 mysql-bin.000024
-rw-r----- 1 mysql mysql      1009 May 31 03:35 mysql-bin.000025
-rw-r----- 1 mysql mysql       217 May 31 03:36 mysql-bin.000026
-rw-r----- 1 mysql mysql       217 May 31 03:37 mysql-bin.000027
-rw-r----- 1 mysql mysql       217 May 31 03:38 mysql-bin.000028
-rw-r----- 1 mysql mysql       217 May 31 03:40 mysql-bin.000029
-rw-r----- 1 mysql mysql      1563 May 31 03:45 mysql-bin.000030
-rw-r----- 1 mysql mysql       217 May 31 06:50 mysql-bin.000031
-rw-r----- 1 mysql mysql       217 May 31 06:59 mysql-bin.000032
-rw-r----- 1 mysql mysql       217 May 31 07:04 mysql-bin.000033
-rw-r----- 1 mysql mysql       217 May 31 07:13 mysql-bin.000034
-rw-r----- 1 mysql mysql       217 May 31 07:15 mysql-bin.000035
-rw-r----- 1 mysql mysql       217 May 31 07:42 mysql-bin.000036
-rw-r----- 1 mysql mysql       461 May 31 08:22 mysql-bin.000037
-rw-r----- 1 mysql mysql       217 May 31 08:25 mysql-bin.000038
-rw-r----- 1 mysql mysql       613 May 31 10:37 mysql-bin.000039
-rw-r----- 1 mysql mysql       369 May 31 10:41 mysql-bin.000040
-rw-r----- 1 mysql mysql       613 May 31 11:28 mysql-bin.000041
-rw-r----- 1 mysql mysql      3141 Jun  1 10:10 mysql-bin.000042
-rw-r----- 1 mysql mysql      5677 Jun  1 11:38 mysql-bin.000043
-rw-r----- 1 mysql mysql       217 Jun  4 07:54 mysql-bin.000044
-rw-r----- 1 mysql mysql       194 Jun  4 07:57 mysql-bin.000045
-rw-r----- 1 mysql mysql       217 Jun  4 07:57 mysql-bin.000046
-rw-r----- 1 mysql mysql       217 Jun  4 11:23 mysql-bin.000047
-rw-r----- 1 mysql mysql 268435609 Jun  5 08:48 mysql-bin.000048
-rw-r----- 1 mysql mysql 268435737 Jun  5 08:50 mysql-bin.000049
-rw-r----- 1 mysql mysql 268435737 Jun  5 08:52 mysql-bin.000050
-rw-r----- 1 mysql mysql 268435737 Jun  5 08:54 mysql-bin.000051
-rw-r----- 1 mysql mysql 268435737 Jun  5 08:55 mysql-bin.000052
-rw-r----- 1 mysql mysql 268435737 Jun  5 08:57 mysql-bin.000053
-rw-r----- 1 mysql mysql 268435737 Jun  5 08:58 mysql-bin.000054
-rw-r----- 1 mysql mysql 268435737 Jun  5 09:00 mysql-bin.000055
-rw-r----- 1 mysql mysql 268435737 Jun  5 09:02 mysql-bin.000056  --From binlog 48 to 56,each one exhausted the max size at 256M.
-rw-r----- 1 mysql mysql  25366220 Jun  5 09:02 mysql-bin.000057  --This is the last binlog file contain the last several records.
-rw-r----- 1 mysql mysql      1716 Jun  5 09:02 mysql-bin.index

[root@zlm3 09:09:43 /data/mysql/mysql3306/logs]
#mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000057| tail -20
#180605  9:03:45 server id 1023306  end_log_pos 244082462 CRC32 0x9db85944     Query    thread_id=5    exec_time=0    error_code=0
SET TIMESTAMP=1528182225/*!*/;
BEGIN
/*!*/;
# at 244082462
#180605  9:03:45 server id 1023306  end_log_pos 244082511 CRC32 0x8bb85bae     Table_map: `zlm`.`test` mapped to number 104
# at 244082511
#180605  9:03:45 server id 1023306  end_log_pos 244082561 CRC32 0x6ede8301     Write_rows: table id 104 flags: STMT_END_F
### INSERT INTO `zlm`.`test`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='aaron8219' /* STRING(150) meta=65174 nullable=0 is_null=0 */
# at 244082561
#180605  9:03:45 server id 1023306  end_log_pos 244082592 CRC32 0xf983b21b     Xid = 30000069  --The "end_log_pos 244082592" is equal with the output result of the postion in "show master status;" command.
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; --GTID_NEXT='AUTOMATIC' means there're no more sequential binlogs behind.
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@zlm3 09:11:21 /data/mysql/mysql3306/logs]
#

    now,let's do some testing query below(using distinct/group by):

1 (root@localhost mysql3306.sock)[zlm]09:16:31>select count(distinct(id)) from test group by name;
2 ERROR 3 (HY000): Error writing file '/data/mysql/mysql3306/tmp/MYCUMGkU' (Errcode: 28 - No space left on device)
3 (root@localhost mysql3306.sock)[zlm]09:18:34>system ps aux|grep mysql
4 root      6420  0.0  0.2 134112  2292 pts/0    S+   08:58   0:00 mysql
5 root      6667  0.0  0.1 113116  1360 pts/0    S+   09:17   0:00 sh -c  ps aux|grep mysql
6 root      6669  0.0  0.0 112640   944 pts/0    R+   09:17   0:00 grep mysql
7 (root@localhost mysql3306.sock)[zlm]10:22:13>

    eventually,the query ended with error "Errcode:28 - no space left on device",obviously the mysqld process has dead now.check the disk space,it's 99% in Use% column,why does the disk space continuously increase?let's see the official document about this bellow:

8.4.4 Internal Temporary Table Use in MySQL

In some cases, the server creates internal temporary tables while processing statements. Users have no direct control over when this occurs.

The server creates temporary tables under conditions such as these:

• Evaluation of UNION statements, with some exceptions described later.

• Evaluation of some views, such those that use the TEMPTABLE algorithm, UNION, or aggregation.

• Evaluation of derived tables (see Section 13.2.11.8, “Derived Tables”).

• Evaluation of common table expressions (see Section 13.2.13, “WITH Syntax (Common Table Expressions)”).

• Tables created for subquery or semi-join materialization (see Section 8.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”).

• Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.

• Evaluation of DISTINCT combined with ORDER BY may require a temporary table.

• For queries that use the SQL_SMALL_RESULT modifier, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

• To evaluate INSERT ... SELECT statements that select from and insert into the same table, MySQL creates an internal temporary table to hold the rows from the SELECT, then inserts those rows into the target table. See Section 13.2.6.1, “INSERT ... SELECT Syntax”.

• Evaluation of multiple-table UPDATE statements.

• Evaluation of GROUP_CONCAT() or COUNT(DISTINCT) expressions.

    here's the conclusion,whenever above conditions occured,it will induce MySQL server to do sort operation in memory or in disk(this is too bad thing),hence,in our test above,the disk space was exhausted soon.

  1 [root@zlm3 08:58:29 /data/mysql/mysql3306]
  2 #df -h
  3 Filesystem               Size  Used Avail Use% Mounted on
  4 /dev/mapper/centos