mysqld thread count exceeds max_connections
1
vote
0
answers
153
views
As the question title suggests: I have set
max_connections = 2
in my my.cnf
file, but when I activate my mysql daemon, the thread count sits at 37. I am searching online but cannot find indication my expectations are wrong. Am I understanding the max_connections
directive correctly ? Can anyone suggest a reason as to why this may not be limiting the thread count ?
Attempts at a solution
---------------------
1) I query mysql global variables via the mysql CLI client:
| Variable_name | Value |
+----------------------------+----------------------+
| max_allowed_packet | 67108864 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_connect_errors | 100 |
| max_connections | 2 |
| max_delayed_threads | 20 |
| max_digest_length | 1024 |
| max_error_count | 1024 |
| max_execution_time | 0 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 4096 |
| max_points_in_geometry | 65536 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_user_connections | 2 |
| max_write_lock_count | 18446744073709551615 |
This confirms the my.cnf
file is being correctly loaded as the max_connections
variable is indeed set to 2.
2) As is visible from the output in (1), I have attempted limiting the max_user_connections
variable also, but again, no luck.
3) I killed other server processes that I suspected could be querying the mysql daemon - httpd, php-fpm, but this has not reduced the number of threads.
System Specs
------------
My MySQL version is 8.3.0, and is a minimal install:
mysql-8.3.0-linux-glibc2.17-x86_64-minimal
Update
------
Running the query SHOW PROCESSLIST
returns:
*************************** 1. row ***************************
Id: 5
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 146457
State: Waiting on empty queue
Info: NULL
*************************** 2. row ***************************
Id: 11
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: SHOW PROCESSLIST
2 rows in set, 1 warning (0.00 sec)
I found more helpful running SELECT * FROM performance_schema_threads\G
, which returned information about many tasks mysqld is executing. I had a suspicion this list of tasks would correspond to the number of threads and running:
SELECT COUNT(*) FROM performance_schema.threads\G
Returns:
38
Which nicely corresponds to the number of threads - I suspect the extra task corresponds to the master process. In the terminal:
pstree | grep sql
|-mysqld---37*[{mysqld}
Reading through the task names returned by performance_schema.threads
, I gather these threads are necessary for MySQL to function as their names are io_read_thread
,io_write_thread
,log_writer_thread
and similar.
Setting max_connections
works as expected - setting max_user_connections=2
and attempting to open a third client as root
returns:
ERROR 1203 (42000): User root already has more than 'max_user_connections' active connections
However, if max_connections
is set to 2
, while max_user_connections
is at 6
, I can have 3 mysql clients open at once with user root. When I try and open a fourth client as root, I receive:
ERROR 1040 (HY000): Too many connections
How does a limit of three client sessions under one user arise from my.cnf
limits of:
max_connections = 2
max_user_connections = 6
thread_cache_size = 2
?
I am continuing to read up MySQL documentation but am yet to find an explanation. If someone more experienced can offer clarity, I would be greatly appreciative. How do these settings affect connection (and so thread) number ?
Asked by user10709800
(73 rep)
Apr 5, 2024, 09:59 AM
Last activity: Apr 6, 2024, 02:42 PM
Last activity: Apr 6, 2024, 02:42 PM