How can I find the Unix process that owns a local Sleeping MySQL connection?_问答_开发者

I'm fighting a 'Too many connections' problem with my MySQL process and I've got to the point when mysqladmin processlist -uroot -pXXXXX results in:

+------+------------+-----------+------------+---------+------+-------+------------------+
| Id   | User       | Host      | db         | Command | Time | State | Info             |
+------+------------+-----------+------------+---------+------+-------+------------------+
| 842  | svcControl | localhost | svcObjects | Sleep   | 1772 |       |                  |
| 875  | svcControl | localhost | svcObjects | Sleep   | 1773 |       |                  |
| 884  | svcControl | localhost | svcObjects | Sleep   | 1770 |       |                  |
| 896  | svcControl | localhost | svcObjects | Sleep   | 1540 |       |                  |
| 951  | svcControl | localhost | svcObjects | Sleep   | 709  |       |                  |
| 1023 | svcControl | localhost | svcObjects | Sleep   | 708  |       |                  |
| 1063 | svcControl | localhost | svcObjects | Sleep   | 708  |       |                  |
| 1069 | svcControl | localhost | svcObjects | Sleep   | 708  |       |                  |
| 1075 | svcControl | localhost | svcObjects | Sleep   | 707  |       |                  |
| 1083 | svcControl | localhost | svcObjects | Sleep   | 707  |       |                  |
| 1091 | svcControl | localhost | svcObjects | Sleep   | 706  |       |                  |
| 1097 | svcControl | localhost | svcObjects | Sleep   | 706  |       |                  |
| 1107 | svcControl | localhost | svcObjects | Sleep   | 705  |       |                  |
| 1112 | svcControl | localhost | svcObjects | Sleep   | 702  |       |                  |
| 1120 | svcControl | localhost | svcObjects | Sleep   | 704  |       |                  |
| 1127 | svcControl | localhost | svcObjects | Sleep   | 704  |       |                  |
| 1136 | svcControl | localhost | svcObjects | Sleep   | 704  |       |                  |
| 1141 | svcControl | localhost | svcObjects | Sleep   | 644  |       |                  |
| 1202 | svcControl | localhost | svcObjects | Sleep   | 268  |       |                  |
| 1222 | svcView    | localhost | svcObjects | Sleep   | 8    |       |                  |
| 1224 | svcControl | localhost | svcObjects | Sleep   | 280  |       |                  |
| 1225 | svcControl | localhost | svcObjects | Sleep   | 8    |       |                  |
| 1265 | root       | localhost |            | Query   | 0    |       | show processlist |
+------+------------+-----------+------------+---------+------+-------+------------------+

So, clearly, I'm leaking connections somewhere, but I can't figure out where. I've been looking for sources that tell me how to get from a connection ID to a Unix process ID, but most of the approaches expect the connections to be from remote servers, allowing you to use netstat to debug them. Either that, or they require you to modify your codebase to log each attempted connection, which seems a little like overkill.

So... are there any approaches that anyone knows of? Any files that this sleeping connection will have open, so that I can use fuser, for instance? Or... is it possible that these are zombie connections that have no owning process?

In response to answers below

If I run ps -ef | grep mysql, I see:

root      5960     1  0 Oct14 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql
mysql     6007  5960  0 Oct14 ?        00:24:12 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

Running the suggest commands, I get:

# fuser -u /var/lib/mysql/mysql.sock
/var/lib/mysql/mysql.sock:  6007(mysql)

# lsof -p 5960
COMMAND    PID USER   FD   TYPE DEVICE     SIZE    NODE NAME
mysqld_sa 5960 root  cwd    DIR    8,1     4096       2 /
mysqld_sa 5960 root  rtd    DIR    8,1     4096       2 /
mysqld_sa 5960 root  txt    REG    8,1   735004  117216 /bin/bash
mysqld_sa 5960 root  mem    REG    8,1    50848   90123 /lib/libnss_files-2.5.so
mysqld_sa 5960 root  mem    REG    8,1   129832   87812 /lib/ld-2.5.so
mysqld_sa 5960 root  mem    REG    8,1  1689388   90858 /lib/libc-2.5.so
mysqld_sa 5960 root  mem    REG    8,1    20668   90874 /lib/libdl-2.5.so
mysqld_sa 5960 root  mem    REG    8,1    13276   91815 /lib/libtermcap.so.2.0.8
mysqld_sa 5960 root  mem    REG 253,18 56418144  656494 /usr/lib/locale/locale-archive
mysqld_sa 5960 root  mem    REG 253,18    25462 1573763 /usr/lib/gconv/gconv-modules.cache
mysqld_sa 5960 root    0r   CHR    1,3             1432 /dev/null
mysqld_sa 5960 root    1w   CHR    1,3             1432 /dev/null
mysqld_sa 5960 root    2w   CHR    1,3             1432 /dev/null
mysqld_sa 5960 root  255r   REG 253,18    13077 1181028 /usr/bin/mysqld_safe

lsof -p 6007
COMMAND  PID  USER   FD   TYPE     DEVICE      SIZE     NODE NAME
mysqld  6007 mysql  cwd    DIR     253,19      4096    33005 /var/lib/mysql
mysqld  6007 mysql  rtd    DIR        8,1      4096        2 /
mysqld  6007 mysql  txt    REG     253,18   7413588  1998921 /usr/libexec/mysqld
mysqld  6007 mysql  mem    REG        8,1   1296932    91720 /lib/libcrypto.so.0.9.8e
mysqld  6007 mysql  mem    REG     253,18    612180  1514570 /usr/lib/libkrb5.so.3.3
mysqld  6007 mysql  mem    REG        8,1    293108    91722 /lib/libssl.so.0.9.8e
mysqld  6007 mysql  mem    REG        8,1   3200288    87758 /lib/libnss_ldap-2.5.so
mysqld  6007 mysql  mem    REG        8,1     21948    90121 /lib/libnss_dns-2.5.so
mysqld  6007 mysql  mem    REG        8,1    129832    87812 /lib/ld-2.5.so
mysqld  6007 mysql  mem    REG        8,1   1689388    90858 /lib/libc-2.5.so
mysqld  6007 mysql  mem    REG        8,1    216544    90877 /lib/libm-2.5.so
mysqld  6007 mysql  mem    REG        8,1     20668    90874 /lib/libdl-2.5.so
mysqld  6007 mysql  mem    REG        8,1    137908    90866 /lib/libpthread-2.5.so
mysqld  6007 mysql  mem    REG     253,18     75284  1514584 /usr/lib/libz.so.1.2.3
mysqld  6007 mysql  mem    REG        8,1     48156    90867 /lib/librt-2.5.so
mysqld  6007 mysql  mem    REG        8,1     50848    90123 /lib/libnss_files-2.5.so
mysqld  6007 mysql  mem    REG        8,1    245376    90881 /lib/libsepol.so.1
mysqld  6007 mysql  mem    REG        8,1     93508    91676 /lib/libselinux.so.1
mysqld  6007 mysql  mem    REG        8,1     46636    90847 /lib/libgcc_s-4.1.2-20080825.so.1
mysqld  6007 mysql  mem    REG     253,18    936908  1514611 /usr/lib/libstdc++.so.6.0.8
mysqld  6007 mysql  mem    REG        8,1    109740    90873 /lib/libnsl-2.5.so
mysqld  6007 mysql  mem    REG        8,1     80636    90879 /lib/libresolv-2.5.so
mysqld  6007 mysql  mem    REG        8,1      7748    91684 /lib/libcom_err.so.2.1
mysqld  6007 mysql  mem    REG     253,18    190712  1514583 /usr/lib/libgssapi_krb5.so.2.2
mysqld  6007 mysql  mem    REG     253,18    157304  1514569 /usr/lib/libk5crypto.so.3.1
mysqld  6007 mysql  mem    REG        8,1      8072    90878 /lib/libkeyutils-1.2.so
mysqld  6007 mysql  mem    REG     253,18     33712  1509918 /usr/lib/libkrb5support.so.0.1
mysqld  6007 mysql  mem    REG        8,1     45432    90876 /lib/libcrypt-2.5.so
mysqld  6007 mysql    0r   CHR        1,3               1432 /dev/null
mysqld  6007 mysql    1w   REG     253,19    251337   163863 /var/log/mysqld.log
mysqld  6007 mysql    2w   REG     253,19    251337   163863 /var/log/mysqld.log
mysqld  6007 mysql    3u  IPv4   26825288                TCP monstermunch.ssd.hursley.ibm.com:59850->hurgsa.hursley.uk.ibm.com:ldap (CLOSE_WAIT)
mysqld  6007 mysql    4uW  REG     253,19 161480704    33142 /var/lib/mysql/ibdata1
mysqld  6007 mysql    5u   REG     253,16         0       13 /tmp/ibmydRNj (deleted)
mysqld  6007 mysql    6u   REG     253,16        20       14 /tmp/ib59yuxj (deleted)
mysqld  600开发者_StackOverflow社区7 mysql    7u   REG     253,16         0       15 /tmp/ib0Vp8gj (deleted)
mysqld  6007 mysql    8u   REG     253,16         0       16 /tmp/ibjkCG1i (deleted)
mysqld  6007 mysql    9uW  REG     253,19   5242880    33143 /var/lib/mysql/ib_logfile0
mysqld  6007 mysql   10uW  REG     253,19   5242880    33144 /var/lib/mysql/ib_logfile1
mysqld  6007 mysql   11u  IPv4   26825294                TCP *:mysql (LISTEN)
mysqld  6007 mysql   12u   REG     253,16         0       17 /tmp/ibKpCJ1i (deleted)
mysqld  6007 mysql   13u  unix 0xea432900           26825295 /var/lib/mysql/mysql.sock
mysqld  6007 mysql   14u   REG     253,19     30720    65557 <DB Table>
mysqld  6007 mysql   15u  unix 0xf5188c80           30430332 /var/lib/mysql/mysql.sock
mysqld  6007 mysql   16u   REG     253,19      3072    98468 <DB Table>
mysqld  6007 mysql   17u  unix 0xf5925680           30433174 /var/lib/mysql/mysql.sock
mysqld  6007 mysql   18u   REG     253,19      1024    65605 <DB Table>
mysqld  6007 mysql   19u  unix 0xf5188880           30430336 /var/lib/mysql/mysql.sock
mysqld  6007 mysql   20u   REG     253,19        40    98469 <DB Table>
mysqld  6007 mysql   21u  unix 0xf5925280           30433176 /var/lib/mysql/mysql.sock
mysqld  6007 mysql   22u   REG     253,19     39344    65558 <DB Table>
mysqld  6007 mysql   23u   REG     253,19      1024    65623 <DB Table>
mysqld  6007 mysql   24u   REG     253,19         0    65624 <DB Table>
mysqld  6007 mysql   25u  unix 0xf51f6880           30430339 /var/lib/mysql/mysql.sock

In the output above, <DB Table> is my edit.

I may be missing something, but I don't see anything there that is a process of mine holding open a connection... unless those deleted /tmp files are key.

If the connections were made over a local socket, you might have some luck with sudo fuser -u /tmp/mysql.sock. You could also find mysql's process ID (let's call it $MYSQL_PID) and then run sudo lsof -p $MYSQL_PID, which will include all open sockets.

If you can, change the way you are connecting to the mysql server by using 127.0.0.1 instead of localhost.

By doing that, a tcp/ip connection will be used and you will have in the processlist the port number that you can use to trace back to the process.

A tcp/ip connection is slower than a unix file socket connection, but it'll do the trick.

references :

http://dev.mysql.com/doc/refman/5.1/en/can-not-connect-to-server.html

http://dev.mysql.com/doc/refman/5.1/en/connecting.html

Check which application uses "svcObjects" database with user "svcControl". This will lead you to the problem root I guess.