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
UNIONstatements, with some exceptions described later. -
Evaluation of some views, such those that use the
TEMPTABLEalgorithm,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 BYclause and a differentGROUP BYclause, or for which theORDER BYorGROUP BYcontains columns from tables other than the first table in the join queue. -
Evaluation of
DISTINCTcombined withORDER BYmay require a temporary table. -
For queries that use the
SQL_SMALL_RESULTmodifier, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage. -
To evaluate
INSERT ... SELECTstatements that select from and insert into the same table, MySQL creates an internal temporary table to hold the rows from theSELECT, then inserts those rows into the target table. See Section 13.2.6.1, “INSERT … SELECT Syntax”. -
Evaluation of multiple-table
UPDATEstatements. -
Evaluation of
GROUP_CONCAT()orCOUNT(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-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 " $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.