I have a MySQL 5.6 server that feeds to 6 web servers and a slave read-only mysql 5.6 server. These are the only clients with the exception of a monitoring software. If any were failing to work, I'd expect my users to notify me immediately or get a report from my monitoring software, instead I am seeing performance issues under heavy load that I'd like to improve upon.
All the web servers and the db servers are on the same subnet in a VMWare cluster with SAN storage behind. I'm not seeing any performance/latency/service-affecting issues on the hardware or storage networks on other VMs hosted on this infrastructure so I'm not seeing a reason to blame hardware.
In looking into this, I've noticed high connection problems on both the mysql master and slave servers. Details below are from the master server, but look similar on both.
$ mysqladmin ext|grep Abort
| Aborted_clients | 1171 |
| Aborted_connects | 11044700 |
$ ~/mysql_uptime.sh
19 days 7 hours 46 minutes
During normal day use, I'm seeing the Aborted_connects rising by around 5/sec.
I looked into turning on the general log, but that didn't seem to provide any insight. I'm open to possibly being blinded by the volume of information coming through that log file and not knowing where it goes. I have it off right now because of how quickly it grew in size.
How can I get more details into the cause of these aborted connections so I can clean them up?
EDIT: Here is some further details that I've found which might be relevant:
mysql> show processlist;
+----------+--------------+-------------------+----------------------------+-------------+---------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+--------------+-------------------+----------------------------+-------------+---------+-----------------------------------------------------------------------+------------------+
| 1269 | replicator | 10.0.100.67:49545 | NULL | Binlog Dump | 1671359 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 23312602 | root | localhost | NULL | Query | 0 | init | show processlist |
| 23314268 | user_prod | 10.0.100.52:42645 | my_production | Sleep | 0 | | NULL |
| 23314272 | user_prod | 10.0.100.51:48595 | my_production | Sleep | 0 | | NULL |
| 23314274 | user_prod | 10.0.100.55:48785 | my_production | Sleep | 0 | | NULL |
| 23314276 | user_prod | 10.0.100.53:47137 | my_production | Sleep | 0 | | NULL |
+----------+--------------+-------------------+----------------------------+-------------+---------+-----------------------------------------------------------------------+------------------+
6 rows in set (0.00 sec)
mysql> show status like '%onn%';
+-----------------------------------------------+----------+
| Variable_name | Value |
+-----------------------------------------------+----------+
| Aborted_connects | 11060852 |
| 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 | 23314326 |
| Max_used_connections | 528 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 3 |
+-----------------------------------------------+----------+
14 rows in set (0.00 sec)
mysql> show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 300 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| wait_timeout | 300 |
+-----------------------------+----------+
12 rows in set (0.00 sec)
cat /etc/mysql/my.cnf |grep -v ^# (Only the mysqld section)
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-name-resolve
bind-address = 0.0.0.0
key_buffer = 90M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 650
wait_timeout = 300
interactive_timeout = 300
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 67108864 #64M
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes
general_log = 0
general_log_file = /var/log/mysql/mysql-query.log
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
tmp_table_size = 256M
max_heap_table_size = 256M
table_open_cache = 7500
innodb_buffer_pool_size = 1G
innodb_flush_method = O_DIRECT
I've been using mysqltuner script to try to tune it. Here is the current results.
$ mysqltuner
>> MySQLTuner 1.1.1 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.33-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MyISAM tables: 337M (Tables: 301)
[--] Data in InnoDB tables: 1007M (Tables: 316)
[!!] Total fragmented tables: 46
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 19d 8h 26m 52s (548M q [328.153 qps], 23M conn, TX: 12932B, RX: 71B)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 1.4G global + 1.1M per thread (550 max threads)
[OK] Maximum possible memory usage: 2.0G (25% of installed RAM)
[OK] Slow queries: 0% (328K/548M)
[!!] Highest connection usage: 96% (528/550)
[OK] Key buffer size / total MyISAM indexes: 90.0M/120.9M
[OK] Key buffer hit rate: 100.0% (260M cached / 126K reads)
[OK] Query cache efficiency: 87.2% (434M cached / 498M selects)
[!!] Query cache prunes per day: 60703
[OK] Sorts requiring temporary tables: 0% (15K temp sorts / 12M sorts)
[!!] Temporary tables created on disk: 32% (4M on disk / 14M total)
[OK] Thread cache hit rate: 99% (190K created / 23M connections)
[!!] Table cache hit rate: 4% (223 open / 4K opened)
[OK] Open file limit used: 0% (112/13K)
[OK] Table locks acquired immediately: 99% (73M immediate / 73M locks)
[!!] Connections aborted: 47%
[OK] InnoDB data size / buffer pool: 1007.6M/1.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce or eliminate persistent connections to reduce connection usage
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
max_connections (> 550)
wait_timeout (< 300)
interactive_timeout (< 300)
query_cache_size (> 64M)
table_cache (> 6500)
aborted_connectsper day; 70th percentile: 9/hour; 90th: 2/minute. So, I agree that your 7/sec is quite high.