pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Vacuum Verbose output
6+ messages / 5 participants
[nested] [flat]

* Vacuum Verbose output
@ 2005-10-31 20:13  Subbiah, Stalin <SSubbiah@netopia.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Subbiah, Stalin @ 2005-10-31 20:13 UTC (permalink / raw)
  To: pgsql-admin

Hi All,

We vacuum busy tables in different frequencies depending on how busy the
table is. Today I noticed that one of the task that was suppose to
delete rows from a busy table was running for a long time. And below is
the output of vacuum verbose after the task was stopped from running.
Trying to understand if the table/index has been bloated or not.

INFO:  vacuuming "public.eventlog"
INFO:  index "ix_eventlog_eventtime" now contains 11465395 row versions
in 77113 pages
DETAIL:  8737797 index row versions were removed.

Does it mean ix_eventlog_ index had 11.4mil row versions out of which
8.7mil were removed cause of deletes?

33189 index pages have been deleted, 0 are currently reusable.

Does it mean out of 77113 index pages, 33189 were deleted?

CPU 5.24s/13.76u sec elapsed 67.05 sec.


INFO:  "eventlog": removed 8737797 row versions in 361856 pages
DETAIL:  CPU 14.53s/22.00u sec elapsed 122.13 sec.
INFO:  index "ix_eventlog_eventtime" now contains 8023399 row versions
in 77116 pages
DETAIL:  3442698 index row versions were removed.
46439 index pages have been deleted, 0 are currently reusable.
CPU 3.18s/5.35u sec elapsed 22.85 sec.
INFO:  "eventlog": removed 3442698 row versions in 143144 pages
DETAIL:  CPU 5.64s/8.74u sec elapsed 69.15 sec.
INFO:  "eventlog": found 12180495 removable, 8022620 nonremovable row
versions in 837938 pages
DETAIL:  0 dead row versions cannot be removed yet.

There were 154716 unused item pointers.
0 pages are entirely empty.
CPU 44.37s/54.07u sec elapsed 357.04 sec.

Why does index and table INFO reported twice with different row version
numbers on a vacuum run.

Thanks,
Subbiah
Postgres v7.4.7

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Vacuum Verbose output
@ 2005-10-31 21:22  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Subbiah, Stalin <SSubbiah@netopia.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2005-10-31 21:22 UTC (permalink / raw)
  To: Subbiah, Stalin <SSubbiah@netopia.com>; +Cc: pgsql-admin

"Subbiah, Stalin" <SSubbiah@netopia.com> writes:
> INFO:  vacuuming "public.eventlog"
> INFO:  index "ix_eventlog_eventtime" now contains 11465395 row versions
> in 77113 pages
> DETAIL:  8737797 index row versions were removed.

> Does it mean ix_eventlog_ index had 11.4mil row versions out of which
> 8.7mil were removed cause of deletes?

No, it says "now contains", ie those are the post-removal statistics.

> 33189 index pages have been deleted, 0 are currently reusable.

> Does it mean out of 77113 index pages, 33189 were deleted?

Yeah --- so almost half the space in the index is free space (more than
half according to the later report).  Possibly you should be vacuuming
this table a bit more often...

> Why does index and table INFO reported twice with different row version
> numbers on a vacuum run.

Because it had to do two passes of row removal, due to not having enough
vacuum_mem to remember all the dead rows at once.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Vacuum Verbose output
@ 2005-10-31 22:34  Tomeh, Husam <htomeh@firstam.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tomeh, Husam @ 2005-10-31 22:34 UTC (permalink / raw)
  To: pgsql-admin

My understanding that the current release of Postgres does not allow the
DBA to pre-allocated space for the data files or tablespace in advance
(as Oracle or SQL Server does). Am I correct on that ? If that's still
the case, is this item on the TODO list or the white board?
Pre-allocating space will prevent extending the datafile during loading
massive data (batch processing) and would improve the overall batch
write performance. 
 
Thanks in advance.

-- 
 Husam 

---------------------------(end of broadcast)---------------------------

TIP 2: Don't 'kill -9' the postmaster





**********************************************************************
This message contains confidential information intended only for the 
use of the addressee(s) named above and may contain information that 
is legally privileged.  If you are not the addressee, or the person 
responsible for delivering it to the addressee, you are hereby 
notified that reading, disseminating, distributing or copying this 
message is strictly prohibited.  If you have received this message by 
mistake, please immediately notify us by replying to the message and 
delete the original message immediately thereafter.

Thank you.                                       FADLD Tag
**********************************************************************

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Vacuum Verbose output
@ 2005-10-31 22:52  Scott Marlowe <smarlowe@g2switchworks.com>
  parent: Tomeh, Husam <htomeh@firstam.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Scott Marlowe @ 2005-10-31 22:52 UTC (permalink / raw)
  To: Tomeh, Husam <htomeh@firstam.com>; +Cc: pgsql-admin

On Mon, 2005-10-31 at 16:34, Tomeh, Husam wrote:
> My understanding that the current release of Postgres does not allow
> the DBA to pre-allocated space for the data files or tablespace in
> advance (as Oracle or SQL Server does). Am I correct on that ? If
> that's still the case, is this item on the TODO list or the white
> board? Pre-allocating space will prevent extending the datafile during
> loading massive data (batch processing) and would improve the overall
> batch write performance. 

I can't see pre-allocation making much if any difference in any modern
OS, except it might lower fragmentation of the file a bit.  Writing data
is writing data.  It takes time, whether you're writing it into a file
that already exists with zeros in it or a new blank file you just
created.

Have you got any file system benchmarks that back up this assertion?  I 
would love to see something that shows one way or the other if that
really makes any difference.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Vacuum Verbose output
@ 2005-11-01 03:59  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Scott Marlowe <smarlowe@g2switchworks.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2005-11-01 03:59 UTC (permalink / raw)
  To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Tomeh, Husam <htomeh@firstam.com>; pgsql-admin

Scott Marlowe <smarlowe@g2switchworks.com> writes:
> On Mon, 2005-10-31 at 16:34, Tomeh, Husam wrote:
>> Pre-allocating space will prevent extending the datafile during
>> loading massive data (batch processing) and would improve the overall
>> batch write performance. 

> Have you got any file system benchmarks that back up this assertion?  I 
> would love to see something that shows one way or the other if that
> really makes any difference.

Barring some pretty solid evidence, you're unlikely to attract any
enthusiasm among pghackers for this sort of thing.  We are generally
disinclined to reinvent functionality that properly belongs to the
kernel or filesystem layer.  "Oracle does it" cuts no ice in this
connection, because Oracle is designed around a twenty-year-old
assumption that the database is smarter than the kernel, and the world
has changed a lot since then.

In short: show us some numbers that prove this is worth our attention.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Vacuum Verbose output
@ 2005-11-02 19:00  Robert Treat <xzilla@users.sourceforge.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Robert Treat @ 2005-11-02 19:00 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Scott Marlowe <smarlowe@g2switchworks.com>; Tomeh, Husam <htomeh@firstam.com>

On Monday 31 October 2005 22:59, Tom Lane wrote:
> Scott Marlowe <smarlowe@g2switchworks.com> writes:
> > On Mon, 2005-10-31 at 16:34, Tomeh, Husam wrote:
> >> Pre-allocating space will prevent extending the datafile during
> >> loading massive data (batch processing) and would improve the overall
> >> batch write performance.
> >
> > Have you got any file system benchmarks that back up this assertion?  I
> > would love to see something that shows one way or the other if that
> > really makes any difference.
>
> Barring some pretty solid evidence, you're unlikely to attract any
> enthusiasm among pghackers for this sort of thing.  We are generally
> disinclined to reinvent functionality that properly belongs to the
> kernel or filesystem layer.  "Oracle does it" cuts no ice in this
> connection, because Oracle is designed around a twenty-year-old
> assumption that the database is smarter than the kernel, and the world
> has changed a lot since then.
>
> In short: show us some numbers that prove this is worth our attention.
>

I'm not terribly excited about the idea, but it might be worth hearing a 
better argument. (FWIW I think this is somewhat debunkable too, but it gives 
one something to think about)

"PostgreSQL unlike other commercial databases does not allow database files to 
pregrow to certain sizes. So if you are loading multiple tables via different 
connections there are two things that hurts scalability: One is the semaphore 
locking which it needs to perform IO to the database files and second is file 
fragmentation since it creates all tables in the same file system and grows 
them as needed. So if both the tables are loaded then both files are growing 
at "same" time which typically is seralized as blocks are allocated to each 
of the file one at a time which means they will be dispersed and not 
contiguous. How this hurts? Well if you do total row scans and compare the 
time you can easily huge degradations. (I have seen about 50% degradations). 
This means you have to load 1 table at a time. However if there was a way to 
increase the space for the tables (pre-grown them) then it will be a bit 
easier to load multiple tables simultaneously. (Of course the semaphore 
problem is still there and that needs to be more granular also). Duh.. I 
forgot the workaround here.. TABLESPACES are finally available in PostgreSQL 
8. But semaphore problems are still existing and pre-growing files will still 
help a lot since "growing" the files will be in your "1" process connection 
timeline. "

taken from an interesting post at 
http://blogs.sun.com/roller/page/jkshah?anchor=postgres_what_needs_to_be

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2005-11-02 19:00 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-31 20:13 Vacuum Verbose output Subbiah, Stalin <SSubbiah@netopia.com>
2005-10-31 21:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-31 22:34 Re: Vacuum Verbose output Tomeh, Husam <htomeh@firstam.com>
2005-10-31 22:52 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-11-01 03:59   ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-02 19:00     ` Robert Treat <xzilla@users.sourceforge.net>

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