Hello,
I have actually a mariadb server standalone (4G of memory)and i wanted to go on XtraDB Cluster for HA(4G x 3).
I follow the tutorial on percona website for CentOS, and it’s working fine, all is sync, i have a standard my.cnf configuration from the website, just added some inno buffer pool size.
So i started to migrate one of our databases and this is very slow about query.
On MariaDB server:
MariaDB [dev_xxxxxxxxxxxx]> SELECT p0_.id AS id0, p0_.start_date AS start_date1, p0_.end_date AS end_date2, p0_.coupon_expiration_date AS coupon_expiration_date3, p0_.status AS status4, p0_.active AS active5, p0_.product_id AS product_id6, p0_.winner_id AS winner_id7 FROM product_auction_instance p0_ INNER JOIN products p1_ ON p0_.product_id = p1_.id INNER JOIN sites s2_ ON p1_.site_id = s2_.id WHERE s2_.code = 'xxxxxxxxxx' AND p1_.active = 1 AND p1_.featured = 0 AND p0_.active = 1 AND p0_.start_date > CURRENT_TIMESTAMP AND p1_.id NOT IN (SELECT p3_.id FROM product_auction_instance p4_ INNER JOIN products p3_ ON p4_.product_id = p3_.id WHERE p3_.active = 1 AND p3_.featured = 0 AND p4_.active = 1 AND p4_.start_date <= CURRENT_TIMESTAMP AND p4_.end_date >= CURRENT_TIMESTAMP GROUP BY p3_.id) GROUP BY p1_.id ORDER BY p0_.start_date ASC LIMIT 3;
+--------+---------------------+---------------------+-------------------------+---------+---------+-------------+------------+
| id0 | start_date1 | end_date2 | coupon_expiration_date3 | status4 | active5 | product_id6 | winner_id7 |
+--------+---------------------+---------------------+-------------------------+---------+---------+-------------+------------+
| 107921 | 2014-01-24 12:00:00 | 2014-02-01 19:41:00 | 2014-05-01 00:00:00 | Planned | 1 | 1074 | NULL |
+--------+---------------------+---------------------+-------------------------+---------+---------+-------------+------------+
1 row in set ([B]0.11 sec[/B])
MariaDB [dev_xxxxxxxxxxx]>
On XtraDB Cluster server (node 1):
mysql> SELECT p0_.id AS id0, p0_.start_date AS start_date1, p0_.end_date AS end_date2, p0_.coupon_expiration_date AS coupon_expiration_date3, p0_.status AS status4, p0_.active AS active5, p0_.product_id AS product_id6, p0_.winner_id AS winner_id7 FROM product_auction_instance p0_ INNER JOIN products p1_ ON p0_.product_id = p1_.id INNER JOIN sites s2_ ON p1_.site_id = s2_.id WHERE s2_.code = 'xxxxxxxxxxxx' AND p1_.active = 1 AND p1_.featured = 0 AND p0_.active = 1 AND p0_.start_date > CURRENT_TIMESTAMP AND p1_.id NOT IN (SELECT p3_.id FROM product_auction_instance p4_ INNER JOIN products p3_ ON p4_.product_id = p3_.id WHERE p3_.active = 1 AND p3_.featured = 0 AND p4_.active = 1 AND p4_.start_date <= CURRENT_TIMESTAMP AND p4_.end_date >= CURRENT_TIMESTAMP GROUP BY p3_.id) GROUP BY p1_.id ORDER BY p0_.start_date ASC LIMIT 3;
+--------+---------------------+---------------------+-------------------------+---------+---------+-------------+------------+
| id0 | start_date1 | end_date2 | coupon_expiration_date3 | status4 | active5 | product_id6 | winner_id7 |
+--------+---------------------+---------------------+-------------------------+---------+---------+-------------+------------+
| 107921 | 2014-01-24 12:00:00 | 2014-02-01 19:41:00 | 2014-05-01 00:00:00 | Planned | 1 | 1074 | NULL |
+--------+---------------------+---------------------+-------------------------+---------+---------+-------------+------------+
1 row in set ([B]40.08 sec[/B])
As you can see, the difference is HUGE.
But with a Benchmark we have :
On MariaDB server :
MariaDB [dev_xxxxxxxxxxxx]> SELECT BENCHMARK(1000000000,1+1);
+---------------------------+
| BENCHMARK(1000000000,1+1) |
+---------------------------+
| 0 |
+---------------------------+
1 row in set ([B]20.70 sec[/B])
On XtraDB Cluster server :
mysql> SELECT BENCHMARK(1000000000,1+1);
+---------------------------+
| BENCHMARK(1000000000,1+1) |
+---------------------------+
| 0 |
+---------------------------+
1 row in set ([B]19.95 sec[/B])
So, i don’t get why my SELECT is so long on XtraDB Cluster…
Do you need more information ?
maybe im missing some extra option to add to my.cnf ? http://pastebin.com/jvDW28wS
Thanks a lot !
