MySQL database debugging

[ssuexed]

Hi,

We have a small database at Sonoma State's Extended Education and upon looking at the phpMyAdmin status report, I noticed that there is an excessive amount of queries being ran on the server (a few hundred per second). We don't have many users (a few hundred a month) and should not be seeing these numbers.

http://imgur.com/6uPI1

I was wondering what tools are available for monitoring either 1) what queries are being run hundreds of times a second or 2) which pages the queries are originating from.

[toast0]

Is this on the Sonic MySQL server or your own? If it's the Sonic server, I think those numbers are server wide statistics, so you need not be alarmed. If it's your own, you might not need to be alarmed either, the total number of queries is from when the server started (81 days ago), and the queries per second is averaged; you may have run some intensive stuff a while ago and are still seeing the higher averages as a result.

show full processlist; will let you know what's being run currently. If you have a shell that can access the sql server, http://jeremy.zawodny.com/mysql/mytop/ is a nice interactive tool as well. If it's your server, you can also turn on logging of all queries (but if your stats are accurate, the log is going to fill up pretty quick)

Tracking queries back to pages can be tough, best way to do it is to insert sql comments in your queries with either the page url, or some other identifying remark, but I haven't actually seen that done anywhere

[williamt]

Hi,

We have a small database at Sonoma State's Extended Education and upon looking at the phpMyAdmin status report, I noticed that there is an excessive amount of queries being ran on the server (a few hundred per second). We don't have many users (a few hundred a month) and should not be seeing these numbers.

http://imgur.com/6uPI1

I was wondering what tools are available for monitoring either 1) what queries are being run hundreds of times a second or 2) which pages the queries are originating from.

There are several different ways to accomplish this. See: http://www.mysqlperformanceblog.com/200 ... y-logging/ for a good start.

Once you identify the queries you will have to grep through your code/pages to find out which one is responsible.