Skip to main content

Mysql large number of running threads, causing hangs

Chirag Aggarwal
Hello, I'm trying to figure out a weird mysql issue. We have a cpanel server running mysql 8.0.30 which works fine but every once or twice a day, mysql becomes unresponsive or painfully slow. RAM, SWAP, IO usage increases but CPU is fine. Upon checking, # mysqladmin status Uptime: 27795 Threads: 751 Questions: 32905673 Slow queries: 15 Opens: 2708499 Flush tables: 3 Open tables: 4000 Queries per second avg: 1183.870 | Connections | 456613 | | Threads_cached | 10 | | Threads_connected | 705 | | Threads_created | 20043 | | Threads_running | 687 |
So, Threads_running is usually around 5-10 but it keeps building up and becomes unresponsive at around 1500. There is no single abusive user in processlist and seems pretty normal: I monitored a few threads for sometime and even after few minutes they are sometimes in processlist with same or other Query. I guess it's because mysql is very slowly responding to php and thus connection is kept alive. There are hardly any sleeping query. Also, server is running Cloudlinux Governor along with it, I've tried disabling it and changing my.cnf configs. Cloudflare blocked me from pasting process list and config file. Please check it here:

Comments

7 comments

Date Votes
  • cPRex Jurassic Moderator
    Hey there! From that output, my concerns would be the number of queries per second and if your server can handle that, and the number of open tables. Can you run this on the command line and paste the output here? mysql -e "show variables like '%open%';"
    0
  • Chirag Aggarwal
    Hey there! From that output, my concerns would be the number of queries per second and if your server can handle that, and the number of open tables. Can you run this on the command line and paste the output here? mysql -e "show variables like '%open%';"

    # mysql -e "show variables like '%open%';" +----------------------------+---------+ | Variable_name | Value | +----------------------------+---------+ | have_openssl | YES | | innodb_open_files | 4000 | | open_files_limit | 1048576 | | table_open_cache | 4000 | | table_open_cache_instances | 16 | +----------------------------+---------+
    Server has these queries avg during regular time period as well and it works. Specs are pretty good AMD EPYC 7302P with 128GB ram.
    0
  • cPRex Jurassic Moderator
    I'm just wondering if you're hitting that open tables limit and then that's when the slowness happens.
    0
  • Chirag Aggarwal
    I'm just wondering if you're hitting that open tables limit and then that's when the slowness happens.

    Now that I think about it, there were errors related to mysql hitting these limits some weeks ago but we set it to 1048576 which should be lot more than required. Normal usage is only around 2000. Error hasn't reappeared since in logs. Do you think I should still try increasing it further? Also, I tried that before and it threw this error: 2022-08-05T11:52:37.122007Z 0 [Warning] [MY-010140] [Server] Could not increase number of max_open_files to more than 1048576 (request: 9999999)
    # grep -i limit /etc/systemd/system/mysql.service # but not limited to OpenSSL) that is licensed under separate terms, # Sets open_files_limit LimitNOFILE = 10000
    0
  • cPRex Jurassic Moderator
    I mean, I wouldn't go too crazy, but 10000 seems reasonable.
    0
  • Chirag Aggarwal
    I mean, I wouldn't go too crazy, but 10000 seems reasonable.

    I guess my.cnf is overriding that to 1048576. So it shouldn't be the cause of problem right? Anything else I can check?
    0
  • cPRex Jurassic Moderator
    That was the open files limit, and the 4000 value was the open tables, which is the "table_open_cache" value from that earlier command.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post