Values of created_tmp_disk_tables increases
4
votes
1
answer
6089
views
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> 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> 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> 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> 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> 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)
Asked by Mohamed Ben HEnda
(153 rep)
Dec 23, 2016, 01:09 PM
Last activity: Apr 20, 2025, 06:03 AM
Last activity: Apr 20, 2025, 06:03 AM