pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance on temp table inserts
9+ messages / 4 participants
[nested] [flat]
* Performance on temp table inserts
@ 2003-05-19 19:11 Jeff Boes <jboes@nexcerpt.com>
0 siblings, 3 replies; 9+ messages in thread
From: Jeff Boes @ 2003-05-19 19:11 UTC (permalink / raw)
To: pgsql-sql
What factors affect the performance of a temp table insert? On nearly
identical boxes, I have recorded these kinds of statistics:
"INSERT INTO temp_link_checksums VALUES (?)","25957","0.009","245.546"
(That's the statement text, the number of executions, the average, and
the total wallclock time.)
"INSERT INTO temp_link_checksums VALUES (?)","76962","0.041","3184.835"
Box #1 is our "production" server, and actually has more going on during
the day than #2 which is the "test" server. #1 has "shared_buffers" set
to 131072; #2 has it at 250000. Hardware, O/S and PostgreSQL versions are
all identical. Software executing the query (Perl code using DBI) is the
same.
I don't have stats on std-dev for these results; I suppose there could be
an outlying value in the #2 box that dragged down the average. But before
I re-instrument my statistics-gathering code, I thought I'd check to see
if anyone knows how temp tables and shared memory might interact.
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance on temp table inserts
@ 2003-05-19 22:04 Josh Berkus <josh@agliodbs.com>
parent: Jeff Boes <jboes@nexcerpt.com>
2 siblings, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2003-05-19 22:04 UTC (permalink / raw)
To: Jeff Boes <jboes@nexcerpt.com>; pgsql-sql
Jeff,
> Box #1 is our "production" server, and actually has more going on during
> the day than #2 which is the "test" server. #1 has "shared_buffers" set
> to 131072; #2 has it at 250000. Hardware, O/S and PostgreSQL versions are
> all identical. Software executing the query (Perl code using DBI) is the
> same.
First, I think you need to join the PGSQL-PERFORMANCE list; we discuss things
like your issue all the time there.
Collective wisdom on that list is that shared_buffer settings above 5000-6000
actually degrade performance by robbing the kernel buffer of resources. The
postgres shared_buffers are just a "holding area" for operations spooling to
the kernel buffer which actually does most of the work.
HTH.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance on temp table inserts
@ 2003-05-19 23:19 David W Noon <dwnoon@spamtrap.ntlworld.com>
parent: Jeff Boes <jboes@nexcerpt.com>
2 siblings, 1 reply; 9+ messages in thread
From: David W Noon @ 2003-05-19 23:19 UTC (permalink / raw)
To: pgsql-sql
On Monday 19 May 2003 20:11 in <baba9i$2vi8$1@news.hub.org>, Jeff Boes
(jboes@nexcerpt.com) wrote:
> What factors affect the performance of a temp table insert?
I would run an EXPLAIN on each of the two servers and see what differences
there are in the respective execution strategies.
Also, a VACUUM ANALYZE might be of interest to speed up the slower box.
--
Regards,
Dave [RLU#314465]
======================================================
dwnoon@spamtrap.ntlworld.com (David W Noon)
Remove spam trap to reply via e-mail.
======================================================
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance on temp table inserts
@ 2003-05-20 04:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Boes <jboes@nexcerpt.com>
2 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2003-05-20 04:51 UTC (permalink / raw)
To: Jeff Boes <jboes@nexcerpt.com>; +Cc: pgsql-sql
Jeff Boes <jboes@nexcerpt.com> writes:
> What factors affect the performance of a temp table insert?
PG version would have a lot to do with that, considering how we've
whacked around the temp-table implementation in the last few releases.
> Hardware, O/S and PostgreSQL versions are all identical.
And they are?
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance on temp table inserts
@ 2003-05-20 12:37 Jeff Boes <jboes@nexcerpt.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Jeff Boes @ 2003-05-20 12:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Tue, 2003-05-20 at 00:51, Tom Lane wrote:
> Jeff Boes <jboes@nexcerpt.com> writes:
> > What factors affect the performance of a temp table insert?
>
> PG version would have a lot to do with that, considering how we've
> whacked around the temp-table implementation in the last few releases.
>
> > Hardware, O/S and PostgreSQL versions are all identical.
>
> And they are?
Red Hat Linux release 7.1 (Seawolf)
Kernel 2.4.18-17.7.xbigmem on a 2-processor i686
We have 4 GB of memory and a 53GB partition on a RAID device for the PG
data.
PG version is 7.2.4.
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance on temp table inserts
@ 2003-05-20 16:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Boes <jboes@nexcerpt.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2003-05-20 16:04 UTC (permalink / raw)
To: Jeff Boes <jboes@nexcerpt.com>; +Cc: pgsql-sql
Jeff Boes <jboes@nexcerpt.com> writes:
>>> What factors affect the performance of a temp table insert?
>>
>> PG version would have a lot to do with that, considering how we've
>> whacked around the temp-table implementation in the last few releases.
> PG version is 7.2.4.
IIRC, temp tables are really indistinguishable from regular tables in
7.2, as far as performance goes. The bottleneck for the bare insert
itself would probably be WAL --- are you sure all the WAL settings are
the same on the two boxes? If you have indexes, foreign keys, triggers,
rules, etc on the table then of course you have other overhead to worry
about ... but that seems somewhat unlikely for a temp table.
Also, is the temp table being filled in the same transaction that
created it, or in separate transaction(s)? That makes a difference in
7.2 and before (but not in 7.3).
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance on temp table inserts
@ 2003-05-20 16:15 Jeff Boes <jboes@nexcerpt.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Jeff Boes @ 2003-05-20 16:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Tue, 2003-05-20 at 12:04, Tom Lane wrote:
> Jeff Boes <jboes@nexcerpt.com> writes:
> >>> What factors affect the performance of a temp table insert?
> >>
> >> PG version would have a lot to do with that, considering how we've
> >> whacked around the temp-table implementation in the last few releases.
>
> > PG version is 7.2.4.
>
> IIRC, temp tables are really indistinguishable from regular tables in
> 7.2, as far as performance goes. The bottleneck for the bare insert
> itself would probably be WAL --- are you sure all the WAL settings are
> the same on the two boxes? If you have indexes, foreign keys, triggers,
> rules, etc on the table then of course you have other overhead to worry
> about ... but that seems somewhat unlikely for a temp table.
wal_files = 2
wal_buffers = 32
on both machines. There isn't an index on the temp table.
>
> Also, is the temp table being filled in the same transaction that
> created it, or in separate transaction(s)? That makes a difference in
> 7.2 and before (but not in 7.3).
>
> regards, tom lane
The inserts are being processed in DBI using 'AutoCommit=1', thus they
are in a separate transaction. But it's the same on both boxes.
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance on temp table inserts
@ 2003-05-20 16:21 Jeff Boes <jboes@nexcerpt.com>
parent: David W Noon <dwnoon@spamtrap.ntlworld.com>
0 siblings, 1 reply; 9+ messages in thread
From: Jeff Boes @ 2003-05-20 16:21 UTC (permalink / raw)
To: pgsql-sql
On Mon, 19 May 2003 19:19:32 -0400, David W Noon wrote:
> On Monday 19 May 2003 20:11 in <baba9i$2vi8$1@news.hub.org>, Jeff Boes
> (jboes@nexcerpt.com) wrote:
>
>> What factors affect the performance of a temp table insert?
>
> I would run an EXPLAIN on each of the two servers and see what
> differences there are in the respective execution strategies.
>
Hmm ... I'm not sure you followed my original post. However, in the
interests of science:
explain insert into temp_link_checksums values ('a');
NOTICE: QUERY PLAN:
Result (cost=0.00..0.01 rows=1 width=0)
EXPLAIN
This is identical on the two systems. As I would expect for an insert
statement ...
> Also, a VACUUM ANALYZE might be of interest to speed up the slower box.
But the table doesn't exist before I create and insert it. (Well,
actually it does: after the first CREATE, the subsequent uses of the
table are preceded by TRUNCATE TABLE, so it exists but is empty. I dunno
what a VACUUM ANALYZE will do for an insert, though.)
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance on temp table inserts
@ 2003-05-21 11:03 David W Noon <dwnoon@spamtrap.ntlworld.com>
parent: Jeff Boes <jboes@nexcerpt.com>
0 siblings, 0 replies; 9+ messages in thread
From: David W Noon @ 2003-05-21 11:03 UTC (permalink / raw)
To: pgsql-sql
On Tuesday 20 May 2003 17:21 in <badklp$uot$1@news.hub.org>, Jeff Boes
(jboes@nexcerpt.com) wrote:
> explain insert into temp_link_checksums values ('a');
> NOTICE: QUERY PLAN:
>
> Result (cost=0.00..0.01 rows=1 width=0)
>
> EXPLAIN
>
> This is identical on the two systems. As I would expect for an insert
> statement ...
Ok.
> But the table doesn't exist before I create and insert it. (Well,
> actually it does: after the first CREATE, the subsequent uses of the
> table are preceded by TRUNCATE TABLE, so it exists but is empty. I dunno
> what a VACUUM ANALYZE will do for an insert, though.)
The reason I suggested a VACUUM was that my thought processes on this
problem lead me to the same conclusion I would have come to 25 years ago:
DASD fragmentation.
My reasoning is that the PostgreSQL catalogue on the test box is possibly
more fragmented than on the production box because developers tend to do
more CREATEs and DROPs of new tables than do production users. Moreover,
the DASD farm backing PostgreSQL could be similarly fragmented. If a VACUUM
does not have a beneficial effect then it would seem that the underlying
physical filesystem could be severely fragmented.
--
Regards,
Dave [RLU#314465]
======================================================
dwnoon@spamtrap.ntlworld.com (David W Noon)
Remove spam trap to reply via e-mail.
======================================================
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-05-21 11:03 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-19 19:11 Performance on temp table inserts Jeff Boes <jboes@nexcerpt.com>
2003-05-19 22:04 ` Josh Berkus <josh@agliodbs.com>
2003-05-19 23:19 ` David W Noon <dwnoon@spamtrap.ntlworld.com>
2003-05-20 16:21 ` Jeff Boes <jboes@nexcerpt.com>
2003-05-21 11:03 ` David W Noon <dwnoon@spamtrap.ntlworld.com>
2003-05-20 04:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-05-20 12:37 ` Jeff Boes <jboes@nexcerpt.com>
2003-05-20 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-05-20 16:15 ` Jeff Boes <jboes@nexcerpt.com>
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