New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Performance issue due to many parallel SQL queries #18823
Comments
Hi @utrautmann |
@utrautmann you could try and see if enabling the I would not expect such a query to be executed though that often in parallel or in parallel at all unless you launch heaps of archivers and many of the segments check if the url contains the url.
|
@tsteur
The customer explicitly requested this setting (enable_segments_cache =0) to get the old behavior. Can that be a reason for the problems? To your questions:
One further amendment: |
It's great on the segment reduction 👍 The settings above should be fine to keep if you specifically needed that behaviour that the customer required. I assume in this case it may be an issue of how many archivers run in parallel. There are options to limit the amount of archivers that run in parallel see https://matomo.org/docs/setup-auto-archiving/#help-for-corearchive-command Eg |
Thanks for creating this issue! We appreciate your input. This issue covers a lot of ground, and it's a bit too broad for us to tackle effectively as is. So, we're going to close it for now. FYI we've published several new guides at https://matomo.org/subcategory/improve-performance/ that may be of interest. But don't let this stop you! If you've got specific ideas or improvements related to this topic, feel free to open new issues for each one. That way, we can dive into them in more detail. |
A customer of mine has occasional performance problems in Matomo. The system hangs because all 150 database connections to MySQL are in use.
My analysis revealed that these problems could be related to the generation of segment reports, which put too much load on the MySQL DB.
To the background:
The customer has created about 40 segments. The reports are updated hourly by a cron job. Browser updates are disabled.
Analysis approach
From the analysis approach, we enabled the MySQL Slow Query Log and analyzed the outputs.
Of all slow SQL queries (defined as longer than 10sec), the statement
SELECT idaction FROM piwik_log_action WHERE ( name LIKE CONCAT('?', '?', '?') AND type = ? )
was determined in 90 percent.
Analysis results
An identified problem is certainly that the customer uses the matching URL with "contains" instead of "is" far too often in the segment definition. We will optimize this.
But when analyzing the Slow Query Log, I noticed that the same SQL query was issued almost simultaneously up to 50 times, which always returned the same results.
Sending a query to the database from Matomo 50 times more or less at the same time is very unusual.
My question is whether this can be optimized by caching?
Important to know: This problem does not always occur. So we don't have the problem every hour when the cron job starts.
Here is an anonymous example of the slow queries during segment creation as a short excerpt from the MySQL Slow Query Log. This query was made a total of 50 times and required an average of 13 seconds per query.
For clarity: The logging takes place after the statement has been completed.
The text was updated successfully, but these errors were encountered: