Add maintenance vacuum

[rubo77]

On a configurable time, once a week the homeserver.db should go down for maintenance to do a VACUUM to minimize the database size on the server.

It would be cool, if there was a maintenance mode, so the users see, they just have to wait some minutes.

[rubo77]

I tried vacuum on my 3.5GB homeserver.db, which took about 45minutes to finish, and in the end it was still 3.3GB, so it seems vacuum has no big effect. Maybe we can add some more maintenance, that shrinks the database file better?

I checked and found, that there are 28.886.226 entries in state_groups_state
can't I delete some old entries there?

[ara4n]

@erikjohnston has been working on precisely this.

On 10 Jul 2016, at 01:29, Ruben Barkow notifications@github.com wrote:

I tried vacuum on my 3.5GB homeserver.db, which took about 45minutes to finish, and in the end it was still 3.3GB, so it seems vacuum has no big effect. Maybe we can add some more maintenance, that scrinks the database file better?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub, or mute the thread.

[rubo77]

Do you mean this?

https://github.com/matrix-org/synapse/blob/master/docs/admin_api/purge_history_api.rst

[rubo77]

I created a gist file here: https://gist.github.com/rubo77/458eb4808d665e619feeaf556476f73f

But I need some more help

[rubo77]

#911 added this

so is there a vacuum needed after pruning old history?

[aperezdc]

FWIW, some databases may do additional work during a VACUUM which may result in improved performance, especially for very busy tables. In particular, for PostgreSQL it is documented that vacuuming will make the query planner and index accessing work faster:

PostgreSQL's VACUUM command has to process each table on a regular basis for several reasons:

1. To recover or reuse disk space occupied by updated or deleted rows.

2. To update data statistics used by the PostgreSQL query planner.

3. To update the visibility map, which speeds up index-only scans.

4. To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound.

Maybe it would be good idea to add a note to postgres.rst recommending automatic vacuuming in postgresql.conf, so Synapse does not need to it by itself (though it's something that maybe needs to be handled anyway in Synapse when using SQLite).

[richvdh]

postgres manages its own vacuums; it's not our job to sort it out.

[rubo77]

Maybe it would be good idea to add a note to postgres.md recommending automatic vacuuming in postgresql.conf

#8651