Handling of a Mysql/tmp catalog full problem

Suddenly received Zabbix alarm, said MySQL server/directory disk space is insufficient.

Log on to the server, looked at the root of the discovery 100GB, incredibly used almost 90GB. This server only ran a MySQL, should not log not clean up and other reasons caused.

(Note: The following are some of the late-cut, when some of the SQL ran, freed up some disk space)

Lsof |grep deleted found as follows:

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/99/39/wKioL1lF_ezRJ2IyAABQPE-MFtA584.png "title=" 1.png "alt=" Wkiol1lf_ezrj2iyaabqpe-mfta584.png "/> can see that this temporary file is almost 40GB.

Show Processlist; As follows:

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/99/39/wKiom1lF_mTxOcCPAAApUEGKL_0121.png "title=" 2.png "alt=" Wkiom1lf_mtxoccpaaapuegkl_0121.png "/>

See, not involved in the operation of writing Binlog, but because the simple select does not cause the/tmp directory full situation, so guess he this same transaction before there is involved in writing Binlog operation (update, delete, etc.).

Official notes:

Https://dev.mysql.com/doc/refman/5.6/en/binary-log.html

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/99/39/wKioL1lF_rbCcs8ZAAAYLZg3Ma8340.png "title=" 3.png "alt=" Wkiol1lf_rbccs8zaaaylzg3ma8340.png "/>

When the transaction starts, it assigns a buffer statement to a buffer of size binlog_cache_size (which I set here is 16777216bytes, or 16MB). If a statement is larger than this, the thread opens a temporary file to store the transaction (by default, it is stored in the/tmp/directory). When the thread ends, the temporary file is automatically deleted.

The above is because the temporary files in the transaction more than 16MB, was placed in the/tmp directory, but this temporary file is too large, resulting in insufficient disk space alarm.

Workaround:

When the above query is finished, we will first close the mysqld. (The condition can be allowed, of course, let the query itself end.) If you kill directly, you'll probably have a long time to roll back.

Then adjust the MySQL tmpdir to other larger disks to go.

Mkdir/bdata/mysql_tmp

Chown Mysql.mysql/bdata/mysql_tmp-r

Chown 1777-r/bdata/mysql_tmp-r

Vim/etc/my.cnf

[Mysqld]

Tmpdir =/bdata/mysql_tmp

Then start MySQL and you can

Execute Lsof|grep deleted again to see that the path to the temporary file has been changed to the/bdata/mysql_tmp directory.

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/99/39/wKioL1lF_t_yqanfAABBKYK1EhA286.png "title=" 4.png "alt=" Wkiol1lf_t_yqanfaabbkyk1eha286.png "/>

Handling of a Mysql/tmp catalog full problem