MSQL state "closing tables" taking double the time of a process 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?
Closing tables this 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.

This bug report can be related as well.

Further, 

MySQL closes an unused table and removes it from the table cache under the following circumstances:


When the cache is full and a thread tries to open a table that is not in the cache.
When the cache contains more than table_cache entries and a table in the cache is no longer being used by any threads.
When a table flushing operation occurs. This happens when someone issues a FLUSH TABLES statement or executes a mysqladmin flush-tables or mysqladmin refresh command.


You can determine whether your table cache is too small by checking the mysqld status variable Opened_tables, which indicates the number of table-opening operations since the server started:

mysql&gt; SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741  |
+---------------+-------+


If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, you should increase the table cache size
For more information refer how MySQL opens and closes a tables here:

http://dev.mysql.com/doc/refman/5.0/en/table-cache.html