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 [EMAIL PROTECTED] so > that your > message can get through to the mailing list cleanly ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
