How to track what owns a MySQL connection at Xaprb

How to track what owns a MySQL connection

MySQL doesn’t yet provide good tools for some troubleshooting tasks. Fortunately, there is some low-hanging fruit you can pluck. One example is a tool to record who owns a MySQL database connection, so long-running transactions can be traced back to the source. This article demonstrates an easy way to solve that problem.

Introduction

One of the reasons I wrote the innotop InnoDB and MySQL monitor was to monitor long-running transactions. Sure enough, I began to see some transactions being held open for tens of thousands of seconds (ouch!), sometimes with open locks and undo log entries. From MySQL itself I can find the connection ID, transaction number, username and hostname of the offender, but not what program opened the connection. Since our software mostly uses a single login to access the database, even the username and hostname didn’t help. It could be one of dozens of systems.

How I solved it

Everything has the same username because all our software connects through one database abstraction layer, which creates a single point of access. I changed it to identify the caller to the database. This is fast and painless, and provides a reverse lookup.

Here’s the trick: create a table like this,

create table connection_info (
   connection_id int not null,
   unix_proc_id int not null,
   prog_name varchar(50) not null,
   ts timestamp not null,
   primary key(connection_id),
   key(unix_proc_id)
) engine = MyISAM;

This is obviously designed for UNIX systems. Our client software is written in Perl, so any program that connects can be recorded simply by changing the DB access layer to issue the following query right after connecting:

replace into connection_info
   (connection_id, unix_proc_id, prog_name, ts)
   select connection_id(), $PID, '$PROGRAM_NAME', current_timestamp

$PID and $PROGRAM_NAME are Perl variables for the current process’s ID and the name of the program currently executing. Every programming language and operating system I know has some way to get this information.

The query is fast, so there’s very little added overhead when making a connection, especially given that our systems tend to connect and hold the connection open for a long time while doing a lot of work. The initial extra cost becomes vanishingly small.

Results

After I made this change, we were able to start looking up who held an open transaction for a long time. The culprit turned out to be processes owned by an Apache web server, which were running instances of mod_perl that didn’t really get unloaded after the HTTP request was serviced. The real trouble was poor coding practice, of course; explicitly closing the database connection is the solution.

Although this system works well for us, I’m curious if there are better ways to do it. What do you think? Leave a comment!

Written by Baron Schwartz

July 23rd, 2006 at 6:04 pm

Posted in Uncategorized

12 Responses to 'How to track what owns a MySQL connection'

Subscribe to comments with RSS

If you want a global solution you could add it to –init-connect instead of having to modify your db access layer.

Tobias "flupps" Asplund

24 Jul 06 at 5:34 am
That’s interesting, I never knew about it (init_connect). I’m not sure how I could get the same functionality though, since that SQL statement executing on the MySQL server would not know the UNIX process ID or program name of whatever was trying to connect.

Xaprb

24 Jul 06 at 8:00 am
Nice article. But isn’t “SHOW processlist” enough to do this type of troubleshooting?

Selwin

25 Aug 06 at 7:39 pm
Selwin,

Thanks! No, because SHOW PROCESSLIST doesn’t say what program connected and what its UNIX process ID is. When you have a bunch of cloned machines and programs all running the same queries, and one of them is causing trouble, knowing what queries it’s running doesn’t narrow it down.

In fact, we even found the need to add more information in — a few days ago I tweaked the connection code to add the REQUEST_URI in cases where the connection was coming from our intranet, because tracing back to the UNIX process ID just tells us /usr/bin/apache2, and it really helps to know exactly what code is using the connection.

As if that weren’t enough, I even added one more thing in: what Class::DBI module made the connection.

All these things have been absolutely indispensable for finding badly behaved code and dealing with it. Much of the code was written by people who freely admit they knew nothing about transactions, and sprinkled BEGIN and COMMIT through the code without knowing what they meant. It’s pretty disconcerting when that code goes into wider production and the mysterious side effects begin piling up. “Hey! I just entered that information and now it disappeared! Where did it go?”

Definitely not something SHOW PROCESSLIST can even start to do. But your needs may be different from ours.

Xaprb

25 Aug 06 at 9:16 pm
```
SELECT connection_id();
```
Returns the connection ID (thread ID) for the connection. Every connection has its own unique ID.
Javier

14 Sep 06 at 12:19 pm
Yes, I know, and I’ve known that for years :-) But what is the UNIX process ID of the program that made the connection?

This article isn’t about knowing what your connection ID is, or what your connection is doing. This article is about being a DBA and seeing a connection that is doing something ridiculous, or hung, or just staying open for three days. You don’t want to just kill the connection. You want to find the code that made the connection, and fix it. OK, you can easily find out the connection ID (in fact, by definition, you should already know that information before any of the rest of my article is of any use to you), and the machine where the connection was made. But can you find out the program on that machine that made the connection? Let’s pretend you can (probably impossible for most of you). And if it’s “/usr/sbin/apache2,” what then?

The connection is not the source of the trouble. Knowing the connection ID is trivial, and doesn’t get you anywhere. The program that made the connection is the problem, and that is what you need to find out. That is the point of this article. The further point of this article is that you cannot find this information from within MySQL. You have to do some sort of technique such as the ones I suggest here.

Xaprb

14 Sep 06 at 1:35 pm
Nice idea that, totally understand why you needs this.

Another approach is to have a different user for each “application” that connects. It isn’t quite as good since you can’t see what process opened the connection, but it does mean you can monitor which applications are doing what if you know that application A corresponds to a certain user. I used it to see which applications aren’t releasing connections.

Something that might help solve all problems would be the ability to pass a token in when opening a connection. Such as this
```
conStr = "blah=blah;token=" + this.Process.Id;
con = new MySqlConnection(con);
```
Then, you could see in MySQL process list the token associated with the connection. This would help me greatly as then you could even indicate in the token which function/subroutine the connection was opened from if tha twas the level of granularity required.

Tobin
Tobin Harris

25 Sep 06 at 9:12 am
SoftTree Technologies now provides full MySQL auditing including both network based and local access as part of the DB Audit solution. The solution comes with graphical tools for setting up the auditing and for monitoring and reporting on database access and user activities within MySQL databases; and also includes a back-end API that allows hooking the auditing into third-party applications.

For details, see http://www.softtreetech.com/idbaudit.htm

For more details contact SoftTree Technologies

Dmitriy

13 Jun 07 at 7:23 pm
[...] and InnoDB Transaction List modes. These show the originating program and PID for connections by querying tables in which this data is stored. The plugin adds the columns and expressions for them, and then adds the data in by using [...]

innotop 1.5.0 released at Xaprb

10 Sep 07 at 5:59 pm
looks like thread id in “prstat -L -p $mysqld_pid ” is the select connection_id()+10.

thread 1-10 are internal thread and not in show processlist.

binzhang

10 Feb 09 at 4:31 am
A lot of applications are generally responsible and close the connection properly. I’m more interested in slow queries that bog down the database across HTTP (Apache). We also use a common database layer. An alternate approach is to simply track the HTTP requests that take a long time and, specifically, the one SQL query that hung up the page. We use PHP, so we can also dump a call stack and put serialize()’d request data into the database. If the user has already waited more than 5 seconds for a response, what is an extra 20ms? To do this, modify the database layer to make one extra SQL query to a “long queries” table that contains all the information you think you will need to diagnose issues.

Another Approach

26 Aug 10 at 6:37 pm
Hi there, i know that this articule was written in 2006, but..

mysql> show full processlist;
from ‘Host’ column you can read _host_ and _PORT_

log into that _host_ and:
# netstat -pt state connected |grep _PORT_
and you have program name and pid

greetings

faja

2 Sep 11 at 12:27 pm

Xaprb