| 12411529 | unauthenticated user | 10.11.11.30:38784 | NULL | Connect | NULL | login | NULL | | 12411530 | unauthenticated user | 10.11.11.30:38785 | NULL | Connect | NULL | login | NULL | | 12411531 | unauthenticated user | 10.11.11.40:42175 | NULL | Connect | NULL | login | NULL | | 12411532 | unauthenticated user | 10.11.11.40:42176 | NULL | Connect | NULL | login | NULL | | 12411533 | unauthenticated user | 10.11.11.30:38786 | NULL | Connect | NULL | login | NULL | | 12411534 | unauthenticated user | 10.11.11.40:42177 | NULL | Connect | NULL | login | I've got a system running MySQL 4.1.22 which is getting into a situation where it gives >1000 lines like the above in the output of "show processlist;" and then doesn't respond. When this happens the system is under no real load (CPU use an IO wait are almost 0). In normal operation there is something less than 100 connections most of which are in "Sleep" state. Any suggestions for what I can do to investigate this? -- My Main Blog http://etbe.coker.com.au/ My Documents Blog http://doc.coker.com.au/
On Wed, Jan 18, 2012 at 08:30:27AM +1100, Russell Coker wrote:
[thousands of connections in 'show processlist' like: 10.11.11.30:38784, 10.11.11.30:38785, 10.11.11.30:38786 and 10.11.11.40:42175, 10.11.11.40:42176, 10.11.11.40:42177 stuck in the login stage]
I've got a system running MySQL 4.1.22
any particular reason why you're running such an ancient version of mysql? 4.1 has been deprecated for years....and 4.1.22 isn't even the latest in the 4.1 series - 4.1.22 was released in 2006, and 4.1.25 was released in 2008.
Any suggestions for what I can do to investigate this?
look on hosts 10.11.11.30 and 10.11.11.40 and find out what's making all those connections. start with netstat and grep for the source port numbers...maybe something like 'netstat -tupan | grep :3878[456]' what version of the mysql client libs are on those hosts? craig -- craig sanders <cas@taz.net.au> BOFH excuse #130: new management
On Thu, 19 Jan 2012, Craig Sanders <cas@taz.net.au> wrote:
I've got a system running MySQL 4.1.22
any particular reason why you're running such an ancient version of mysql? 4.1 has been deprecated for years....and 4.1.22 isn't even the latest in the 4.1 series - 4.1.22 was released in 2006, and 4.1.25 was released in 2008.
The database server has been running that version mostly without problems for years. I tried to upgrade it a while ago but had some sort of password compatibility issue and haven't tried since. Upgrading a 24*7 database is a major PITA.
Any suggestions for what I can do to investigate this?
look on hosts 10.11.11.30 and 10.11.11.40 and find out what's making all those connections. start with netstat and grep for the source port numbers...maybe something like 'netstat -tupan | grep :3878[456]'
They are all legitimate connections for things such as looking up users who are receiving mail. The reason there are so many connections is simply because they aren't getting processed. Client processes were blocking on database queries and more processes are created.
what version of the mysql client libs are on those hosts?
Almost all the client systems are now running Debian/Squeeze with all the latest updates. There is one client system running RHEL4. Anyway I think I've solved this problem. I've edited /etc/hosts on the database server and added entries for every client system. It seems that the MySQL server may have been doing DNS lookups or something. It's been working well since I changed /etc/hosts. -- My Main Blog http://etbe.coker.com.au/ My Documents Blog http://doc.coker.com.au/
Thus spake Russell Coker:
Anyway I think I've solved this problem. I've edited /etc/hosts on the database server and added entries for every client system. It seems that the MySQL server may have been doing DNS lookups or something. It's been working well since I changed /etc/hosts.
Is there any reason why you don’t maintain a reverse DNS zone for your hosts? Even a $GENERATE–based one would do wonders for sanity. It may be a lot of work, but in the long run it would pay off big time.
On Sat, Feb 4, 2012 at 5:58 PM, Russell Coker <russell@coker.com.au> wrote:
Anyway I think I've solved this problem. I've edited /etc/hosts on the database server and added entries for every client system. It seems that the MySQL server may have been doing DNS lookups or something. It's been working well since I changed /etc/hosts.
If you start mysqld with the --skip-name-resolve option, you disable this behaviour, and you can get far better performance if your clients don't have reverse DNS entries.
-- My Main Blog http://etbe.coker.com.au/ My Documents Blog http://doc.coker.com.au/ _______________________________________________ luv-main mailing list luv-main@luv.asn.au http://lists.luv.asn.au/listinfo/luv-main
participants (4)
-
Craig Sanders -
Jeremy Visser -
Leigh Sharpe -
Russell Coker