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.)
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.
Here's the current tail of the VACUUM ANALYZE VERBOSE output:
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"
And here's a snippet from vmstat 1 output:
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
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?
-Kevin
PostgreSQL 8.2.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070115 (prerelease) (SUSE Linux)
ccsa(at)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
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
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")
Schema | Name | Type | Modifier | Check
--------+------------+----------+----------+-------
public | CountyNoT | smallint | |
public | DocIdT | integer | |
public | SectionNoT | integer | |
public | ImageT | bytea | |