Skip site navigation (1) Skip section navigation (2)

Re: Update using primary key slow

From: Denis <denis(dot)sailer(at)yellowbook(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Update using primary key slow
Date: 2005-10-27 18:09:57
Message-ID: Xns96FC85EFAC158denissaileryellowboo@200.46.204.72 (view raw or flat)
Thread:
Lists: pgsql-performance
tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote in
news:19722(dot)1130429883(at)sss(dot)pgh(dot)pa(dot)us: 

> Denis <denis(dot)sailer(at)yellowbook(dot)com> writes:
>> The following update was captured in the database log and the elapsed
>> time was 1058.956 ms.  A later explain analyze shows total run time
>> of 730 ms.  Although isn't the actual time to update the row 183 ms. 
>> Where is the other 547 ms coming from?  Updating the two secondary
>> indexes?? 
> 
> The 183 msec is the time needed to *fetch* the row, not the time to
> update it.  So it could well be that the other time is just the time
> needed to update the table and indexes.  If this seems slower than
> your hardware ought to be able to handle, I'd wonder about how
> recently the table has been vacuumed.
> 
>                regards, tom lane
> 
> ---------------------------(end of
> broadcast)--------------------------- TIP 1: 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
> 
> 

There is a vacuumdb done at 6 AM and 5PM

In addition this table is vacuumed at 2AM, 8AM, 10AM, 12PM, 2PM, and 4PM

This is the vacuum from last night at 5PM


INFO:  vacuuming "ods.contract"
INFO:  index "XIE1_Contract" now contains 5105322 row versions in 27710 
pages
DETAIL:  2174 index row versions were removed.
893 index pages have been deleted, 893 are currently reusable.
CPU 1.91s/1.58u sec elapsed 34.14 sec.
INFO:  index "XIE2_Contract" now contains 5105331 row versions in 21701 
pages
DETAIL:  2174 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.40s/1.42u sec elapsed 22.73 sec.
INFO:  index "contract_pkey" now contains 5105337 row versions in 21480 
pages
DETAIL:  2174 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.80s/1.52u sec elapsed 18.59 sec.
INFO:  "contract": removed 2174 row versions in 893 pages
DETAIL:  CPU 0.42s/0.08u sec elapsed 1.22 sec.
INFO:  "contract": found 2174 removable, 5105321 nonremovable row 
versions in 129154 pages
DETAIL:  1357 dead row versions cannot be removed yet.
There were 1967941 unused item pointers.
0 pages are entirely empty.
CPU 11.38s/5.09u sec elapsed 85.48 sec.
INFO:  analyzing "ods.contract"
INFO:  "contract": 129154 pages, 3000 rows sampled, 5277622 estimated 
total rows


Here is the latest vacuum today.

INFO:  vacuuming "ods.contract"
INFO:  index "XIE1_Contract" now contains 5106346 row versions in 28233 
pages
DETAIL:  64146 index row versions were removed.
706 index pages have been deleted, 669 are currently reusable.
CPU 2.03s/2.33u sec elapsed 20.08 sec.
INFO:  index "XIE2_Contract" now contains 5106347 row versions in 21951 
pages
DETAIL:  64146 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.16s/3.39u sec elapsed 12.23 sec.
INFO:  index "contract_pkey" now contains 5106347 row versions in 21516 
pages
DETAIL:  64146 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.76s/2.47u sec elapsed 11.80 sec.
INFO:  "contract": removed 64146 row versions in 26115 pages
DETAIL:  CPU 1.94s/2.55u sec elapsed 7.78 sec.
INFO:  "contract": found 64146 removable, 5106307 nonremovable row 
versions in 129154 pages
DETAIL:  890 dead row versions cannot be removed yet.
There were 1905028 unused item pointers.
0 pages are entirely empty.
CPU 14.83s/11.48u sec elapsed 60.96 sec.
INFO:  analyzing "ods.contract"
INFO:  "contract": 129154 pages, 3000 rows sampled, 5236929 estimated 
total rows


I would think this should be very fast.  I already described the CPU and 
memory.  THe disk is backed by an EMC DMX2000.  This particular server 
has 1 physical volume group of 500GB which is split over two logical 
volumes.  One for $PGDATA and the other ofr PG_XLOG.  THis split was not 
really done for performance since it comes from the same physical volume 
group, but more for space manageability.  The physical volume group 
consists of 11GB stripes from across the EMC san.  So that would be 
about 50 stripes which is really coming from dozens of backend disk 
drives.  Typical I/O response times for these is 3-5 ms.

In response to

Responses

pgsql-performance by date

Next:From: PostgreSQLDate: 2005-10-27 19:31:43
Subject: How much memory?
Previous:From: Tom LaneDate: 2005-10-27 16:18:03
Subject: Re: Update using primary key slow

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group