Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13943

MariaDB Query Cache not working with ORDER BY clause

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.1.26, 10.2.9
    • Fix Version/s: 10.2
    • Labels:
      None
    • Environment:
      Debian 8
      Galera Cluster (10.1.26)
      Standalone testbench (10.2.9)
      4 vCPU
      60GB Ram

      Description

      Hey, we are experiencing a problem with the query cache. We have a WordPress System that uses a ORDER BY clause that adds a "+0" to convert the parameter to an number. If the +0 is there the query won't get cached, but if we remove it it gets cached.

      We setup a standalone MariaDB server because we monitored this issue on our galera cluster but even with the default my.cnf and a fresh installation of MariaDB (10.2.9) our test queries wont get cached. This is a problem because the WordPress systems are using a lot of those ORDER BY clauses with this type conversion and the loadtime spikes if our varnish cache is cold.

      We also tried the same queries with the same DB dump on a MariaDB 10.0.23 standalone server and there those queries got cached.

      Here is the output from MariaDB 10.2.9 and 10.1.26 with one of the problematic queries:

      SELECT SQL_CALC_FOUND_ROWS example_posts.ID
      FROM example_posts 
      LEFT JOIN example_postmeta
      ON ( example_posts.ID = example_postmeta.post_id ) 
      LEFT JOIN example_postmeta AS mt1
      ON ( example_posts.ID = mt1.post_id ) 
      LEFT JOIN example_postmeta AS mt2
      ON ( example_posts.ID = mt2.post_id ) 
      LEFT JOIN example_postmeta AS mt3
      ON (example_posts.ID = mt3.post_id
      AND mt3.meta_key = 'example_text_foo_bar' ) 
      LEFT JOIN example_postmeta AS mt4
      ON ( example_posts.ID = mt4.post_id )
      WHERE 1=1 
      AND ( example_postmeta.meta_key = 'some_random_text' 
      AND ( ( mt1.meta_key = 'foo_bar'
      AND mt1.meta_value = 'bar_foo' ) 
      AND ( mt2.meta_key = 'foo_bar_type'
      AND mt2.meta_value = 'example' ) 
      AND ( mt3.post_id IS NULL 
      OR ( mt4.meta_key = 'foo_bar_nope'
      AND mt4.meta_value = '0' ) ) ) )
      AND example_posts.post_type = 'post'
      AND ((example_posts.post_status = 'publish'))
      GROUP BY example_posts.ID
      ORDER BY replace(SUBSTRING_INDEX(example_postmeta.meta_value, " ", 1), ",",".")+0 DESC
      LIMIT 0, 3;
       
      +-------+
      | ID    |
      +-------+
      | 18557 |
      | 21513 |
      |   269 |
      +-------+
      3 rows in set, 1 warning (0.77 sec)
       
      /* 2nd call */
       
      +-------+
      | ID    |
      +-------+
      | 18557 |
      |   269 |
      | 21513 |
      +-------+
      3 rows in set, 1 warning (0.16 sec)
       
       
      /* after 2nd call */
       
      show status like "Qcache_%";
      +-------------------------+----------+
      | Variable_name           | Value    |
      +-------------------------+----------+
      | Qcache_free_blocks      | 1        |
      | Qcache_free_memory      | 67091088 |
      | Qcache_hits             | 0        |
      | Qcache_inserts          | 0        |
      | Qcache_lowmem_prunes    | 0        |
      | Qcache_not_cached       | 3        |
      | Qcache_queries_in_cache | 0        |
      | Qcache_total_blocks     | 1        |
      +-------------------------+----------+
      8 rows in set (0.00 sec)
       
      /* call without +0  after restart */
       
      SELECT SQL_CALC_FOUND_ROWS example_posts.ID
      FROM example_posts 
      LEFT JOIN example_postmeta
      ON ( example_posts.ID = example_postmeta.post_id ) 
      LEFT JOIN example_postmeta AS mt1
      ON ( example_posts.ID = mt1.post_id ) 
      LEFT JOIN example_postmeta AS mt2
      ON ( example_posts.ID = mt2.post_id ) 
      LEFT JOIN example_postmeta AS mt3
      ON (example_posts.ID = mt3.post_id
      AND mt3.meta_key = 'example_text_foo_bar' ) 
      LEFT JOIN example_postmeta AS mt4
      ON ( example_posts.ID = mt4.post_id )
      WHERE 1=1 
      AND ( example_postmeta.meta_key = 'some_random_text' 
      AND ( ( mt1.meta_key = 'foo_bar'
      AND mt1.meta_value = 'bar_foo' ) 
      AND ( mt2.meta_key = 'foo_bar_type'
      AND mt2.meta_value = 'example' ) 
      AND ( mt3.post_id IS NULL 
      OR ( mt4.meta_key = 'foo_bar_nope'
      AND mt4.meta_value = '0' ) ) ) )
      AND example_posts.post_type = 'post'
      AND ((example_posts.post_status = 'publish'))
      GROUP BY example_posts.ID
      ORDER BY replace(SUBSTRING_INDEX(example_postmeta.meta_value, " ", 1), ",",".") DESC
      LIMIT 0, 3;
       
      +-------+
      | ID    |
      +-------+
      | 18557 |
      |   269 |
      | 21513 |
      +-------+
      3 rows in set (0.74 sec)
       
      /* 2nd call without +0 */
       
      +-------+
      | ID    |
      +-------+
      | 18557 |
      |   269 |
      | 21513 |
      +-------+
      3 rows in set (0.00 sec)
       
      +-------------------------+----------+
      | Variable_name           | Value    |
      +-------------------------+----------+
      | Qcache_free_blocks      | 1        |
      | Qcache_free_memory      | 67086096 |
      | Qcache_hits             | 1        |
      | Qcache_inserts          | 2        |
      | Qcache_lowmem_prunes    | 0        |
      | Qcache_not_cached       | 1        |
      | Qcache_queries_in_cache | 2        |
      | Qcache_total_blocks     | 7        |
      +-------------------------+----------+
      8 rows in set (0.00 sec)
      
      

      Structure of post and postmeta table (CREATE TABLE statement copied from the example DB dump, but it should be the Wordpress default schema):

      CREATE TABLE `example_postmeta` (
        `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `post_id` bigint(20) unsigned NOT NULL DEFAULT 0,
        `meta_key` varchar(255) DEFAULT NULL,
        `meta_value` longtext DEFAULT NULL,
        PRIMARY KEY (`meta_id`),
        KEY `post_id` (`post_id`),
        KEY `meta_key` (`meta_key`(191))
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      CREATE TABLE `example_posts` (
        `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
        `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        `post_content` longtext NOT NULL,
        `post_title` text NOT NULL,
        `post_excerpt` text NOT NULL,
        `post_status` varchar(20) NOT NULL DEFAULT 'publish',
        `comment_status` varchar(20) NOT NULL DEFAULT 'open',
        `ping_status` varchar(20) NOT NULL DEFAULT 'open',
        `post_password` varchar(255) NOT NULL DEFAULT '',
        `post_name` varchar(200) NOT NULL DEFAULT '',
        `to_ping` text NOT NULL,
        `pinged` text NOT NULL,
        `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        `post_content_filtered` longtext NOT NULL,
        `post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
        `guid` varchar(255) NOT NULL DEFAULT '',
        `menu_order` int(11) NOT NULL DEFAULT 0,
        `post_type` varchar(20) NOT NULL DEFAULT 'post',
        `post_mime_type` varchar(100) NOT NULL DEFAULT '',
        `comment_count` bigint(20) NOT NULL DEFAULT 0,
        PRIMARY KEY (`ID`),
        KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
        KEY `post_parent` (`post_parent`),
        KEY `post_author` (`post_author`),
        KEY `post_name` (`post_name`(191))
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      

      SanjaByelkin from the IRC-Chat suggested to try this little testsuite to see if we can replicate the problem but in this example the query cache works.

      create table t1 (a int, b char(20));
      insert into t1 values (1, "123,4 B"),(2, "325,4 C");
       
      /* fresh restart */
      show status like "Qcache_%";
      +-------------------------+----------+
      | Variable_name           | Value    |
      +-------------------------+----------+
      | Qcache_free_blocks      | 1        |
      | Qcache_free_memory      | 67091088 |
      | Qcache_hits             | 0        |
      | Qcache_inserts          | 0        |
      | Qcache_lowmem_prunes    | 0        |
      | Qcache_not_cached       | 3        |
      | Qcache_queries_in_cache | 0        |
      | Qcache_total_blocks     | 1        |
      +-------------------------+----------+
      8 rows in set (0.00 sec)
       
      select * from t1 ORDER BY replace(SUBSTRING_INDEX(t1.b, " ", 1), ",",".")+0;
      +------+---------+
      | a    | b       |
      +------+---------+
      |    1 | 123,4 B |
      |    2 | 325,4 C |
      +------+---------+
      2 rows in set (0.00 sec)
       
      select * from t1 ORDER BY replace(SUBSTRING_INDEX(t1.b, " ", 1), ",",".")+0;
      +------+---------+
      | a    | b       |
      +------+---------+
      |    1 | 123,4 B |
      |    2 | 325,4 C |
      +------+---------+
      2 rows in set (0.00 sec)
       
      show status like "Qcache_%";
      +-------------------------+----------+
      | Variable_name           | Value    |
      +-------------------------+----------+
      | Qcache_free_blocks      | 1        |
      | Qcache_free_memory      | 67089552 |
      | Qcache_hits             | 1        |
      | Qcache_inserts          | 1        |
      | Qcache_lowmem_prunes    | 0        |
      | Qcache_not_cached       | 3        |
      | Qcache_queries_in_cache | 1        |
      | Qcache_total_blocks     | 4        |
      +-------------------------+----------+
      

      We would appreciate any feedback on this issue.

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            Horrorhorst Aljoscha Dembowsky
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.