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?

Continuation
  • 3,050
  • 5
  • 29
  • 38

3 Answers3

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
hdanniel
  • 4,253
  • 22
  • 25
  • 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 03: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 '11 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

René Höhle
  • 1,418
  • 3
  • 17
  • 26
  • 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 02: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 '11 at 02: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 '11 at 02: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 03: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](http://www.petefreitag.com/item/233.cfm) DONT FORGET TO RESTART the MySQL deamon after settings the options. ;) – René Höhle Feb 27 '11 at 03:14
0

mysqladmin extended-status|head can also help here.

womble
  • 95,029
  • 29
  • 173
  • 228
vnix27
  • 856
  • 2
  • 11
  • 19