Sample Header Ad - 728x90

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