Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 583FD2E017C for ; Thu, 13 Dec 2007 11:46:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 74196-07 for ; Thu, 13 Dec 2007 11:46:26 -0400 (AST) Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id 01D232E00DE for ; Thu, 13 Dec 2007 11:46:25 -0400 (AST) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Thu, 13 Dec 2007 09:46:23 -0600 Message-Id: <4760FF69.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Thu, 13 Dec 2007 09:46:17 -0600 From: "Kevin Grittner" To: Subject: Heavy write activity on first vacuum of fresh TOAST data 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/184 X-Sequence-Number: 28000 Yesterday we moved a 300 GB table containing document images (mostly = raster-scanned from paper), into a 215 GB PostgreSQL 8.2.5 database which = contains the related case management data. (This separation was never = "right", since there were links from one to the other, but was necessary = under our previous database package for practical reasons.) =20 The data was inserted through a Java program using a prepared statement = with no indexes on the table. The primary key was then added, and now = I've started a vacuum. The new table wound up being the first big table = vacuumed, and I noticed something odd. Even though there have been no = rollbacks, updates, or deletes on this table, the vacuum is writing as = much as it is reading while dealing with the TOAST data. =20 Here's the current tail of the VACUUM ANALYZE VERBOSE output: =20 INFO: analyzing "pg_catalog.pg_auth_members" INFO: "pg_auth_members": scanned 0 of 0 pages, containing 0 live rows and = 0 dead rows; 0 rows in sample, 0 estimated total rows INFO: vacuuming "public.DocImage" INFO: index "DocImage_pkey" now contains 2744753 row versions in 10571 = pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.15s/0.01u sec elapsed 0.28 sec. INFO: "DocImage": found 0 removable, 2744753 nonremovable row versions in = 22901 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 32 pages contain useful free space. 0 pages are entirely empty. CPU 0.46s/0.10u sec elapsed 1.12 sec. INFO: vacuuming "pg_toast.pg_toast_7729979" =20 And here's a snippet from vmstat 1 output: =20 procs -----------memory---------- ---swap-- -----io---- -system-- = -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id = wa st 0 1 156 316500 556 63491808 0 0 58544 57910 1459 2417 2 4 = 85 9 0 0 1 156 317884 556 63490780 0 0 61240 62087 1425 2106 2 5 = 84 9 0 0 3 156 307500 556 63499004 0 0 56968 57882 1472 2091 2 5 = 84 10 0 2 0 156 309280 556 63497976 0 0 59920 58218 1600 4503 5 4 = 79 11 0 0 1 156 313592 556 63494892 0 0 57608 62371 1695 3425 3 5 = 84 8 0 2 1 156 305844 556 63502088 0 0 54568 58164 1644 2962 3 4 = 84 9 0 0 1 156 306560 556 63502088 0 0 61080 57949 1494 2808 3 5 = 83 9 0 1 0 156 303432 552 63505176 0 0 49784 53972 1481 2629 2 4 = 84 10 0 0 1 156 308232 552 63500036 0 0 57496 57903 1426 1954 1 4 = 85 9 0 1 0 156 309008 552 63499008 0 0 62000 61962 1442 2401 2 4 = 85 8 0 =20 It's been like this for over half an hour. Not that I expect a vacuum of = a 300 GB table to be blindingly fast, but if the data has just been = inserted, why all those writes? =20 -Kevin =20 PostgreSQL 8.2.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) = 4.1.2 20070115 (prerelease) (SUSE Linux) =20 ccsa@SOCRATES:/var/pgsql/data/cc> free -m total used free shared buffers cached Mem: 64446 64147 298 0 0 62018 -/+ buffers/cache: 2128 62318 Swap: 1027 0 1027 =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 Table "public.DocImage" Column | Type | Modifiers -----------+--------------+----------- countyNo | "CountyNoT" | not null docId | "DocIdT" | not null sectionNo | "SectionNoT" | not null docImage | "ImageT" | not null Indexes: "DocImage_pkey" PRIMARY KEY, btree ("countyNo", "docId", "sectionNo") =20 Schema | Name | Type | Modifier | Check --------+------------+----------+----------+------- public | CountyNoT | smallint | | public | DocIdT | integer | | public | SectionNoT | integer | | public | ImageT | bytea | | =20