Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2009
    Posts
    34

    Strange Mysql Server Load Problem

    I've come across a strange problem recently whenever I insert a row into one of my Mysql tables it instantly creates a high server load, which at least quadruples the server load. The number of apache connections also instantly increases a good 4-5 times as well. As soon as I delete the new row I inserted, the problem disappears and the server load decreases. This only happens for one specific table, other tables appear to be unaffected.

    If I don't delete the row, the server load will remain high for a couple days and then magically disappear. As soon as I insert a new row, the server load increases again and this process repeats.

    I've checked all possible queries and they seem fine. Not to mention this problem started happening out of no where. Looking back at the date it began, I couldn't find any changes that were made.

    It's really difficult to find any relevant results regarding this problem, so hopefully someone has an idea of what could be causing this.

    Thanks.

  2. #2
    Join Date
    Jun 2009
    Posts
    66
    You propably have to check your my.cnf .
    Freelancer Linux System Administrator
    www.hirekostas.com

  3. #3
    Join Date
    Aug 2009
    Posts
    34
    This is what my.cnf has:

    [mysqld]
    max_connections = 250
    safe-show-database
    skip-locking
    key_buffer = 128M
    max_allowed_packet = 1M
    table_cache = 256
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 2M
    myisam_sort_buffer_size = 64M
    query_cache_size= 32M
    max_heap_table_size = 48M
    tmp_table_size = 48M
    thread_concurrency = 8
    thread_cache_size = 8
    wait_timeout = 30
    interactive_timeout = 300

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [mysql]
    no-auto-rehash

    [isamchk]
    key_buffer = 64M
    sort_buffer_size = 64M
    read_buffer = 2M
    write_buffer = 2M

    [myisamchk]
    key_buffer = 64M
    sort_buffer_size = 64M
    read_buffer = 2M
    write_buffer = 2M
    See anything abnormal?

  4. #4
    Join Date
    May 2009
    Location
    On a Speck!!!!!
    Posts
    216
    Reduce the value of following variables (say below 100) and restart Mysql.

    interactive_timeout
    wait_timeout
    max_connections

    Also it will be better to use the following script to optimize Mysql configuration settings.

    http://day32.com/MySQL/tuning-primer.sh

  5. #5
    How large is the table in question and how many queries per second are run against the table?

    Run a 'SHOW PROCESSLIST' against the server during the high load. I am willing to bet that you are having a table lock issue. When using MyISAM tables INSERTS, UPDATES and DELETES will place a full table lock on the table. If the table is large this can take a good amount of time to complete. During this time any queries against the table will be blocked while waiting for the table to unlock.

    It might be time to look at converting the table to InnoDB instead of MyISAM. InnoDB tables do row level locks instead of full table locks.
    ExpressHosting.net - Fast. Reliable. Affordable.
    Shared Hosting | Dedicated Servers | Colocation | Managed Cloud | AS53255

  6. #6
    Join Date
    Aug 2009
    Posts
    34
    Quote Originally Posted by Thomas Manning View Post
    Reduce the value of following variables (say below 100) and restart Mysql.

    interactive_timeout
    wait_timeout
    max_connections
    Hmm, didn't seem to make a difference or either it had little effect. In any case should I change back the settings or were those values a bit high?

    Quote Originally Posted by expresscolo View Post
    How large is the table in question and how many queries per second are run against the table?

    Run a 'SHOW PROCESSLIST' against the server during the high load. I am willing to bet that you are having a table lock issue. When using MyISAM tables INSERTS, UPDATES and DELETES will place a full table lock on the table. If the table is large this can take a good amount of time to complete. During this time any queries against the table will be blocked while waiting for the table to unlock.

    It might be time to look at converting the table to InnoDB instead of MyISAM. InnoDB tables do row level locks instead of full table locks.
    I did see quite some table locks so I think the issue may be as you stated, however, this table is quite small (less than 1000 rows) and inserts, updates, and deletes are not common. There are a lot of joins to this table though.

  7. #7
    How complex are the Indexes on the table? These could take a while to rebuild and it might be effecting the query performance as well.

    If that isn't the case, then it could be a matter of the table finally getting to large to fit in memory as a tmp table and it is now being written to disk.

    I recommend running mysqlreport on the server to get a better understanding of what is going on in your server. The output from mysqlreport can be overwhelming but the site provides a very sensible break down of what each line means and its impact on your server.
    ExpressHosting.net - Fast. Reliable. Affordable.
    Shared Hosting | Dedicated Servers | Colocation | Managed Cloud | AS53255

  8. #8
    Join Date
    Aug 2009
    Posts
    34
    Quote Originally Posted by expresscolo View Post
    How complex are the Indexes on the table? These could take a while to rebuild and it might be effecting the query performance as well.

    If that isn't the case, then it could be a matter of the table finally getting to large to fit in memory as a tmp table and it is now being written to disk.

    I recommend running mysqlreport on the server to get a better understanding of what is going on in your server. The output from mysqlreport can be overwhelming but the site provides a very sensible break down of what each line means and its impact on your server.
    The indexes are not really complex at all.

    I ran the report and I saw a few things I can change, however this was with the server load as being normal. As well, the mysql server was restarted earlier and it was recommended to be up for at least a day for solid results. Would that mean I need to leave the server with the high server load for a full day and then run the report? Or do I run the report when the server load is normal?

  9. #9
    I would recommend letting it run normally for a day, and then run the "bad" insert that is causing you problems and run mysqlreport at that time. You might see something jump out at you that would make it obvious.

    Good luck, sounds like a tricky problem to pin down.
    ExpressHosting.net - Fast. Reliable. Affordable.
    Shared Hosting | Dedicated Servers | Colocation | Managed Cloud | AS53255

  10. #10
    Join Date
    Nov 2003
    Location
    Kherson, Ukraine
    Posts
    267
    Can you please show table structure?
    Can you please show exactly data you put into table?
    How many rows exist in table?
    Are existed rows have same data as new? What difference is?

    Try to run "show full processlist" query few times when high load occurs. You'll see what exactly queries performing your server.
    Private remote administrator of Linux servers - www.petrov.ks.ua
    Quality hosting - Host-Web-Site.com

  11. #11
    Join Date
    Dec 2006
    Posts
    477
    I can't see this being due to index rebuilds - not if it lasts days. The hitting the maximum tmp table size explanation doesn't work either, as it doesn't explain why if you don't delete the row, load returns to normal after a period of time, and then spikes again when you insert another one.

    You haven't actually stated what process is consuming the CPU time - we are all assuming it is mysql?

    What application is using this database - is it a custom app or well known script? A possible explanation is the additional row of data causes the application to do some additional processing that it did not do when it didn't see that row, and there isn't a mysql configuration error.

  12. #12
    Join Date
    Aug 2009
    Posts
    34
    Quote Originally Posted by RBBOT View Post
    I can't see this being due to index rebuilds - not if it lasts days. The hitting the maximum tmp table size explanation doesn't work either, as it doesn't explain why if you don't delete the row, load returns to normal after a period of time, and then spikes again when you insert another one.

    You haven't actually stated what process is consuming the CPU time - we are all assuming it is mysql?

    What application is using this database - is it a custom app or well known script? A possible explanation is the additional row of data causes the application to do some additional processing that it did not do when it didn't see that row, and there isn't a mysql configuration error.
    Yea, it is mysql that is consuming. This is a custom app, but there were no changes to the code when this problem occurred which makes me think it is hitting some limit like others said. However the mysterious thing as you stated is why the problem disappears after a random period of time. I'll run the report again tomorrow and see if I notice anything more.

Similar Threads

  1. Server load problem - strange commands?!?
    By strahinjas in forum Hosting Security and Technology
    Replies: 0
    Last Post: 08-12-2006, 10:28 AM
  2. Strange php,mysql problem with a server
    By moocatz in forum Programming Discussion
    Replies: 5
    Last Post: 05-29-2006, 03:37 AM
  3. mysql problem ... server load 20-50 help!
    By swijaya0101 in forum Dedicated Server
    Replies: 9
    Last Post: 05-09-2004, 08:32 PM
  4. Very strange load problem...
    By djwins in forum Hosting Security and Technology
    Replies: 1
    Last Post: 04-02-2004, 06:51 PM
  5. Strange high load problem
    By ColoCenter in forum Hosting Security and Technology
    Replies: 13
    Last Post: 04-05-2003, 07:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •