MySQL crashing after reaching max connections
Description
Environment
Smart Checklist
Activity
Lalit Choudhary May 9, 2019 at 7:50 AM
@Shishu Naik
Tested with https://perconadev.atlassian.net/browse/PS-5#icft=PS-5.7.22 as well and I do not see any crash.
If there is no error or warning related to max_connection in error log how you are concluding that the crash caused due to max_connections?
if it's consistently reproducible in your environment then provide us the following the information.
1. Steps to reproduce this issue.
2. type of MySQL installation, it is binary or rpm installation?
3. MySQL configuration file, provide us my.cnf for problematic instance
The output of the following commands
show variables like '%audit%';
show variables like 'general_log%';
show variables like 'max_connections';
show status like 'max%connec%';
Shishu Naik May 8, 2019 at 1:29 PM
Please find the stack trace information of crash.
0xec60ec my_print_stacktrace + 44
0x79e811 handle_fatal_signal + 1121
0x347000f7e0 _end + 1847578896
0x346fc897f2 _end + 1843884322
0x7f44cac6897b _end + -924491093
0x7f44cac695cb _end + -924487941
0x79f00e handle_fatal_signal + 3166
0x79f4dd mysql_audit_notify(THD*, mysql_event_general_subclass_t, char const*, int, char const*, unsigned long) + 605
0xbe7c6c Query_logger::general_log_print(THD*, enum_server_command, char const*, ...) + 204
0xc66037 mysql_change_db(THD*, st_mysql_const_lex_string const&, bool) + 887
0x7a9759 acl_authenticate(THD*, enum_server_command, bool) + 4937
0xc60aa4 free_user + 2356
0xc6239a thd_prepare_connection(THD*, bool) + 90
0xd6a411 handle_connection + 641
0xeddd84 pfs_spawn_thread + 436
0x3470007aa1 _end + 1847546833
0x346fce8c4d _end + 1844274557
Shishu Naik May 7, 2019 at 12:27 PM
Hello Lalit,
The strange thing is we didn't get any too many connections warnings in error logs even when log_error_verbosity is set to 3.
Lalit Choudhary May 7, 2019 at 11:16 AM
Hi @Shishu Naik
Thank you for the report.
In my test with audit log and general log enabled PS server 5.7.25, I do not see any crash after connections reaching to the max limit. all we get is warnings in error log.
Please provide a reproducible test case or steps and my .cnf file. Also, test same with the latest percona server version in your test environment.
Test:
mysql [localhost] {msandbox} ((none)) > show variables like '%audit%';
+-----------------------------+---------------+
| Variable_name | Value |
+-----------------------------+---------------+
| audit_log_buffer_size | 1048576 |
| audit_log_exclude_accounts | |
| audit_log_exclude_commands | |
| audit_log_exclude_databases | |
| audit_log_file | audit.log |
| audit_log_flush | OFF |
| audit_log_format | OLD |
| audit_log_handler | FILE |
| audit_log_include_accounts | |
| audit_log_include_commands | |
| audit_log_include_databases | |
| audit_log_policy | ALL |
| audit_log_rotate_on_size | 0 |
| audit_log_rotations | 0 |
| audit_log_strategy | ASYNCHRONOUS |
| audit_log_syslog_facility | LOG_USER |
| audit_log_syslog_ident | percona-audit |
| audit_log_syslog_priority | LOG_INFO |
+-----------------------------+---------------+
18 rows in set (0.01 sec)
mysql [localhost] {msandbox} ((none)) > set global general_log=ON;
Query OK, 0 rows affected (0.01 sec)
mysql [localhost] {msandbox} ((none)) > show variables like '%general_log%';
+------------------+---------------------------------------------------------------+
| Variable_name | Value |
+------------------+---------------------------------------------------------------+
| general_log | ON |
| general_log_file | /home/lalit/sandboxes/msb_5_7_25/data/lalit-ThinkPad-T480.log |
+------------------+---------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) > show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.01 sec)
mysql [localhost] {msandbox} ((none)) > set global max_connections=10;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} ((none)) > show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 10 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) > show status like 'max%connec%';
+---------------------------+---------------------+
| Variable_name | Value |
+---------------------------+---------------------+
| Max_used_connections | 7 |
| Max_used_connections_time | 2019-05-07 16:34:37 |
+---------------------------+---------------------+
2 rows in set (0.01 sec)
$ sysbench /usr/share/sysbench/oltp_insert.lua --mysql_storage_engine=innodb --table-size=1000 --tables=100 --mysql-db=test --mysql-user=msandbox --mysql-password=msandbox --mysql-socket=/tmp/mysql_sandbox5725.sock --threads=23 --time=900 --report-interval=1 --events=0 --db-driver=mysql prepare
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)
Initializing worker threads...
FATAL: unable to connect to MySQL server on socket '/tmp/mysql_sandbox5725.sock', aborting...
(last message repeated 1 times)
FATAL: error 1040: Too many connections
FATAL: unable to connect to MySQL server on socket '/tmp/mysql_sandbox5725.sock', aborting...
FATAL: error 1040: Too many connections
mysql error log:
Version: '5.7.25-28-log' socket: '/tmp/mysql_sandbox5725.sock' port: 5725 Percona Server (GPL), Release 28, Revision c335905
2019-05-07T11:11:43.453566Z 0 [Warning] Too many connections
2019-05-07T11:11:44.861565Z 0 [Warning] Too many connections
2019-05-07T11:11:44.862087Z 0 [Warning] Too many connections
mysql [localhost] {msandbox} ((none)) > show status like 'max%connec%';
+---------------------------+---------------------+
| Variable_name | Value |
+---------------------------+---------------------+
| Max_used_connections | 11 |
| Max_used_connections_time | 2019-05-07 16:41:43 |
+---------------------------+---------------------+
2 rows in set (0.00 sec)
Details
Details
Assignee
Reporter
Labels
Affects versions
Priority
HighSmart Checklist
Open Smart Checklist
Smart Checklist
Hello Team,
MySQL crashed few times due to maxed out connections. We are using 5.7.22-22-log Percona Server (GPL), Release 22, Revision f62d93c on CentOS release 6.10 (Final) 64 bit OS.
Please find the below reference from error log.
09:27:48 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/
key_buffer_size=268435456
read_buffer_size=2097152
max_used_connections=583
max_threads=1001
thread_count=512
connection_count=512
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4372306 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7f2bf805f810
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f30d8911d40 thread_stack 0x80000
/usr/sbin/mysqld(my_print_stacktrace+0x2c)[0xec60ec]
/usr/sbin/mysqld(handle_fatal_signal+0x461)[0x79e811]
/lib64/libpthread.so.0[0x347000f7e0]
/lib64/libc.so.6(memcpy+0xd2)[0x346fc897f2]
/usr/lib64/mysql/plugin/server_audit.so(auditing+0xb5b)[0x7f31c87aa97b]
/usr/lib64/mysql/plugin/server_audit.so(+0x95cb)[0x7f31c87ab5cb]
/usr/sbin/mysqld[0x79f00e]
/usr/sbin/mysqld(_Z18mysql_audit_notifyP3THD30mysql_event_general_subclass_tPKciS3_m+0x25d)[0x79f4dd]
/usr/sbin/mysqld(_ZN12Query_logger17general_log_writeEP3THD19enum_server_commandPKcm+0x67)[0xbe7947]
/usr/sbin/mysqld(_ZN12Query_logger17general_log_printEP3THD19enum_server_commandPKcz+0x15f)[0xbe7cff]
/usr/sbin/mysqld(_Z15mysql_change_dbP3THDRK25st_mysql_const_lex_stringb+0x377)[0xc66037]
/usr/sbin/mysqld(_Z16acl_authenticateP3THD19enum_server_commandb+0x1349)[0x7a9759]
/usr/sbin/mysqld[0xc60aa4]
/usr/sbin/mysqld(_Z22thd_prepare_connectionP3THDb+0x5a)[0xc6239a]
/usr/sbin/mysqld(handle_connection+0x281)[0xd6a411]
/usr/sbin/mysqld(pfs_spawn_thread+0x1b4)[0xeddd84]
/lib64/libpthread.so.0[0x3470007aa1]
/lib64/libc.so.6(clone+0x6d)[0x346fce8c4d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 73820
Status: NOT_KILLED
There was no error related to too many connections errors in the mysql error log. There was no core dump enabled in the server.