UNAUTHENTICATED USER causing mysql 5.6 to hang

I upgraded one of our alpha servers from MySQL 5.1 to 5.5 and then to Percona Server 5.6. This server has a few hundred databases and each databases has a few hundred tables. The next day the server kept hanging and would eventually become so sluggish that it had to be restarted. When attempting to establish a new connection it would indicate max connections had been reached. If I was able to log on before the max connections were all used up, I would see a few hundred "unauthenticated user" which would continue to build up without closing.

From searching online, I first saw some blogs telling you to make sure that skip-name-resolve is in the my.cnf file and that you are only using IP address for limiting user permissions.

Here is another site that talks about this problem:
http://codeinthehole.com/writing/solving-mysql-connection-problems-caused-by-a-dead-name-server/

I had already done that so skip-name-resolve wasn't the problem.

This problem continued to happen so I started pt-stalk and demonized it so that if anything started to build up, it would capture forensic data.

I was finally able to capture this happening by using pt-stalk and a threshold of threads_running at 10. At first I tried setting the threshold with threads_connected but the "unauthenticated user" did not count as a connection and I wasn't able to catch it happening.

pt-stalk generates a lot of files...I started reviewing each one of them.

I checked the error log and nothing looked like a problem.

I looked in processlist and saw a number of entries like this:

*************************** 110. row ***************************
           Id: 1021
         User: unauthenticated user
         Host: connecting host
           db: NULL
      Command: Connect
         Time: NULL
        State: login
         Info: NULL
    Rows_sent: 0
Rows_examined: 0

That was expected.

One of the files was called "2016_03_31_18_24_11-opentables2" and all it contained was "Too many open tables:".

That was interesting.

The next useful file was the "Output" file which was full of errors like this:
ERROR 2013 (HY000): Lost connection to MySQL server at 'sending authentication information', system error: 32

Those two errors were the key. I read up on some forums, such as this one:
http://stackoverflow.com/questions/17813630/mysql-5-6-headaches-on-mac-osx

Developers noted that they had this problem on their local testing MySQL instance because table_open_cache was too low.

I checked my system like this:

show global variables like '%table_open_cache%';


Table_open_cache was set at 64.

I increased it like this:

SET GLOBAL table_open_cache = 32768;

I also updated it in the my.cnf file.

Then I asked our quality engineering team to run some tests and I watched the open tables increase by running a command like this:

SHOW STATUS LIKE 'Open_tables'

Within a few minutes it was past 17,000 open tables and all the queries were finishing too quickly to even catch with SHOW PROCESSLIST. It didn't crash.

MySQL 5.6 can handle a much larger table_open_cache than MySQL 5.1 could.

I'm not convinced that is the only problem with this instance but it seems to be doing better.

We also found a number of app servers that had outdated MySQL client tools running on the app side (MySQL 5.1). Those various client tools were being used to connect to MySQL 5.6 and appeared to be contributing to the un-authenticated users hanging around and eventual hanging of MySQL. Since updating all the client tools to MySQL 5.6, haven't seen this issue anymore.

pt-kill

I've started using pt-kill on an internal MySQL system to snipe queries that run too long. It works very well. For reasons unknown to me, we have code that logs to a table but does so serially and LOCKS all the tables involved in the logging effort. At times this will cause bottlenecks and the tables will be locked for 30 minutes and eventually the server will reach max connections and crash because nothing can get through. I've demonized pt-kill and it watches for any queries that match the specific databases, specific user, busy time and then kills it. I like how to logs every query it kills either to a file or to table so I can keep track of how often it is working.

Here are my examples:

This first example is more judicious as it only kills one query at a time and then waits:

pt-kill --interval=5 --busy-time=10 --create-log-table --log-dsn=h=localhost,D=percona,t=kill_log --daemonize --user=USERNAME --password=xxxxxxx --victims=oldest --wait-after-kill=10 --match-command=Query --match-info "^(Lock|LOCK|lock)" --match-user=app_user --match-db=logs --kill-query

This second examples will kill more queries more freuqently which it catches any and all with the busy-time of 10 seconds (notices the victims is set to all):

pt-kill --interval=5 --busy-time=10 --create-log-table --log-dsn=h=localhost,D=percona,t=kill_log --daemonize --user= USERNAME --password= xxxxxxx --victims=all --wait-after-kill=1 --match-command=Query --match-info "^(Lock|LOCK|lock)" --match-user= app_user --match-db= logs --kill-query

Generating random data in MySQL

I've had to do this a number of times and found the ability to create random data in tables with a stored procedure very useful. Here are a couple of my favorite examples of doing this:

http://stackoverflow.com/questions/11042546/how-do-i-populate-a-mysql-table-with-many-random-numbers

CREATE TABLE rand_numbers (
    number INT NOT NULL
) ENGINE = InnoDB;

DELIMITER $$
CREATE PROCEDURE InsertRand(IN NumRows INT, IN MinVal INT, IN MaxVal INT)
    BEGIN
        DECLARE i INT;
        SET i = 1;
        START TRANSACTION;
        WHILE i <= NumRows DO
            INSERT INTO rand_numbers VALUES (MinVal + CEIL(RAND() * (MaxVal - MinVal)));
            SET i = i + 1;
        END WHILE;
        COMMIT;
    END$$
DELIMITER ;

CALL InsertRand(1111, 2222, 5555);

http://stackoverflow.com/questions/9927933/how-to-insert-random-numbers-mysql

I made a couple spelling corrections.

CREATE TABLE foo (
    bar INT NOT NULL
) ENGINE = InnoDB;

DELIMITER $$
CREATE PROCEDURE random_fill( IN cnt INT )
BEGIN

    fold: LOOP

         IF cnt < 1 THEN
             LEAVE fold;
         END IF;

        INSERT INTO foo ( bar ) VALUES ( 9 + CEIL( RAND() * 90 ) );

        SET cnt = cnt - 1;
    END LOOP fold;

END$$   
DELIMITER ;

CALL random_fill(10000);

Here is an example I've made:

CREATE TABLE `random` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `unique` varchar(255) NOT NULL DEFAULT '',
  `date_action_performed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `data` text NOT NULL,
  `type` enum('blue','green','yellow') NOT NULL DEFAULT 'blue',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;



DROP PROCEDURE IF EXISTS `random_fill_fun`;


DELIMITER $$
CREATE PROCEDURE random_fill_fun( IN cnt INT)
BEGIN

    fold: LOOP

         IF cnt < 1 THEN
             LEAVE fold;
         END IF;

        SET @MIN_DATE = '2016-03-01 00:00:00';
        SET @MAX_DATE = '2016-03-23 23:59:59';

        INSERT INTO `random` (`id`, `unique`, `date_action_performed`, `user_id`, `data`, `type`) VALUES (NULL,  UUID(), TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN_DATE, @MAX_DATE)), @MIN_DATE), 9 + CEIL( RAND() * 90 ), SUBSTRING(MD5(RAND()) FROM 1 FOR 50), ELT(0.5 + RAND() * 3, 'blue', 'green', 'yellow'));


        SET cnt = cnt - 1;
    END LOOP fold;

END$$  
DELIMITER ;



CALL random_fill_fun(10000);

How could DBAs be more programmer friendly?

Found this question/answer on stackexchange. I thought it was a good read, I'm going to copy it here:

http://dba.stackexchange.com/questions/2471/how-could-dbas-be-more-programmer-friendly/2485#2485

Technically speaking, DBAs who have one or more of the following qualities are the best to work with:

1. Spent years as developers themselves
2. Have a grasp of database theory
3. Have a good understanding of how the RDBMS works internally
4. Have superior knowledge of the operating system

Very disciplined, knowledgeable DBAs have a lot to share and offer. They may see database performance from a perspective not really considered by Developers. Developers know what they want from the database. DBAs know how to be "polite" to the database.

As far as personalities go, there will always be conflicts, pettiness, and maybe even envy. One thing is for certain: In no particular order, DBAs and Developers are like husbands and wives (I've been happily married for 16 years with on-going projects [4 children]).

Regardless who is viewed as the husband and who is viewed as the wife, these principles apply:

1. one must consult the other
2. one must value the perspective of the other
3. one must make decisions for the good of both parties
4. one must support, and not sabatoge, the decision made
5. one must not denigrade the other if decisions result in bad consequences
6. one must rejoice in the contribution of both parties to the success of decisions
7. one must consult a higher authority (HA) if a decision cannot be mutually agreed upon

These seven(7) principles apply just as much in the workplace, especially in the IT realm.

By communicating every step of way, all should :

1. layout their expectations
2. engender respect for the other party's ability to do their part based on past performance
3. have trust and confidence that the other party can complete their assignment
4. live up to our own expectations
5. acquiese under the guidance of the HA (see principle #7)

There is no room for micromanagement in this. DBAs SHOULD NOT TELL Developers how to think like DBAs. Developers SHOULD NOT TELL DBAs how to be Developers. Final decisions ondatabase performance and usage must rest with DBAs. Final decisions on application needs must rest with Developers. This symbiosis must be maintained always.

FINAL THOUGHTS

Principle #7 requires active participation and oversight by the HIGHER AUTHORITY (the HA), i.e., project manager, team leader, lead developer. Your HA better know how both parties work individually and how both parties should work together. If the HA does not establish ground rules for both parties, or if the HA fails to guide the parties individually and together, projects will always come to a halt at some point and endanger the very existence (employment) of the Developer, the DBA, or even the HA.