Re: 10+hrs vs 15min because of just one index

We've done a lot of testing on large DB's with a lot of "inserts" and
have a few comments.

The updates are "treated" as a large "insert" as we all know from pg's
point of view.

We've run into 2 classes of problems: excessing WAL checkpoints and
affects of low correlation.

WAL log write's full 8K block for first modification, then only changes.
This can be the source of "undesireable" behaviour during large batch
inserts like this.

From your config, a check point will be forced when

(checkpoint_segments * 16 M) < rows * (8K/N*h + (1-h)*8K) * B

Where h is the "hitrate" or correlation between the update scan and the
index. Do you have a sense of what this is? In the limits, we have 100%
correlation or 0% correlation. N is the lower cost of putting the
change in the WAL entry, not sure what this is, but small, I am
assuming, say N=100. B is the average number of blocks changed per
updated row (assume B=1.1 for your case, heap,serial index have very
high correlation)

In the 0% correlation case, each updated row will cause the index update
to read/modify the block. The modified block will be entirely written to
the WAL log. After (30 * 16M) / (8K) / 1.1 ~ 55k rows, a checkpoint
will be forced and all modified blocks in shared buffers will be written
out.

Increasing checkpoint_segments to 300 and seeing if that makes a
difference. If so, the excessive WAL checkpoints are your issue. If
performance is exactly the same, then I would assume that you have close
to 0% correlation between the rows in the heap and index.

Can you increase shared_buffers? With a low correlation index, the only
solution is to hold the working set of blocks in memory. Also, make
sure that the checkpoint segments are big enough for you to modify them
in place, don't want checkpoints occurring....

Note that the more updates you do, the larger the tables/index become
and the worse the problem becomes. Vacuuming the table is an "answer"
but unfortunately, it tends to decrease correlation from our
observations. :-(

From our observations, dropping index and rebuilding them is not always
practical, depends on your application; table will be exclusively locked
during the transaction due to drop index.

I haven't looked at pg's code for creating an index, but seriously
suspect it's doing an extern sort then insert into the index. Such
operations would have 100% correlation from the index insert point of
view and the "sort" could be in memory or the tape variety (more
efficient i/o pattern).

Summary, # of indexes, index correlation, pg's multi versioning,
shared_buffers and checkpoint_segments are interconnected in weird and
wonderful ways... Seldom have found "simple" solutions to performance
problems.

Marc

> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of
> Aaron Turner
> Sent: Friday, February 10, 2006 3:17 AM
> To: pgsql-performance(at)postgresql(dot)org
> Subject: [PERFORM] 10+hrs vs 15min because of just one index
>
> So I'm trying to figure out how to optimize my PG install
> (8.0.3) to get better performance without dropping one of my indexes.
>
> Basically, I have a table of 5M records with 3 columns:
>
> pri_key (SERIAL)
> data char(48)
> groupid integer
>
> there is an additional unique index on the data column.
>
> The problem is that when I update the groupid column for all
> the records, the query takes over 10hrs (after that I just
> canceled the update). Looking at iostat, top, vmstat shows
> I'm horribly disk IO bound (for data not WAL, CPU 85-90%
> iowait) and not swapping.
>
> Dropping the unique index on data (which isn't used in the
> query), running the update and recreating the index runs in
> under 15 min.
> Hence it's pretty clear to me that the index is the problem
> and there's really nothing worth optimizing in my query.
>
> As I understand from #postgresql, doing an UPDATE on one
> column causes all indexes for the effected row to have to be
> updated due to the way PG replaces the old row with a new one
> for updates. This seems to explain why dropping the unique
> index on data solves the performance problem.
>
> interesting settings:
> shared_buffers = 32768
> maintenance_work_mem = 262144
> fsync = true
> wal_sync_method = open_sync
> wal_buffers = 512
> checkpoint_segments = 30
> effective_cache_size = 10000
> work_mem = <default> (1024 i think?)
>
> box:
> Linux 2.6.9-11EL (CentOS 4.1)
> 2x Xeon 3.4 HT
> 2GB of RAM (but Apache and other services are running)
> 4 disk raid 10 (74G Raptor) for data
> 4 disk raid 10 (7200rpm) for WAL
>
> other then throwing more spindles at the problem, any suggestions?
>
> Thanks,
> Aaron
>
> --
> Aaron Turner
> http://synfin.net/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>