MySQL Connection Failure

[Anon]

I have been trouble for over a week now with Apache/PHP/Mysql connection failure problems. Seems my system (VALinux) is using all its RAM (or nearly all 512M😎 then I get errors printed to the screen. This system has worked without hickups for over a year. The error messages says:

Warning: MySQL Connection Failed: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug in /home/web/public_html/lib/php/dblib.php on line 14. Line 14 is where it is trying to create the DB connection.

What ever it is takes over the whole machine. From a SSH I can not even execute commands. I get "bash: fork: Resource temporarily unavailable"

I have added another 512 MB of RAM and I still had the problem. Soon after adding the new RAM the machine became complete unresponsive and I had to physically go to the machine. I found a message on the console screen that said I might have trouble with some RAM, so I thought is might have been the original RAM chip. I removed it which seemed to help, but the problem is occuring again.

I am at a loss and will certainly appreciate any help someone can offer. I have gottne fairly good at writing PHP code, but the DB connections is not something I have a lot of knowledge about. If I need to study something, please point me in the right direction.

I have also noticed that I seem to have more processes running of mysql than I do of httpd (see below). Am I not killing all my DB connections? Doesn't PHP do this when the page is finished?

bash-2.04$ ps -ae | grep httpd | wc -l
53
bash-2.04$ ps -ae | grep mysql | wc -l
bash: fork: Resource temporarily unavailable
bash-2.04$ ps -ae | grep mysql | wc -l
105

Any ideas? Your help is greatly appreciated.

[[deleted]]

Look at the output of 'top' and see which process is taking up all the memory.

It's possible that your MySQL is configured to use more memory than you have available.

Either reduce the amount of memory that MYSQL is allowed to use, or lower the maximum number of connection that MYSQL will accept.

[Anon]

Thanks for your reply. I have been watching top for a while to see if it will give me any clues. The output is below. It does not look to me that anything is a real hog of the memory, just that there are so many processes at one time.

You mentioned to decrease the number of processes. I actually increased this several months ago when I was getting other connection errors. I am reluctant to decrease this because I think it might cause failure to connect errors for another reason. Is this right?

How do I set the amount of memory MySQL can use? Is this the smartest thing to do, or should I increase the RAM. What is the smartest solution?

TOP OUTPUT:

8:13am up 2 days, 22:56, 1 user, load average: 0.13, 0.14, 0.10
486 processes: 482 sleeping, 3 running, 1 zombie, 0 stopped
CPU0 states: 8.3% user, 8.5% system, 0.0% nice, 82.2% idle
CPU1 states: 9.4% user, 10.4% system, 0.0% nice, 79.1% idle
Mem: 523844K av, 520048K used, 3796K free, 0K shrd, 226416K buff
Swap: 530112K av, 0K used, 530112K free 82032K cached

PID USER PRI NI SIZE RSS SHARE LC STAT %CPU %MEM TIME COMMAND
28021 thom 18 0 1164 1164 680 3 R 8.0 0.2 0:25 top
28598 root 19 0 10152 9.9M 1792 0 R 4.7 1.9 0:00 mysqld
10471 nobody 1 0 6196 6196 2188 3 S 1.2 1.1 0:20 httpd
10486 nobody 3 0 5772 5772 2184 0 S 1.2 1.1 0:21 httpd
10943 nobody 1 0 5208 5208 2204 3 S 1.2 0.9 0:19 httpd
10501 nobody 0 0 5656 5656 2188 0 S 1.1 1.0 0:23 httpd
18963 nobody 1 0 5384 5384 2168 3 S 0.9 1.0 0:10 httpd
27178 nobody 2 0 4472 4472 2172 0 S 0.9 0.8 0:01 httpd
10470 nobody 0 0 5652 5652 2204 0 S 0.7 1.0 0:20 httpd
20167 nobody 0 0 5488 5488 2168 3 S 0.7 1.0 0:10 httpd
10467 nobody 0 0 5812 5812 2184 0 S 0.3 1.1 0:21 httpd
10473 nobody 0 0 5724 5724 2188 3 S 0.3 1.0 0:21 httpd
10477 nobody 0 0 5296 5296 2176 3 S 0.3 1.0 0:21 httpd
10480 nobody 0 0 5800 5800 2184 3 S 0.3 1.1 0:22 httpd
10490 nobody 0 0 6032 6032 2188 3 S 0.3 1.1 0:22 httpd
31100 root 0 0 10016 9.8M 1792 0 S 0.1 1.9 0:04 mysqld
31103 root 0 0 10016 9.8M 1792 0 S 0.1 1.9 0:02 mysqld
10816 nobody 0 0 5584 5584 2180 3 S 0.1 1.0 0:20 httpd
19115 nobody 0 0 5320 5320 2188 3 S 0.1 1.0 0:09 httpd
1 root 0 0 484 484 408 3 S 0.0 0.0 0:29 init
2 root 0 0 0 0 0 0 SW 0.0 0.0 0:25 kflushd
3 root 0 0 0 0 0 0 SW 0.0 0.0 0:21 kupdate

Thanks for your help.

[Anon]

I just posted a reply, and after I did I noticed something in the TOP output I have not noticed before. I have been watching the %MEM and this does not seem like anything is a memory hog, BUT I just noticed the RSS for the mysql processes is 9.8MB. Now that does seem like a lot of memory for each process. Is that what you were trying to call to my attention? Is that my problem? How do I tell those processes to use less memory and how do I know what to set this to?

[[deleted]]

RSS is resident, shared memory that is common to all threads, it's only allocated once.

[[deleted]]

Do you get any exotic messages in your syslog or mysql's error logs?

[Anon]

Nothing in syslog about mysql.

MySQL log just has standard starts, stops, ready for connections.