-
-
Notifications
You must be signed in to change notification settings - Fork 296
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
[docs] Add database maintenance section; update info message on ANALYZE run (sqlite) #2756
Merged
Conversation
This file contains 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
if you're now going to not limit the size of the analyze run and instead using this for documentation changes, i think it'd be worth renaming the PR |
Will do yeah :) |
tsmethurst
changed the title
[chore] Limit size of
[docs] Add database maintenance section; update info message on ANALYZE run (sqlite)
Mar 14, 2024
ANALYZE
run after migration (sqlite)
daenney
reviewed
Mar 14, 2024
daenney
approved these changes
Mar 14, 2024
If this is the case, you can try manually running a full `ANALYZE` command, by doing the following: | ||
|
||
1. Stop GoToSocial. | ||
2. While connected to your GoToSocial database file in the `sqlite3` shell, run `PRAGMA analysis_limit=0; ANALYZE;` (this may take quite a few minutes). |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others. Learn more.
Suggested change
2. While connected to your GoToSocial database file in the `sqlite3` shell, run `PRAGMA analysis_limit=0; ANALYZE;` (this may take quite a few minutes). | |
2. While connected to your GoToSocial database file in the `sqlite3` shell, run `ANALYZE;` (this may take quite a few minutes). |
There's not much point to PRAGMA analysys_limit=0
, it's the same thing as not setting it at all.
Co-authored-by: Daenney <daenney@users.noreply.github.com>
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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
The full ANAYLZE command we run for SQLite after each DB migration is not really necessary, and takes bloody ages (minutes and minutes on my 1cpu 1gb ram machine). This PR updates the post-migration ANALYZE command to use an analysis limit of 10000, which should be plenty (for comparison, we use 1000 for on-close callbacks).Correction to the above: I've been messing about with running partial and full analyzes, and it turns out that running
PRAGMA anaylsis_limit=10000; ANALYZE;
actually doesn't cut it for the purposes of helping the query optimizer use the correct indexes.Here's the contents of
sqlite_stat1
for statuses after a fullANALYZE
:And here's the same after a partial
ANALYZE
:Huge difference in emphasis across some of the important indexes.
Running the statuses web view query after a full analyze uses the intended index:
Where running the same query after a partial analyze causes the query planner to not use correct indexes:
So in other words, we should actually keep that full analyze after all!
Also adds some v. basic docs about db maintenance, for SQLite at least.
Checklist
Please put an x inside each checkbox to indicate that you've read and followed it:
[ ]
->[x]
If this is a documentation change, only the first checkbox must be filled (you can delete the others if you want).
go fmt ./...
andgolangci-lint run
.