Slow DB on fresh install

Question

On a new installation Craft CMS 3.7.34, with no sections and no plugins, it takes from 10 to 50 seconds to get the dashboard. And a similar time to get the default index page that says, "Welcome. Thanks for installing Craft."

This is on shared hosting, and their tech support says it's a problem with the database, but they don't think it's their issue.

Looking at web.log, I see this query, which possibly is taking 19 seconds.

2022-02-25 07:43:06 [-][1][-][profile end][yii\db\Command::query] SELECT `s`.`id`, `s`.`name`, `s`.`handle`, `s`.`language`, `s`.`primary`, `s`.`hasUrls`, `s`.`baseUrl`, `s`.`sortOrder`, `s`.`uid`, `s`.`dateCreated`, `s`.`dateUpdated`, `s`.`groupId`, `s`.`enabled`
FROM `sites` `s`
INNER JOIN `sitegroups` `sg` ON `sg`.`id` = `s`.`groupId`
WHERE (`s`.`dateDeleted` IS NULL) AND (`sg`.`dateDeleted` IS NULL)
ORDER BY `sg`.`name`, `s`.`sortOrder`
2022-02-25 07:43:25 [-][1][-][info][yii\web\Session::open] Session started

Following the advice from https://craftcms.com/knowledge-base/troubleshooting-performance-issues, if I run that query in PHPMyAdmin, it takes .0006 seconds.

Any thoughts where to go from here?

Their support says:

It seems like the issue is related to your database. I started monitoring the website using x-ray tool then opened the website in browser and printed MySQL processes related to your account. The results are the following According to the browser full load time is almost 29 seconds
https://paste.websitehostserver.net/Z3Jvb3QK_2022-02-19_23-52-04_NXbZrpgHpT

As I mentioned above, I was manually checking MySQL processes from time to time, please take a look at the screenshot below
https://paste.websitehostserver.net/text_2022-02-19_23-54-46_Mcn9EVmmdO

As you can see there were two queries that took, roughly speaking, 26 seconds. However, please note since I was checking them manually I might have missed something.

And here are the results of x-ray tool related to my attempt to check the website
https://paste.websitehostserver.net/Z3Jvb3QK_2022-02-20_00-01-00_UdYX9cmDiC
https://paste.websitehostserver.net/Z3Jvb3QK_2022-02-20_00-01-29_cf65oq0rLL

---

Answer 1

Sounds like you're running into this: https://github.com/craftcms/cms/issues/4037

Which is an issue in shared hosting environments where you're on a MySQL database server that contains a lot of other databases.

Some workarounds in that issue.

Answer 2

Occasionally when I've run into performance issues with MySQL for no discernible reason, I run the mysqlcheck optimize command to rebuild the tables and indexes, and it all just magically works again:

mysqlcheck -u <your mysql user> -p<your mysql password> -o --all-databases