Shulou(Shulou.com)06/02 Report--

Original: http://proxysql.com/blog/scaling-with-proxysql-query-cache

Author: Rene

Mysql query cache

Before writing about the ProxySQL query cache, let's take a look at the MySQL query cache.

MySQL query caching is a very interesting feature, referencing official files:

Stores the text of the SELECT statement with the results sent to the client. If the same statement is received later, the service layer returns the result from the query cache instead of parsing and executing the statement again

It is a cache designed to improve performance. However, he is not a "panacea" and can see severe performance degradation or random random freezes from time to time. Why?

Peter Zaitsev wrote a series of articles about what MySQL query caching is (MySQL Query Cache is) and a series of ideas about giving it a second chance (second chance).

But the fact is that the MySQL query cache is difficult to expand due to locking and invalidation algorithms. The technical details of why it cannot be extended will not be repeated here, and the articles mentioned describe the reasons very well.

If you want to optimize your MySQL Query Cache, I highly recommend Domas Mituzas's query cache tuner.

ProxySQL query cache

ProxySQL query caching is completely different from MySQL query caching.

It is a key / value stored in memory, using:

Key is a combination of user name, library name and query text

Value is the result set returned by the backend (mysqld, or another proxysql)

The only way to invalidate an item in ProxySQL is through a time to live (time-to-live) in milliseconds. Some people think that there is a limit to failure through lifetime, but this is not the case for most applications. If the application requires absolutely correct data, transparent caching may not be the right solution.

Any application that accepts reading slightly obsolete data from slave can benefit from QC (query cache).

This concept is not new, and the driver itself has an implementation of query caching: mysqlnd, for example.

Query cache benchmark

The reason I describe MySQL Query Cache and ProxySQL Query Cache is that they are different in nature, so it means that comparing the two of them is not trivial, and they cannot be compared of the same kind.

It is known that Mysql Query Cache does not scale well. The benchmark I found that Mysql Query Cache doesn't scale well is a blog published by Szymon Komendera (Amazon Aurora (Amazon Aurora) database engineer) (which requires *). In blogs, Aurora with 4GB query caching can improve MySQL performance by up to 3.1 times (here is a comparison between Aurora QC and Mysql QC).

I will benchmark in the same way to see if I can get similar results with MySQL Query Cache and see how much performance Proxy Query Cache can improve.

Initialization Settin

2 clients with sysbench 0.5.

The reason for using two clients: under the current hardware conditions, one client cannot generate enough traffic to make the Proxy Query Cache reach its limit.

Sysbench command:

. / sysbench-- num-threads=512-- max-time=900-- max-requests=0-- test=./tests/db/oltp.lua-- mysql-user=sbtest-- mysql-password=sbtest-- mysql-host=10.1.1.22-- oltp-table-size=10000000-- mysql-port=$ {PORT}-- mysql-ps-mode=disable-- oltp-read-only=on-- oltp-point-selects=25-- oltp-skip-trx=on-- oltp-sum-ranges=0-oltp-simple-ranges=0-- oltp-distinct-ranges=0-- oltp-order-ranges=0-- oltp-dist-type=uniform run

1 mysql database (Percona Server 5.6.25) and proxysql (1.4.0)

Throughout the test, all the data has been cached in InnoDB buffer pool (in memory, no IO is involved), and the Query Cache is reset before testing.

The results of the above figure show that mysql QC really cannot be scaled, and using it can result in an 84% performance degradation. On the other hand, ProxySQL QC improves performance by 3.3x

Another interesting result is that the results vary according to the length of the test.

With Mysql QC, the longer the benchmark time, the lower the throughput (significantly reduced).

With ProxySQL QC, there is no decline in throughput, but a 1 per cent increase is considered to be due to volatility.

The above results should be noted that 10 million different SELECT statements can be generated in this environment, so there are 10 million entries in Query Cache because the size of the table is 10 million.

Smaller-- oltp-table-size will result in higher results for MySQL no QC and ProxySQL QC. In fact, out of curiosity, using-- oltp-table-size=1000000 a single instance ProxySQL can return more than 1 million QPS.

Move the query cache to make it closer to the application

So far, I've run ProxySQL with MySQL. Why? This is done to simulate the expectation that the current query caches the data itself.

Although I believe that for most workloads, the cache layer should not be near the location of the data storage (the back end), but should be close to the place where the data is consumed (the front end). For example, mysqlnd.

What happens if we use the previous test environment to move ProxySQL from the database server to the application server?

We now have two ProxySQL instances.

Not surprisingly, it expands better. The database server currently only needs to execute the sql that is not in the query cache.

ProxySQL is used in database servers, and QC improves performance by 3.3x.

ProxySQL is used on the client side, QC improves the performance by 5.2 times!

Can we get better results? Maybe it will. Because QC can be moved and dispersed (no longer needed in the database server), we can also create more complex configurations that separate the cache layer itself. For example, you can create two shards, each of which processes and caches half of the queries, or you can create a multi-tier cache system.

Conclusion

Although MySQL query caching is designed to improve performance, it has serious scalability problems and can easily become a serious bottleneck.

ProxySQL Query Cache can greatly improve the performance of certain workloads: read-intensive and can cache a lot of results. ProxySQL still allows you to spread out the cache layer, and you can move the cache layer closer to the application layer from the database server.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.