Answers.Body: <p>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.</p>
  <p>The fix is to make MariaDB rebuild all the indexes. This command should do that:</p>
  <p><code>mysqlcheck -u yourUser -p &quot;yourPassword&quot; -o --all-databases</code></p>
  <p>Alternatively, running <code>OPTIMIZE</code> on all tables should also do it.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-05-02T00:19:11.827Z
Answers.Text: 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.
Answers.UserDisplayName: cballenar
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-10-06T07:44:48.147Z
Answers.Text: This saves my skin, thank you!
Answers.UserDisplayName: MrCarrot
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-10-26T22:41:20.303Z
Answers.Text: @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 :)
Answers.UserDisplayName: Mats Mikkel Rummelhoff
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-12T19:47:23.730Z
Answers.Text: I had this issue after importing a Craft CMS 4 dump from MySQL 8 to MySQL 8 - no MariaDB involved. (Thanks!)
Answers.UserDisplayName: David Oliver
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-05-01T19:45:06.250Z
Answers.LastActivityDate: 2022-05-01T19:53:10.853Z
Answers.LastEditDate: 2022-05-01T19:53:10.853Z
Answers.UserDisplayName: Mats Mikkel Rummelhoff
Body: <p>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).</p>
  <p>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:</p>
  <ul>
  <li>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.</li>
  <li>Any other page doesn't seem to finish (past 15min)</li>
  <li>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).</li>
  <li>Memory rarely goes above 1.5Gb</li>
  <li>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.</li>
  </ul>
  <p>I've tried:</p>
  <ul>
  <li>Bumping up to higher server specs, no luck</li>
  <li>Clearing/Flushing caches and queue from cli (I mention the queue because I saw a lot of image transformation requests at one point)</li>
  <li>Checked craft logs as well as server logs. No particular info seems to be getting logged during these loads</li>
  </ul>
  <h2>Specs</h2>
  <ul>
  <li>CraftCMS 3.7.19.1</li>
  <li>PHP 7.4 on Ubuntu via Apache (from scratch)</li>
  <li>MariaDB 10.7 (from Bitnami image)</li>
  </ul>
  <p>Any help or pointers on how to troubleshoot what's going on are welcome. I'm not sure what to look for. :\</p>
  <p>Thanks all!</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-05-01T19:40:07.460Z
Comments.Text: Have you checked the slow query log to find out which queries are not completing?
Comments.UserDisplayName: Matthias Redl-Mann
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-05-02T00:20:18.250Z
Comments.Text: I didn't know that was a thing... Thanks for the tip! @MatthiasRedl-Mann
Comments.UserDisplayName: cballenar
Tags: <database><migration><mariadb>
Title: DB CPU running at 100%, Pages Never Load, Moving from MySQL to MariaDB
ThreadText: # DB CPU running at 100%, Pages Never Load, Moving from MySQL to MariaDB
  
  (Asked by: username_0 on 2022-05-01)
  
  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!
  
  **Tags:** database, migration, mariadb
  
  > **Comments:**
  > **username_1**: Have you checked the slow query log to find out which queries are not completing?  
  > **username_0**: I didn't know that was a thing... Thanks for the tip! @username_1  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2022-05-01. Score: 7 | Accepted answer
  
  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.
  
  > **Comments:**
  > **username_0**: 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.  
  > **username_3**: This saves my skin, thank you!  
  > **username_2**: @username_0 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 :)  
  > **username_4**: I had this issue after importing a Craft CMS 4 dump from MySQL 8 to MySQL 8 - no MariaDB involved. (Thanks!)  
  
  ---
  
  