pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum performance
4+ messages / 3 participants
[nested] [flat]
* vacuum performance
@ 2004-03-19 04:22 Alan Stange <stange@rentec.com>
2004-03-19 06:18 ` Re: vacuum performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Alan Stange @ 2004-03-19 04:22 UTC (permalink / raw)
To: pgsql-performance
Hello all,
I have a question/observation about vacuum performance. I'm running
Solaris 9, pg 7.4.1.
The process in questions is doing a vacuum:
bash-2.05$ /usr/ucb/ps auxww | grep 4885
fiasco 4885 19.1 3.7605896592920 ? O 19:29:44 91:38 postgres:
fiasco fiasco [local] VACUUM
I do a truss on the process and see the output below looping over and
over. Note the constant opening and closing of the file 42064889.3.
Why the open/close cycle as opposed to caching the file descriptor
somewhere?
If PG really does need to loop like this, it should be much faster to
set the cwd and then open without the path in the file name. You're
forcing the kernel to do a lot of work walking the path, checking for
nfs mounts, symlinks, etc.
Thanks!
-- Alan
open64("/export/nst1/fi/pg/data1/base/91488/42064889.3", O_RDWR) = 47
llseek(47, 0x18F6E000, SEEK_SET) = 0x18F6E000
write(47, "\0\0\0 zA9A3D9E8\0\0\0 "".., 8192) = 8192
close(47) = 0
read(29, "\0\0\0 }ED WF1B0\0\0\0 $".., 8192) = 8192
open64("/export/nst1/fi/pg/data1/base/91488/42064889.3", O_RDWR) = 47
llseek(47, 0x18F78000, SEEK_SET) = 0x18F78000
write(47, "\0\0\0 zA9AC 090\0\0\0 "".., 8192) = 8192
close(47) = 0
llseek(43, 0x26202000, SEEK_SET) = 0x26202000
read(43, "\0\0\084 EC9FC P\0\0\0 )".., 8192) = 8192
semop(52, 0xFFBFC5E0, 1) = 0
semop(52, 0xFFBFC640, 1) = 0
open64("/export/nst1/fi/pg/data1/base/91488/42064889.3", O_RDWR) = 47
llseek(47, 0x18F62000, SEEK_SET) = 0x18F62000
write(47, "\0\0\0 zA9C2\bB8\0\0\0 "".., 8192) = 8192
close(47) = 0
read(29, "\0\0\0 }ED X1210\0\0\0 $".., 8192) = 8192
semop(52, 0xFFBFC5E0, 1) = 0
semop(52, 0xFFBFC640, 1) = 0
open64("/export/nst1/fi/pg/data1/base/91488/42064889.3", O_RDWR) = 47
llseek(47, 0x18018000, SEEK_SET) = 0x18018000
write(47, "\0\0\0 zA997ADB0\0\0\0 "".., 8192) = 8192
close(47) = 0
llseek(43, 0x26200000, SEEK_SET) = 0x26200000
read(43, "\0\0\084 EC4F5E8\0\0\0 )".., 8192) = 8192
semop(52, 0xFFBFC5E0, 1) = 0
semop(52, 0xFFBFC640, 1) = 0
llseek(13, 13918208, SEEK_SET) = 13918208
write(13, "D0 Z\001\0\0\0 )\0\0\087".., 8192) = 8192
write(13, "D0 Z\001\0\0\0 )\0\0\087".., 8192) = 8192
write(13, "D0 Z\001\0\0\0 )\0\0\087".., 8192) = 8192
write(13, "D0 Z\001\0\0\0 )\0\0\087".., 8192) = 8192
write(13, "D0 Z\001\0\0\0 )\0\0\087".., 8192) = 8192
open64("/export/nst1/fi/pg/data1/base/91488/42064889.3", O_RDWR) = 47
llseek(47, 0x18F52000, SEEK_SET) = 0x18F52000
write(47, "\0\0\0 zABE7 V10\0\0\0 "".., 8192) = 8192
close(47) = 0
semop(46, 0xFFBFC5D0, 1) = 0
read(29, "\0\0\0 }ED X 2 p\0\0\0 $".., 8192) = 8192
semop(52, 0xFFBFC5E0, 1) = 0
semop(52, 0xFFBFC640, 1) = 0
llseek(43, 0x270DA000, SEEK_SET) = 0x270DA000
write(43, "\0\0\087A2E8 #B8\0\0\0 )".., 8192) = 8192
llseek(43, 0x261FE000, SEEK_SET) = 0x261FE000
read(43, "\0\0\084 EC498\0\0\0\0 )".., 8192) = 8192
poll(0xFFBFC100, 0, 10) = 0
open64("/export/nst1/fi/pg/data1/base/91488/42064889.3", O_RDWR) = 47
llseek(47, 0x1804A000, SEEK_SET) = 0x1804A000
write(47, "\0\0\0 zAA0F8DE0\0\0\0 "".., 8192) = 8192
close(47) = 0
read(29, "\0\0\0 }ED X RD0\0\0\0 $".., 8192) = 8192
semop(52, 0xFFBFC5E0, 1) = 0
semop(52, 0xFFBFC640, 1) = 0
write(13, "D0 Z\001\0\0\0 )\0\0\087".., 8192) = 8192
write(13, "D0 Z\001\0\0\0 )\0\0\087".., 8192) = 8192
write(13, "D0 Z\001\0\0\0 )\0\0\087".., 8192) = 8192
write(13, "D0 Z\001\0\0\0 )\0\0\087".., 8192) = 8192
open64("/export/nst1/fi/pg/data1/base/91488/42064889.3", O_RDWR) = 47
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: vacuum performance
2004-03-19 04:22 vacuum performance Alan Stange <stange@rentec.com>
@ 2004-03-19 06:18 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-03-19 06:18 UTC (permalink / raw)
To: Alan Stange <stange@rentec.com>; +Cc: pgsql-performance
Alan Stange <stange@rentec.com> writes:
> I do a truss on the process and see the output below looping over and
> over. Note the constant opening and closing of the file 42064889.3.
> Why the open/close cycle as opposed to caching the file descriptor
> somewhere?
This is probably a "blind write". We've gotten rid of those for 7.5.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* VACUUM Performance
@ 2006-10-20 21:42 Steve Oualline <soualline@stbernard.com>
2006-10-21 05:02 ` Re: VACUUM Performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Steve Oualline @ 2006-10-20 21:42 UTC (permalink / raw)
To: pgsql-performance; +Cc: Jiu Zheng <Jiu@stbernard.com>
Question: I have a big table with 120,000,000 records.
Let's assume that I DELETE 4,000,000 records, VACUUM FULL, and REINDEX.
Now I have the same table, but with 240,000,000 records.
I DELETE 8,000,000 records, VACUUM FULL, and REINDEX.
Should the second operation with twice the data take twice the time as the first?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: VACUUM Performance
2006-10-20 21:42 VACUUM Performance Steve Oualline <soualline@stbernard.com>
@ 2006-10-21 05:02 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-10-21 05:02 UTC (permalink / raw)
To: Steve Oualline <soualline@stbernard.com>; +Cc: pgsql-performance@postgresql.org, "Jiu Zheng" <Jiu@stbernard.com>
"Steve Oualline" <soualline@stbernard.com> writes:
> Question: I have a big table with 120,000,000 records.
> Let's assume that I DELETE 4,000,000 records, VACUUM FULL, and REINDEX.
> Now I have the same table, but with 240,000,000 records.
> I DELETE 8,000,000 records, VACUUM FULL, and REINDEX.
> Should the second operation with twice the data take twice the time as =
> the first?
At least. If you intend to reindex all the indexes, consider instead
doing
DROP INDEX(es)
VACUUM FULL
re-create indexes
as this avoids the very large amount of effort that VACUUM FULL puts
into index maintenance --- effort that's utterly wasted if you then
reindex.
CLUSTER and some forms of ALTER TABLE can accomplish a table rewrite
with less hassle than the above, although strictly speaking they violate
MVCC by discarding recently-dead tuples.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-10-21 05:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-19 04:22 vacuum performance Alan Stange <stange@rentec.com>
2004-03-19 06:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-20 21:42 VACUUM Performance Steve Oualline <soualline@stbernard.com>
2006-10-21 05:02 ` Re: VACUUM Performance Tom Lane <tgl@sss.pgh.pa.us>
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