3

I've been running a somewhat busy database (~800Mb) in GCP SQL. Due to the cost of it I've been working on migrating to a custom server running MariaDB. Which I'd heard could be much more cost effective. (I've been using this setup locally via nitro for development and I've not had any issues working with the same data. So at least the data seems to have no issue living in a MariaDB environment).

Now, running this setup in actual servers seems to be choking on the database and I can't figure out why. Here are the symptoms:

  • Pages never load after actual data attempts to be loaded. Meaning that if I go to the login screen, it comes up almost right away, once I log in, it never ends loading.
  • Any other page doesn't seem to finish (past 15min)
  • Server's CPUs run at 100% (I've attempted using up to 8 cores, of course only one gets maxed at a time depending on how many connections I trigger).
  • Memory rarely goes above 1.5Gb
  • Server does not report any errors (even though server is running on developer mode) probably because the page never actually errors, it just never finishes working.

I've tried:

  • Bumping up to higher server specs, no luck
  • Clearing/Flushing caches and queue from cli (I mention the queue because I saw a lot of image transformation requests at one point)
  • Checked craft logs as well as server logs. No particular info seems to be getting logged during these loads

Specs

  • CraftCMS 3.7.19.1
  • PHP 7.4 on Ubuntu via Apache (from scratch)
  • MariaDB 10.7 (from Bitnami image)

Any help or pointers on how to troubleshoot what's going on are welcome. I'm not sure what to look for. :\

Thanks all!

Improve this question
2
  • 1
    Have you checked the slow query log to find out which queries are not completing?
    – Matthias Redl-Mann
    Commented May 1, 2022 at 19:40
  • I didn't know that was a thing... Thanks for the tip! @MatthiasRedl-Mann
    – cballenar
    Commented May 2, 2022 at 0:20

1 Answer 1

Reset to default
7

You're likely running into an issue seen with recent versions of MariaDB (10.6 and later), where importing an SQL dump can result in indexes not being created properly. Essentially, this will make your queries super slow, which will manifest as the behaviour you describe.

The fix is to make MariaDB rebuild all the indexes. This command should do that:

mysqlcheck -u yourUser -p "yourPassword" -o --all-databases

Alternatively, running OPTIMIZE on all tables should also do it.

Improve this answer
4
  • 2
    Thank you!!! That did it. All tables reported they couldn't be optimized but the rebuild process seems to have cleared up the issue nonetheless.
    – cballenar
    Commented May 2, 2022 at 0:19
  • This saves my skin, thank you!
    – MrCarrot
    Commented Oct 6, 2022 at 7:44
  • 1
    @cballenar Yeah, most Craft tables use InnoDB, which doesn't support OPTIMIZE – but running it will make MariaDb rebuild the indexes, which is exactly what you want in this case :)
    – Mats Mikkel Rummelhoff
    Commented Oct 26, 2022 at 22:41
  • I had this issue after importing a Craft CMS 4 dump from MySQL 8 to MySQL 8 - no MariaDB involved. (Thanks!)
    – David Oliver
    Commented Jun 12, 2023 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.