2

I have a dedicated server running multiple website accounts via WHM/cpanel.

The server has 64GB of RAM and 20 cores. It has been running well for years. I can't think of anything that changed recently.

Now one of the websites (a phpbb forum) has a lot of active MySQL connections (up to 200). I set a max_user_connections of 200 and a max_connections of 500 so that this won't impact my other website as bad. These forum queries are running absurdly slow. I restarted mysql and had no improvement.

For example, this query:

SELECT * FROM Rate WHERE RateDate < '2025-01-01' ORDER BY RateDate DESC LIMIT 1

Can take up to 11 seconds, even though RateDate column has a unique index. This is an InnoDB table only has 2500 rows. Here is the EXPLAIN:

EXPLAIN SELECT * FROM Rate WHERE RateDate < '2025-01-01' ORDER BY RateDate DESC LIMIT 1; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | Rate | NULL | range | RateDate | RateDate | 3 | NULL | 5387 | 100.00 | Using index condition; Backward index scan | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------------------------+

I ran it through the mysql profiler:

show profile for query 2; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000134 | | Executing hook on transaction | 0.000030 | | starting | 0.000016 | | checking permissions | 0.000015 | | Opening tables | 0.000047 | | init | 0.000015 | | System lock | 0.000016 | | optimizing | 0.000021 | | statistics | 0.000062 | | preparing | 0.000041 | | executing | 1.352365 | | end | 0.000079 | | query end | 0.000028 | | waiting for handler commit | 0.000019 | | closing tables | 0.000019 | | freeing items | 0.000074 | | cleaning up | 0.000017 | +--------------------------------+----------+

As you can see, "executing" seems to take forever. I have seen it take up to 11 seconds to execute this simple query.

Here is my mysqltuner output. Disclaimer: MySQLtuner recommends changing all of the MyISAM tables to InnoDB, but these specifically are massive data tables in my database that are in MyISAM. These are not used regularly and I have done lots of testing to confirm that MyISAM is faster for this specific use case. All of the other tables in my database are InnoDB.

https://pastebin.com/neQNNYpm

Here is "SHOW GLOBAL STATUS": https://pastebin.com/HyR1wzaJ

Here is "SHOW VARIABLES": https://pastebin.com/01y6HPby

Here is top command with swap turned on:

top - 13:35:09 up 186 days, 14:36, 1 user, load average: 1.98, 1.84, 1.90 Tasks: 478 total, 1 running, 477 sleeping, 0 stopped, 0 zombie %Cpu(s): 45.6 us, 0.8 sy, 0.7 ni, 52.5 id, 0.1 wa, 0.1 hi, 0.1 si, 0.0 st MiB Mem : 63794.1 total, 35467.7 free, 11822.3 used, 16504.0 buff/cache MiB Swap: 0.0 total, 0.0 free, 0.0 used. 48030.6 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND SWAP 2982653 mysql 20 0 54.0g 6.3g 44252 S 793.4 10.2 985:07.33 mysqld 0 3099903 tspcent+ 20 0 225084 25972 14296 S 9.2 0.0 0:00.28 lsphp 0 3092876 tspcent+ 20 0 223720 25532 16060 S 8.6 0.0 0:08.42 lsphp 0 3096094 tspcent+ 20 0 227816 29060 16184 S 8.6 0.0 0:04.76 lsphp 0 3014543 nobody 20 0 1032552 95872 4460 S 7.9 0.1 4:40.51 litespeed 0 3099245 tspcent+ 20 0 225652 28212 15332 S 7.6 0.0 0:00.69 lsphp 0 3093638 tspcent+ 20 0 234080 34372 16528 S 6.3 0.1 0:07.04 lsphp 0

Here is iostat:

Linux 4.18.0-553.16.1.lve.1.el8.x86_64 (server.tspcalc.com) 05/06/2025 _x86_64_ (20 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 4.88 0.14 0.59 0.14 0.00 94.25 Device tps kB_read/s kB_wrtn/s kB_read kB_wrtn nvme0n1 102.43 2413.65 2875.91 38916223702 46369458106 nvme1n1 95.75 2106.77 2875.91 33968383397 46369458106 md127 107.68 1170.93 2863.56 18879319621 46170361944 sda 0.29 0.17 0.15 2682684 2368612 md126 0.00 0.01 0.00 224385 550 loop0 4.55 410.08 2053.33 6611835688 33106753682
Improve this question

1 Answer 1

Reset to default
0
  • If there are other MyISAM queries running at the same time, they could block your MyISAM query. The point here is that InnoDB blocks less.
  • If mysqldump (or similar backup) was running, then it could be the cause.
  • The EXPLAIN says that it is doing the optimal thing.
  • I find PROFILE to be virtually useless.
  • Having thousands of tables is a burden on MySQL. (I am guessing that you are "manually partitioning" from "strategy_stats_2124_feb".) We can discuss alternatives in another Question -- if you provide more info on those tables and their queries.
  • Increasing table_open_cache may help with my previous point. What is its current value? How many tables do you have.
  • For digging deeper: https://mysql.rjweb.org/doc.php/mysql_analysis
  • Since you are using both MyISAM and InnoDB, we should discuss balancing key_buffer_size and innodb_buffer_pool_size. See https://mysql.rjweb.org/doc.php/memory
  • See also https://mysql.rjweb.org/doc.php/myisam2innodb
Improve this answer
4
  • Thank you for your answer. Commented May 7 at 0:40
  • I added a SHOW GLOBAL STATUS dump to my question. I am reading through your links now. Commented May 7 at 0:54
  • table_open_cache is currently set to 5000. I have 4017 tables. Commented May 7 at 1:00
  • 1
    @Matt That does not imply that this is handled efficiently. You may also want to check your ulimits specifically your max open files. Commented May 9 at 6:30

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.