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
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!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With