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