2

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?

Improve this question

3 Answers 3

Reset to default
2

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
Improve this answer
2
  • 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, 2011 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.
    – hdanniel
    Feb 27, 2011 at 13:41
1

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

Improve this answer
5
  • 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, 2011 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;
    – René Höhle
    Feb 27, 2011 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.
    – René Höhle
    Feb 27, 2011 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, 2011 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. ;)
    – René Höhle
    Feb 27, 2011 at 3:14
0

mysqladmin extended-status|head can also help here.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .