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