pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum_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