Mariadb Galera Cluster Multi-Master Slow execution of queries

Question

I have a problem with Galera multi-master. When I run query(SELECT) in node1 is executed for 0.025 sec , but on node2 same query is executed for 5.172 sec and in node3 result is 4.347 sec.

Node1 config :

[mysqld]

log_error=/var/log/mariadb.log

[galera]

wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.2.47,192.168.2.48,192.168.2.49,172.29.44.11"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_cluster_name="CCCluster"
bind-address=0.0.0.0
wsrep_node_address="192.168.2.47"
wsrep_node_name=”node1"
wsrep_sst_method=rsync

Node2 config :

[mysqld]

log_error=/var/log/mariadb.log

[galera]

wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.2.47,192.168.2.48,192.168.2.49,172.29.44.11"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_cluster_name="CCCluster"
bind-address=0.0.0.0
wsrep_node_address="192.168.2.48"
wsrep_node_name="node2"
wsrep_sst_method=rsync

Node3 config:

[mysqld]

log_error=/var/log/mariadb.log

[galera]

wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.2.47,192.168.2.48,192.168.2.49,172.29.44.11"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_cluster_name="CCCluster"
bind-address=0.0.0.0
wsrep_node_address="192.168.2.49"
wsrep_node_name="node1"
wsrep_sst_method=rsync

The query I'am execute on nodes is:

select  `users`.*, `upd`.`first_name`, `upd`.`middle_name`, `upd`.`last_name`,
        CONCAT_WS(upd.first_name, upd.middle_name, upd.last_name) AS full_name
    from  `users`
    inner join  `users_personal_data` as `upd`  ON `upd`.`user_id` = `users`.`id`
    order by  `id` desc
    limit  20 offset 0;

Where does this problem come from?

Please get `EXPLAIN SELECT ...` on one of the slow nodes. It may give us a clue. Also `SHOW CREATE TABLE`. — Rick James, Dec 18 '18 at 21:12

score 0 · Answer 1 · answered Dec 19 '18 at 09:33

Result from EXPLAIN SELECT is :

+------+-------------+-------+------+-------------------------------------+------+---------+------+------+-------------------------------------------------+
| id   | select_type | table | type | possible_keys                       | key  | key_len | ref  | rows | Extra                                           |
+------+-------------+-------+------+-------------------------------------+------+---------+------+------+-------------------------------------------------+
|    1 | SIMPLE      | users | ALL  | PRIMARY                             | NULL | NULL    | NULL |    1 | Using temporary; Using filesort                 |
|    1 | SIMPLE      | upd   | ALL  | users_personal_data_user_id_foreign | NULL | NULL    | NULL |    1 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+-------------------------------------+------+---------+------+------+-------------------------------------------------+

Mariadb Galera Cluster Multi-Master Slow execution of queries

1 Answers1