Comments (5)
Hi @flopex , thank you for your report.
The memory structure in which query digest information are store is a hash table: best case and worse case complexity for lookup and and insert for a hash table are well documented, and I won't try to summarize it here.
But in a nutshell, both lookup and insert in a hash table should be very fast .
Why a large query_digest_memory
can affect performance?
As explained earlier, lookup and insert in it shouldn't be a problem.
Although other reasons are possible.
For example, a large query_digest_memory
may result in excessive memory usage and swapping. If your system is swapping, everything becomes extremely slow. This doesn't seem to be your case.
Your test with 5000 simple SELECT 1
shows an interesting result.
The vast majority of queries doesn't seem to face any performance issue. Yet, at regular interval, in a pattern that is almost predictable, you have a spike in execution time. (Note the highlight on at regular interval).
Is the spike in execution time really caused by the query digest table?
In this GH issue there is not enough evidence to be 100% sure, but I will assume this is true and evaluate possible reasons.
If the query digest table is responsible for these stalls it means that the query digest digest itself is blocked: some thread is performing some operation other than lookup or insert.
There are mostly 5 possible operations against query digest:
- lookup of a single entry (to then update it counters)
- insert of a new entry
- traverse of the full table to generate memory information (
query_digest_memory
) - traverse of the full table to delete all its entries
- traverse of the full table to generate
stats_mysql_query_digest
table on SQLite
The first 2 operations are extremely fast.
The other 3 operations are a lot slower, and they are also ordered in term of execution time (as at ProxySQL 2.3.2, things changed in newer version).
Because you have spikes at regular interval, I assume that the table is not truncated, thus point 4 shouldn't apply to you.
Therefore the stalls are either caused by queries on stats_mysql_query_digest
(point 5) , or queries on stats_memory_metrics
or Prometheus exporter (point 3).
Would be curious to know if there was an explanation to the pattern seen during these tests.
I hope the above explains it.
What is the suggested mitigation/setup for keeping
stats_mysql_query_digest
at a reasonable size?
I think the first step is to identify why stats_mysql_query_digest
is so large.
What queries pattern are stored that cause so many entries in it?
And finally, as you would expect, "upgrade" is often a solution :)
In ProxySQL 2.4.0 parsing of queries and the query digests was extensively improved, and at least 3 new variables were introduced to control how query digest are generated.
In latest ProxySQL (2.6.2 , for future reference) there are 10 variables that control how query digests are generated: tuning them may reduce the size of stats_mysql_query_digest
.
Thus an upgrade and tuning of these variable can reduce the size of query digest table.
Also, In ProxySQL 2.5.2 there is an improvement that makes point 5 (the generation of stats_mysql_query_digest
) almost not blocking, using an adaptive algorithm that blocks query digest table for very very short time.
And again in ProxySQL 2.5.2 , TRUNCATE
on stats_mysql_query_digest
is a not blocking operation.
As a final note, in latest ProxySQL the 5 operations on query digest table ordered by blocking time should be:
TRUNCATE
- lookup of a single entry (to then update it counters)
- insert of a new entry
- traverse of the full table to generate
stats_mysql_query_digest
table on SQLite - traverse of the full table to generate memory information (
query_digest_memory
)
from proxysql.
Thank you for the very detailed response, it all makes more sense now. We'll look into further testing out these to find the root cause, but the Prometheus exporter does sound like an interesting one, we do run it and could explain the predictable timings.
from proxysql.
Finally had some time to test this in a dev environment. I can confirm that it was the the proxysql_exporter scrape interval that was causing the stalls.
Series of 5000 tests with no spikes after disabling the exporter.
I was able to reproduce the stalls by disabling query_digests_no_digits
, running a bunch of bogus SAVEPOINTS
with unique identifiers and watching the query_digest_memory
to grow in size.
from proxysql.
Hi @flopex
[...] a bunch of bogus SAVEPOINTS with unique identifiers [...]
I am not sure if this was only for testing purposes, or if you identified that this is the reason (a lot of savepoints with unique identifiers) why you have a large table.
But if this is the case, these "unique entries" should have a last_seen
that is probably never updated.
This gave me an idea, described in #4543 .
from proxysql.
Not only for testing, because we also noticed the bloat of the query-digest was because of Django, similar to this other issue https://groups.google.com/g/proxysql/c/MeCodsiqlo0
We found this to be the easiest way to fill up the stats metrics to similar levels seen in prod.
from proxysql.
Related Issues (20)
- Question : when a node from reader hostgroup and lost his READ_ONLY HOT 1
- Implement setting in `mysql_hostgroup_attributes` to override global `mysql-monitor_slave_lag_when_null`
- proxySQL can not properly distribute slow queries after linux update HOT 2
- Unable to parse query. If correct, report it as a bug: SET SESSION wsrep_sync_wait=? HOT 2
- [Question] Does ProxySQL Provide Metrics for User Maximum Connections and Current Connection Usage? HOT 2
- Evaluate exporting `stats_mysql_users` using the Prometheus exporter HOT 3
- Every uneven traffic distribution for nodes with same weight after upgrading from v2.0.10-1 to v2.5.5-1 HOT 1
- Official documentation has missing introduced version information for mysql-connection_warming
- Implement setting in `mysql_hostgroup_attributes` to define custom load balancing algorithm
- ProxySQL errorlog not getting picked up from proxysql.cnf in docker HOT 5
- crashes during cluster shutdown in CI testing HOT 1
- Support some form of partial cleanup of `stats_mysql_query_digest`
- Shunned writer node not becoming online HOT 8
- Intermittent "Access denied" errors HOT 1
- Different server_version per port
- Wrong column order in table mysql_servers HOT 3
- aarch64 / i686 builds fail of v2.6.2 (narrowing conversion of timeout in clickhouse) HOT 2
- Can not set servers_defaults field of mysql_hostgroup_attributes table from config file HOT 4
- Clients seeing "Lost connection to MySQL server during query" errors HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from proxysql.