|
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.
|
testbench_my.cnf