I’m trying a percona xtradb cluster (3 nodes) on a rancher environment (automatically created from rancher catalog).
Replication works great, but I’ve lots of problems with slow queries.
First of all, for exclude network problems etc, I’m executing the query directly inside the node container.
The query is simple, and this is the result:
mysql> SELECT SQL_NO_CACHE SUM(A.price) as sales
-> FROM appointments A
-> WHERE A.venue_id = 24
-> AND A.treatment_id IN (2,3,14,26,43,46,66,77,84,100,101,114,115,144,161,1
62,163,164,192,193,194,209,211,267,322)
-> AND (DATE(A.time) >= '2016-05-01' AND DATE(A.time) <= '2016-05-31')
-> AND A.STATE IN ('booked','checked_in','checked_out','archived');
+-------+
| sales |
+-------+
| 11975 |
+-------+
1 row in set (34.88 sec)
I created a simple mysql container on the same server, without custom configurations. I imported the same DB, and executed the same query:
mysql> SELECT SQL_NO_CACHE SUM(A.price) as sales
-> FROM appointments A
-> WHERE A.venue_id = 24
-> AND A.treatment_id IN (2,3,14,26,43,46,66,77,84,100,101,114,115,144,161,1
62,163,164,192,193,194,209,211,267,322)
-> AND (DATE(A.time) >= '2016-05-01' AND DATE(A.time) <= '2016-05-31')
-> AND A.STATE IN ('booked','checked_in','checked_out','archived');
+-------+
| sales |
+-------+
| 11975 |
+-------+
1 row in set (0.02 sec)
Same server, same DB, no custom configurations… so… What can be the problem?
I hope that somebody can help me.
Thanks!
EDIT:
EXPLAIN RESULTS:
PXC SERVER:
+----+-------------+-------+-------+------------------------------------------+--------------+---------+------+-------+------------------------------------+
| id | select_type | table | type | possible_keys |key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------------------------------+--------------+---------+------+-------+------------------------------------+
| 1 | SIMPLE | A | range | venue_id,state,treatment_id,multi_column |treatment_id | 4 | NULL | 15900 | Using index condition; Using where |
+----+-------------+-------+-------+------------------------------------------+--------------+---------+------+-------+------------------------------------+
1 row in set (0.00 sec)
MYSQL:
+----+-------------+-------+------------+-------+------------------------------------------+--------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------------------------------+--------------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | A | NULL | range | venue_id,state,treatment_id,multi_column | multi_column | 26 | NULL | 107113 | 65.10 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+------------------------------------------+--------------+---------+------+--------+----------+----------------------------------+
1 row in set, 1 warning (0.01 sec)
Versions:
PXC: 5.6.28-76.1-56
MYSQL: 5.7.12
Indexes (same for both):
+--------------+------------+----------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| appointments | 0 | id | 1 | id | A | 3120188 | NULL | NULL | | BTREE | | |
| appointments | 1 | venue_id | 1 | venue_id | A | 368 | NULL | NULL | | BTREE | | |
| appointments | 1 | state | 1 | state | A | 8 | NULL | NULL | | BTREE | | |
| appointments | 1 | time | 1 | time | A | 45835 | NULL | NULL | | BTREE | | |
| appointments | 1 | treatment_id | 1 | treatment_id | A | 2081 | NULL | NULL | | BTREE | | |
| appointments | 1 | staff_member_id | 1 | staff_member_id | A | 2516 | NULL | NULL | | BTREE | | |
| appointments | 1 | customer_id | 1 | customer_id | A | 421915 | NULL | NULL | | BTREE | | |
| appointments | 1 | multi_column | 1 | venue_id | A | 320 | NULL | NULL | | BTREE | | |
| appointments | 1 | multi_column | 2 | state | A | 2248 | NULL | NULL | | BTREE | | |
| appointments | 1 | multi_column | 3 | time | A | 2158187 | NULL | NULL | | BTREE | | |
| appointments | 1 | multi_column | 4 | treatment_id | A | 2713420 | NULL | NULL | | BTREE | | |
+--------------+------------+----------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
EDIT2:
It seems that it doesn’t use the multi_column index, so if I force it in PXC this is the result:
mysql> SELECT SQL_NO_CACHE SUM(A.price) as sales
-> FROM appointments A FORCE INDEX(multi_column)
-> WHERE A.venue_id = 24
-> AND A.treatment_id IN (2,3,14,26,43,46,66,77,84,100,101,114,115,144,161,1
62,163,164,192,193,194,209,211,267,322)
-> AND (DATE(A.time) >= '2016-05-01' AND DATE(A.time) <= '2016-05-31')
-> AND A.STATE IN ('booked','checked_in','checked_out','archived');
+-------+
| sales |
+-------+
| 11975 |
+-------+
1 row in set (0.04 sec)
What could be the problem because pxc doesn’t automatically use the multi columns index?
