Prevent unnecessary VACUUM executions for SQLite3 stats database - Closes #4856#4859
Merged
renecannao merged 4 commits intov3.0from Mar 12, 2025
Merged
Prevent unnecessary VACUUM executions for SQLite3 stats database - Closes #4856#4859renecannao merged 4 commits intov3.0from
renecannao merged 4 commits intov3.0from
Conversation
Introduced generic approach based on the potential benefits for 'VACUUM' over the 'stats' database instead of the previous selective, table based, approach.
Measurements ('perf') revealed that 'prepare' from 'execute' where
taking roughtly half the execution time of the queries used for table
generation.
4 tasks
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
2 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Description / Main work
This PR introduces a generic approach for performing
VACUUMoperations overstatsdatabase, performing them only when these operations could result in space optimization for the database. The PR also introduces other optimization, substituting simple query executions with prepared statements forSQLite. These optimizations alone should be enough to solve the increase onCPUusage that was affecting testreg_test_3765_ssl_pollout-t, described in issue #4828, other changes required in that issue description are also included in this PR.The load used for getting the
perfdata was just a loop of the querySELECT Variable_Name, Variable_Value FROM stats_mysql_global. After the two main optimizations introduced, we can see howperfreport has changed:original:patched:These optimizations are enough to keep most of the CPU time on the actual query result computation.