GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

inconsistent performance

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • inconsistent performance

    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.

    Comment

    Working...
    X