Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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

share|improve this answer
    
thank you for your reply. we have enough disk space(500gb+) and free ram (5GB+) –  sankar.suda Jul 25 '12 at 12:22
    
I too can see that you have enough space left but disk activity matters during execution time of query as well, How many rows are affected by delete query etc.. –  Mahesh Patil Jul 25 '12 at 12:25
    
I have updated my answer, try by increasing table_cache size and see behaviour –  Mahesh Patil Jul 25 '12 at 12:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.