1

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)
1
  • 1
    Some stats from multiple servers: Median: 8 aborted_connects per day; 70th percentile: 9/hour; 90th: 2/minute. So, I agree that your 7/sec is quite high.
    – Rick James
    Mar 2, 2020 at 18:30

1 Answer 1

1

There is a LOG variable that can log connection errors to a file.

http://dev.mysql.com/doc/refman/5.6/en/error-log.html

Speculating on the high number of aborted connections, are there virus/port scanners/monitors that could have ran across the server? Are there possibly bad programs or users (aka. spyware/malware) attempting to break into the database through brute force?

http://dev.mysql.com/doc/refman/5.6/en/communication-errors.html

Hope this helps.

8
  • Are you talking about log_error_verbosity? On speculation, I'm fairly certain that this is really old WordPress plugins with bad code, but I'm in need of precisely demonstrating that or it will be ignored. There are a very small number of machines that have any access to this and they are only applicable web servers and a monitoring server. If I was getting bad connections, I'm pretty sure I'd know about that. I'm also fairly certain there are no attempted attacks as this server isn't reachable outside the subnet with very precise exceptions in border firewalls.
    – flickerfly
    Nov 9, 2016 at 15:10
  • There is --log-warnings in 5.6 (in addition to --general-log). (Sorry I was thinking in 5.1). Not too familiar with WordPress plugins, but do they connect (via outside of WordPress) to access the database? Perhaps there is a authentication issue with the correct user/password/database. Also, there had been some security notices with WordPress. Latest version currently is 4.6.1. Hopefully nothing came through. Nov 9, 2016 at 15:37
  • They aren't supposed to, but these do. I think there was a time when plugins handled their own connections, but I may be giving prior development too much credit.
    – flickerfly
    Nov 9, 2016 at 16:40
  • Sorry... Working on tablet. See if setting --log-warnings=n with n having a value greater than 1. dev.mysql.com/doc/refman/5.6/en/… Nov 9, 2016 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.