Sample Header Ad - 728x90

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