PostgreSQL / No autovacuum

Discuss with the community any ideas you'd love to see in future DiskStations and DSM updates! We do our best to monitor and forward all of them, but we recommend to also use this form as our team will systematically see your suggestion:
https://www.synology.com/form/inquiry/feature
Forum rules
We've moved! Head over to Synology Community (community.synology.com) to meet up with our team and other Synology enthusiasts!
asynouser
I'm New!
I'm New!
Posts: 2
Joined: Wed Nov 29, 2017 5:28 pm

PostgreSQL / No autovacuum

Unread post by asynouser » Wed Nov 29, 2017 5:55 pm

Hi,

I am very surprised that the autovacuum is disabled in the PostgreSQL database used by dsm.

Code: Select all

postgres=# show autovacuum;
 autovacuum
------------
 off
(1 row)
I ran a vacuum full on the database on the database became a lot smaller. (I just got my syno It's nearly empty)

Code: Select all

      Name      |      Owner      | Encoding  | Collate | Ctype |   Access privileges   |  Size   | Tablespace |                Description
----------------+-----------------+-----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
 download       | DownloadStation | SQL_ASCII | C       | C     |                       | 6793 kB | pg_default |
 mediaserver    | MediaIndex      | SQL_ASCII | C       | C     |                       | 50 MB   | pg_default |
 photo          | PhotoStation    | SQL_ASCII | C       | C     |                       | 24 MB   | pg_default |
 postgres       | postgres        | SQL_ASCII | C       | C     |                       | 6154 kB | pg_default | default administrative connection database
 template0      | postgres        | SQL_ASCII | C       | C     | =c/postgres          +| 5993 kB | pg_default | unmodifiable empty database
                |                 |           |         |       | postgres=CTc/postgres |         |            |
 template1      | postgres        | SQL_ASCII | C       | C     | =c/postgres          +| 6041 kB | pg_default | default template for new databases
                |                 |           |         |       | postgres=CTc/postgres |         |            |
 video_metadata | VideoStation    | SQL_ASCII | C       | C     |                       | 499 MB  | pg_default |
(7 rows)
after vacuum full:

Code: Select all

      Name      |      Owner      | Encoding  | Collate | Ctype |   Access privileges   |  Size   | Tablespace |                Description
----------------+-----------------+-----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
 download       | DownloadStation | SQL_ASCII | C       | C     |                       | 5505 kB | pg_default |
 mediaserver    | MediaIndex      | SQL_ASCII | C       | C     |                       | 26 MB   | pg_default |
 photo          | PhotoStation    | SQL_ASCII | C       | C     |                       | 20 MB   | pg_default |
 postgres       | postgres        | SQL_ASCII | C       | C     |                       | 5146 kB | pg_default | default administrative connection database
 template0      | postgres        | SQL_ASCII | C       | C     | =c/postgres          +| 5993 kB | pg_default | unmodifiable empty database
                |                 |           |         |       | postgres=CTc/postgres |         |            |
 template1      | postgres        | SQL_ASCII | C       | C     | =c/postgres          +| 5033 kB | pg_default | default template for new databases
                |                 |           |         |       | postgres=CTc/postgres |         |            |
 video_metadata | VideoStation    | SQL_ASCII | C       | C     |                       | 203 MB  | pg_default |
(7 rows)
I dont understand the rationale behind disabling autovacuum on a 9.3 PostgreSQL database.
It's the cleanup process and the process responsible for automatic stat calculation.

I didnt find much info on this except for [1]

Please note that :
  • doing a "vacuum full" under heavy load is not the best idea. It takes an Exclusive lock on the tables preventing transaction to read / write from them during the operation [2]. I did it because I am alone and autovacuum was off.
  • It also requiers some space to create the "new" table.
  • vacuum full is usually not needed except if you delete a lot of lines (or dont run autovacuum).
[1] https://forum.synology.com/enu/viewtopic.php?t=43942
[2] https://www.postgresql.org/docs/9.3/sta ... acuum.html

asynouser
I'm New!
I'm New!
Posts: 2
Joined: Wed Nov 29, 2017 5:28 pm

Re: PostgreSQL / No autovacuum

Unread post by asynouser » Thu Jan 04, 2018 11:07 am

Here is what I have done:

Code: Select all

track_activities = on
track_counts = on
autovacuum = on
The Track* are prerequisites for the autovacuum. I dont see any drawbacks to this.

I also notice some :

Code: Select all

2017-12-19T16:06:21+01:00 dsbenoit postgres[27245]: [16-1] LOG:  checkpoints are occurring too frequently (2 seconds apart)
2017-12-19T16:06:21+01:00 dsbenoit postgres[27245]: [16-2] HINT:  Consider increasing the configuration parameter "checkpoint_segments".
This warning occures because the amount of wal needed to trigger a checkpoint is too small [1][2]. Thus postgres is flushing all the time to disk. The performance impact his big. This is made worse by full_pages_writes. For each new modification after a checkpoint postgres will write the full page in the transaction log (wal), for the following modifications of theses pages only the modified part is loggued. (this is important to protect from corruptions). So we are in a situation where writes trigger more writes.

I increased checkpoint_segments to spread checkpoints to the ideal chekpoint_timeout (5mn by default) and checkpoint_completion_target to spread the writes during checkpoint.
There is no drawback except a little more space used on the system at peak time (we are talking of under 500GB of space).

Code: Select all

checkpoint_segments = 10
checkpoint_completion_target = 0.9
I noticed that some upgrades do modify max_connections which I kind of understand (more apps = more connections) and kind of don't (what if my app needs more connections and an upgrade decreases it, maybe it's supported in the api ?).

[1] https://www.postgresql.org/docs/9.3/sta ... point.html
[2] https://www.postgresql.org/docs/9.3/sta ... ation.html
[3} https://www.postgresql.org/docs/9.3/sta ... AGE-WRITES

NB: when I say there is now draw back please read no draw back that I know of. So use at your own risk this light not be supported blablala

Locked

Return to “Feature Requests & Product Improvement Suggestions”