Shulou(Shulou.com)06/01 Report--

I suddenly received a zabbix alarm saying that the / directory disk space of the mysql server was insufficient.

Log in to the server, look at the root directory where you found 100GB, and use almost 90GB. There is only one MySQL running on this server, which should not be caused by other reasons such as uncleaned logs.

(note: the following screenshots were taken at a later stage, when some of the SQL had already finished running and some disk space had been freed.)

Lsof | grep deleted finds the following:

You can see that this temporary file is almost 40GB.

Show processlist; is as follows:

As shown in the figure above, the operation of writing binlog is not involved, but since select alone will not cause the / tmp directory to be full, it is speculated that the operation of writing binlog (update, delete, etc.) was involved in the same transaction.

Official statement:

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

When a thread that handles the transaction starts, it allocates a buffer of binlog_cache_size to buffer statements. If a statement is bigger than this, the thread opens a temporary file to store the transaction. The temporary file is deleted when the thread ends.

The Binlog_cache_use status variable shows the number of transactions that used this buffer (and possibly a temporary file) for storing statements. TheBinlog_cache_disk_use status variable shows how many of those transactions actually had to use a temporary file. These two variables can be used for tuning binlog_cache_size to a large enough value that avoids the use of temporary files.

The max_binlog_cache_size system variable (default 4GB, which is also the maximum) can be used to restrict the total size used to cache a multiple-statement transaction. If a transaction is larger than this many bytes, it fails and rolls back. The minimum value is 4096.

If you are using the binary log and row based logging, concurrent inserts are converted to normal inserts for CREATE... SELECT or INSERT... SELECTstatements. This is done to ensure that you can re-create an exact copy of your tables by applying the log during a backup operation. If you are using statement-based logging, the original statement is written to the log.

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

The temporary file in the transaction is placed in the / tmp directory because the temporary file in the transaction exceeds 16MB, but the temporary file is so large that it causes an alarm of insufficient disk space.

Solution:

After the above query is finished, we will close mysqld first. (if conditions permit, of course, let the query end itself. If the kill is dropped directly, it will take a long time to roll back.)

Then adjust mysql's tmpdir to other larger disks.

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.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.