Re: tune postgres for UPDATE

On Mon, Dec 8, 2008 at 3:28 AM, Sebastian Böhm <seb(at)exse(dot)net> wrote:
> Hi,
>
> I have a table with a lot of columns (text and integer).
>
> It currently has 3Mio Rows.
>
> Updating a column in all rows (integer) takes endless (days).

I'm afraid you may not understand how postgresql's MVCC implementation
works here. Updating a row creates a new copy of the row and leaves
the old copy in place. Running such an update several times in a row
can result in a table that is mostly dead space and very slow to
access, both for reads and writes.

What does vacuum verbose tablename say about your table?

Is there a valid reason you're updating every row? Do they all really
need to change?

> How can I tune postgres to do this much more quickly?

Get a faster hard drive.

> VMstat looks like this:
> r b swpd free buff cache si so bi bo in cs us sy id
> wa
> 0 1 188 14160 16080 867064 0 0 880 888 168 479 1 2 0
> 97
> 1 1 188 15288 16080 865980 0 0 832 512 152 474 7 2 0
> 91
> 0 1 188 15464 16080 865348 0 0 872 592 144 461 2 1 0
> 97

Wow, that's a REALLY REALLY slow drive subsystem. Here's the numbers
from my laptop while updating a similar table, with 1.2 million rows
(update table xxx set y=y+1 kinda query):

procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
2 0 43124 30344 79804 2709708 0 0 16 21156 913 2456 22 4 38 36
0 3 43124 26472 79808 2713384 0 0 80 20232 725 2163 22 2 44 32
0 2 43124 25656 79508 2714084 0 0 148 24200 706 2187 31 4 36 29
0 2 43124 29336 79400 2710700 0 0 0 23616 788 2577 36 5 33 26

Note that I'm writing out at 20+megs a second, you're not even hitting
1Meg. I've got pretty slow USB memory sticks that hit 8 to 10 megs a
second.

> so mostly iowait.
>
> iostat shows about 10000 block writes per second.

Then either iostat or vmstat are lying to you. 10000 1k blocks per
second is about 10 times as fast as we're seeing in vmstat.

>
> My systems is debian-lenny (postgresql 8.3.5)
>
> I already increased checkpoint_segments to 32, shared_buffers to 200MB
>
> I also tried do disable autovacuum

Probably not your best move. it's there for a good reason. You can
tune it to make it more or less aggresive, but this kind of update is
likely causing plenty of bloating and turning off autovacuum is likely
counterproductive.

>
>
> here is a sample statement:
>
> update users set price = (select price from prices where type =
> 'normal_price' and currency = users.currency)

Any way to make that selective so it only updates the prices that need
to be updated?

> (the table price only has 30 rows)

Then why don't you just FK to point to it instead of this?