cPanel Question posted in
The official documentation can be seen here.

Mysql VS MariaDb Same query performance – CPanel

I have the same database on 2 servers. One is with Cpanel running mysql other one its CWP with mariadb. Both databases are identical, tables have the same index, mysql variables are identical but the performance its way different:

SELECT 
    DISTINCT item_id
FROM                                                        
    site_plugin_products_cache_filters
WHERE
    value_id IN (32)

On MySql I get 1,939 rows (1.284 s)
On MaridDb I get 1,937 rows (0.097 s)

Any idea why this happens ?

When i get to sub-queries the differences are way bigger ( 0.01s vs 100+ seconds )

SELECT 
    DISTINCT item_id
FROM                                                        
    site_plugin_products_cache_filters
WHERE
    value_id IN (32) AND
    item_id IN (
        SELECT item_id FROM 
            site_plugin_products_cache_cats
        WHERE
            cat_id=1
    )

Or

SELECT  DISTINCT item_id  FROM site_plugin_products_cache_cats
WHERE cat_id IN (362)  AND item_id != 2519 ORDER BY rand()
LIMIT 4

4 rows (0.053 s) vs 4 rows (0.103 s)

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Seems WHM / CPanel comes by default with query_cache_size=0 and this was killing the performance. After set to an optimal value all its normal :)


  2. 0

    No, setting query_cache_size to a nonzero value hides the real execution time. When any write to the table occurs, all entries in the QC are purged. Suddenly the query takes the “long” time instead of the arbitrarily short time.

    0.01s smells like a QC time. 0.097s does not.

    Your first query may benefit from the composite INDEX(value_id, item_id).

    site_plugin_products_cache_cats may benefit from INDEX(cat_id, item_id).

    There is one very valid reason for performance differences between MySQL and MariaDB — the Optimizers have diverged, especially in the area of subqueries. IN ( SELECT ... ) used to have notoriously poor performance. Now both products have made improvements — in different ways.

    For comparing the real performance, one technique is to say SELECT SQL_NO_CACHE ... and run the query twice. Take the second time. (The first might be doing I/O to fill other caches.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search