After upgrading from 7.9 LTS → 8.9 LTS, we are seeing our database size grow by 3gb a day, when before the update, the db size was barely increasing in size at all.
Specs:
7.9.1 → 8.9.0.43852
PostgreSQL 11.10; driver 42.2.19
The table growing most seems to be the file_sources table
Housekeeping settings are all default
We have 955 projects
We have 8.9 million LOC
Our database was 30gb before upgrade, and is 60gb now 2 weeks after upgrading
Please let us know if there is a new setting we’re missing that can alleviate this, or if this is expected and we simply need to increase our storage size.
Greetings @lukebickell
sorry about this late answer.
Were you able to solve this mystery in the last couple of weeks?
Did you measure some 3GB increase day after day, or did you come upon this 30GB increase after 10 days?
I’m asking because we have documented as Additional Database Maintenance a recommendation to run a VACUUM after the upgrade on PostgreSQL.
What we determined is that there were a few very large projects (1m+ lines) that were storing some 13k file sources per branch, of which there were dozens (56 in one project’s case).
What we did was simply decrease the branch-lifetime from 30 days to 7 to reduce the impact of branches.
We guessed that projects started storing more data per branch in 8.9 than in 7.9 leading to the increase
Hi Luke
thanks for your answer and clarification, and you guessed correctly
Indeed branches have changed a lot between 7.9 and 8.9, and with the removal of the short-lived branch specific implementation, all of the code from each branch last analysis is now stored in DB, and in this table specifically.
There is no easy way to predict the DB impact for all SonarQube users, e.g. it is not expected to impact the majority of users relying mostly on PR analysis in their development flow.
Also a larger DB may not be a problem:
it is not expected to impact your DB or SonarQube performances much, SonarQube data model was designed for this and the additional written data is not expected to be read more.
DB size, related to this change, should peak after the sonar.dbcleaner.daysBeforeDeletingInactiveBranchesAndPRs period is reached (30 days per default), then it should be stable. In your case DB size would probably have stabilized, and resumed its previous average growth, around 100GB.
Decreasing the sonar.dbcleaner.daysBeforeDeletingInactiveBranchesAndPRs housekeeping parameter to 7 days has probably not improved IOPS or CPU usage much on your DB instance(?), it shrunk its size though.
Keeping a feature branch analysis for more than 7 days is probably rarely needed, but in case it would be, you may allow more than 7 days for it, with additional costs only bound to the increased DB storage.
Thanks for the extra info! We weren’t worried about performance – our backup system has an implicit size limit, so we were worried that we would end up exceeding that limit. So far, we haven’t heard any complaints from our 100+ users about the 7 day limit, so that seems to be the best solution for us right now.
Hopefully, we’ll be able to update our CI soon to start utilizing PR analysis and lessen this issue!
