Geek asks and answers

MySQL forces "close tables" with doubled process time

I have a delete request that takes a lot of time. When I check processlist, this status is displayed as "closing tables". It takes a long time to close the tables.

Example:
I run a query, and the total time is 1:42 seconds, and within 80-90 seconds to close the table.

This happens for all queries, such as load data, selectand insert.

The following are 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 changes do I need to make in my.cnf?

+5
source share
3 answers

. . , , .

MySql Thread .

!

0

, mysql .

query_cache_size : - The query cache is cleared from each insert, this will hinder the performance of MySQL, as this increases the overhead. Make sure the value is too large and in megabytes.

Innodb_flush_log_at_trx_commit : - you can set it to 0 (the log file is flushed to disk every second), 1 (the log file is flushed to disk for each commit) and 2 (the log file is cleared for every short interval in seconds).

0
source

More articles:


All Articles