pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Seum-Lim Gan <slgan@lucent.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Subject: Re: index file bloating still in 7.4 ?
Date: Sun, 19 Oct 2003 11:55:57 -0500
Message-ID: <p05100318bbb86e62801a@[192.168.10.52]> (raw)
In-Reply-To: <19692.1066578442@sss.pgh.pa.us>
References: <p0510030cbbb7667a7f87@[192.168.10.52]>
	<200310181458.15575.josh@agliodbs.com>
	<p0510030dbbb79c742da5@[192.168.10.52]>
	<16213.1066530060@sss.pgh.pa.us>
	<p0510030ebbb7cabb0ceb@[192.168.10.52]>
	<17172.1066542509@sss.pgh.pa.us>
	<p05100313bbb852b0fe68@[192.168.10.52]>
	<19692.1066578442@sss.pgh.pa.us>

Hi Tom,

The key is a range from 1 to 30000 and picked randomly.

Oh, so in order to reclaim the disk space, we must run
reindex or vacuum full ?
This will lock out the table and we won't be able to do anything.
Looks like this is a problem. It means we cannot use it for
24x7 operations without having to stop the process and do the vacuum full
and reindex. Is there anything down the road that these operations
will not lock out the table ?

I let the process ran overnight. The last email I sent you with
the vacuum analyze output just about an hour ago, that was after
I removed the process that does the updates.

However, I search through all the vacuum I did just before I
went to bed and found that earlier vacuum did say 5 indexes deleted and
5 reusable. It has been pretty constant for about 1 to 2 hours and
then down to zero and has been like this since.

Sun Oct 19 00:50:07 CDT 2003
INFO:  vacuuming "scncraft.dsperf_rda_or_key"
INFO:  index "dsperf242_1105" now contains 402335 row versions in 7111 pages
DETAIL:  5 index pages have been deleted, 5 are currently reusable.
CPU 1.32s/0.17u sec elapsed 22.44 sec.
INFO:  "dsperf_rda_or_key": found 0 removable, 401804 nonremovable 
row versions in 35315 pages
DETAIL:  101802 dead row versions cannot be removed yet.
There were 1646275 unused item pointers.
0 pages are entirely empty.
CPU 2.38s/0.71u sec elapsed 27.09 sec.
INFO:  analyzing "scncraft.dsperf_rda_or_key"
INFO:  "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 156124 
estimated total rows
VACUUM
Sleep 60 seconds

Sun Oct 19 00:51:40 CDT 2003
INFO:  vacuuming "scncraft.dsperf_rda_or_key"
INFO:  index "dsperf242_1105" now contains 411612 row versions in 7111 pages
DETAIL:  5 index pages have been deleted, 5 are currently reusable.
CPU 1.28s/0.22u sec elapsed 23.38 sec.
INFO:  "dsperf_rda_or_key": found 0 removable, 410889 nonremovable 
row versions in 35315 pages
DETAIL:  110900 dead row versions cannot be removed yet.
There were 1637190 unused item pointers.
0 pages are entirely empty.
CPU 2.13s/0.92u sec elapsed 27.13 sec.
INFO:  analyzing "scncraft.dsperf_rda_or_key"
INFO:  "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 123164 
estimated total rows
VACUUM
Sleep 60 seconds
.
.
.
Sun Oct 19 02:14:41 CDT 2003
INFO:  vacuuming "scncraft.dsperf_rda_or_key"
INFO:  index "dsperf242_1105" now contains 1053582 row versions in 7112 pages
DETAIL:  5 index pages have been deleted, 5 are currently reusable.
CPU 0.58s/0.29u sec elapsed 21.63 sec.
INFO:  "dsperf_rda_or_key": found 0 removable, 1053103 nonremovable 
row versions in 35315 pages
DETAIL:  753064 dead row versions cannot be removed yet.
There were 995103 unused item pointers.
0 pages are entirely empty.
CPU 1.54s/1.35u sec elapsed 26.17 sec.
INFO:  analyzing "scncraft.dsperf_rda_or_key"
INFO:  "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 106627 
estimated total rows
VACUUM
Sleep 60 seconds

Sun Oct 19 02:16:16 CDT 2003
INFO:  vacuuming "scncraft.dsperf_rda_or_key"
INFO:  index "dsperf242_1105" now contains 1065887 row versions in 7119 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.71s/0.36u sec elapsed 21.12 sec.
INFO:  "dsperf_rda_or_key": found 0 removable, 1065357 nonremovable 
row versions in 35315 pages
DETAIL:  765328 dead row versions cannot be removed yet.
There were 982849 unused item pointers.
0 pages are entirely empty.
CPU 1.70s/1.42u sec elapsed 26.65 sec.
INFO:  analyzing "scncraft.dsperf_rda_or_key"
INFO:  "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 106627 
estimated total rows
VACUUM
Sleep 60 seconds
.
.
.

Thanks.
Gan


At 11:47 am -0400 2003/10/19, Tom Lane wrote:
>Seum-Lim Gan <slgan@lucent.com> writes:
>>  vacuum verbose analyze dsperf_rda_or_key;
>>  INFO:  vacuuming "scncraft.dsperf_rda_or_key"
>>  INFO:  index "dsperf242_1105" now contains 300000 row versions in 
>>12387 pages
>>  DETAIL:  3097702 index row versions were removed.
>  > 0 index pages have been deleted, 0 are currently reusable.
>
>Hm, interesting that you deleted 90% of the entries and still had no
>empty index pages at all.  What was the pattern of your deletes and/or
>updates with respect to this index's key?
>
>>  However, when I check the disk space usage, it has not changed.
>
>It won't in any case.  Plain VACUUM is designed for maintaining a
>steady-state level of free space in tables and indexes, not for
>returning major amounts of space to the OS.  For that you need
>more-invasive operations like VACUUM FULL or REINDEX.
>
>			regards, tom lane


-- 
+--------------------------------------------------------+
| Seum-Lim GAN                 email : slgan@lucent.com  |
| Lucent Technologies                                    |
| 2000 N. Naperville Road, 6B-403F  tel : (630)-713-6665 |
| Naperville, IL 60566, USA.        fax : (630)-713-7272 |
|       web : http://inuweb.ih.lucent.com/~slgan         |
+--------------------------------------------------------+



view thread (18+ messages)  latest in thread

Message-ID: <p05100318bbb86e62801a@[192.168.10.52]>
Permalink:  ../p05100318bbb86e62801a@%5B192.168.10.52%5D/
Also on:    postgresql.org/message-id/p05100318bbb86e62801a@[192.168.10.52]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: slgan@lucent.com, tgl@sss.pgh.pa.us, josh@agliodbs.com
  Subject: Re: index file bloating still in 7.4 ?
  In-Reply-To: <p05100318bbb86e62801a@[192.168.10.52]>
  References: <p0510030cbbb7667a7f87@[192.168.10.52]> <200310181458.15575.josh@agliodbs.com> <p0510030dbbb79c742da5@[192.168.10.52]> <16213.1066530060@sss.pgh.pa.us> <p0510030ebbb7cabb0ceb@[192.168.10.52]> <17172.1066542509@sss.pgh.pa.us> <p05100313bbb852b0fe68@[192.168.10.52]> <19692.1066578442@sss.pgh.pa.us> <p05100318bbb86e62801a@[192.168.10.52]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox