Mysql – Unknown source of high number of connections

aws-auroraconnectionsconnectivityMySQLmysql-5.7

In MySQL/Aurora I see from time to time a spike in connections number (from about 100 to 4000):

MySQL doc says:

Connections

The number of connection attempts (successful or not) to the MySQL server.

So I was thinking those coming from failed connection, however I don't see failures:

MySQL [(none)]> SHOW GLOBAL STATUS like 'Conn%';
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Connection_errors_accept          | 0        |
| Connection_errors_internal        | 0        |
| Connection_errors_max_connections | 0        |
| Connection_errors_peer_address    | 0        |
| Connection_errors_select          | 0        |
| Connection_errors_tcpwrap         | 0        |
| Connections                       | 34388970 |
+-----------------------------------+----------+
7 rows in set (0.00 sec)

MySQL [(none)]> show global variables like '%conn%';
+-----------------------------------------------+-------------------+
| Variable_name                                 | Value             |
+-----------------------------------------------+-------------------+
| aurora_max_connections_limit                  | 16000             |
| connect_timeout                               | 10                |
| disconnect_on_expired_password                | ON                |
| init_connect                                  |                   |
| max_connect_errors                            | 100               |
| max_connections                               | 4000              |
| max_user_connections                          | 0                 |
| performance_schema_session_connect_attrs_size | 512               |
+-----------------------------------------------+-------------------+

I would very much like to understand what those are and when they are coming from.

Best Answer

Sounds like a hacker.
Based on your graphs, I suggest that max_connections = 4000 is unreasonably high. It looks like 20 might be reasonable.
You might want to set max_user_connections to some reasonable value (instead of 0 for "unlimited").

Related Solutions

MySQL – What Are User Connections and When Are They Created and Destroyed

Please notice the makeup of mysql.user:

mysql> show create table mysql.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

Notice the column max_user_connections. It puts a moratorium on the number of connections a user may make per hour. Check and make sure the user '557574_prod' isn't connecting to mysql a ton of times within less than an hour.

This is used in conjunction with the option max_user_connections that you can set in /etc/my.cnf

Please change either max_user_connections in /etc/my.cnf or, if the individual user has this column set, that column mysql.user.max_user_connections. If you have no config control over my.cnf and you cannot do SQL against the mysql schema, then you beg and implore your hosting company to raise those limits for you.

I believe you can set the max_user_connections as a session variable, but you may need certain privileges, probably SUPER.

As for establish connections, you need to keep those connections open. Perhaps sending a heartbeat of some kind by doing a SELECT. But guess what ??? The db columns max_questions monitor SELECT queries per hour and max_updates monitors INSERTs, UPDATEs, DELETEs per hour. YOu may need to check these columns in mysql.user. You can quickly check those number with:

SHOW GRANTS;

I am not sure if the the user's per-hour connection limits are per thread or per user. You could answer that quickly by connecting to the DB aggressively using 2 or 3 DB connecitons at the same time and the error message should manifest itself. It is up you how to count the number of connections, perhaps "SHOW STATUS LIKE 'Connections';" It should reach either the max limits of the connection within the one connections or it could be the sum of all connections' connection status value.

Please also check the wait_timeout and interactive_timeout values. The default is 28800 in a standalone server. They may be set already. You cannot set them within a current connection. You must be able to set them as follows: (for new conenctions coming)

SET GLOBAL interactive_timeout = 86400;
SET GLOBAL wait_timeout = 86400;

If you cannot run even these commands, my condolences !!! :(

MySQL database drop insanely slow

I hate the checking permissions issue.

You may have to disable key checks before the DROP DATABASE

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.