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



  • 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



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2