I have a performance issue on my server and I guess the problem is that Created_tmp_disk_tables are around 30% of Created_tmp_tables. I increased table_cache to 768, sort_buffer to 5 and after 8, tmp_table_size and max_heap_table_size to 64, with no positive result. There’s always a respectable amount of Created_tmp_disk_tables.
I use a lot of temporary tables.
I don’t use Blob/text columns.
I have 1,5GB of RAM.
Do you have any idea how to solve it ?
Thank you in advance
my.cnf file
set-variable = key_buffer=384Mset-variable = table_cache=512set-variable = sort_buffer=4Mset-variable = record_buffer=4Mset-variable = record_rnd_buffer=4Mset-variable = thread_cache=40set-variable = max_allowed_packet=8Mset-variable = wait_timeout=100set-variable = tmp_table_size=32Mset-variable = max_heap_table_size=32M
show status output :
±-------------------------±----------+| Variable_name | Value |±-------------------------±----------+| Aborted_clients | 109 || Aborted_connects | 5 || Bytes_received | 106488635 || Bytes_sent | 93132416 || Com_admin_commands | 2620 || Com_alter_table | 0 || Com_analyze | 0 || Com_backup_table | 0 || Com_begin | 0 || Com_change_db | 2890 || Com_change_master | 0 || Com_check | 0 || Com_commit | 0 || Com_create_db | 0 || Com_create_function | 0 || Com_create_index | 0 || Com_create_table | 2438 || Com_delete | 0 || Com_drop_db | 0 || Com_drop_function | 0 || Com_drop_index | 0 || Com_drop_table | 4837 || Com_flush | 0 || Com_grant | 0 || Com_insert | 3018 || Com_insert_select | 83639 || Com_kill | 0 || Com_load | 0 || Com_load_master_table | 0 || Com_lock_tables | 0 || Com_optimize | 2 || Com_purge | 0 || Com_rename_table | 0 || Com_repair | 0 || Com_replace | 0 || Com_replace_select | 0 || Com_reset | 0 || Com_restore_table | 0 || Com_revoke | 0 || Com_rollback | 0 || Com_select | 423209 || Com_set_option | 0 || Com_show_binlogs | 0 || Com_show_create | 0 || Com_show_databases | 5 || Com_show_fields | 6 || Com_show_grants | 0 || Com_show_keys | 3 || Com_show_logs | 0 || Com_show_master_status | 0 || Com_show_open_tables | 0 || Com_show_processlist | 0 || Com_show_slave_status | 0 || Com_show_status | 11 || Com_show_innodb_status | 0 || Com_show_tables | 124 || Com_show_variables | 0 || Com_slave_start | 0 || Com_slave_stop | 0 || Com_truncate | 0 || Com_unlock_tables | 0 || Com_update | 41264 || Connections | 194 || Created_tmp_disk_tables | 2533 || Created_tmp_tables | 8605 || Created_tmp_files | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Delayed_errors | 0 || Flush_commands | 1 || Handler_delete | 0 || Handler_read_first | 429 || Handler_read_key | 400442 || Handler_read_next | 4515218 || Handler_read_prev | 178397 || Handler_read_rnd | 355490 || Handler_read_rnd_next | 16955578 || Handler_update | 41293 || Handler_write | 3054908 || Key_blocks_used | 17823 || Key_read_requests | 3593432 || Key_reads | 15290 || Key_write_requests | 74937 || Key_writes | 0 || Max_used_connections | 40 || Not_flushed_key_blocks | 0 || Not_flushed_delayed_rows | 0 || Open_tables | 512 || Open_files | 1021 || Open_streams | 0 || Opened_tables | 6216 || Questions | 561566 || Select_full_join | 0 || Select_full_range_join | 0 || Select_range | 2657 || Select_range_check | 0 || Select_scan | 87519 || Slave_running | OFF || Slave_open_temp_tables | 0 || Slow_launch_threads | 0 || Slow_queries | 0 || Sort_merge_passes | 0 || Sort_range | 39993 || Sort_rows | 355490 || Sort_scan | 6480 || Table_locks_immediate | 423467 || Table_locks_waited | 0 || Threads_cached | 0 || Threads_created | 41 || Threads_connected | 41 || Threads_running | 1 || Uptime | 7859 |±-------------------------±----------+