MySQL hangs Server?

English Applications
#1

Hello,
I’m running Suse 11, in a vmware esx environment. The server setup is
2GB RAM, 2GB Swap, two AMD Processors.

Its being used as a LAMP server. Over the past couple of weeks, the
MySQL has been filling up the memory and swap, the processor goes to
100% use and the system grinds to a halt. Using top shows:

Code:

top - 15:46:25 up 22:22, 4 users, load average: 17.59, 5.89, 2.22
Tasks: 114 total, 25 running, 89 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us,100.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 2075172k total, 2026180k used, 48992k free, 212k buffers
Swap: 2216960k total, 2216960k used, 0k free, 4480k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
20 root 15 -5 0 0 0 R 2 0.0 2:21.47 kswapd0
1 root 20 0 772 32 0 R 2 0.0 0:05.50 init
10223 mysql 20 0 608m 6116 256 S 1 0.3 4:58.08 mysqld
2154 root 20 0 1800 332 156 R 1 0.0 0:02.98 klogd
9142 dfse11 20 0 9252 496 296 R 1 0.0 0:04.76 sshd
13128 wwwrun 20 0 657m 248m 1280 R 1 12.3 1:42.37 httpd2-prefork
29755 wwwrun 20 0 94916 1728 432 D 1 0.1 0:04.32 httpd2-prefork
3009 wwwrun 20 0 861m 192m 1244 R 1 9.5 2:36.29 httpd2-prefork
28208 wwwrun 20 0 336m 225m 1652 R 1 11.1 0:57.50 httpd2-prefork
2151 root 20 0 2200 264 124 R 1 0.0 0:05.96 syslog-ng
5124 wwwrun 20 0 865m 190m 1348 R 1 9.4 2:35.50 httpd2-prefork
7201 root 20 0 14856 1004 208 R 1 0.0 0:19.98 smbd
15942 wwwrun 20 0 437m 191m 1636 R 1 9.4 1:59.64 httpd2-prefork
20906 wwwrun 20 0 204m 114m 344 R 1 5.7 0:27.88 httpd2-prefork
29752 root 20 0 1728 256 144 R 1 0.0 0:07.18 modprobe
29754 root 20 0 94916 1296 76 R 1 0.1 0:04.56 httpd2-prefork
29756 root 20 0 94916 1388 156 R 1 0.1 0:04.28 httpd2-prefork

I know that there are no exceptional queries being made to the
database, I’ve checked over and over, I’ve tried various settings found
on google, increasing various MySQL buffer and cache sizes, and altered
the PHP code to use the mysqli extension rather than mysql.

Yet it still grinds to a halt.

An alternative I’ve been given is to use an MS-SQL cluster. Something
I’m not too keen on.

Can anyone suggest anything? The system is much the same as the SUSE 9
setup that was running previously without any problems.

Thanks,
Karl.

–
karl101

karl101’s Profile: http://forums.opensuse.org/member.php?userid=13515
View this thread: http://forums.opensuse.org/showthread.php?t=400595

#2

Hi,

are you sure that mysql is is responsible for this issue, because on
the posted top output mysqld is only using 0.3% of the memory which
should be normal. But you have a lot apache prefork processes which
consumes a lot of memory. Maybe somewhere there is your problem and you
can verify this by restarting apache2 when you notice that the problem
occurs. But I have no idea why this happens, maybe you have to tweak
some parameters ‘prefork - Apache HTTP Server’
(http://httpd.apache.org/docs/2.2/en/mod/prefork.html)

hope this helps

–
Monex

Monex’s Profile: http://forums.opensuse.org/member.php?userid=160
View this thread: http://forums.opensuse.org/showthread.php?t=400595

#3

Thanks for that. I shall investigate further. Currently
/etc/apache2/server-tuning.conf is set at its defaults.

One of the reasons I suspect MySQL is the cause the when watching top,
I see lots of little httpd forks appear, the MySQL rises to the top of
the list, then the system hangs.

Unfortunately, it happens quickly, so I have little chance of
restarting Apache before it freezes. I then have to reset the VM.

Here’s a different top, from a couple of days ago, before we doubled
the RAM and swap, and added another processor:

Code:

top - 12:31:27 up 4 days, 3:39, 3 users, load average: 8.81, 3.88, 1.52
Tasks: 101 total, 5 running, 96 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.1%us, 98.9%sy, 0.0%ni, 0.0%id, 0.7%wa, 0.1%hi, 0.1%si, 0.0%st
Mem: 1034864k total, 1022880k used, 11984k free, 256k buffers
Swap: 1052248k total, 1052248k used, 0k free, 4888k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9987 mysql 20 0 111m 4784 640 S 36.8 0.5 2:09.81 mysqld
15 root 15 -5 0 0 0 D 13.1 0.0 2:58.89 kswapd0
20191 wwwrun 20 0 171m 82m 176 D 11.4 8.1 0:10.66 httpd2-prefork
2539 root 20 0 2740 264 116 R 11.2 0.0 4:01.23 vmware-guestd
7850 root 20 0 60828 332 104 D 11.1 0.0 1:21.88 packagekitd
22080 wwwrun 20 0 94072 884 172 R 11.1 0.1 0:01.68 httpd2-prefork
19446 wwwrun 20 0 185m 94m 148 D 10.0 9.3 0:16.42 httpd2-prefork
22101 root 20 0 2064 524 424 R 10.0 0.1 0:00.92 find
22100 wwwrun 20 0 94072 1012 304 S 8.6 0.1 0:01.58 httpd2-prefork
22099 root 20 0 2272 472 224 R 7.3 0.0 0:01.36 top
18046 wwwrun 20 0 278m 102m 152 R 5.9 10.2 0:18.28 httpd2-prefork
1558 haldaemo 20 0 6160 344 40 S 5.7 0.0 0:33.31 hald
14855 ddwee2 20 0 9252 316 184 S 5.5 0.0 0:02.58 sshd
22102 root 20 0 94072 1000 300 D 4.1 0.1 0:00.24 httpd2-prefork
2005 root 20 0 2200 208 76 S 1.4 0.0 0:19.76 syslog-ng
18037 wwwrun 20 0 231m 90m 116 S 1.4 8.9 0:30.36 httpd2-prefork
1 root 20 0 772 52 28 S 0.7 0.0 0:32.69 init

I keep thinking that MySQL is filling the ram and swap with cache data,
but restarting mysql doesn’t clear it. I’ll check again in the morning
when things have filled up again, and restart apache to see if that
clears it (but then apache restarts when logrotate runs).

Karl.

–
karl101

karl101’s Profile: http://forums.opensuse.org/member.php?userid=13515
View this thread: http://forums.opensuse.org/showthread.php?t=400595

#4

Have you checked, using mysql’s data repair commands to see if there are
any inconsistencies in the database? If you don’t serve anything with
Apache, does mysql get into trouble? (I understand if you are not able
to take the app offline to test this.)

–
ken_yap

ken_yap’s Profile: http://forums.opensuse.org/member.php?userid=221
View this thread: http://forums.opensuse.org/showthread.php?t=400595

#5

ken_yap;1901041 Wrote:
> Have you checked, using mysql’s data repair commands to see if there are
> any inconsistencies in the database? If you don’t serve anything with
> Apache, does mysql get into trouble? (I understand if you are not able
> to take the app offline to test this.)

Doing repairs and optimizations was one of the first things I did. If
it was a bad query wouldn’t it be failing every time that query was run
from the PHP code?

The webalizser statistics show the server averages about 32Mb in data
transfer a day, from 10095 hits. Is that a lot or is it fairly modest?

Karl

–
karl101

karl101’s Profile: http://forums.opensuse.org/member.php?userid=13515
View this thread: http://forums.opensuse.org/showthread.php?t=400595

#6

32MB seems pretty modest.

You never know with these servers. My theory was that mysql was busy
repairing the damage on some other part of the DB.

You may have to bring out the heavy artillery and strace the server to
see what it’s doing.

–
ken_yap

ken_yap’s Profile: http://forums.opensuse.org/member.php?userid=221
View this thread: http://forums.opensuse.org/showthread.php?t=400595

#7

ken_yap;1901250 Wrote:
> 32MB seems pretty modest.
>
> You never know with these servers. My theory was that mysql was busy
> repairing the damage on some other part of the DB.
>
> You may have to bring out the heavy artillery and strace the server to
> see what it’s doing.

Just been trying out strace. It looks like its output may mean little
to me.

I’ve been using SUSE for over 5 years, and never before have I had
these problems. Its only in v11 that its all gone wrong.

–
karl101

karl101’s Profile: http://forums.opensuse.org/member.php?userid=13515
View this thread: http://forums.opensuse.org/showthread.php?t=400595

#8

Perhaps I was wrong blaming MySql. It seems to be a PHP or Apache2
problem.

What’s happening is that over time, a couple of days, the RAM gradually
fills up, once thats full the sawp fills. Once thats full the VM
computer freezes.

It appears to add to the memory when there’s substantial httpd
activity.

Restarting httpd releases a large block of that memory.

For example, httpd running:

Code:

top - 09:19:52 up 1 day, 4:18, 1 user, load average: 0.00, 0.00, 0.00
Tasks: 89 total, 1 running, 88 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.3%us, 0.3%sy, 0.0%ni, 99.0%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 2075228k total, 1424100k used, 651128k free, 3480k buffers
Swap: 2096472k total, 1185680k used, 910792k free, 29452k cached

after httpd stopped:

Code:

top - 09:20:59 up 1 day, 4:19, 2 users, load average: 0.62, 0.17, 0.06
Tasks: 85 total, 2 running, 83 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 1.3%sy, 0.0%ni, 98.3%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 2075228k total, 269604k used, 1805624k free, 4692k buffers
Swap: 2096472k total, 21360k used, 2075112k free, 40416k cached

starting httpd again and the memory stays at the same lower level.

Any suggestions as to what could be causing this?

Thanks

Karl.

–
karl101

karl101’s Profile: http://forums.opensuse.org/member.php?userid=13515
View this thread: http://forums.opensuse.org/showthread.php?t=400595

#9

A memory leak in one of your web apps.

–
ken_yap

ken_yap’s Profile: http://forums.opensuse.org/member.php?userid=221
View this thread: http://forums.opensuse.org/showthread.php?t=400595

#10

ken_yap;1903712 Wrote:
> A memory leak in one of your web apps.

OK. After some searching I’ve found this:
‘What’s new in PHP V5.2, Part 1: Using the new memory manager’
(http://tinyurl.com/5gt3yy)

I wrote a version to write the information to a database, within two
minutes I had over 100 records. This could keep me busy for a while.

Unless there’s an issue with PHP5.2 that I didn’t come across in v5.1?

Karl.

–
karl101

karl101’s Profile: http://forums.opensuse.org/member.php?userid=13515
View this thread: http://forums.opensuse.org/showthread.php?t=400595