Professional Web Applications Themes

Problem with slow mysql after 5 days - MySQL

Hi I have a problem with mysql for anyone that can help. I have about 10,000 very simple inserts per day and probably a few thousand queries every day and everything works beautifully for about 5 or 6 days but after 5 or 6 days the insert speed into the database rockets up to 15 seconds per insert and a massive backlog ensues. Restarting the mysql engine with a shutdown brings everything back to normal . Has anyone encountered such an issue and know how to fix this?...

  1. #1

    Default Problem with slow mysql after 5 days

    Hi I have a problem with mysql for anyone that can help. I have about
    10,000 very simple inserts per day and probably a few thousand queries
    every day and everything works beautifully for about 5 or 6 days but
    after 5 or 6 days the insert speed into the database rockets up to 15
    seconds per insert and a massive backlog ensues.

    Restarting the mysql engine with a shutdown brings everything back to
    normal . Has anyone encountered such an issue and know how to fix this?

    dalouis Guest

  2. #2

    Default Re: Problem with slow mysql after 5 days

    dalouis wrote: 

    the answer is 42.

    this is the only answer I can come up with given the fact you have not provide
    any useful information like the version and platform.

    Well, not really, but is a hint for future posts...

    Look at cpu/memory utilization. Sounds like a memory leak in either the app or
    the database engine. Or you have something that goes into a cpu-bound loop.

    --
    Michael Austin.
    Database Consultant
    Michael Guest

  3. #3

    Default Re: Problem with slow mysql after 5 days

    Its 5.0 running on linux redhat es3 i believe. What can i do to
    diagnose and solve the problem? Which app are you talking about? The
    apps run remotely and do inserts/queries through the network, mysql
    runs on its own machine.

    dalouis Guest

  4. #4

    Default Re: Problem with slow mysql after 5 days

    dalouis wrote:
     

    look at `free` or `vmstat` to see what memory utilization looks like before
    things slow down and what they look like after.

    --
    Michael Austin.
    Database Consultant
    Michael Guest

  5. #5

    Default Re: Problem with slow mysql after 5 days

    I saw this a bit too late but i got the error again and i made sure to
    do a 'top' and show full processlist before and during the problem:

    show full processlist doesnt seem to show anything very different
    before(normal state)/during the problem, this list is the same before
    or after the problem. There is one gui client connected and a few
    clients using jdbc which reflect the connection pool from apache.

    mysql> show full processlist;
    +----+-----------+--------------------+-----------+---------+------+-------+----
    -------------------+
    | Id | User | Host | db | Command | Time |
    State | Inf
    o |
    +----+-----------+--------------------+-----------+---------+------+-------+----
    -------------------+
    | 1 | mydb| 10.10.0.143:1678 | mydb| Sleep | 0 | NULL | NUL
    L |
    | 2 | mydb| localhost:7846 | mydb| Sleep | 176 | NULL | NUL
    L |
    | 11 | mydb| 10.10.136.16:52470 | mydb| Sleep | 892 | NULL | NUL
    L |
    | 12 | mydb| 10.10.136.16:52471 | mydb| Sleep | 408 | NULL | NUL
    L |
    | 13 | mydb| 10.10.135.16:52711 | mydb| Sleep | 3273 | NULL | NUL
    L |
    | 14 | mydb| 10.10.135.16:52712 | mydb| Sleep | 4162 | NULL | NUL
    L |
    | 19 | mydb| 10.10.0.28:4827 | mydb| Sleep | 147 | NULL | NUL
    L |
    | 22 | mydb| 10.10.129.16:53925 | mydb| Sleep | 839 | NULL | NUL
    L |
    | 27 | root | localhost | NULL | Query | 0 |
    NULL | sho
    w full processlist |
    +----+-----------+--------------------+-----------+---------+------+-------+----
    -------------------+
    9 rows in set (0.00 sec)




    Top shows mysql eating up a lot of resources during the problem time:
    first i will post what is the normal state for the machine:

    11:50:19 up 65 days, 23:49, 3 users, load average: 0.21, 0.14, 0.10
    63 processes: 62 sleeping, 1 running, 0 zombie, 0 stopped
    CPU states: cpu user nice system irq softirq iowait
    idle
    total 2.6% 0.0% 0.0% 0.0% 0.0% 0.0%
    97.3%
    cpu00 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
    100.0%
    cpu01 0.2% 0.0% 0.0% 0.0% 0.0% 0.0%
    99.7%
    cpu02 10.3% 0.0% 0.0% 0.0% 0.0% 0.0%
    89.6%
    cpu03 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
    100.0%
    Mem: 2037012k av, 2019100k used, 17912k free, 0k shrd,
    100080k buff
    1520452k actv, 282584k in_d, 39624k in_c
    Swap: 2097144k av, 611516k used, 1485628k free
    1567828k cached

    PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU
    COMMAND
    4158 mysql 15 0 89956 78M 3336 S 2.5 3.9 22:02 0
    mysqld
    1 root 15 0 520 476 444 S 0.0 0.0 0:31 1 init
    2 root RT 0 0 0 0 SW 0.0 0.0 0:00 0
    migration/0
    3 root RT 0 0 0 0 SW 0.0 0.0 0:00 1
    migration/1
    4 root RT 0 0 0 0 SW 0.0 0.0 0:00 2
    migration/2
    5 root RT 0 0 0 0 SW 0.0 0.0 0:00 3
    migration/3
    6 root 15 0 0 0 0 SW 0.0 0.0 0:05 3
    keventd
    7 root 34 19 0 0 0 SWN 0.0 0.0 0:00 0
    ksoftirqd/0
    8 root 34 19 0 0 0 SWN 0.0 0.0 0:00 1
    ksoftirqd/1
    9 root 34 19 0 0 0 SWN 0.0 0.0 0:00 2
    ksoftirqd/2
    10 root 34 19 0 0 0 SWN 0.0 0.0 0:00 3
    ksoftirqd/3
    13 root 25 0 0 0 0 SW 0.0 0.0 0:00 0
    bdflush


    and now the state when the inserts/queries take an inordinate amount of
    time:

    13:42:46 up 72 days, 1:41, 3 users, load average: 1.04, 1.05, 1.00
    64 processes: 63 sleeping, 1 running, 0 zombie, 0 stopped
    CPU states: cpu user nice system irq softirq iowait
    idle
    total 25.1% 0.0% 0.0% 0.0% 0.0% 0.0%
    74.8%
    cpu00 100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
    0.0%
    cpu01 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
    100.0%
    cpu02 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
    100.0%
    cpu03 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
    100.0%
    Mem: 2037012k av, 2019280k used, 17732k free, 0k shrd,
    136212k buff
    1523004k actv, 283060k in_d, 42728k in_c
    Swap: 2097144k av, 1017248k used, 1079896k free
    470020k cached

    PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU
    COMMAND
    4158 mysql 15 0 806M 710M 3348 S 24.9 35.7 2412m 2
    mysqld
    32647 mysql 15 0 1360 1360 968 R 0.3 0.0 0:00 3 top
    1 root 15 0 520 468 444 S 0.0 0.0 0:33 2 init
    2 root RT 0 0 0 0 SW 0.0 0.0 0:00 0
    migration/0
    3 root RT 0 0 0 0 SW 0.0 0.0 0:00 1
    migration/1
    4 root RT 0 0 0 0 SW 0.0 0.0 0:00 2
    migration/2
    5 root RT 0 0 0 0 SW 0.0 0.0 0:00 3
    migration/3
    6 root 15 0 0 0 0 SW 0.0 0.0 0:07 3
    keventd
    7 root 34 19 0 0 0 SWN 0.0 0.0 0:00 0
    ksoftirqd/0
    8 root 34 19 0 0 0 SWN 0.0 0.0 0:00 1
    ksoftirqd/1
    9 root 34 19 0 0 0 SWN 0.0 0.0 0:00 2
    ksoftirqd/2
    10 root 34 19 0 0 0 SWN 0.0 0.0 0:00 3
    ksoftirqd/3


    Thanks for any insight into this problem.




    Michael Austin wrote: 
    >
    > look at `free` or `vmstat` to see what memory utilization looks like before
    > things slow down and what they look like after.
    >
    > --
    > Michael Austin.
    > Database Consultant[/ref]

    dalouis Guest

  6. #6

    Default Re: Problem with slow mysql after 5 days

    dalouis wrote:
     
    +----+-----------+--------------------+-----------+---------+------+-------+---- 
    +----+-----------+--------------------+-----------+---------+------+-------+---- 
    <snip> 
    <snip> 


    The status of the process is S (sleep) but it has consumed 24.9% of the
    available CPU cycles (99.6% of 1 CPU) in the last time slice evaluated by
    top. This means that it is not constantly active.

    To me this suggests that one of your connections is issuing thousands upon
    thousands of very fast queries one after another. I have had this happen
    to me before, for some reason SHOW PROCESSLIST seems to be unable to show
    the status of a query that returns very quickly, opting only to show the
    very short sleep state between queries, perhaps there is a flaw in the way
    it is implemented.

    You can see if this is the case by asking:

    SHOW STATUS LIKE "Questions";

    If it increases by several thousand per second then what I have said is
    indeed the case.


    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

  7. #7

    Default Re: Problem with slow mysql after 5 days

    > The status of the process is S (sleep) but it has consumed 24.9% of the 

    Thanks for your reply. There definitely are many very rapid queries it
    might not be in the thousands per second though. I will try to run the
    show status command the next time this problem crops up.

    Most importantly though you said you had this problem - how was it
    resolved for you? What could be the cause of this problem and how could
    i overcome it? It seems to work just fine (with the same amount of
    load) for a few days and then and only then does this slowness problem
    begin.

    At this point I have a script that restarts mysql engine every night at
    2am to overcome this problem. A terrible solution but one which I have
    resorted to in an attempt to not have users experience the
    slowness/backlog during the day on which the problem decides to crop
    up.


    Thanks again.

    dalouis Guest

  8. #8

    Default Re: Problem with slow mysql after 5 days

    dalouis wrote:
     
    >
    > Thanks for your reply. There definitely are many very rapid queries it
    > might not be in the thousands per second though. I will try to run the
    > show status command the next time this problem crops up.
    >
    > Most importantly though you said you had this problem - how was it
    > resolved for you?[/ref]


    By terminating the script that was stuck in an infinite loop.


    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

Similar Threads

  1. #39560 [NEW]: Inconsistent behaviour of strtotime when days > days in month
    By php at colin dot guthr dot ie in forum PHP Bugs
    Replies: 1
    Last Post: November 20th, 05:21 PM
  2. Why is my MySQL database so slow
    By Stefan Mueller in forum MySQL
    Replies: 21
    Last Post: December 3rd, 06:29 PM
  3. Replies: 4
    Last Post: March 23rd, 11:21 AM
  4. Slow MySql query
    By jack in forum PHP Development
    Replies: 2
    Last Post: August 31st, 08:44 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139