MySQL state "closing tables" taking double the time of process

Question

I have a delete query that is taking a long time. When I check in processlist that status is showing as "closing tables". It is taking a long time to close tables.

Example:
I run a query and the total time is 1:42 seconds and in that 80-90 seconds are for closing table.

This is happening for all queries like load data , select and insert.

Below is my.cnf settings:

key_buffer_size = 2G
sort_buffer_size = 8M
read_buffer_size = 10M
read_rnd_buffer_size = 10M
join_buffer_size = 2M
bulk_insert_buffer_size = 100M
myisam_sort_buffer_size = 64M

#tmp_table_size = 100M
#max_heap_table_size = 64M
#max_allowed_packet = 64M

table_cache=1024

My meminfo

[root@localhost ~]# free -m
             total       used       free     shared    buffers     cached
Mem:          7862       6686       1175          0         11       4091
-/+ buffers/cache:       2583       5278
Swap:        15998         18      15980

Please tell me what kind of changes I need to do in my.cnf file?

Answer 1

closing tables

The thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, you should verify that you do not have a full disk and that the disk is not in very heavy use.

You can look at General MySql Thread States for additional details.

Hope this help!