Buy Percona ServicesBuy Now!

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    I have a problem with this query:
    Code:
        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)
    Code:
    +----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+
    | 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)
    Code:
    +----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+
    | 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:
    Code:
    +-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | 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:
    Code:
    +-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | 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?
    Last edited by sal00m; 03-31-2016, 03:31 AM. Reason: mysql percona server 5.6
Working...
X