Mysql not spawning secondary connections

[onematchfire]

My mysql appears to be only running as a single process and not spawning secondary connections. No matter how many requests are put in, in top I only see a single mysqld and and in mtop I see requests backing up. I can see that my custom my.cnf setting for max connection to 180, but still there is only 1 mysqld in top?

Any ideas?

[fastduke]

What does this show?
ps auxf

[onematchfire]

HERE IS WHAT I SEE:
root 26566 0.0 0.0 4340 1144 ? S 01:32 0:00 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf
mysql 26589 0.0 0.3 17832 6624 ? S 01:32 1:16 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking

I HAVE ANOTHER SB BOX WHICH I PS AUXF'd TO SEE HOW IT SHOULD BE:
05:21 0:00 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf
mysql 811 0.0 1.3 35832 14244 ? SN 05:21 0:00 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir
mysql 831 0.0 1.3 35832 14244 ? SN 05:21 0:00 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --dat
mysql 832 0.0 1.3 35832 14244 ? SN 05:21 0:01 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 840 0.0 1.3 35832 14244 ? SN 05:21 0:00 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 1085 1.7 1.3 35832 14244 ? SN 05:21 4:23 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 1086 1.6 1.3 35832 14244 ? SN 05:21 4:01 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 1087 1.7 1.3 35832 14244 ? SN 05:21 4:10 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 1088 1.7 1.3 35832 14244 ? SN 05:21 4:14 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 1089 1.6 1.3 35832 14244 ? SN 05:21 4:08 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 2501 1.9 1.3 35832 14244 ? SN 08:10 1:29 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3030 1.4 1.3 35832 14244 ? SN 09:18 0:06 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3031 1.0 1.3 35832 14244 ? SN 09:18 0:05 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3032 0.6 1.3 35832 14244 ? SN 09:18 0:03 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3033 2.1 1.3 35832 14244 ? SN 09:18 0:10 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3111 0.7 1.3 35832 14244 ? SN 09:21 0:02 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3112 0.6 1.3 35832 14244 ? SN 09:21 0:01 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3113 0.1 1.3 35832 14244 ? SN 09:21 0:00 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3114 1.0 1.3 35832 14244 ? SN 09:21 0:02 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3115 0.8 1.3 35832 14244 ? SN 09:21 0:02 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3116 0.8 1.3 35832 14244 ? SN 09:21 0:02 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3117 0.8 1.3 35832 14244 ? SN 09:21 0:02 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3118 0.7 1.3 35832 14244 ? SN 09:21 0:01 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3119 0.3 1.3 35832 14244 ? SN 09:21 0:00 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3122 1.1 1.3 35832 14244 ? SN 09:21 0:03 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -
mysql 3123 1.0 1.3 35832 14244 ? SN 09:21 0:02 \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr -

[QT]

Originally posted by onematchfire
My mysql appears to be only running as a single process and not spawning secondary connections. No matter how many requests are put in, in top I only see a single mysqld and and in mtop I see requests backing up. I can see that my custom my.cnf setting for max connection to 180, but still there is only 1 mysqld in top?

Any ideas?

On newer versions of the RedHat, some utilities do not show threads as separate processes. Check the man page for top or ps for information on displaying threads as processes.

Are these servers running the same version of Linux?

[onematchfire]

I'm wondering if the problem is due to the fact on the problematic box mysql is running as root, but on the working box it's running as a custom user, mysql?

[onematchfire]

Are these servers running the same version of Linux?

the 'working' box is RH7.3, the 'problem' box is RH 9.0

[knightfoo]

The /usr/bin/safe_mysqld always runs as root and is responsible for spawning the additional mysqld processes when necessary. The mysqld processes should always rung as the mysql user for security reasons.

One of two things is happening here: the processes are being spawned as threads and you can't see them or safe_mysqld is not able to spawn mysqld processes at all. Red Hat 9 is a bit newer than Red Hat 7.3 and may be using a new threading model. There are some command-line options for ps that will make threads visible as processes.

There should be a logfile for safe_mysqld in /var/log/mysqld.log or specified in the my.cnf file that may help you track down any safe_mysqld problems. There is also a mysqld error log in /var/lib/mysql (or where ever the database files are stored) that will have some useful information.

-knightfoo

[onematchfire]

okay, I now see all the threads on the problem box when doing ps auxfm. thanks for the illumination.

BUT DANG, that means I still can't explain why on the 'problem' box mysql requests queue up the minute there is a 2nd or 3rd query. with mtop I see queue and locks with only 2 or 3 requests. The box is doing nothing else, taking no web requests and has a top of 0.1?

What could be making this machine handle mysql requests so poorly?

[knightfoo]

Here is an example of the thread hiding on my Debian workstation:

# ps -ef | grep mysql
root 7271 1 0 12:01 pts/3 00:00:00 /bin/sh /usr/bin/mysqld_safe
mysql 7307 7271 0 12:01 pts/3 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
# ps -efL | grep mysql
root 7271 1 7271 0 1 12:01 pts/3 00:00:00 /bin/sh /usr/bin/mysqld_safe
mysql 7307 7271 7307 0 3 12:01 pts/3 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
mysql 7307 7271 7308 0 3 12:01 pts/3 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
mysql 7307 7271 7310 0 3 12:01 pts/3 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock


-knightfoo

[onematchfire]

In my mysqld.log each time I start mysql I see the following error:

041116 6:04:18 Found invalid password for user: 'root'@'telac.dogster.com'; Ignoring user
/usr/libexec/mysqld: ready for connections

Is this a problem or just one of those things?

Thanks for clueing me in about suppressed process listing on RH9. It's funny how much one learns when things go wrong ;>