mysql 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 table.

It is taking a long time to close tables.

ex: I run a query and the total time is : 1:42 Sec. in that 80-90 sec 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
table_open_cache = 10000
table_definition_cache = 10000

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 cnf.

Answer 1

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> 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