Values of created_tmp_disk_tables increases I noticed that the values of Created_tmp_disk_tables increased.
At a certain time, MYD files are created in /tmp directory and my site run very slowly.
I am forced to restart the mysql service to runing my site.

What is the cause of the creation of disk table in tmp directory and the stop of the mysql?

My server:


Ubuntu 12
RAM : 32 G
Processors Number: 12


my.cnf
key_buffer_size         = 1024M
max_allowed_packet      = 512M
thread_stack            = 256K
thread_cache_size       = 8
read_buffer_size        = 2M
join_buffer_size        = 2M
tmp_table_size          = 256M
max_heap_table_size     = 256M
bulk_insert_buffer_size = 256M
open-files-limit        = 2048
query_cache_type = 0
query_cache_limit       = 0
query_cache_size        = 0
innodb_file_per_table
innodb_table_locks              = true
innodb_lock_wait_timeout        = 60
innodb_thread_concurrency       = 8
innodb_commit_concurrency       = 2
innodb_support_xa               = true
innodb_buffer_pool_size = 16G
innodb_log_file_size    = 64M
innodb_additional_mem_pool_size = 16M
innodb_data_file_path   = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size  = 64M
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
[myisamchk]
key_buffer                = 16M
myisam_sort_buffer_size   = 16M
myisam_max_sort_file_size = 2147483648
myisam_repair_threads     = 1
myisam-recover            = BACKUP

mysql&gt; show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25461 |
| Created_tmp_tables      | 29134 |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql&gt; show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25566 |
| Created_tmp_tables      | 29327 |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql&gt; show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25566 |
| Created_tmp_tables      | 29328 |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql&gt; show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25566 |
| Created_tmp_tables      | 29329 |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql&gt; show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25570 |
| Created_tmp_tables      | 29357 |
+-------------------------+-------+
2 rows in set (0.00 sec)
Created_tmp_disk_tables is an indication of poorly written queries.  Especially when Created_tmp_disk_tables/Created_tmp_tables is a high percentage.

The settings cannot necessarily prevent it.  You already have tmp_table_size and max_heap_table_size as high as the should be for 32GB of RAM.

The issue may not be a real issue -- If the tmp table is only a few rows long, you probably won't notice an issue.

The real issue is whether queries are running "too slow".  That is best judged by turning on the slowlog.

When a query is too complex, it needs to generate a tmp table.  The first choice is to make a MEMORY.  But that cannot be done if any referenced columns are TEXT or BLOB.  The second choice is MyISAM, which implies "disk tmp table", although it may stay cached in RAM and never actually touch the disk.  Another cause for it is when the MEMORY table exceeds those two max... settings, it will be converted to MyISAM.  This is unlikely in your case because of how big they are.

So, once you have discovered which queries are slow, we can discuss how to add composite indexes and/or reformulate the queries to avoid the need for disk tmp tables.  Or, at least try to speed them up.