Hi, On Wed, Jan 06, 2021 at 09:55:08AM -0800, Andres Freund wrote: > On 2021-01-06 12:02:40 -0500, Stephen Frost wrote: > > * Andres Freund (and...@anarazel.de) wrote: > > > On 2021-01-04 19:11:43 +0100, Michael Banck wrote: > > > > This looks much better from the WAL size perspective, there's now almost > > > > no additional WAL. However, that is because pgbench doesn't do TOAST, so > > > > in a real-world example it might still be quite larger. Also, the vacuum > > > > runtime is still 15x longer. > > > > > > That's obviously an issue. > > > > It'd certainly be nice to figure out a way to improve the VACUUM run but > > I don't think the impact on the time to run VACUUM is really a good > > reason to not move forward with changing the default. > > Vacuum performance is one of *THE* major complaints about > postgres. Making it run slower by a lot obviously exascerbates that > problem significantly. I think it'd be prohibitively expensive if it > were 1.5x, not to even speak of 15x.
To maybe clarify, the vacuum slowdown is just as large in my (somewhat contrived as a worst-case scenario) tests when wal_log_hints is on and not data_checksums, I just ommitted those numbers due to being basically identical (or maybe a bit worse even): |data_checksums=off, wal_log_hints=off: | |done in 10.24 s (vacuum 3.31 s, primary keys 6.92 s). |done in 8.81 s (vacuum 2.72 s, primary keys 6.09 s). |done in 8.35 s (vacuum 2.32 s, primary keys 6.03 s). | |data_checksums=off, wal_log_hints=on: | |1,5G data1/pg_wal |1,5G data1/base |2,5G data1_archive/ | |done in 87.89 s (vacuum 69.67 s, primary keys 18.23 s). |done in 73.71 s (vacuum 60.19 s, primary keys 13.52 s). |done in 75.12 s (vacuum 62.49 s, primary keys 12.62 s). | |data_checksums=on, wal_log_hints=off: | |done in 67.42 s (vacuum 54.57 s, primary keys 12.85 s). |done in 65.03 s (vacuum 53.25 s, primary keys 11.78 s). |done in 77.57 s (vacuum 62.64 s, primary keys 14.94 s). Of course, wal_log_hints is not the default either and can be turned off easily. You mostly lose the ability to run pg_rewind I think, are there other use-cases for it? Michael -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: michael.ba...@credativ.de credativ GmbH, HRB Mönchengladbach 12080 USt-ID-Nummer: DE204566209 Trompeterallee 108, 41189 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer Unser Umgang mit personenbezogenen Daten unterliegt folgenden Bestimmungen: https://www.credativ.de/datenschutz