Web lists-archives.com

RE: mysql strangeness...




FYI - just an example...

mysql> SELECT VERSION();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    203720459
Current database: NNTP

+-----------------------------+
| VERSION()                   |
+-----------------------------+
| 5.5.38-0ubuntu0.12.04.1-log |
+-----------------------------+
1 row in set (33.94 sec)


mysql> SELECT VERSION();
+-----------------------------+
| VERSION()                   |
+-----------------------------+
| 5.5.38-0ubuntu0.12.04.1-log |
+-----------------------------+
1 row in set (0.00 sec)


This is from the mysql client running on the same host as the mysql server,
connected to localhost via TCP.  Current connections to the DB was at about
200 out of 500

--
Chris.



-----Original Message-----
From: Chris Knipe [mailto:savage@xxxxxxxxxxxxx] 
Sent: Sunday, December 07, 2014 11:34 PM
To: 'mysql@xxxxxxxxxxxxxxx'
Subject: mysql strangeness...

Hi,

I have a "not so" busy MySQL server (+- 150 Selects/sec, 180 Deletes/Sec,
320 Updates/Sec, 90 Inserts/Sec and 200 Replace/Sec), max 512 concurrent
connections. 

The server is running on a Dell R720 with 64GB Ram, Xeon E5-2620.  Data is
on a 4 x 3TB (RAID10) SATA3 array, and binlogs on a  4 x 600GB (RAID10) SSD
array. MySQL 5.5.38

Up to a while ago (few days / week), everything was running absolutely fine.
Lately however, I have noticed more and more times that I am hitting my max
connection limits.  Standard tools like mysql-tuner (which took more than an
hour to run because of slow connections to MySQL), tuning-primer, innotop,
etc. all shows that the configured variables are within normal parameters,
and MySQL is also not under *any* IO load what so ever.  I'd say 99% of all
tables are InnoDB, and even the one single Memory table that I have, seems
to be running slow queries :-(  I'm also seeing a lot of "connection reset
by peer" network related errors.

What I am seeing, is that even when there are only like 300 or so
connections established to the server, even the mysql (command utility)
running on the localhost, takes MINUTES to establish a connection to the
mysql server - however, once the connection IS established, queries execute
absolutely fine and within seconds.  It's just the initial establishment of
the connection to mysql which is taking forever.  The process list shows 99%
of all the active connections in a "READING FROM NET" state (unauthenticated
users).  Skip-name-resolve and all those things are disabled, and tcpdumps
also confirmed that mysql is in fact NOT doing DNS lookups.

The server IMHO is under no significant load at all (dedicated mysql
system), and there's only +- 5Mbps of traffic on the 1Gbps interface in the
server.   All in all, graphs indicate 750/800 QPS with about 30% reads, and
70% writes.

Can anyone perhaps suggest some things to look into here?  I'm pretty sure
that the problem is with the initial establishment of the TCP connection to
mysql, rather than it being a mysql issue related to queries and what not.
Telneting to the mysql socket, I do get the initial greeting from the server
virtually instantaneous like it should be. So the only thing that I can
think of is that something is whacked in terms of authenticating users?

Box is firewalled, only authorised hosts are permitted to connect, all users
are authenticating by password and IP (%)

--
Chris.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql