0

Have been monitoring mysql server for year, I notice the mysql database max_used_connections cannot get pass 4832. Once hitting 4832, MySQL begins to show slowness, while the loads/cpu usage/memory are under moderate usage. The max_connections is set to 6000 (SHOW VARIABLES LIKE "max_connections"). Running Ubuntu 20.04, Mysql5.7.33 Below are configuration I modified:

mysql.conf

max_connections = 6000 # have tried other values like 10000
open_files_limit = 1048576

Modifying /etc/systemd/system/mysql.service

LimitNOFILE=infinity 
LimitMEMLOCK=infinity

/etc/security/limits.conf

*    soft nofile 1048576
*    hard nofile 1048576
*    soft nproc  1084576
mysql   soft    nproc   1084576
mysql   hard    nproc   1084576
mysql   soft    nofile   1084576
mysql   hard    nofile   1084576

Ulimit

ulimit -n; // 1048576
ulimit -u; // 257436

Previously the server is 12 core 48GB memory, hitting 4832. I upgraded to 16 core 64GB memory, max_used_connections still hitting 4832 max. It is always hitting 4832 and no further. Any ideas?

Improve this question
6
  • mysql.conf is this the exact filename, my{sql}.cnf is usually the extension used. Can you share a show global status when the limit is reached. Does the slowdown occur regardless of what the connections are doing (e.g. select sleep(600))?
    – danblack
    Jun 25, 2021 at 5:55
  • I will share again if it happens. The slowdown only occurs when 4832 limit is hit. The variable query from mysql, max_connections is already 6000 or any value I modified.
    – neobie
    Jun 25, 2021 at 6:57
  • the max connection limit iws not only a mysql problem, there are also operation system values that influence it.
    – nbk
    Jun 25, 2021 at 8:15
  • @nbk but where else ?
    – neobie
    Jun 25, 2021 at 17:51
  • that is something for ubuntu specialists, so try your luck there, but i believe there are a lot of qustions arpund that
    – nbk
    Jun 25, 2021 at 18:03

1 Answer 1

Reset to default
0

Sounds like the system started swapping. That is terrible for performance.

Almost no MySQL system really needs more than 1000 connections. Before addressing that, what to you get from this (when it hits 4823)?

SHOW GLOBAL STATUS LIKE 'Threads_running';

I need some more info... RAM size, values of innodb_buffer_pool_size, max_connections. Is this a web app? Or are the clients something else?

A typical problem comes from the clients (eg, web server children) being unnecessarily numerous. I suggest this is the first thing to tackle. Lower the "max_children" (or whatever).

If Threads_running is more than 20, then you probably have some slow queries that need optimizing. Set long_query_time = 1 and turn on the slowlog. After some time, use pt-query-digest to analyze the slowlog for naughty queries.

More: http://mysql.rjweb.org/doc.php/mysql_analysis

Improve this answer
2
  • Threads_running is 10. I am still identifying what could cause the problem (eg malicious scraping). However, no matter how is my settings, it always hit 4832 only. Is this the limit of MySQL ? max_connections = 6000 innodb_buffer_pool_size=24G
    – neobie
    Oct 6, 2021 at 11:07
  • @neobie - Nothing magical about 4832. Set max_connections lower than that; see what happens.
    – Rick James
    Oct 6, 2021 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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