a fellow in IMG wechat group 2 met an error about running out of disk space when using MySQL query today,now let's mimic the situation:
    first,i'll create a test table and a procedure,then using the procedure to insert 1000W records into test table "test":
 
(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.


来源: https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html

 

    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-root  8.4G  8.3G  122M  99% /
  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   78G  8.3G  91% /vagrant
 11 
 12 [root@zlm3 09:19:20 /data/mysql/mysql3306]
 13 #du -sh /data
 14 3.8G    /data --Even though the "df -h" shows that it's out of space already,but the "/data/" dir still don't increase at all.
 15 
 16 [root@zlm3 09:20:21 /data/mysql/mysql3306]
 17 #mysqladmin var|grep -i tmp --Check the parameter about key word "tmp".
 18 | default_tmp_storage_engine                               | InnoDB                                                                                                                                                                                                                                                                                                                                                                                                           |
 19 | innodb_temp_data_file_path                               | ibtmp1:12M:autoextend                                                                                                                                                                                                                                                                                                                                                                                            |
 20 | innodb_tmpdir                                            |                                                                                                                                                                                                                                                                                                                                                                                                                  |
 21 | internal_tmp_disk_storage_engine                         | InnoDB                                                                                                                                                                                                                                                                                                                                                                                                           |
 22 | max_tmp_tables                                           | 32                                                                                                                                                                                                                                                                                                                                                                                                               |
 23 | slave_load_tmpdir                                        | /data/mysql/mysql3306/tmp                                                                                                                                                                                                                                                                                                                                                                                        |
 24 | socket                                                   | /tmp/mysql3306.sock                                                                                                                                                                                                                                                                                                                                                                                              |
 25 | tmp_table_size                                           | 100663296                                                                                                                                                                                                                                                                                                                                                                                                        |
 26 | tmpdir                                                   | /data/mysql/mysql3306/tmp                                                                                                                                                                                                                                                                                                                                                                                        |
 27 
 28 [root@zlm3 09:22:54 /data/mysql/mysql3306/tmp]
 29 #ls -l --No files here at all,why?'cause it has been deleted,see below:
 30 total 0
 31 
 32 [root@zlm3 09:22:56 /data/mysql/mysql3306/tmp]
 33 #lsof -n|grep deleted --List open files using "lsof" command.there're lots of deleted tmp file used by mysqld process
 34 tuned      566         root  txt       REG              253,0      7136   17004368 /usr/bin/python2.7;5b15ee3d (deleted)
 35 gmain      566  719    root  txt       REG              253,0      7136   17004368 /usr/bin/python2.7;5b15ee3d (deleted)
 36 tuned      566  721    root  txt       REG              253,0      7136   17004368 /usr/bin/python2.7;5b15ee3d (deleted)
 37 tuned      566  723    root  txt       REG              253,0      7136   17004368 /usr/bin/python2.7;5b15ee3d (deleted)
 38 tuned      566  728    root  txt       REG              253,0      7136   17004368 /usr/bin/python2.7;5b15ee3d (deleted)
 39 mysqld    6031        mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 40 mysqld    6031        mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 41 mysqld    6031        mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 42 mysqld    6031        mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 43 mysqld    6031 6036   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 44 mysqld    6031 6036   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 45 mysqld    6031 6036   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 46 mysqld    6031 6036   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 47 mysqld    6031 6037   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 48 mysqld    6031 6037   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 49 mysqld    6031 6037   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 50 mysqld    6031 6037   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 51 mysqld    6031 6038   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 52 mysqld    6031 6038   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 53 mysqld    6031 6038   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 54 mysqld    6031 6038   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 55 mysqld    6031 6039   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 56 mysqld    6031 6039   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 57 mysqld    6031 6039   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 58 mysqld    6031 6039   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 59 mysqld    6031 6040   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 60 mysqld    6031 6040   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 61 mysqld    6031 6040   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 62 mysqld    6031 6040   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 63 mysqld    6031 6041   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 64 mysqld    6031 6041   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 65 mysqld    6031 6041   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 66 mysqld    6031 6041   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 67 mysqld    6031 6042   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 68 mysqld    6031 6042   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 69 mysqld    6031 6042   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 70 mysqld    6031 6042   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 71 mysqld    6031 6043   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 72 mysqld    6031 6043   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 73 mysqld    6031 6043   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 74 mysqld    6031 6043   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 75 mysqld    6031 6044   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 76 mysqld    6031 6044   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 77 mysqld    6031 6044   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 78 mysqld    6031 6044   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 79 mysqld    6031 6045   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 80 mysqld    6031 6045   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 81 mysqld    6031 6045   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 82 mysqld    6031 6045   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 83 mysqld    6031 6046   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 84 mysqld    6031 6046   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 85 mysqld    6031 6046   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 86 mysqld    6031 6046   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 87 mysqld    6031 6047   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 88 mysqld    6031 6047   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 89 mysqld    6031 6047   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 90 mysqld    6031 6047   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 91 mysqld    6031 6049   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 92 mysqld    6031 6049   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 93 mysqld    6031 6049   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 94 mysqld    6031 6049   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 95 mysqld    6031 6050   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
 96 mysqld    6031 6050   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
 97 mysqld    6031 6050   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
 98 mysqld    6031 6050   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
 99 mysqld    6031 6051   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
100 mysqld    6031 6051   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
101 mysqld    6031 6051   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
102 mysqld    6031 6051   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
103 mysqld    6031 6052   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
104 mysqld    6031 6052   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
105 mysqld    6031 6052   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
106 mysqld    6031 6052   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
107 mysqld    6031 6053   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
108 mysqld    6031 6053   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
109 mysqld    6031 6053   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
110 mysqld    6031 6053   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
111 mysqld    6031 6054   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
112 mysqld    6031 6054   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
113 mysqld    6031 6054   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
114 mysqld    6031 6054   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
115 mysqld    6031 6055   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
116 mysqld    6031 6055   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
117 mysqld    6031 6055   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
118 mysqld    6031 6055   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
119 mysqld    6031 6056   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
120 mysqld    6031 6056   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
121 mysqld    6031 6056   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
122 mysqld    6031 6056   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
123 mysqld    6031 6057   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
124 mysqld    6031 6057   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
125 mysqld    6031 6057   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
126 mysqld    6031 6057   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
127 mysqld    6031 6058   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
128 mysqld    6031 6058   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
129 mysqld    6031 6058   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
130 mysqld    6031 6058   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
131 mysqld    6031 6059   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
132 mysqld    6031 6059   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
133 mysqld    6031 6059   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
134 mysqld    6031 6059   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
135 mysqld    6031 6060   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
136 mysqld    6031 6060   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
137 mysqld    6031 6060   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
138 mysqld    6031 6060   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
139 mysqld    6031 6061   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
140 mysqld    6031 6061   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
141 mysqld    6031 6061   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
142 mysqld    6031 6061   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
143 mysqld    6031 6062   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
144 mysqld    6031 6062   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
145 mysqld    6031 6062   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
146 mysqld    6031 6062   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
147 mysqld    6031 6064   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
148 mysqld    6031 6064   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
149 mysqld    6031 6064   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
150 mysqld    6031 6064   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
151 mysqld    6031 6387   mysql    6u      REG              253,0         0   33919399 /data/mysql/mysql3306/tmp/ibuwlBKV (deleted)
152 mysqld    6031 6387   mysql    7u      REG              253,0         0   33919400 /data/mysql/mysql3306/tmp/ibhJgkTN (deleted)
153 mysqld    6031 6387   mysql    8u      REG              253,0         0   33919402 /data/mysql/mysql3306/tmp/ibrjB3fy (deleted)
154 mysqld    6031 6387   mysql   13u      REG              253,0         0   33919403 /data/mysql/mysql3306/tmp/ibEqcSBq (deleted)
155 
156 [root@zlm3 09:24:32 /data/mysql/mysql3306/tmp]
157 #lsof -n | grep deleted | awk -F ' ' '{printf "kill -9 %s\n" $2}' > /root/free_handle_space.sh
158 awk: cmd. line:1: (FILENAME=- FNR=1) fatal: not enough arguments to satisfy format string
159     `kill -9 %s
160 566'
161              ^ ran out for this one --Is this a gramma mistake here?i've been tested before and it's ok,should it be the lack of disk space?
162 
163 [root@zlm3 09:53:07 /data/mysql/mysql3306/tmp]
164 #

 

    the process id here is 6031,i supposed that i can kill the process to release the handler of files and get back the disk space:

 

 1 [root@zlm3 09:56:06 /data/mysql/mysql3306/tmp]
 2 #kill 6031
 3 
 4 [root@zlm3 10:13:42 /data/mysql/mysql3306/tmp]
 5 #lsof -n | grep deleted  --The lots of deleted tmp file has gone now.
 6 tuned      566         root  txt       REG              253,0      7136   17004368 /usr/bin/python2.7;5b15ee3d (deleted)
 7 gmain      566  719    root  txt       REG              253,0      7136   17004368 /usr/bin/python2.7;5b15ee3d (deleted)
 8 tuned      566  721    root  txt       REG              253,0      7136   17004368 /usr/bin/python2.7;5b15ee3d (deleted)
 9 tuned      566  723    root  txt       REG              253,0      7136   17004368 /usr/bin/python2.7;5b15ee3d (deleted)
10 tuned      566  728    root  txt       REG              253,0      7136   17004368 /usr/bin/python2.7;5b15ee3d (deleted)
11 
12 [root@zlm3 10:14:06 /data/mysql/mysql3306/tmp]
13 #

 

    what shocked me is that the disk space was not released after my killing operation:

 

 1 [root@zlm3 10:14:17 ~]
 2 #df -h
 3 Filesystem               Size  Used Avail Use% Mounted on
 4 /dev/mapper/centos-root  8.4G  8.3G  134M  99% /  --Still 99% in Use% cloumn
 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   78G  8.3G  91% /vagrant
11 
12 [root@zlm3 10:15:25 ~]
13 #

 

    what's wrong with it?what if i kill the mysql process?

 

 1 [root@zlm3 10:14:06 /data/mysql/mysql3306/tmp]
 2 #ps aux|grep mysql
 3 root      6420  0.0  0.2 134112  2292 pts/0    S+   08:58   0:00 mysql
 4 root      6656  0.0  0.0 112640   960 pts/1    R+   10:21   0:00 grep --color=auto mysql
 5 
 6 [root@zlm3 10:21:31 /data/mysql/mysql3306/tmp]
 7 #kill 6420
 8 
 9 [root@zlm3 10:29:39 /data/mysql/mysql3306/tmp]
10 #ps aux|grep mysql
11 root      6679  0.0  0.0 112640   960 pts/1    R+   10:29   0:00 grep --color=auto mysql
12 
13 [root@zlm3 10:29:49 /data/mysql/mysql3306/tmp]
14 #

 

    nothing changed,the disk space is still full:

 

 1 [root@zlm3 10:29:47 ~]
 2 #df -h
 3 Filesystem               Size  Used Avail Use% Mounted on
 4 /dev/mapper/centos-root  8.4G  8.3G  134M  99% /  --Change nothing,99% again.
 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   78G  8.3G  91% /vagrant
11 
12 [root@zlm3 10:29:55 ~]
13 #

 

    after i drop the table "test",it just release 10% disk sapce,where has the free space gone?

 

 1 [root@zlm3 10:29:55 ~]
 2 #df -h
 3 Filesystem               Size  Used Avail Use% Mounted on
 4 /dev/mapper/centos-root  8.4G  7.5G  962M  89% /
 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   78G  8.3G  91% /vagrant
11 
12 [root@zlm3 10:34:16 ~]
13 #

 

   then,i use "reset master;",the dike space finally came back:

 

 1 (root@localhost mysql3306.sock)[zlm]10:34:10>reset master;  --It's utterly forbidden to use this command on product MySQL server,take care.
 2 Query OK, 0 rows affected (0.08 sec)
 3 
 4 (root@localhost mysql3306.sock)[zlm]10:41:44>show tables;
 5 +---------------+
 6 | Tables_in_zlm |
 7 +---------------+
 8 | t1            |
 9 | t2            |
10 | t3            |
11 +---------------+
12 3 rows in set (0.00 sec)
13 
14 (root@localhost mysql3306.sock)[zlm]10:42:00>

 

 1 [root@zlm3 10:34:16 ~]
 2 #df -h
 3 Filesystem               Size  Used Avail Use% Mounted on
 4 /dev/mapper/centos-root  8.4G  5.0G  3.5G  60% /  --Now,all the space came back(before the insertation).
 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   78G  8.3G  91% /vagrant
11 
12 [root@zlm3 10:41:50 ~]
13 #cd /data/mysql/mysql3306/logs
14 
15 [root@zlm3 10:47:49 /data/mysql/mysql3306/logs]
16 #ls -l
17 total 8
18 -rw-r----- 1 mysql mysql 154 Jun  5 10:41 mysql-bin.000001
19 -rw-r----- 1 mysql mysql  44 Jun  5 10:41 mysql-bin.index
20 
21 [root@zlm3 10:47:50 /data/mysql/mysql3306/logs]
22 #

 

Summary:
  • the test what i made just now was not so good to explain what i wanna say,perhaps i didn't use the accurate sample data.
  • generally speaking,the file handler is the common reason why the deletion operation doesn't release disk space.
  • operations such as "distinct","group by" in MySQL query will use temperory table to sort on disk,it depend on the several parameter("max_tmp_tables","max_heap_table_size" and etc.)you've set in config file "my.cnf".
  • not using index will lead to huge amount of sort operation which will generate poor performance of  MySQL server.
  • before you kill the process to release the disk space,do think twice that the deleted file is really unnecessary any more.