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