MySQL tmp under the #sql_xxx_0.myd class file fills the space experience

Tag: MySQL tmp under #sql_xxx_0.myd full disk MySQL temporary disk full #sql_xxx_0. myd full Disk

As a full-time operation, at any time will encounter various problems, this evening received a tight alarm, a database server disk space use fast from 80% usage to 90%. Our databases are all >2T disks and realize that this is definitely a more serious problem to go online immediately.

The situation is as follows:

[Email protected] tmp]# ls#sql_8cc3_0.myd #sql_8cc3_0. MYI #sql_8cc3_10. MYD #sql_8cc3_10. MYI #sql_8cc3_5. MYD #sql_8cc 3_5.myi[[email protected] tmp]# du-sh *36khsperfdata_root346g#sql_8cc3_0.myd4.0k#sql_8cc3_0.myi336g#sql_8cc3_10. Myd4.0k#sql_8cc3_10.myi340g#sql_8cc3_5.myd4.0k#sql_8cc3_5.myi

Based on the attempt to determine this is a MySQL-generated file, view the database:

mysql> show processlist;+------------+-----------------+----------------------+-----------+------ -------+----------+-----------------------------------------------------------------------+-------------------- ----------------------------------------------------------------------------------+| id          | User             | Host                  | db        | Command      | Time     | State                                                                    |  Info                                                                                                      |+------------+-----------------+----------------------+ -----------+-------------+----------+-----------------------------------------------------------------------+-- ----------------------------------------------------------------------------------------------------+|           1 | event_scheduler | localhost             | NULL      | Daemon       | 54024745 | Waiting on empty queue                                                   | NULL                                                                                                      | |  2912394659 | nginxs_rw       | 172.17.11.99:12936   | nginxs    | Execute     |     12508 | Sending data                                                             | select   month ( A.blog_date),    count (distinct b.usname)  as android,   count ( Distinct c.usname)  | |  2912395083 |&Nbsp;nginxs_rw       | 172.17.11.99:34020  | nginxs     | execute     |    12051 |  Sending data                                                             | select   month (a.blog_date),    count (Distinct b.usname)  as android,   count (distinct c.usname)   ||  2912402122 | root            |  localhost            | nginxs     | query&nBsp;      |        0 | init                                                                      | show processlist                                                                                          |+------------+-----------------+----------------------+-----------+- ------------+----------+-----------------------------------------------------------------------+--------------- ---------------------------------------------------------------------------------------+10 rows in  set  (0.00 SEC)

Based on experience to quickly determine the above two SQL execution has a problem, immediately contact the relevant personnel to determine that SQL can be killed,

Mysql> kill 2912394659; Query OK, 0 rows Affected (0.00 sec) mysql> kill 2912395083; Query OK, 0 rows Affected (0.00 sec)

After killing these two SQL, the database immediately begins to release the temporary files and the disk space is restored to normal.

Summarize:

When using the database online, try to limit some temporary files

Tmp_table_size = 256M

Max_heap_table_size = 256M

Thread_cache_size = 64

Myisam_sort_buffer_size = 32M

Myisam_max_sort_file_size = 10G

This article is from the "My Operations Journey" blog, please be sure to keep this source http://nginxs.blog.51cto.com/4676810/1933625

#sql_xxx_0.myd class files under MySQL tmp full space experience