\n
In this blog post we have explained about ProxySQL query cache and how it creates value for MySQL query performance. Before we get deep in to ProxySQL query cache, Let us explain how MySQL implemented query cache for performance, The MySQL query cache (deprecated as of MySQL 5.7.20 and removed in MySQL 8.0<\/em> ) caches the text of a SELECT statement with corresponding results which was sent to the client. In the future when identical SELECT statement is received, \u00a0MySQL server retrieves the results from query cache rather than parsing and executing the statement again. Technically, MySQL query cache is a query results cache. It compare every incoming queries that start with SEL<\/strong> to a hash table, and if there is a match the results are returned directly from the query cache. A classic use case for a MySQL query cache is SELECT queries on e-commerce site with millions of articles in the inventory with complex listing rules for optimal SORT\/SEARCH operations. But, MySQL 8.0 retired query cache due to following limitations:<\/p>\n In MySQL query cache, There exists common caching space for all the tables and in ProxySQL we have to configure which traffic should be cached. You can enable query cache in ProxySQL by defining query rules that match the traffic which eventually will be cached by setting cache_ttl<\/em> for the cached results. You can define matches for the incoming traffic either by query or ProxySQL query digest patterns. Whenever the query successfully passes the matching criteria, the resultset is cached for the future queries to be replied directly from the ProxySQL instance than from DB instance of the hostgroup (this is possible only if the cache_ttl is not expired)<\/em><\/p>\n ProxySQL Query Cache is a in-memory key-value storage which retains a metadata in the pattern explained below:<\/p>\n If you are first timer with ProxySQL and want to lean installation (it’s out of the scope of this blog so we haven’t explained about ProxySQL installation here), We strongly recommend you to follow steps here: \u00a0https:\/\/github.com\/sysown\/proxysql\/wiki#getting-started<\/span> <\/a>\u00a0. Before doing any benchmarks, I will try to give you a short description of how ProxySQL query cache gets enabled. Unlike MySQL query cache, where a common caching space exists for all tables, in ProxySQL query cache we have to define what traffic gets cached. This is done by defining query rules that match traffic that is going to be cached and setting a \u201ccache_ttl\u201d for the cached results. There are many ways to define matches for incoming traffic, either by query or digest using patterns. All we need to cache the resultset is to define the matching criteria and the TTL. If a query passed the matching criteria, the resultset is cached so the next requests are served directly from the ProxySQL instance instead of querying the hostgroup DB nodes (if cache_ttl has not expired).<\/p>\n We have a full operational ProxySQL step with 3 instances of Percona Server for MySQL 5.7 and a ProxySQL instance with Sysbench instance installed. Database instances are configured in same hostgroup with traffic load-balanced equally with same priority. Let’s confirm all the servers are going good by querying from ProxySQL admin:<\/p>\n If you notice above results, You can see there are two host groups configured:<\/p>\n Before using sysbench, Let’s check the “stats_mysql_query_digest<\/strong>” table:<\/p>\n ProxySQL query digests are stored in “stats_mysql_query_digest<\/strong>” table.<\/p>\n To explain the ProxySQL query cache, We have created some data with Sysbench and this benchmarking activity will do selects by Primary Key or RANGE:<\/p>\n The benchmark above is done with ProxySQL query cache disabled. Now let us show you what is recorded in “stats_mysql_query_digest”\u00a0<\/strong>table:\u00a0<\/span><\/p>\n We are updating table mysql_query_rules to match the results that should be cached. In this example, We have used matching criteria and a cache_ttl<\/em> of 15000ms, So we have added rules below:<\/p>\n P.S.: Please don’t forget to load the query rules at runtime. If you miss that step, ProxySQL will lose these rules with a restart so we should also this to disk:<\/p>\n We will reset the stats_mysql_query_digest<\/strong> table to evaluate ProxySQL query cache:<\/p>\n Now we are re-running the benchmarking with query cache enabled, We can confirm the traffic was cached querying the stats_mysql_query_digest<\/strong> table:<\/p>\n If you notice above the results, the cached queries will be marked with a special hostgroup “-1<\/strong>” (these queries were not sent to any hostgroup) and total execution time for the cached queries is “0<\/strong>”<\/p>\n ProxySQL query cache is definitely a instant gratification performance booster for MySQL query performance (though it doesn’t support prepared statements<\/em>). You can tune the total amount of memory used by the ProxySQL query cache by setting variable –mysql-query_cache_size_MB\u00a0<\/em>and please note, the current implementation of mysql-query_cache_size_MB doesn’t impose any hard limit, the system variable\u00a0–mysql-threshold_resultset_size <\/em>controls\u00a0the maximum resultset size the ProxySQL will buffer before it start sending to the client, We recommend to carefully tweak the variable –mysql-threshold_resultset_size\u00a0<\/em>for performance because setting it too low will arrest the reattempt of queries failed while retrieving the resultset from the backend DB node and setting it too high will make ProxySQL a memory intensive infrastructure to manage.<\/p>\n\n
How ProxySQL implemented Query Cache ?<\/h3>\n
\n
Configuring ProxySQL for query caching<\/h3>\n
Step-by-step configuration of ProxySQL query cache<\/h4>\n
ProxySQLAdmin> select * from mysql_servers;\r\n+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+\r\n| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |\r\n+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+\r\n| 2 | 88.8.6.32 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |\r\n| 1 | 88.8.6.31 | 3306 | ONLINE | 1 | 0 \t | 1000 | 0 \t | 0 | 0 | \t|\r\n| 2 | 88.8.6.33 | 3306 | ONLINE | 1 | 0 \t | 1000 | 0 \t | 0 | 0 | \t|\r\n| 2 | 88.8.6.31 | 3306 | ONLINE | 1 | 0 \t | 1000 | 0 \t | 0 | 0 | \t|\r\n+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+<\/pre>\n
\n
ProxySQLAdmin> SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest_reset ORDER BY sum_time DESC;\r\nEmpty set (0.00 sec)<\/pre>\n
sysbench --threads=64 --max-requests=0 --time=180 --mysql-user=mdblab --mysql-password=mdblabinfra --mysql-db=sbtest --mysql-host=127.0.0.1 --mysql-port=6033 --oltp-table-size=250000000 --oltp-read-only=on --oltp-skip-trx=on --oltp-test-mode=simple --oltp-sum-ranges=0 --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-point-selects=1 --oltp-simple-ranges=0 \/usr\/share\/sysbench\/tests\/include\/oltp_legacy\/oltp.lua run\r\n\r\nsysbench --threads=64 --max-requests=0 --time=180 --mysql-user=mdblab --mysql-password=mdblabinfra --mysql-db=sbtest --mysql-host=127.0.0.1 --mysql-port=6033 --oltp-table-size=250000000 --oltp-read-only=on --oltp-skip-trx=on --oltp-test-mode=simple --oltp-sum-ranges=0 --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-point-selects=0 --oltp-simple-ranges=1 \/usr\/share\/sysbench\/tests\/include\/oltp_legacy\/oltp.lua run<\/pre>\n
ProxySQLAdmin> SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest_reset ORDER BY sum_time DESC;\r\n+------------+-------------+-----------+--------------------+------------------------------------------------+\r\n| count_star | sum_time | hostgroup | digest | digest_text |\r\n+------------+-------------+-----------+--------------------+------------------------------------------------+\r\n| 517314 | 51813612616 | 2 | 0x7721D69250CB40 | SELECT c FROM sbtest1 WHERE id=? |\r\n| 8173 | 71351732 | 2 | 0x3BC2F7549D058B6F | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? |\r\n+------------+-------------+-----------+--------------------+------------------------------------------------+\r\n2 row in set (0.01 sec)<\/pre>\n
Update MySQL queries to be cached<\/span><\/h4>\n
ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,digest,cache_ttl,apply) VALUES (1,1,'0x7721D69250CB40',15000,1);\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,digest,cache_ttl,apply) VALUES (2,1,'0x3BC2F7549D058B6F',15000,1);\r\nQuery OK, 1 row affected (0.00 sec<\/pre>\n
ProxySQLAdmin> LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;\r\nQuery OK, 0 rows affected (0.00 sec)<\/pre>\n
ProxySQLAdmin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;\r\n+---+\r\n| 1 |\r\n+---+\r\n| 1 |\r\n+---+\r\n1 row in set (0.01 sec)\r\n----------<\/pre>\n
ProxySQLAdmin> SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;\r\n+------------+------------+-----------+--------------------+------------------------------------------------+\r\n| count_star | sum_time | hostgroup | digest | digest_text |\r\n+------------+------------+-----------+--------------------+------------------------------------------------+\r\n| 715815 | 9513175314 | 2 | 0x7721D69250CB40 | SELECT c FROM sbtest1 WHERE id=? |\r\n| 835193 | 0 | -1 | 0x7721D69250CB40 | SELECT c FROM sbtest1 WHERE id=? |\r\n+------------+------------+-----------+--------------------+------------------------------------------------+\r\n2 rows in set (0.00 sec)<\/pre>\n
ProxySQLAdmin> SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;\r\n+------------+-----------+-----------+--------------------+------------------------------------------------+\r\n| count_star | sum_time | hostgroup | digest | digest_text |\r\n+------------+-----------+-----------+--------------------+------------------------------------------------+\r\n| 58136 | 857050510 | 2 | 0x3BC2F7549D058B6F | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? |\r\n| 182175 | 0 | -1 | 0x3BC2F7549D058B6F | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? |\r\n+------------+-----------+-----------+--------------------+------------------------------------------------+\r\n2 rows in set (0.00 sec)<\/pre>\n
Conclusion<\/h4>\n
References:<\/h4>\n
\n
![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2020\/02\/Screenshot-2020-02-09-at-12.10.30-AM.png\")
<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"