Not the answer you need?
Register and ask your own question!

Different execution plan and time for two nodes in same Percona Server

sal00msal00m EntrantCurrent User Role Participant
I have a problem with this query:
    SELECT
        uca.user_activity_id,
        uca.user_call_id,
        uca.call_activity_id,
        uca.user_activity_token,
        uc.call_group_id,
        uc.user_id
    FROM users_calls_activities uca
    INNER JOIN users_calls_activities uca2 ON uca2.user_activity_id = uca.user_activity_is_validated_with
        AND aux.user_call_id = 1744136
    INNER JOIN users_calls uc ON uc.user_call_id = uca.user_call_id;

We have a cluster with percona server (5.6.29) with 5 nodes(from 0 to 4) in Azure. The difference between nodes 0-3 and 4 is that, the first ones are in a balancer and the node 4 is out of the balancer (but in the cluster)

The problem is that in four of the servers (nodes 0-3) the query is really slow (15 sec) and in the other one (node 4)the query is really fast (0,002)

Afaik, the explain plan should be the same but i execute an `EXPLAIN` and the result is this:

Nodes 0-3 (Slow)
+----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+
| id | select_type | table | type | possible_keys                                               | key          | key_len | ref                           | rows    | Extra                                 |
+----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+
|  1 | SIMPLE      | uca2  | ref  | PRIMARY,user_call_id,user_call_id_2                         | user_call_id | 4       | const                         |       1 | Using index                           |
|  1 | SIMPLE      | uc    | ALL  | PRIMARY,user_call_id                                        | NULL         | NULL    | NULL                          | 2098152 | Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | uca   | ref  | user_call_id,user_call_id_2,is_validated_with               | user_call_id | 4       | db.uc.user_call_id            |       1 | Using where                           |
+----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+
Node 4 (Fast)
+----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+
| id | select_type | table | type   | possible_keys                                               | key                             | key_len | ref                               | rows    | Extra                 |
+----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+
|  1 | SIMPLE      | uca2  | ref    | PRIMARY,user_call_id,user_call_id_2                         | user_call_id                    | 4       | const                             |       1 | Using index           |
|  1 | SIMPLE      | uca   | ref    | user_call_id,user_call_id_2,is_validated_with               | is_validated_with               | 5       | db.uc2.user_activity_id           | 2755595 | Using index condition |
|  1 | SIMPLE      | uc    | eq_ref | PRIMARY,user_call_id                                        | PRIMARY                         | 4       | db.uca.user_call_id               |       1 | NULL                  |
+----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+

I notice that in the slow one the index is not being used. so i check the indexes:

Node 0:
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name             | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users_calls     |          0 | PRIMARY              |            1 | user_call_id         | A         |     2099153 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | call_group_id        |            1 | call_group_id        | A         |       16659 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | user_call_begin_date |            1 | user_call_begin_date | A         |     1049576 |     NULL | NULL   | YES  | BTREE      |         |               |
| users_calls     |          1 | user_call_begin_date |            2 | user_call_end_date   | A         |     2099153 |     NULL | NULL   | YES  | BTREE      |         |               |
| users_calls     |          1 | user_call_id         |            1 | user_call_id         | A         |     2099153 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | user_call_id         |            2 | user_id              | A         |     2099153 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | user_id              |            1 | user_id              | A         |       91267 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | user_id              |            2 | call_id              | A         |     2099153 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | user_id              |            3 | user_call_status     | A         |     2099153 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | fk_users_calls_calls |            1 | call_id              | A         |       23067 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Node 4:
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name             | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users_calls     |          0 | PRIMARY              |            1 | user_call_id         | A         |     2091476 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | call_group_id        |            1 | call_group_id        | A         |       26813 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | user_call_begin_date |            1 | user_call_begin_date | A         |     1045738 |     NULL | NULL   | YES  | BTREE      |         |               |
| users_calls     |          1 | user_call_begin_date |            2 | user_call_end_date   | A         |     2091476 |     NULL | NULL   | YES  | BTREE      |         |               |
| users_calls     |          1 | user_call_id         |            1 | user_call_id         | A         |     2091476 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | user_call_id         |            2 | user_id              | A         |     2091476 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | user_id              |            1 | user_id              | A         |       53627 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | user_id              |            2 | call_id              | A         |     2091476 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | user_id              |            3 | user_call_status     | A         |     2091476 |     NULL | NULL   |      | BTREE      |         |               |
| users_calls     |          1 | fk_users_calls_calls |            1 | call_id              | A         |       15608 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
My first question is why are the indexes different? it should be the same due to both are in the same cluster

Why the execute plans are different?

Why the query is using the index in one of the nodes and not in the other ones?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.