Re: 3 million record updates

Jie Liang,

PostgreSQL needs to keep track of you 3 million updates
until you perform a commit.

So one way to avoid the WAL contention is to use a FOR
loop and issuing a commit every 1,000 records or so.

Regards,
Nikolaus Dilger

Jie Liang wrote:

>
> Tom,
>
> I tried to update a table with ~3 million records a
> set=null,
> it takes a hour unfinished. I checked my vmstat, it
> shown that disk where
> the WAL written
> was 100% busy, so I think we reach the bottleneck.
What
> else parameters I
> can turn?
> or I misconfigured?
>
> my current pg conf:
>
> shared_buffers=60000
> wal_buffers=8
> sort_mem=512
> vacuum_mem=8192
>
> OS conf:
> shmmax=535404544
> shmmin=1
> shmmni=192
> shmseg=256
> shmall=130714
> shm_use_phys=1
>
> OS: FreeBSD-4.6.2
> DB: Postgresql-7.2.1
>
> Thanks.
>
> Jie Liang
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send
> an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org
so
> that your
> message can get through to the mailing list cleanly