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 wrote:
> 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?
>

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

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 wrote:

> 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.
>

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

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:
> dalouis wrote:
>
> > 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.
> >
>
> 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

dalouis wrote:

> 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 |
<snip>
> 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
<snip>
>
> Thanks for any insight into this problem.


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

> 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.

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 wrote:

>> 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.
>
> 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?


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


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