pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
vacuum_mem
2+ messages / 2 participants
[nested] [flat]

* vacuum_mem
@ 2004-07-08 18:03  Litao Wu <litaowu@yahoo.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Litao Wu @ 2004-07-08 18:03 UTC (permalink / raw)
  To: pgsql-performance; +Cc: pgsql-performance

Hi,

I tested vacuum_mem setting under a 
4CPU and 4G RAM machine. I am the only person 
on that machine.

The table:
         tablename         | size_kb |   reltuples
---------------------------+-------------------------
 big_t                     | 2048392 |   7.51515e+06

Case 1:
1. vacuum full big_t;
2. begin;
   update big_t set email = lpad('a', 255, 'b');
   rollback;
3. set vacuum_mem=655360; -- 640M
4. vacuum big_t;
It takes 1415,375 ms
Also from top, the max SIZE is 615M while 
SHARE is always 566M

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM
  TIME COMMAND
5914 postgres  16   0   615M 615M  566M D     7.5 15.8
 21:21 postgres: postgres mydb xxx.xxx.xxx.xxx:34361
VACUUM

Case 2:
1. vacuum full big_t;
2. begin;
   update big_t set email = lpad('a', 255, 'b');
   rollback;
3. set vacuum_mem=65536; -- 64M
4. vacuum big_t;
It takes 1297,798 ms
Also from top, the max SIZE is 615M while 
SHARE is always 566M

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM
  TIME COMMAND
 3613 postgres  15   0  615M 615M  566M D    17.1 15.8
  9:04 postgres: postgres mydb xxx.xxx.xxx.xxx:34365
VACUUM

It seems vacuum_mem does not have performance 
effect at all.

In reality, we vaccum nightly and I want to find out 
which vacuum_mem value is the 
best to short vacuum time.

Any thoughts?

Thanks,


		
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail 



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

* Re: vacuum_mem
@ 2004-07-08 18:25  Rod Taylor <pg@rbt.ca>
  parent: Litao Wu <litaowu@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Rod Taylor @ 2004-07-08 18:25 UTC (permalink / raw)
  To: Litao Wu <litaowu@yahoo.com>; +Cc: pgsql-performance

> It seems vacuum_mem does not have performance 
> effect at all.

Wrong conclusion. It implies that your test case takes less than 64M of
memory to track your removed tuples. I think it takes 8 bytes to track a
tuple for vacuuming an index, which means it should be able to track
800000 deletions. Since you're demonstration had 750000 for removal,
it's under the limit.

Try your test again with 32MB; it should make a single sequential pass
on the table, and 2 passes on each index for that table.

Either that, or do a few more aborted updates.





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


end of thread, other threads:[~2004-07-08 18:25 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-08 18:03 vacuum_mem Litao Wu <litaowu@yahoo.com>
2004-07-08 18:25 ` Rod Taylor <pg@rbt.ca>

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