renecannao
commented
2 years ago Have you checked if the query rule is being hit at all?
You can check from stats_mysql_query_rules
Open prime-minister-of-fun opened 2 years ago
renecannao
commented
2 years ago Have you checked if the query rule is being hit at all?
You can check from stats_mysql_query_rules
I followed several of the many howtos on using the query cache and nothing is being selected from the query cache. Example: https://minervadb.com/index.php/proxysql-query-cache/
OS: Linux, 20.04.2 LTS (Focal Fossa) ARM64 running in Kubernetes environment on AWS. proxysql version: 2.2.0-72-ge14accd, aarch package.
Steps to reproduce: Proxysql is running using attached config and started with
/usr/bin/proxysql -f --initial --datadir /data --idle-threads -c /etc/proxysql.cnfAdd Aurora cluster hosts: (anonymized)
INSERT INTO main.mysql_aws_aurora_hostgroups (writer_hostgroup, reader_hostgroup, active, domain_name, writer_is_also_reader) VALUES (100,200,1,'.cluster-mycluster.us-east-2.rds.amazonaws.com',1);Add the AWS nodes. Logging reports no issues connecting. `insert into mysql_servers (hostgroup_id, hostname, compression, max_connections) VALUES (200, 'reader-node.rds.amazonaws.com',1,10000); insert into mysql_servers (hostgroup_id, hostname, compression, max_connections) VALUES (100, 'writer-node.rds.amazonaws.com',1,10000);
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; ` Stdout shows: Activating Monitoring of 1 AWS Aurora clusters
Add the query hash to enable caching:
INSERT INTO mysql_query_rules(active, digest, destination_hostgroup, cache_ttl, apply) VALUES(1,'0xDFE1CD5386492AAF',200,6000000,1);LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;Log into sqlproxy as the frontend to the AWS cluster and run the query. (anonymized)
SELECT column1, column2, ... column6 from demo_db.activity where created_at > (NOW() - INTERVAL 1 DAY) - INTERVAL 365 DAY LIMIT 5000;Sqlproxy successfully returns 5000 results from the big giant table. Without the limit, it's about 190MB of plain text when written to file and no LIMIT. I don't want to wait all day.Check if the proxysql is using the cache:
SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;Sqlproxy returns results, There is no -1 hostgroup in the results. My query is in the results.Check the digest in stats_mysql_query_digest = the one in mysql_query_rules. It is.
Rerun the big, giant results query:
SELECT column1, column2, ... column6 from demo_db.activity where created_at > (NOW() - INTERVAL 1 DAY) - INTERVAL 365 DAY LIMIT 5000;Sqlproxy successfully returns 5000 results from the big giant table.Check if the proxysql is using the cache:
SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;Sqlproxy returns results, There is no -1 hostgroup in the results. Still not caching anything.No errors reported in the logging. I realize NOW() - 365 days isn't really a good idea for caching, but, just trying to get an idea if caching will address our scaling problem. What am I missing?
proxysql-conf-bug-report.txt