User story #2974
closed
Archive reports does not free space on disk
Added by Vincent MEMBRÉ about 12 years ago.
Updated over 9 years ago.
Description
When archiving reports, reports size on disk does not change.
lines from "ruddersysevents" are correctly inserted in "archivedsysevents" and are correctly deleted, but the datatable size stays the same.
processing a request "vacuum full ruddersysevents" free it effectively.
problem, a vacuum full you have to be veeeeeeeery careful when processing a vacuum full, as it locks the database.
A solution would be to process "vacuum" regurlarly and to add the possibility to proceed a vacuum full rarely (one time per month), by default no vacuum full
We should add a temporary table to store datas, when a vacuum full is at work.
Indeed, VACUUM FULL is frowned upon if it's not done outside on maintenance windows ( http://serverfault.com/questions/264221/postgresql-8-4-auto-vacuum-is-enabled-to-get-the-best-performance last answer) for it locks the database.
Erasing gigs of data inside a table can be a very long process (in terms of dozens of minutes), effectively blocking the whole application (and log collection)
The archived reports can be, I feel, safely vacuum fulled, it would prevent other archiving during this time, and people could leave with it
Setting a temporary table would probably not solve the issue, and would have too much impact to allow the release of Rudder 2.4 in November (it would impact log collection and reporting)
Yes for temporary table, I was just keeping a track about that idea to explore in a following version (2.5).
I'm ok with the idea of having a vacuum full only on the archived reports, it should not be a problem
- Status changed from New to 2
The chosen solution is:
- before each cleaning, check if a full vaccuum is currently running, and if so, fail the cleaning, adding a log message (and an error message for the user if the clean request was interactive) explaining why the cleaning can not occures at that time.
- after each cleaning (archive or delete), do:
- a (simple) vaccuum on table Reports
- a full caccuum on table Archive Reports
These actions have to be performed by the Scala / SQL code performing the cleaning.
François ARMAND wrote:
The chosen solution is:
- before each cleaning, check if a full vaccuum is currently running, and if so, fail the cleaning, adding a log message (and an error message for the user if the clean request was interactive) explaining why the cleaning can not occures at that time.
I've looked in the Postgres documentation and forum, there doesn't seems to be an easy way to know if a vacuum is in progress. Here there might be something that could be an inspiration on how to do it: http://pgsql.inb4.se/2009/february/follow-up-vacuum-full-progress.html
- Status changed from 2 to Pending technical review
We decided to perform a vacuum full on archived reports and a vacuum on "active" reports
so disk space will be freed by rudder on archived reports, but not block the active reports table, I think this is the best compromise we can do for now
I made a pull request about that, https://github.com/Normation/rudder/pull/4
I change this ticket status into "Technical review"
- Target version changed from 2.4.0~rc1 to 2.5.0~beta1
The new feature about automation of DB management tasks will not be included in Rudder 2.4. Then, postponed to Rudder 2.5
- Assignee set to Nicolas CHARLES
- Tracker changed from Bug to Enhancement
- Status changed from Pending technical review to 12
This is valid, thank you Vincent
I have no idea why this ticket is in rudder-doc, and I can't change its project
- Status changed from 12 to Pending release
- % Done changed from 0 to 100
- Status changed from Pending release to Released
- Project changed from 30 to Rudder
- Category set to Documentation
- Tracker changed from Enhancement to User story
Also available in: Atom
PDF
Updated by 
Updated by 
Updated by 
Updated by Nicolas PERRON 
Updated by Matthieu CERDA 
Updated by