memory

Few days ago MariaDB announced MaxScale 2.1.0 Beta, and among the new Query Performance capabilities there is Query Cache Filter. Query Cache was among the very first features introduced in ProxySQL, and therefore I was extremely curious to compare them! Reading the documentation on MaxScale Query Cache Filter I noticed few interesting things compares to ProxySQL Query Cache:

  • MaxScale supports in-memory and on disk storage, while ProxySQL supports only in-memory storage
  • MaxScale can have either shared or per-thread cache, while ProxySQL supports only shared cache: in 2014 ProxySQL had per-thread cache, but benchmark showed that a partitioned shared cache is way more efficient

To compare ProxySQL 1.3.4 Query Cache and MaxScale 2.1.0 Query Cache Filter, I used the same cluster used in the previously benchmark so we know already how they perform without caching enabled.

Configuring MaxScale

In maxscale.cnf we added:

[MyCache]
type=filter
module=cache
storage=storage_inmemory
soft_ttl=10
hard_ttl=10
cached_data=shared
rules=cache_rules.json

We also created the rule in a file named cache_rules.json. The rule is very simple, it caches all the queries where database=sbtest:

{
    "store": [
        {
            "attribute": "database",
            "op": "=",
            "value": "sbtest"
        }
    ]
}

And finally we add filters=MyCache into [Read-Write Service] (router=readwritesplit). Note: for now we won’t enable filters=MyCache on router=readconnroute: more details later.

Configuring ProxySQL

In ProxySQL we simply executed these commands to modify what already configured in the previous benchmark :

UPDATE mysql_query_rules SET cache_ttl=10000 WHERE rule_id=1;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

TTL was set to 10 seconds. Note: MaxScale configures TTL in seconds, ProxySQL in milliseconds since version 1.1 .

Benchmark

sysbench was executed with commands like the follow (number of connections varies based on number of threads):

for t in 64 128 256 512 1024 ; do
for s in maxscale_4006 proxysql ; do
./sysbench --num-threads=$t --max-time=60 --max-requests=0 --test=./lua/oltp_point_select.lua \
--mysql-user=sbtest --mysql-password=sbtest --oltp-table-size=1000 --oltp-tables-count=8 \
--report-interval=1 run --mysql-socket=/tmp/$s.sock > query_cache_4/conn"$t"_"$s".txt
done ; done

Note that the tables have 10 millions rows, but sysbench was executed with --oltp-table-size=1000 because this workload needs to measure the performance of the query cache, therefore the resultset should be easy to cache.

All the results are available here.

4 worker threads

From 1 to 4 worker threads, ProxySQL provides around 4x times the throughput of MaxScale.

I am not sure if the difference in throughput is only due to the Query Cache, or overall data flow: I won’t investigate that.

For 8 worker threads I also checked the performance of MaxScale with cached_data=thread_specific. It doesn’t seem to be any faster than cached_data=shared, but actually slower: probably because it reduces the probability of finding the resultset in the query cache (this is the reason why I removed per-thread cache from ProxySQL in 2014).

8 worker threads

With 8 worker threads, ProxySQL 1.3.4 provides up to 5.5x times the throughput of MaxScale 2.1.0.

Scalability

Here is a graph that shows how throughput relates to the number of worker threads at a fixed number of connections (256 connections). Both ProxySQL 1.3.4 and MaxScale 2.1.0 seems to scale proportionally to the number of worker threads. As scalability matters, it is worth to note that ProxySQL with only 1 worker thread provides the same throughput of MaxScale with 8 worker threads.

scalability

Query Cache and router=readconnroute

From MariaDB KB:

Note that installing the cache causes all statements to be parsed. The implication of that is that unless statements already need to be parsed, e.g. due to the presence of another filter or the chosen router, then adding the cache will not necessarily improve the performance, but may decrease it.

What this actually mean? Enabling Query Cache Filter on [Read-Only Service] (router=readconnroute) can reduce performance. And indeed it does. Once Query Cache Filter is enabled, the throughput of router=readconnroute drop to the same level of router=readwritesplit.

Conclusions

Trying to combine in a single graph (sorry for so many colours) the throughputs of:

  • direct connections to MySQL
  • MaxScale 2.1.0 with 8 worker threads, using both router=readconnroute and router=readwritesplit, with and without Query Cache Filter
  • ProxySQL 1.3.4 with 8 worker threads, with and without Fast-Forward, with and without caching

final comparison

The numbers are pretty clear.

When scalability matters and Query Cache is a needed, ProxySQL is still the most performing proxy for MySQL. ProxySQL Query Cache is able to scale almost linearly with the number of threads and outperform MaxScale Query Cache Filter. MariaDB KB reports that “cache is still experimental”, therefore I expect that in the future MaxScale Query Cache will probably improve. If you need a high-availabilty, scalable, high-performance, and Open Source proxy for MySQL, once again I invite you to download and try ProxySQL.