Mysql Connections topping out within seconds

hello, i have aweird problem that has only just started happening.

i have a small cluster (one web and one db) setup and i host a rather popular group of4 -5 sites that allow users to dynamicly create their own mobile chat communitys automaticly. each site gets its own mysql db createdand populated automaticly.

this is all fine,

but in the last 24hours weird things have begun happening, previously i had the sql max_connections set to 500 and this was perfectly adaqute for the demand but now even when i set the connection to 4000+ they are all maxxed out within 5-10 minutes, and mysql processlist shows thousands of unauthenticated user connections sitting at login status,

i have gone through the sites and all their mysql configs are fine so i cant see what the issue is.

server specs below

db server:

dual amd opteron 246
8GB ram
120gb hd(64gb free)
33gb swap (rarly used but their for emergencys)
centos 5 64bit.
direct 100mbit lan to web serv

only mysql,ssh and webmin running, no other apps installed

web server:

amd athlon 64 3800+
plesk 9.2.1
4gb rram
2x120gb hds

apache status onthe web server only shows 120ish http connections butthe sql keeps climbing

the one problem with that, mysql server is firewalled in such a way that the only accessto mysql is via the backend lan to web, the servers internet ip only allows ssh links

DJ_Steve 2009-05-13 17:22:39

Sorry if I misunderstood the problem, but what if you recorded some info when MySQL connections are made like: IP, page where it came from, etc.; then dumped that info to a log file if the connection ends up in that idle unauthenicated state.

Zenshai 2009-05-13 17:46:44

the ip shown by the mysqladmin processlist command is 192.168.1.3 which is the local lan ip of web server - the connections immediatly show up in that state,

DJ_Steve 2009-05-13 18:45:06

just for reference this is how the lines show in processlist| Id | User | Host | db | Command | Time | State | Info || 101 | unauthenticated user | 192.168.1.3:51319 | | Connect | | login | || 102 | unauthenticated user | 192.168.1.3:51320 | | Connect | | login | || 104 | root | localhost | | Query | 0 | | show processlist |

DJ_Steve 2009-05-13 19:29:25

That makes sense, since the webserver acts as the intermediary between the User and MySQL, its going to be the webserver's IP that shows up in the process list. What im talking about is using the webserver to get a User's IP and associate it with any MySQL connections that user subsequently makes. I do this in PHP using $_SERVER['Remote_address'], not sure what your applications are running on.

Zenshai 2009-05-13 20:08:57

thats possible but ive noticed that now these sites are starting to throw mysql connection erros inthe logs (access denied for apache@localhost etc even though their config files are their and fine.

DJ_Steve 2009-05-13 20:56:23

Can you update your original post with some samples of these error messages (edit out any sensitive info of course)

Zenshai 2009-05-13 21:18:46

ansaurus

tags:

views:

answers:

Mysql Connections topping out within seconds

related questions