Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

A client is using PHP to connect to MySQL. The PHP scripts and the MySQL database are located on 2 different Linux servers. He complained that database connections were being dropped or timed out and asked me to take a look.

Is there any place in MySQL that can show me what and how many connections have been dropped or timed out? I looked into slow query log and didn't see anything.

Any suggestions on how to diagnose this dropped/timed out database connection problem?

Thanks

EDIT:

Slow query log is enabled in my.cnf:

log-slow-queries=/var/log/mysql-slow-queries.log

And when I do a

mysql> show global status;

I got:

| Slow_queries                      | 11402347     | 

So there are a lot of slow queries. But the file /var/log/mysql-slow-queries.log doesn't exist. Why is that?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

The slow query log file must exist and must be writable by the mysql user. On a standard mysql installation I would do:

touch /var/log/mysql-slow-queries.log
chown mysql.mysql /var/log/mysql-slow-queries.log

And then restart mysql.

If you set the slow log file in a particular directory (i.e. /var/log/mysql/slow.log) with enough permissions, MySQL will create the log file by itself:

mkdir /var/log/mysql
chown mysql.mysql /var/log/mysql
share|improve this answer
    
I did that and now the slow query log file is there but it's empty. Is there any way to avoid restarting mysql? Also, for future reference, is MysQL supposed to create the file "mysql-slow-queries.log" on its own or somehow I need to help it to do that? Thanks. –  Continuation Feb 27 '11 at 3:12
    
Newer versions of MySQL 5.1 or higher allow you to turn on/off the slow query log without restart. In mysql 5.0 there are some tricks you can use but i'm not sure about the results. –  HD. Feb 27 '11 at 13:41

Have you enabled your slow query log? Is it empty or does it work?

Normally the slow query log is off for performance reasons and you can enable it to debug if you need.

You can try to use the new MySQL Workbench. With the MySQL Administrator inside the Workbench you can See all open connections in the Tool in a list. Its very helpful to see your connections and manage and monitor your server for a short time.

Other way is to take the MySQL statement:

show processlist;

this shows you the running queries and you can see what's hanging.

Perhaps i could help you. For the slow query log you get more information on the MySQL site or here

share|improve this answer
    
Stony, please see my EDIT above. There are many slow queries according to show global status; but the slow query log file doesn't exist. Do you know why? Thanks. –  Continuation Feb 27 '11 at 2:49
    
hmm very interesting. Perhaps you have the sections 2 times? That you set in on top but any lines behind them is another path? And can you try the follow? set log_slow_queries = ON; –  Stony Feb 27 '11 at 2:55
    
oh the mysql deamon is running under mysql user. Try /var/log/mysql/ for your path if this exists. And show if you have permissions on this folder. –  Stony Feb 27 '11 at 2:59
    
I got en error: mysql> set log_slow_queries = ON; ERROR 1238 (HY000): Variable 'log_slow_queries' is a read only variable –  Continuation Feb 27 '11 at 3:08
    
i have seen its enough when you set the the time and the file and don't forget to change the folder to /var/log/mysql/mysql-slow-query.txt on the mysql folder you there you have enough permissions in different cases you need to create the file like HD says but when you have enough he should create it. here DONT FORGET TO RESTART the MySQL deamon after settings the options. ;) –  Stony Feb 27 '11 at 3:14

mysqladmin extended-status|head can also help here.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.