Announcement

Announcement Module
Collapse
No announcement yet.

migration to Xtradb Cluster done but query very slow

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • migration to Xtradb Cluster done but query very slow

    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:

    Code:
    ​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 (0.11 sec)
    
    MariaDB [dev_xxxxxxxxxxx]>

    On XtraDB Cluster server (node 1):

    Code:
    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 (40.08 sec)
    As you can see, the difference is HUGE.

    But with a Benchmark we have :

    On MariaDB server :

    Code:
    MariaDB [dev_xxxxxxxxxxxx]> SELECT BENCHMARK(1000000000,1+1);
    +---------------------------+
    | BENCHMARK(1000000000,1+1) |
    +---------------------------+
    |                         0 |
    +---------------------------+
    1 row in set (20.70 sec)

    On XtraDB Cluster server :
    Code:
    mysql> SELECT BENCHMARK(1000000000,1+1);
    +---------------------------+
    | BENCHMARK(1000000000,1+1) |
    +---------------------------+
    |                         0 |
    +---------------------------+
    1 row in set (19.95 sec)
    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 !





  • #2
    Hi,

    Can you provide MariaDB and PXC versions which you are using?

    Comment


    • #3
      Hi niljoshi,

      MariaDB version is : 5.5.29-MariaDB-log MariaDB Server, wsrep_23.7.3.rXXXX

      PXC version is : 5.5.34-55-log Percona XtraDB Cluster (GPL), wsrep_25.9.r3928


      Thanks.

      Comment


      • #4
        I can see also that Innodb version is not the same on both.

        MariaDB :
        1.1.8-29.3
        PXC :
        5.5.34-rel32.0
        I don't if it can help.


        Thanks for your support

        Comment


        • #5
          Hi,

          For more information i can add this sysbench also.

          from mariadb :

          http://pastebin.com/MbQfmcMf


          from PXC :

          http://pastebin.com/jbgQc29m

          also a pt-query-digest /var/lib/mysql/slow.log : http://pastebin.com/G9MyD4BH


          Thanks for your help.

          Comment


          • #6
            Hello,

            I think I found it, this query is searching for more than 250M of data in our database.
            in fact, on our standalone MariaDB we have query cache enabled, that's why it was so fast to run it.

            Unfortunately query cache is not supported on Galera, 40sec on this query is normal actually.
            I have to find another way to run this query
            maybe with memcache is possible : http://dev.mysql.com/doc/refman/5.6/...-frontend.html
            I will take a look.


            Thanks anyway.
            Last edited by metalraiden; 12-18-2013, 07:43 AM.

            Comment


            • #7
              Hello guys,

              just for information, this problem disappear by upgrading from PXC 5.5 to PXC 5.6.

              Comment

              Working...
              X