asked on

Redhat 9 LAMP Mysqld performance

Dear Experts,

I am hoping you can help me figure out where my problems with my Redhat 9 box are occuring.

Its become frequent that about 10 minutes after startup of the machine the mysqld process consumes 99% of the CPU for extended periods of time, ususally until i reboot then it runs normal for a while again.

The network connections are good to the machine and there are only a few users using the box. I think it could be a problem with the php pages and the sql contained in them but do not know how to troubleshoot. Is it possible theres a recursive query running?

I have looked in the mysqld.log file and theres nothing in there as in most other log files i've looked at. I can post at request.

Can someone possibly advise?

Thanks Alot for any and all suggestions.

Kind regards,
Richard Walton

richardwalton

ASKER

ok so if i stop the apache server everything seems ok.
Heres a list of processes during the all stop period. I'll post the same again in an hour when the server has ground to a halt:)

So heres a list of memory/cpu usage and processes during the full speed ahead period.
--------------------------------------------------------------------------------------------
13:00:56 up 2:37, 1 user, load average: 0.68, 3.79, 11.97
60 processes: 58 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: 76.8% user 23.1% system 0.0% nice 0.0% iowait 0.0% idle
Mem: 117960k av, 100980k used, 16980k free, 0k shrd, 3480k buff
69876k actv, 7580k in_d, 148k in_c
Swap: 257032k av, 45448k used, 211584k free 45076k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
4619 mysql 15 0 5536 4024 1032 S 98.6 3.4 49:18 0 mysqld
5483 root 15 0 8736 8736 6052 S 1.1 7.4 0:03 0 httpd
5531 root 15 0 1076 1076 868 R 0.1 0.9 0:00 0 top
1 root 15 0 108 80 56 S 0.0 0.0 0:03 0 init
2 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 keventd
3 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kapmd
4 root 34 19 0 0 0 SWN 0.0 0.0 0:00 0 ksoftirqd_CPU0
9 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 bdflush
5 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kswapd
6 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kscand/DMA
7 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kscand/Normal
8 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kscand/HighMem
10 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kupdated
11 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 mdrecoveryd
15 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
72 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 khubd
2795 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
3227 root 15 0 200 168 116 S 0.0 0.1 0:00 0 syslogd
3231 root 15 0 60 4 0 S 0.0 0.0 0:00 0 klogd
3257 rpc 19 0 80 4 0 S 0.0 0.0 0:00 0 portmap
3276 rpcuser 25 0 80 4 0 S 0.0 0.0 0:00 0 rpc.statd
3342 root 24 0 56 4 0 S 0.0 0.0 0:00 0 apmd
3381 named 25 0 656 244 128 S 0.0 0.2 0:00 0 named
3394 root 15 0 236 4 0 S 0.0 0.0 0:00 0 sshd
3408 root 23 0 160 4 0 S 0.0 0.0 0:00 0 xinetd
3427 root 18 0 164 4 0 S 0.0 0.0 0:00 0 safe_mysqld
3470 root 15 0 1000 476 348 S 0.0 0.4 0:00 0 sendmail
3479 smmsp 15 0 532 4 0 S 0.0 0.0 0:00 0 sendmail
3492 root 24 0 13176 4 0 S 0.0 0.0 0:00 0 spamd
3501 root 15 0 92 60 40 S 0.0 0.0 0:00 0 gpm
3523 bin 15 0 444 44 12 S 0.0 0.0 0:00 0 cannaserver
3534 root 18 0 156 136 88 S 0.0 0.1 0:00 0 crond
3553 root 15 0 1752 324 236 S 0.0 0.2 0:00 0 cupsd
3597 wnn 25 0 3348 4 0 S 0.0 0.0 0:00 0 jserver
3636 xfs 15 0 4256 120 64 S 0.0 0.1 0:00 0 xfs
3645 root 15 0 616 80 0 S 0.0 0.0 0:00 0 smbd
3649 root 15 0 1048 592 468 S 0.0 0.5 0:00 0 nmbd
3673 daemon 15 0 172 152 108 S 0.0 0.1 0:00 0 atd

[root@linux2 logs]# ps -ef
UID PID PPID C STIME TTY TIME CMD
root 1 0 0 10:23 ? 00:00:03 init
root 2 1 0 10:23 ? 00:00:00 [keventd]
root 3 1 0 10:23 ? 00:00:00 [kapmd]
root 4 1 0 10:23 ? 00:00:00 [ksoftirqd_CPU0]
root 9 1 0 10:23 ? 00:00:00 [bdflush]
root 5 1 0 10:23 ? 00:00:00 [kswapd]
root 6 1 0 10:23 ? 00:00:00 [kscand/DMA]
root 7 1 0 10:23 ? 00:00:00 [kscand/Normal]
root 8 1 0 10:23 ? 00:00:00 [kscand/HighMem]
root 10 1 0 10:23 ? 00:00:00 [kupdated]
root 11 1 0 10:23 ? 00:00:00 [mdrecoveryd]
root 15 1 0 10:23 ? 00:00:00 [kjournald]
root 72 1 0 10:23 ? 00:00:00 [khubd]
root 2795 1 0 10:23 ? 00:00:00 [kjournald]
root 3227 1 0 10:24 ? 00:00:00 syslogd -m 0
root 3231 1 0 10:24 ? 00:00:00 klogd -x
rpc 3257 1 0 10:24 ? 00:00:00 portmap
rpcuser 3276 1 0 10:24 ? 00:00:00 rpc.statd
root 3342 1 0 10:24 ? 00:00:00 /usr/sbin/apmd -p 10 -w 5 -W -P /etc/sysconfig/apm-scripts/apmscript
named 3381 1 0 10:24 ? 00:00:00 /usr/sbin/named -u named
root 3394 1 0 10:24 ? 00:00:00 /usr/sbin/sshd
root 3408 1 0 10:24 ? 00:00:00 xinetd -stayalive -pidfile /var/run/xinetd.pid
root 3427 1 0 10:24 ? 00:00:00 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf
root 3470 1 0 10:24 ? 00:00:00 sendmail: accepting connections
smmsp 3479 1 0 10:24 ? 00:00:00 sendmail: Queue runner@01:00:00 for /var/spool/clientmqueue
root 3492 1 0 10:24 ? 00:00:00 /usr/bin/spamd -d -c -a
root 3501 1 0 10:24 ? 00:00:00 gpm -t ps/2 -m /dev/psaux
bin 3523 1 0 10:24 ? 00:00:00 /usr/sbin/cannaserver -syslog -u bin
root 3534 1 0 10:24 ? 00:00:00 crond
root 3553 1 0 10:24 ? 00:00:00 cupsd
wnn 3597 1 0 10:24 ? 00:00:00 /usr/bin/jserver
xfs 3636 1 0 10:24 ? 00:00:00 xfs -droppriv -daemon
root 3645 1 0 10:24 ? 00:00:00 smbd -D
root 3649 1 0 10:24 ? 00:00:00 nmbd -D
daemon 3673 1 0 10:24 ? 00:00:00 /usr/sbin/atd
root 3683 1 0 10:24 ? 00:00:00 rhnsd --interval 240
root 3694 1 0 10:24 tty1 00:00:00 /sbin/mingetty tty1
root 3695 1 0 10:24 tty2 00:00:00 /sbin/mingetty tty2
root 3696 1 0 10:24 tty3 00:00:00 /sbin/mingetty tty3
root 3697 1 0 10:24 tty4 00:00:00 /sbin/mingetty tty4
root 3698 1 0 10:24 tty5 00:00:00 /sbin/mingetty tty5
root 3699 1 0 10:24 tty6 00:00:00 /sbin/mingetty tty6
root 3700 1 0 10:24 ? 00:00:00 /usr/bin/gdm-binary -nodaemon
root 3749 3700 0 10:24 ? 00:00:00 /usr/bin/gdm-binary -nodaemon
root 3750 3749 0 10:24 ? 00:00:02 /usr/X11R6/bin/X :0 -auth /var/gdm/:0.Xauth vt7
gdm 3765 3749 0 10:24 ? 00:00:02 /usr/bin/gdmgreeter
mysql 4619 3427 54 11:28 ? 00:47:11 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/my
root 5364 3394 0 12:38 ? 00:00:00 /usr/sbin/sshd
root 5367 5364 0 12:39 pts/0 00:00:00 -bash
root 5483 1 1 12:54 ? 00:00:00 /usr/sbin/httpd -k start
apache 5484 5483 0 12:54 ? 00:00:00 /usr/sbin/httpd -k start
apache 5485 5483 0 12:54 ? 00:00:00 /usr/sbin/httpd -k start
apache 5486 5483 0 12:54 ? 00:00:00 /usr/sbin/httpd -k start
apache 5487 5483 0 12:54 ? 00:00:00 /usr/sbin/httpd -k start
apache 5488 5483 0 12:54 ? 00:00:00 /usr/sbin/httpd -k start
apache 5489 5483 0 12:54 ? 00:00:00 /usr/sbin/httpd -k start
apache 5490 5483 0 12:54 ? 00:00:00 /usr/sbin/httpd -k start
apache 5491 5483 0 12:54 ? 00:00:00 /usr/sbin/httpd -k start
apache 5504 5483 0 12:55 ? 00:00:00 /usr/sbin/httpd -k start

richardwalton

ASKER

Ok heres a process list when its at full stop. The only method i can use to renable the system is to stop the apache server (apachectl stop) and restart (apachectl start).

[root@linux2 logs]# ps -ef
UID PID PPID C STIME TTY TIME CMD
root 1 0 0 10:23 ? 00:00:03 init
root 2 1 0 10:23 ? 00:00:00 [keventd]
root 3 1 0 10:23 ? 00:00:00 [kapmd]
root 4 1 0 10:23 ? 00:00:00 [ksoftirqd_CPU0]
root 9 1 0 10:23 ? 00:00:00 [bdflush]
root 5 1 0 10:23 ? 00:00:00 [kswapd]
root 6 1 0 10:23 ? 00:00:00 [kscand/DMA]
root 7 1 0 10:23 ? 00:00:00 [kscand/Normal]
root 8 1 0 10:23 ? 00:00:00 [kscand/HighMem]
root 10 1 0 10:23 ? 00:00:00 [kupdated]
root 11 1 0 10:23 ? 00:00:00 [mdrecoveryd]
root 15 1 0 10:23 ? 00:00:00 [kjournald]
root 72 1 0 10:23 ? 00:00:00 [khubd]
root 2795 1 0 10:23 ? 00:00:00 [kjournald]
root 3227 1 0 10:24 ? 00:00:00 syslogd -m 0
root 3231 1 0 10:24 ? 00:00:00 klogd -x
rpc 3257 1 0 10:24 ? 00:00:00 portmap
rpcuser 3276 1 0 10:24 ? 00:00:00 rpc.statd
root 3342 1 0 10:24 ? 00:00:00 /usr/sbin/apmd -p 10 -w 5 -W -P /etc/sysconfig/apm-scripts/apmscript
named 3381 1 0 10:24 ? 00:00:00 /usr/sbin/named -u named
root 3394 1 0 10:24 ? 00:00:00 /usr/sbin/sshd
root 3408 1 0 10:24 ? 00:00:00 xinetd -stayalive -pidfile /var/run/xinetd.pid
root 3427 1 0 10:24 ? 00:00:00 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf
root 3470 1 0 10:24 ? 00:00:00 sendmail: rejecting connections on daemon MTA: load average: 24
smmsp 3479 1 0 10:24 ? 00:00:00 sendmail: Queue runner@01:00:00 for /var/spool/clientmqueue
root 3492 1 0 10:24 ? 00:00:00 /usr/bin/spamd -d -c -a
root 3501 1 0 10:24 ? 00:00:00 gpm -t ps/2 -m /dev/psaux
bin 3523 1 0 10:24 ? 00:00:00 /usr/sbin/cannaserver -syslog -u bin
root 3534 1 0 10:24 ? 00:00:00 crond
root 3553 1 0 10:24 ? 00:00:00 cupsd
wnn 3597 1 0 10:24 ? 00:00:00 /usr/bin/jserver
xfs 3636 1 0 10:24 ? 00:00:00 xfs -droppriv -daemon
root 3645 1 0 10:24 ? 00:00:00 smbd -D
root 3649 1 0 10:24 ? 00:00:00 nmbd -D
daemon 3673 1 0 10:24 ? 00:00:00 /usr/sbin/atd
root 3683 1 0 10:24 ? 00:00:00 rhnsd --interval 240
root 3694 1 0 10:24 tty1 00:00:00 /sbin/mingetty tty1
root 3695 1 0 10:24 tty2 00:00:00 /sbin/mingetty tty2
root 3696 1 0 10:24 tty3 00:00:00 /sbin/mingetty tty3
root 3697 1 0 10:24 tty4 00:00:00 /sbin/mingetty tty4
root 3698 1 0 10:24 tty5 00:00:00 /sbin/mingetty tty5
root 3699 1 0 10:24 tty6 00:00:00 /sbin/mingetty tty6
root 3700 1 0 10:24 ? 00:00:00 /usr/bin/gdm-binary -nodaemon
root 3749 3700 0 10:24 ? 00:00:00 /usr/bin/gdm-binary -nodaemon
root 3750 3749 0 10:24 ? 00:00:02 /usr/X11R6/bin/X :0 -auth /var/gdm/:0.Xauth vt7
gdm 3765 3749 0 10:24 ? 00:00:02 /usr/bin/gdmgreeter
mysql 4619 3427 54 11:28 ? 01:00:53 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/my
root 5364 3394 0 12:38 ? 00:00:00 /usr/sbin/sshd
root 5367 5364 0 12:39 pts/0 00:00:00 -bash
root 5483 1 0 12:54 ? 00:00:00 /usr/sbin/httpd -k start
apache 5484 5483 0 12:54 ? 00:00:02 /usr/sbin/httpd -k start
apache 5485 5483 0 12:54 ? 00:00:00 /usr/sbin/httpd -k start
apache 5486 5483 0 12:54 ? 00:00:01 /usr/sbin/httpd -k start
apache 5487 5483 0 12:54 ? 00:00:01 /usr/sbin/httpd -k start
apache 5488 5483 0 12:54 ? 00:00:00 /usr/sbin/httpd -k start
apache 5489 5483 0 12:54 ? 00:00:02 /usr/sbin/httpd -k start
apache 5490 5483 0 12:54 ? 00:00:01 /usr/sbin/httpd -k start
apache 5491 5483 0 12:54 ? 00:00:00 /usr/sbin/httpd -k start
apache 5504 5483 0 12:55 ? 00:00:02 /usr/sbin/httpd -k start
apache 5572 5483 0 13:04 ? 00:00:01 /usr/sbin/httpd -k start
apache 5615 5483 0 13:06 ? 00:00:00 /usr/sbin/httpd -k start
apache 5619 5483 0 13:06 ? 00:00:01 /usr/sbin/httpd -k start
apache 5657 5483 0 13:08 ? 00:00:00 /usr/sbin/httpd -k start
apache 5681 5483 0 13:09 ? 00:00:00 /usr/sbin/httpd -k start
apache 5682 5483 0 13:09 ? 00:00:00 /usr/sbin/httpd -k start
apache 5683 5483 0 13:09 ? 00:00:00 /usr/sbin/httpd -k start
apache 5688 5483 0 13:09 ? 00:00:00 /usr/sbin/httpd -k start
apache 5691 5483 0 13:09 ? 00:00:00 /usr/sbin/httpd -k start
apache 5695 5483 0 13:09 ? 00:00:00 /usr/sbin/httpd -k start
apache 5697 5483 0 13:09 ? 00:00:00 /usr/sbin/httpd -k start
apache 5707 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5711 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5712 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5717 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5719 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5720 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5722 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5723 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5724 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5725 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5728 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5729 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5730 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5731 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5732 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5733 5483 0 13:10 ? 00:00:00 /usr/sbin/httpd -k start
apache 5740 5483 0 13:11 ? 00:00:00 /usr/sbin/httpd -k start
apache 5744 5483 0 13:11 ? 00:00:00 /usr/sbin/httpd -k start
apache 5745 5483 0 13:11 ? 00:00:00 /usr/sbin/httpd -k start
apache 5746 5483 0 13:11 ? 00:00:00 /usr/sbin/httpd -k start
root 5785 3534 0 13:20 ? 00:00:00 CROND
root 5787 3534 0 13:20 ? 00:00:00 CROND
mailman 5788 5787 0 13:20 ? 00:00:00 /usr/bin/python -S /var/mailman/cron/gate_news
root 5790 5785 0 13:20 ? 00:00:00 /usr/bin/perl /usr/bin/mrtg /etc/mrtg/mrtg.cfg
root 5795 5367 0 13:20 pts/0 00:00:00 ps -ef

richardwalton

ASKER

Again. The same problem. Multiple httpd processes running. Could each session, after the user has closed the browser, become stuck in memory?

blkline

You have installed all of the most up-to-date packages, yes? You mention that you are on RH9 -- that's two versions down from the current. There have been quite a number of issues fixed since then.

ASKER CERTIFIED SOLUTION

wesly_chen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

richardwalton

ASKER

The system has been running sucessfully for a number of months as our helpdesk system. It is not totally up2date but will be after tonight:)) Around 5 employees here login and max 10 external users also log in. Its based on the IRM from Altrusville (sourceforge).

A simple apachectl stop and start seems to restore all performance loss! Dont fancy doing that every twenty minutes though;)

I've found a user who has been updating the user details today, which seems to correspond with every time it stops responding. I've asked him to stop for the moment whilst i check out the related php pages. I will post back in a bit with my findings.

Thanks alot,
Richard.

wesly_chen

You can check the following URL for RedHat 9 update:
http://download.fedoralegacy.org/redhat/9/updates/i386/

Wesly

ITG-SSNA

Richard you should also check the filesystems and data models for integrity.

I've seen this happen on systems with a corrupted file in /var/lib/mysql

My suggestion would be to first try (as root)

shutdown -Fr now

This will force the file system check.

After completing this then boot up and run mysqlcheck

mysqlcheck --auto-repair -o dbname tablename

Be advised both operations could possibly result in a data loss in the event you do have errors on the FS.

Regards,

~K Black

richardwalton

ASKER

Problem was bad SQL. Fixing the error results in back to normal good performance:) Happyness comes once again...

Thanks to all the posts involved.