3

One of our customers has an online-shop running xt-commerce 4.2.

This version of the e-commerce-software has a very expensive query for the category-tree. The query adds about 3 seconds delay and is executed on every page request. Since the software is protected by IonCube I have no possibility to change the query made by the shop software.

As I found out, the query is not satisfied from the built-in MySQL-Query-Cache.

The contents of the resultset do not vary very often and could be cached without problems.

What are the possibilities to cache this one specific query?

Idea 1: Increase RAM for MySQL so that all tables and indexes fit in RAM.
- result: Queries duration dropped to "only" 2.5 seconds. Unsatisfactory.

Idea 2: Find out why the query is not satisfied from the query-cache and change MySQL-Settings to cache it.
- result: Not found settings that change the behaviour (until now).

For reference: here is the query. In the slow-log it is the same query every time (no changing typos).

SELECT 
    COUNT(parent.categories_id) AS level,
    c.*,
    cd.*,
    su.*,
    cl.link_url,
    group_permission.*,
    shop.*
FROM
    xt_categories AS c
        CROSS JOIN
    xt_categories AS parent
        LEFT JOIN
    xt_categories_description cd ON c.categories_id = cd.categories_id
        AND cd.categories_store_id = '1'
        LEFT JOIN
    xt_seo_url su ON (c.categories_id = su.link_id
        AND su.link_type = '2'
        AND su.store_id = '1')
        LEFT JOIN
    xt_categories_custom_link_url cl ON (cl.categories_id = c.categories_id
        AND cl.store_id = '1')
        LEFT JOIN
    xt_categories_permission group_permission ON (group_permission.pid = c.categories_id
        AND group_permission.pgroup = 'group_permission_1')
        LEFT JOIN
    xt_categories_permission shop ON (shop.pid = c.categories_id
        AND shop.pgroup = 'shop_1')
WHERE
    c.categories_status = '1'
        AND c.categories_left BETWEEN parent.categories_left AND     parent.categories_right
        AND cd.language_code = 'de'
        AND cd.categories_store_id = '1'
        AND ((c.category_custom_link = 0
        AND su.language_code = 'de'
        AND su.store_id = '1')
        OR (c.category_custom_link = 1
        AND cl.language_code = 'de'
        AND cl.store_id = '1'))
        AND group_permission.permission IS NULL
        AND shop.permission IS NULL
GROUP BY c.categories_id , c.categories_left , c.categories_right
ORDER BY c.sort_order , c.categories_left , cd.categories_name

Idea 3: Install MySQL Proxy and implement something like https://github.com/stephan-hof/mysql-proxy-in-memory-lru-cache/blob/master/in_memory_lru_cache.lua
- result: Works well. Changed the lua-script to only cache the specific queries. Query-times are now equal to queries satisfied from query-cache.

Explain extended-output

+---+--------+------------------+--------+-----------------------+---------+-----+---------------------------------+------+--------+----------------------------------------------+
| 1 | SIMPLE |        cd        |  ALL   | PRIMARY,language_code |         |     |                                 | 5724 | 75.00  | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | c                | eq_ref | PRIMARY               | PRIMARY |   4 | dilife-eu.cd.categories_id      |    1 | 100.00 | Using where                                  |
| 1 | SIMPLE | su               | ref    | link_id               | link_id |   4 | dilife-eu.c.categories_id       |   83 | 100.52 | Using where                                  |
| 1 | SIMPLE | cl               | ALL    |                       |         |     |                                 |    1 | 100.00 | Using where                                  |
| 1 | SIMPLE | parent           | ALL    |                       |         |     |                                 | 9530 | 100.00 | Using where; Using join buffer               |
| 1 | SIMPLE | group_permission | eq_ref | PRIMARY               | PRIMARY | 771 | dilife-eu.c.categories_id,const |    1 | 100.00 | Using where                                  |
| 1 | SIMPLE | shop             | eq_ref | PRIMARY               | PRIMARY | 771 | dilife-eu.c.categories_id,const |    1 | 100.00 | Using where                                  |
+---+--------+------------------+--------+-----------------------+---------+-----+---------------------------------+------+--------+----------------------------------------------+

Explain output after changing all tables to MyISAM.

+---+--------+------------------+--------+-----------------------+---------+---+----------------------------+------+--------+----------------------------------------------+
| 1 | SIMPLE | cl               | system | PRIMARY               |         |   |                            |    0 | 0.00   | const row not found                          |
| 1 | SIMPLE | group_permission | system | PRIMARY               |         |   |                            |    0 | 0.00   | const row not found                          |
| 1 | SIMPLE | shop             | system | PRIMARY               |         |   |                            |    0 | 0.00   | const row not found                          |
| 1 | SIMPLE | cd               | ALL    | PRIMARY,language_code |         |   |                            | 5724 | 75.00  | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | c                | eq_ref | PRIMARY               | PRIMARY | 4 | dilife-eu.cd.categories_id |    1 | 100.00 | Using where                                  |
| 1 | SIMPLE | su               | ref    | link_id               | link_id | 4 | dilife-eu.c.categories_id  |   83 | 100.57 | Using where                                  |
| 1 | SIMPLE | parent           | ALL    |                       |         |   |                            | 9791 | 100.00 | Using where; Using join buffer               |
+---+--------+------------------+--------+-----------------------+---------+---+----------------------------+------+--------+----------------------------------------------+
3

Solution 1: After harmonizing all tables to MyISAM the Query-Cache of MySQL worked as expected again. Added some indexes for cd and parent to speedup the queries about 30%.

Solution 2: Set up MySQL-Proxy and created LUA-Script to cache result of query in MySQL-Proxy. Shop-Software was set to use the MySQL-Proxy on Port 3307 as MySQL-Server.

apt-get install mysql-proxy
nano /etc/default/mysql-proxy

My config for MySQL-Proxy nano /etc/default/mysql-proxy:

ENABLED="true"
OPTIONS="--proxy-lua-script=/usr/share/mysql-proxy/in-memory-lru-cache.lua
     --proxy-address=:3307
     --proxy-backend-addresses=127.0.0.1:3306
     --log-file=/var/log/mysql-proxy.log
     --admin-username=xxxxxxxxxx
     --admin-password=xxxxxxxxxx
     --admin-lua-script=/usr/lib/mysql-proxy/lua/admin.lua"

Changes to /usr/share/mysql-proxy/in-memory-lru-cache.lua:

Line 104: if query.find(query,',c.%*,cd.%*,su.%*,cl.link_url,group_permission.%*,shop.%*') ~= nil then

The original file was from https://github.com/stephan-hof/mysql-proxy-in-memory-lru-cache/blob/master/in_memory_lru_cache.lua

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.