MySQL problems

[Robber]

Seeing as we dont have a MySQL forum I thought I'd post here.

Basically our server has been falling over all day today and MySQL has been giving the too many connections error.

What I'd like to know is this: I have been using the SHOW processlist to see the processes running, but unless I catch the process at the exact time it is querying I can't see which query is running. It then goes to sleep for ages hence why its running out of free connections.

Does anyone know how I can see more details about each of these processes?

Thanks

[coopster]

If you want to log all queries (for example to find a problem query) you should use the general query log [mysql.com].

[bluedevil]

Maybe your traffic has increased and there is no specific query that's causing the problems?

Do you also have performance problems when MySQL is running out of connections? You should increase that value if you have server resources to handle it.

You can also try the The Slow Query Log to get a log of querys that takes more than x seconds to complete.
[mysql.com...]

Are you using mysql_connect och mysql_pconnect to connect to MySQL?

[Robber]

Thanks for the replies. Just trying out your ideas.

I have had a look round the logs and there is no obvious increase in traffic (we have about 20 domains on this server).

Today is the first time I have seen MySQL run out of connections - usually apache dies before this happens. Apache previously exceeded the max connections so I raised this (some time ago) although it still stalls and from looking at the error log I can see there are loads of child processes that have to be killed - I guess because they would not terminate.

It looks as though something is happening when a php script executes which prevents the script from exiting properly, which ties up resources until the thing falls over.

I have narrowed the mysql process problem to two of our domains. Each of these are requesting an xml feed from the same vendor. I am thinking that the problem could be if the remote server does not respond resulting in my script sitting there waiting for a response rather than timing out.

I might be wrong but I figured this would affect mysql as I didnt use mysql_close() since it would normally close connections at the end of the script.

Does that make any sense?

[bluedevil]

the function set_time_limit(x seconds) limits the maximum execution time for a script.

Have you tried that?

[Nova Reticulis]

You might want to check if someone of your users uses persistent connections.

[incywincy]

i'm no mysql guru but could i ask why you don't close your mysql connection once you have completed your database operations? whenever i script for sql stuff i only keep the database conenction open for the bare minimum of time. although it is expensive (in terms of performance) to open and close the database connection it prevents creation of too many open connections. i would never rely on program termination to close a database connection cleanly.

[Robber]

I thought about using the set_time_limit(x seconds) but I figured that since the php.ini max_execution_time set to 30 this isn't really doing the job - just checked this morning and I have a mysql process that has been sleeping for 27279 seconds!

We dont use persistent connections - just mysql_connect() - dont need to worry about other uses as all of the domains on the server are built by us.

Until now I hadn't used mysql_close() because over on php.net they basically say dont both since it closes at the end of your script - but I will definitely be using it in future - no point taking the risk in opinion.

[Robber]

So how I should I start the Slow Query Log - I'm not so hot at command line stuff. I tried:

/etc/init.d/mysql start --log-slow-queries

I thought that would have created a file with default name slow.log as per mysql manual.

Trouble is, can't find it anywhere and there are definitely some slow queries!

[coopster]

>>I thought that would have created a file with default name
>>slow.log as per mysql manual.

Actually, Robber, the filename in this case would be:

[i]host_machine_name[/i]-slow.log

If no file name is given, it defaults to the name of the host machine suffixed with -slow.log. If a filename is given, but doesn't contain a path, the file is written in the data directory. (From: The Slow Query Log [mysql.com])