Hi to all, 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:
[HTML]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)
[/HTML]
I created a simple mysql container on the same server, without custom configurations. I imported the same DB, and executed the same query:
[HTML]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)
[/HTML]
Same server, same DB, no custom configurations… so… What can be the problem?
I hope that somebody can help me.
Thanks!
PS: I’m using version 5.6.28 (no other versions are available in rancher)
PXC SERVER:
[HTML]±—±------------±------±------±-----------------------------------------±-------------±--------±-----±------±-----------------------------------+
| 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)[/HTML]
MYSQL:
[HTML]±—±------------±------±-----------±------±-----------------------------------------±-------------±--------±-----±-------±---------±---------------------------------+
| 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)[/HTML]
Versions:
[HTML]PXC: 5.6.28-76.1-56
MYSQL: 5.7.12[/HTML]
Indexes (same for both):
[HTML]±-------------±-----------±---------------------------±-------------±---------------------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| 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 | | |
±-------------±-----------±---------------------------±-------------±---------------------------±----------±------------±---------±-------±-----±-----------±--------±--------------+[/HTML]
EDIT2:
[HTML]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)[/HTML]