Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2038D2E393C for ; Fri, 14 Dec 2007 13:13:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 47581-02-3 for ; Fri, 14 Dec 2007 13:13:02 -0400 (AST) Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id 21C592E3901 for ; Fri, 14 Dec 2007 13:13:01 -0400 (AST) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Fri, 14 Dec 2007 11:13:00 -0600 Message-Id: <47626534.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Fri, 14 Dec 2007 11:12:52 -0600 From: "Kevin Grittner" To: Subject: VACUUM FREEZE output more than double input Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/222 X-Sequence-Number: 28038 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. =20 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. =20 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 =20 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. =20 Why double writes per read, plus massive writes at checkpoint? =20 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. =20 -Kevin =20 =20 PostgreSQL 8.2.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) = 4.1.2 20070115 (prerelease) (SUSE Linux) =20 listen_addresses =3D '*' port =3D 5412 max_connections =3D 200 shared_buffers =3D 160MB temp_buffers =3D 50MB work_mem =3D 32MB maintenance_work_mem =3D 1GB max_fsm_pages =3D 800000 bgwriter_lru_percent =3D 20.0 bgwriter_lru_maxpages =3D 200 bgwriter_all_percent =3D 10.0 bgwriter_all_maxpages =3D 600 wal_buffers =3D 1MB checkpoint_segments =3D 50 checkpoint_timeout =3D 30min seq_page_cost =3D 0.5 random_page_cost =3D 0.8 effective_cache_size =3D 63GB geqo =3D off from_collapse_limit =3D 15 join_collapse_limit =3D 15 redirect_stderr =3D on log_line_prefix =3D '[%m] %p %q<%u %d %r> ' stats_block_level =3D on stats_row_level =3D on autovacuum =3D on autovacuum_naptime =3D 10s autovacuum_vacuum_threshold =3D 1 autovacuum_analyze_threshold =3D 1 datestyle =3D 'iso, mdy' lc_messages =3D 'C' lc_monetary =3D 'C' lc_numeric =3D 'C' lc_time =3D 'C' escape_string_warning =3D off standard_conforming_strings =3D on sql_inheritance =3D off =20 BINDIR =3D /usr/local/pgsql-8.2.5/bin DOCDIR =3D /usr/local/pgsql-8.2.5/doc INCLUDEDIR =3D /usr/local/pgsql-8.2.5/include PKGINCLUDEDIR =3D /usr/local/pgsql-8.2.5/include INCLUDEDIR-SERVER =3D /usr/local/pgsql-8.2.5/include/server LIBDIR =3D /usr/local/pgsql-8.2.5/lib PKGLIBDIR =3D /usr/local/pgsql-8.2.5/lib LOCALEDIR =3D MANDIR =3D /usr/local/pgsql-8.2.5/man SHAREDIR =3D /usr/local/pgsql-8.2.5/share SYSCONFDIR =3D /usr/local/pgsql-8.2.5/etc PGXS =3D /usr/local/pgsql-8.2.5/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE =3D '--prefix=3D/usr/local/pgsql-8.2.5' '--enable-integer-datetim= es' '--enable-debug' '--disable-nls' CC =3D gcc CPPFLAGS =3D -D_GNU_SOURCE CFLAGS =3D -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline = -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g CFLAGS_SL =3D -fpic LDFLAGS =3D -Wl,-rpath,'/usr/local/pgsql-8.2.5/lib' LDFLAGS_SL =3D LIBS =3D -lpgport -lz -lreadline -lcrypt -ldl -lm VERSION =3D PostgreSQL 8.2.5 =20 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 31 13:07:27 UTC 2007 kgrittn@SOCRATES:~> cat /etc/SuSE-release SUSE Linux Enterprise Server 10 (x86_64) VERSION =3D 10 PATCHLEVEL =3D 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 =20