0

We upgraded our MariaDB server from 10.2 to 10.5.23 and have found a significant CPU spike after this and almost most of the queries landing in the slow query logs. On investigation, we found that queries that were using the indexes on 10.2 were not getting used in 10.5 .

Ive read a lot of material today where it was told that it may have been using the right indexes in 10.2 but 10.5 might NOT be using the same and hence the issue, which I fail to understand as its a straight forward index.

For e.g. this is the query with explain on 10.2

MariaDB [client_db_17843]> explain SELECT p0_.ID AS ID0, c1_.Name AS Name1, c1_.Surname AS Surname2, c1_.CompanyName AS CompanyName3, c1_.MainContact AS MainContact4, c1_.TableType AS TableType5, p0_.ParentPropertyID AS ParentPropertyID6, p0_.AddressLine1 AS AddressLine17, p0_.AddressLine2 AS AddressLine28, p0_.Postcode AS Postcode9, c2_.EmailAddress AS EmailAddress10, c3_.TelephoneNumber AS TelephoneNumber11, c3_.CountryCode AS CountryCode12, c4_.TelephoneNumber AS TelephoneNumber13, c4_.CountryCode AS CountryCode14, c5_.TelephoneNumber AS TelephoneNumber15, c5_.CountryCode AS CountryCode16, s6_.Description AS Description17 FROM Properties p0_ INNER JOIN Contacts c1_ ON (c1_.TableType = 2 AND c1_.TablePKID = p0_.ID AND c1_.Deleted = 0) LEFT JOIN ContactsTelephones c3_ ON (c3_.ContactID = c1_.ID AND c3_.Deleted = 0 AND c3_.Settings_TypesOfTelephoneNumberID = 2) LEFT JOIN ContactsTelephones c4_ ON (c4_.ContactID = c1_.ID AND c4_.Deleted = 0 AND c4_.Settings_TypesOfTelephoneNumberID = 1) LEFT JOIN ContactsTelephones c5_ ON (c5_.ContactID = c1_.ID AND c5_.Deleted = 0 AND c5_.Settings_TypesOfTelephoneNumberID <> 1 AND c5_.Settings_TypesOfTelephoneNumberID <> 2) LEFT JOIN ContactsEmails c2_ ON (c2_.ContactID = c1_.ID AND c2_.Deleted = 0) LEFT JOIN Settings_Titles s6_ ON (s6_.ID = c1_.Settings_TitleID) WHERE (c1_.MainContact = 1 OR c1_.MainContact = 0) AND p0_.Deleted = 0 GROUP BY p0_.ID ORDER BY p0_.ID DESC LIMIT 10 OFFSET 0;
+------+-------------+-------+--------+---------------------------------------------------------------------+----------------------+---------+--------------------------------------+------+-------------+
| id   | select_type | table | type   | possible_keys                                                       | key                  | key_len | ref                                  | rows | Extra       |
+------+-------------+-------+--------+---------------------------------------------------------------------+----------------------+---------+--------------------------------------+------+-------------+
|    1 | SIMPLE      | c1_   | index  | IX_Contacts_idx                                                     | IX_Contacts_idx      | 10      | NULL                                 |   10 | Using where |
|    1 | SIMPLE      | p0_   | eq_ref | PRIMARY                                                             | PRIMARY              | 4       | client_db_17843.c1_.TablePKID        |    1 | Using where |
|    1 | SIMPLE      | c3_   | ref    | IDX_5FD82FF8CDD4564D,IDX_5FD82FF8A9B070B8,ix_Contactstelephones_idx | IDX_5FD82FF8CDD4564D | 5       | client_db_17843.c1_.ID               |    1 | Using where |
|    1 | SIMPLE      | c4_   | ref    | IDX_5FD82FF8CDD4564D,IDX_5FD82FF8A9B070B8,ix_Contactstelephones_idx | IDX_5FD82FF8CDD4564D | 5       | client_db_17843.c1_.ID               |    1 | Using where |
|    1 | SIMPLE      | c5_   | ref    | IDX_5FD82FF8CDD4564D,IDX_5FD82FF8A9B070B8,ix_Contactstelephones_idx | IDX_5FD82FF8CDD4564D | 5       | client_db_17843.c1_.ID               |    1 | Using where |
|    1 | SIMPLE      | c2_   | ref    | IDX_1EC6F1D5CDD4564D,ix_Contactsemails_idx                          | IDX_1EC6F1D5CDD4564D | 5       | client_db_17843.c1_.ID               |    1 | Using where |
|    1 | SIMPLE      | s6_   | eq_ref | PRIMARY                                                             | PRIMARY              | 4       | client_db_17843.c1_.Settings_TitleID |    1 | Using where |
+------+-------------+-------+--------+---------------------------------------------------------------------+----------------------+---------+--------------------------------------+------+-------------+
7 rows in set (0.00 sec)

The same on 10.5 does this

MariaDB [client_db_17843]> explain SELECT p0_.ID AS ID0, c1_.Name AS Name1, c1_.Surname AS Surname2, c1_.CompanyName AS CompanyName3, c1_.MainContact AS MainContact4, c1_.TableType AS TableType5, p0_.ParentPropertyID AS ParentPropertyID6, p0_.AddressLine1 AS AddressLine17, p0_.AddressLine2 AS AddressLine28, p0_.Postcode AS Postcode9, c2_.EmailAddress AS EmailAddress10, c3_.TelephoneNumber AS TelephoneNumber11, c3_.CountryCode AS CountryCode12, c4_.TelephoneNumber AS TelephoneNumber13, c4_.CountryCode AS CountryCode14, c5_.TelephoneNumber AS TelephoneNumber15, c5_.CountryCode AS CountryCode16, s6_.Description AS Description17 FROM Properties p0_ INNER JOIN Contacts c1_ ON (c1_.TableType = 2 AND c1_.TablePKID = p0_.ID AND c1_.Deleted = 0) LEFT JOIN ContactsTelephones c3_ ON (c3_.ContactID = c1_.ID AND c3_.Deleted = 0 AND c3_.Settings_TypesOfTelephoneNumberID = 2) LEFT JOIN ContactsTelephones c4_ ON (c4_.ContactID = c1_.ID AND c4_.Deleted = 0 AND c4_.Settings_TypesOfTelephoneNumberID = 1) LEFT JOIN ContactsTelephones c5_ ON (c5_.ContactID = c1_.ID AND c5_.Deleted = 0 AND c5_.Settings_TypesOfTelephoneNumberID <> 1 AND c5_.Settings_TypesOfTelephoneNumberID <> 2) LEFT JOIN ContactsEmails c2_ ON (c2_.ContactID = c1_.ID AND c2_.Deleted = 0) LEFT JOIN Settings_Titles s6_ ON (s6_.ID = c1_.Settings_TitleID) WHERE (c1_.MainContact = 1 OR c1_.MainContact = 0) AND p0_.Deleted = 0 GROUP BY p0_.ID ORDER BY p0_.ID DESC LIMIT 10 OFFSET 0; 
+------+-------------+-------+------------+---------------------------------------------------------------------+-------------------------------------------+---------+-------------------------------+--------+-------------------------------------------------+
| id   | select_type | table | type       | possible_keys                                                       | key                                       | key_len | ref                           | rows   | Extra                                           |
+------+-------------+-------+------------+---------------------------------------------------------------------+-------------------------------------------+---------+-------------------------------+--------+-------------------------------------------------+
|    1 | SIMPLE      | c1_   | ALL        | IX_Contacts_idx                                                     | NULL                                      | NULL    | NULL                          | 159293 | Using where; Using temporary; Using filesort    |
|    1 | SIMPLE      | p0_   | eq_ref     | PRIMARY                                                             | PRIMARY                                   | 4       | client_db_17843.c1_.TablePKID | 1      | Using where                                     |
|    1 | SIMPLE      | c3_   | ref        | IDX_5FD82FF8CDD4564D,IDX_5FD82FF8A9B070B8,ix_Contactstelephones_idx | IDX_5FD82FF8CDD4564D                      | 5       | client_db_17843.c1_.ID        | 1      | Using where                                     |
|    1 | SIMPLE      | c4_   | ref        | IDX_5FD82FF8CDD4564D,IDX_5FD82FF8A9B070B8,ix_Contactstelephones_idx | IDX_5FD82FF8CDD4564D                      | 5       | client_db_17843.c1_.ID        | 1      | Using where                                     |
|    1 | SIMPLE      | c5_   | ref|filter | IDX_5FD82FF8CDD4564D,IDX_5FD82FF8A9B070B8,ix_Contactstelephones_idx | IDX_5FD82FF8CDD4564D|IDX_5FD82FF8A9B070B8 | 5|5     | client_db_17843.c1_.ID        | 1 (0%) | Using where; Using rowid filter                 |
|    1 | SIMPLE      | c2_   | ref        | IDX_1EC6F1D5CDD4564D,ix_Contactsemails_idx                          | IDX_1EC6F1D5CDD4564D                      | 5       | client_db_17843.c1_.ID        | 1      | Using where                                     |
|    1 | SIMPLE      | s6_   | ALL        | PRIMARY                                                             | NULL                                      | NULL    | NULL                          | 11     | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------------+---------------------------------------------------------------------+-------------------------------------------+---------+-------------------------------+--------+-------------------------------------------------+
7 rows in set (0.001 sec)

Its doing a full table scan on the Contacts table even though the index that it suggested IX_Contacts_idx does have the right columns that are in the ON condition in the query.

We have read through a few bits of documentation but unsure if there are any mariadb optimizer specific hints that we are missing on 10.5.x.

Any pointers will be greatly appreciated as we upgraded it and its been very bad post the upgrade.

Operation System: Rocky Linux 8.8

MariaDB Version: 10.5.23

CPU: 52 core (WE have increased this from 20 core this morning since the box was getting pegged a lot and had to increase to sustain the load)

Memory: 64GB

All tables are INNODB

innodb_params:

MariaDB [client_db_17843]> show variables like 'innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 19327352832    |
+-------------------------------------+----------------+
10 rows in set (0.000 sec)

Optimizer trace for the above is: https://pastebin.com/NtJHf9nw

PS: This is just one query that is slow, Overall, the performance has gone down so bad that we have more than 20x slow queries than what we had in mariadb 10.2. I just got the above as an example for investigation.

Thanks

Raja

Improve this question

1 Answer 1

Reset to default
0

Optimizer Presumably, there were Optimizer changes between the two versions. Rather than chase that issue, let me make some attempt at improving the processing. These may avoid the Optimizer issues.

GROUP_CONCAT Suggestion: Instead of multiple columns for phone numbers, combine them

c3_.TelephoneNumber AS TelephoneNumber11, c3_.CountryCode AS CountryCode12, etc
...
LEFT JOIN c3 ... etc

-->

( SELECT GROUP_CONCAT(
             CONCAT(tn.CountryCode, '+', tn.TelephoneNumber)
             SEPARATOR ', ')
      FROM ContactsTelephones
      WHERE Deleted = 0
        AND ContactID = c1_.ID
    ) AS phone_numbers

Find the 10 first

FROM ( SELECT ID FROM Properties ORDER BY p.ID DESC LIMIT 10 ) AS p
JOIN Properties AS p0_
JOIN ...  (other stuff)
ORDER BY p.ID DESC

Alas the test on both c1_ and p0_ may make this technique tricky to do.

Indexes Some of these may be useful:

Properties:  INDEX(Deleted, ID)
Contacts:  INDEX(MainContact, TableType)
ContactsEmails:  INDEX(ContactID, Deleted,  EmailAddress)
ContactsTelephones:  INDEX(ContactID,  TelephoneNumber, CountryCode, Deleted, Settings_TypesOfTelephoneNumberID)

Difficult This essentially cannot be optimized:

(c1_.MainContact = 1  OR  c1_.MainContact = 0)
Improve this answer
3
  • Thanks for the response. Since this is just one query of the many that we run that have become extremely slow, I was trying to see if there could be a bug or a setting that we were missing during the upgrade. Also, we are at a juncture where rolling back is a better option as it would take time to optimize those queries (similar to what you are suggesting) before we try the upgrade again. Are there any pages that we can look at to see what optimizer changes could have happened between the releases?
    – rajasaur
    Nov 21 at 3:12
  • @rajasaur - At least see if the indexes help.
    – Rick James
    Nov 21 at 6:28
  • Sure, will do and post the results
    – rajasaur
    Nov 21 at 10:08

Your Answer

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

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