inconsistent performance

MySQL & MariaDB Other MySQL® Questions
1

Hi

We’re running MySQL 5.0 with MyISAM on a Dual-Core AMD with 8GB RAM.

The server runs as slave and for a single application - a PHP-CLI script that stops the slave, calcs some data for other machines and starts the slave again)

Some of the queries in the script are bigger joins or e.g. INSERT INTO … SELECT with about 2 million rows.

Sometimes one or more of the queries are lagging for no clear reason, e.g. the mentioned INSERT INTO … SELECT usually takes about 40 secs, but sometimes up to 200 secs (no other queries, table locks or something).

The machine doesn’t swap at all. I also tried to restart the MySQL server by script at the beginning or end of the script to make sure there are no unused buffers that have to be cleaned, etc. - didn’t help. Tuning-Primer had no “suggestions”.

MySQL config:

key_buffer = 1024M
max_allowed_packet = 64M
table_cache = 512
sort_buffer_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 64M
tmp_table_size = 4096M
max_heap_table_size = 4096M
thread_cache_size = 8
query_cache_type = 0
query_cache_size = 0M
ft_min_word_len = 2
thread_concurrency = 8
max_connections = 10

Any idea on how to find out what happens or what leads to that kind of problem? Checking MySQL status before or after the queries didn’t help.

TIA

Uwe

2

Basically it could be anything.

If the query is the same all the time, then how about the execution plan or the data?

And even if the server doesn’t swap, what about IO activity during this time?
The OS file cache (cached memory) might be used sometimes and sometimes not, hence it sometimes needs to write to disk during the execution.

If you want to continue to research this you must arrange so that you can get some information about what is happening in the system during the fast and the slow execution of the same query.