VACUUM FREEZE output more than double input - Mailing list pgsql-performance

I'm grooming a new server to replace one that is soon to be retired.
Most of the data was loaded very close together, in terms of
database transaction numbers, and probably 95% of it will never be
updated.  To assess the potential impact of a "freeze everything in
the database at once" sort of night, I started a VACUUM FREEZE at
the database level, to see what the load looked like.

This seems to be a remarkably good way to cause extreme checkpoint
spikes under 8.2.5, even with an aggressive background writer.  The
I/O pattern was surprising in other respects, too, so I'm looking
to see if someone can help me understand it.

 1  0    156 314760   1888 63457580    0    0 19704 39107 3070 11681  3  4 84 10  0
 0  2    156 313004   1888 63459636    0    0 17176 34654 2807 10696  2  5 84  9  0
 1  0    156 311020   1888 63461692    0    0 17152 34288 2662 10675  4  4 83 10  0
 0  2    156 306404   1888 63465804    0    0 20056 40488 3085 12500  1  3 86  9  0
 3  2    156 304780   1888 63468888    0    0 16936 33403 2798 11424  5  4 80 10  0
 3  2    156 304236   1888 63468888    0    0 15768 37570 3066 10988  3  3 82 12  0
 0  2    156 311384   1888 63462720    0    0 23800 48821 3866 14732  2  5 76 17  0
 3  1    156 304244   1888 63468888    0    0 22440 46684 3609 13133  2  4 79 14  0
 1  2    156 313672   1888 63459636    0    0 21528 43784 3433 12416  2  4 74 20  0
 1  3    156 311856   1888 63461692    0    0 16968 101366 2876 8769  2  7 71 20  0
 0  6    156 307892   1888 63464776    0    0  3824 71225 1178 2592  0  1 79 20  0
 0  5    156 316172   1888 63456552    0    0  6904 99629 1883 5645  3  2 78 17  0
 0  8    156 313232   1888 63459636    0    0  2880 82617 1259 3196  2  1 68 29  0
 0  7    156 310892   1888 63461692    0    0  2384 81262 1118 4415  4  1 55 40  0
 0  5    156 317728   1888 63453468    0    0  8616 104245 2080 8266  5  3 64 29  0
 0  8    156 314368   1888 63457580    0    0  3352 82142 1280 4316  2  1 67 30  0
 0  4    156 310160   1888 63460664    0    0  3928 96361 1466 3885  1  1 70 28  0
 0  9    156 308240   1896 63462712    0    0  1880 77801 1092 2665  1  1 64 33  0
 1  1    156 313044   1904 63460648    0    0 10568 61796 2423 8942  4  2 65 29  0
 1  3    156 311952   1904 63461676    0    0 16112 84713 3038 9919  3  6 69 22  0
 1  2    156 304212   1904 63469900    0    0 23200 78289 4094 14690  3  5 72 20  0
 1  2    156 310516   1896 63463740    0    0 24384 52418 3995 14139  4  4 70 23  0
 1  2    156 303192   1896 63470936    0    0 22608 46513 3689 10554  2  4 73 21  0
 1  2    156 314660   1896 63459628    0    0 19464 40452 3362 9239  1  5 74 20  0
 0  2    156 305652   1896 63467852    0    0 24080 49241 3803 10274  2  4 74 20  0
 0  2    156 312012   1896 63461684    0    0 24360 49745 3995 11190  2  4 71 23  0
 3  2    156 305596   1896 63466824    0    0 21896 45210 3670 12122  3  4 73 20  0

Note that outside of the checkpoints (where writes shoot up and
reads drop down), the writes track along at just over double the
reads.  This is on a database which has had relatively little
activity since the last database vacuum.

Why double writes per read, plus massive writes at checkpoint?

Is there any harm in doing a VACUUM FREEZE after loading from
pg_dump output, before putting the machine into production?
While the normal nightly vacuum, with scattered row freezes,
doesn't seem to cause any problems, a freeze on a mass scale
sure seems to do so.  I'd rather not slow down our regular
nightly vacuum to acommodate the mass freeze case at some
unpredicatable time.

-Kevin


 PostgreSQL 8.2.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070115 (prerelease) (SUSE Linux)

listen_addresses = '*'
port = 5412
max_connections = 200
shared_buffers = 160MB
temp_buffers = 50MB
work_mem = 32MB
maintenance_work_mem = 1GB
max_fsm_pages = 800000
bgwriter_lru_percent = 20.0
bgwriter_lru_maxpages = 200
bgwriter_all_percent = 10.0
bgwriter_all_maxpages = 600
wal_buffers = 1MB
checkpoint_segments = 50
checkpoint_timeout = 30min
seq_page_cost = 0.5
random_page_cost = 0.8
effective_cache_size = 63GB
geqo = off
from_collapse_limit = 15
join_collapse_limit = 15
redirect_stderr = on
log_line_prefix = '[%m] %p %q<%u %d %r> '
stats_block_level = on
stats_row_level = on
autovacuum = on
autovacuum_naptime = 10s
autovacuum_vacuum_threshold = 1
autovacuum_analyze_threshold = 1
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
escape_string_warning = off
standard_conforming_strings = on
sql_inheritance = off

BINDIR = /usr/local/pgsql-8.2.5/bin
DOCDIR = /usr/local/pgsql-8.2.5/doc
INCLUDEDIR = /usr/local/pgsql-8.2.5/include
PKGINCLUDEDIR = /usr/local/pgsql-8.2.5/include
INCLUDEDIR-SERVER = /usr/local/pgsql-8.2.5/include/server
LIBDIR = /usr/local/pgsql-8.2.5/lib
PKGLIBDIR = /usr/local/pgsql-8.2.5/lib
LOCALEDIR =
MANDIR = /usr/local/pgsql-8.2.5/man
SHAREDIR = /usr/local/pgsql-8.2.5/share
SYSCONFDIR = /usr/local/pgsql-8.2.5/etc
PGXS = /usr/local/pgsql-8.2.5/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/usr/local/pgsql-8.2.5' '--enable-integer-datetimes' '--enable-debug' '--disable-nls'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels
-fno-strict-aliasing-g 
CFLAGS_SL = -fpic
LDFLAGS = -Wl,-rpath,'/usr/local/pgsql-8.2.5/lib'
LDFLAGS_SL =
LIBS = -lpgport -lz -lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 8.2.5

kgrittn@SOCRATES:~> cat /proc/version
Linux version 2.6.16.53-0.8-smp (geeko@buildhost) (gcc version 4.1.2 20070115 (prerelease) (SUSE Linux)) #1 SMP Fri Aug
3113:07:27 UTC 2007 
kgrittn@SOCRATES:~> cat /etc/SuSE-release
SUSE Linux Enterprise Server 10 (x86_64)
VERSION = 10
PATCHLEVEL = 1
kgrittn@SOCRATES:~> free -m
             total       used       free     shared    buffers     cached
Mem:         64446      64145        300          0          1      61972
-/+ buffers/cache:       2171      62274
Swap:         1027          0       1027