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 a300 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@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 | |



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Thu, 2007-12-13 at 09:46 -0600, Kevin Grittner wrote:

> 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.

Writing hint bits. Annoying isn't it? :-(

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

>>> On Thu, Dec 13, 2007 at 10:11 AM, in message
<1197562283.4255.1829.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
wrote:
> On Thu, 2007-12-13 at 09:46 -0600, Kevin Grittner wrote:
>
>> 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.
>
> Writing hint bits. Annoying isn't it? :-(

Surprising, anyway. If it allows subsequent operations to be
faster, I'll take it; although to a naive user it's not clear what
is known at vacuum time that the INSERT into the empty table
couldn't have inferred. Bulk loads into empty tables are a pretty
common use case, so if there was some way to set the hints on
insert, as long as the table started the database transaction
empty, nobody else is modifying it, and only inserts have occurred,
that would be a good thing. I'm speaking from the perspective of a
user, of course; not someone who would actually try to wrangle the
code into working that way.

Thanks for the explanation.

-Kevin




---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> ... although to a naive user it's not clear what
> is known at vacuum time that the INSERT into the empty table
> couldn't have inferred.

The fact that the INSERT actually committed.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

>>> On Thu, Dec 13, 2007 at 10:35 AM, in message <13267.1197563721@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> ... although to a naive user it's not clear what
>> is known at vacuum time that the INSERT into the empty table
>> couldn't have inferred.
>
> The fact that the INSERT actually committed.

Fair enough. I suppose that the possibility that of access before
the commit would preclude any optimization that would assume the
commit is more likely than a rollback, and do the extra work only in
the unusual case?

-Kevin




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Thu, 2007-12-13 at 10:39 -0600, Kevin Grittner wrote:
> >>> On Thu, Dec 13, 2007 at 10:35 AM, in message <13267.1197563721@sss.pgh.pa.us>,
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> >> ... although to a naive user it's not clear what
> >> is known at vacuum time that the INSERT into the empty table
> >> couldn't have inferred.
> >
> > The fact that the INSERT actually committed.
>
> Fair enough. I suppose that the possibility that of access before
> the commit would preclude any optimization that would assume the
> commit is more likely than a rollback, and do the extra work only in
> the unusual case?

No chance. There's an optimization of COPY I've not got around to as
yet, but nothing straightforward we can do with the normal case.

We might be able to have bgwriter set hint bits on dirty blocks, but the
success of that would depend upon the transit time of blocks through the
cache, i.e. it might be totally ineffective. So might be just overhead
for the bgwriter and worse, could divert bgwriter attention away from
what its supposed to be doing. That's a lot of work to fiddle with the
knobs to improve things and there's higher things on the list AFAICS.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Simon Riggs wrote:

> We might be able to have bgwriter set hint bits on dirty blocks, but the
> success of that would depend upon the transit time of blocks through the
> cache, i.e. it might be totally ineffective. So might be just overhead
> for the bgwriter and worse, could divert bgwriter attention away from
> what its supposed to be doing. That's a lot of work to fiddle with the
> knobs to improve things and there's higher things on the list AFAICS.

I don't think that works, because the bgwriter has no access to the
catalogs, therefore it cannot examine the page contents. To bgwriter,
pages are opaque.

--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"There is evil in the world. There are dark, awful things. Occasionally, we get
a glimpse of them. But there are dark corners; horrors almost impossible to
imagine... even in our worst nightmares." (Van Helsing, Dracula A.D. 1972)

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Thu, 13 Dec 2007, Simon Riggs wrote:
> On Thu, 2007-12-13 at 09:46 -0600, Kevin Grittner wrote:
> > 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.
>
> Writing hint bits. Annoying isn't it? :-(

Interesting thread. Now, I know absolutely nothing about how the data is
stored, but it strikes me as being non-optimal that every single block on
the disc needs to be written again just to update some hint bits. Could
those bits be taken out into a separate bitmap stored somewhere else? That
would mean the (relatively small) amount of data being written could be
written in a small sequential write to the disc, rather than very sparsely
over the whole table.

Matthew

--
If you let your happiness depend upon how somebody else feels about you,
now you have to control how somebody else feels about you. -- Abraham Hicks

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

>>> On Thu, Dec 13, 2007 at 3:40 PM, in message
<1197582010.4255.1912.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
wrote:
> On Thu, 2007-12-13 at 15:19 -0600, Kevin Grittner wrote:

>> So one would expect a write-intensive initial vacuum after a
>> PITR-style recovery?

> An interesting issue when running with full_page_writes off.

I'm curious. How does the full_page_writes setting affect this?

-Kevin




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Matthew <matthew@flymine.org> writes:
> Interesting thread. Now, I know absolutely nothing about how the data is
> stored, but it strikes me as being non-optimal that every single block on
> the disc needs to be written again just to update some hint bits. Could
> those bits be taken out into a separate bitmap stored somewhere else?

You are trying to optimize the wrong thing. Making vacuum cheaper at
the cost of making every tuple lookup more expensive is not going to
be a win.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org