Hey Guys/Gals

I have started having some mysql problems on a mysql server. The server has 512MB ram PIII 1Gig etc, but it has now become very slow but when i run top, the cpu is 80+ idle, more than half the memory is free, so what gives....also no processes are killing any resources. I have also started to get a problem with "too many connections open" how can i :

1) increase the maximum connection from the defaul 100 ( where is the config file )

2) How can i check which db/user has the poor script thats not closing connections or can i limit the number of open connections per user?

Is there anything else that i can do, perhaps to increase the number of open files for the server ( its Redhat 6.1 ) i have increase these before but dont recall how i did it lol


Cheers

Originally posted by tuvok

2) How can i check which db/user has the poor script thats not closing connections or can i limit the number of open connections per user?


Try mysqladmin processlist, and find the user that is using up all the connection.

open mysql and execute this query:
show processlist;

or start mysql as:
/usr/local/bin/safe_mysqld --user=mysql --basedir=/usr/local --datadir=/var/db/mysql --set-variable=max_connections=500 --set-variable=wait_timeout=200

That will automatically close all "IDLE" connections that are idle for more than 200 seconds, this does not affect working connections and it will raise max connections value to 500.

These kinda errors generally happen when someone forgets to close their mysql connection, but setting idle timeout fixes it.