Why does MySQL server queries freeze on high load of connections?

Question

Hey there I have MySQL server with the next specs: x2 Intel Xeon XP Gold (64 cores total) 256GB of Ram 1Tb of SSD Nvme on Ubuntu 20.04

The mysql is configured with the next settings:

[mysqld]
port=3306

max_allowed_packet=1G
sort_buffer_size=256K
net_buffer_length=256K
read_buffer_size=256K
join_buffer_size=256K
read_rnd_buffer_size=256K
myisam_sort_buffer_size=256K
max_connections = 500000
bind-address="0.0.0.0"
default-time-zone = "+02:00"
innodb_buffer_pool_size=128G
innodb_buffer_pool_instances = 32
innodb_log_file_size=16G
innodb_log_buffer_size=100M
skip-name-resolve=1
innodb_lock_wait_timeout=5
innodb_thread_concurrency       = 64
wait_timeout=5
max_user_connections=500000
open-files-limit=500000

## UTF 8 Settings
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
log_bin_trust_function_creators=1

character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

The problem that happens is, when I start my 10k node js instances, (each instance is connecting to the database) all the queries that have been sent are built up and nothing executes for long time, like mysql is trying to handle them all at the same time instead of resolving the first ones and continue , the result is having 10,000 simple queries as select,update(by indexed id) to hang there for some minutes

How should I tune my Mysql server so it will handle better the situation?

Another note, is if I start those 10k scripts, by 1000 at a time its somehow operates much better and not hangs

Answer 1

max_connections = 500000

No! That the max simultaneous connections. Recommend 1000 until you can demonstrate a need for more. Also lower max_user_connections.

10k node js instances

If that doesn't choke MySQL, it will choke the webserver.

If this is a "stress test", then dial it back. When you find how big a burst you can handle, then ask how you will get that many jobs across the network, through only 64 cores, into the web server's queue, etc.

A more realistic test is to crank it up from, say 100, until things saturate and it all goes to hell in a handbasket.

nothing executes for a long time

MySQL's logic is to give each connection a fair share. The result is that all connections take "forever" to finish.

Would you send 10K people into a grocery store all at the same time? Space them out; then measure "completions per second".

Another tip. It is best to throttle the thundering herd at the webserver.

by 1000 at a time it somehow operates much better and does not hangs

I see you have figure out what I just said.