Thread: Performance on temp table inserts
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
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 BerkusAglio Database SolutionsSan Francisco
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
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
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
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
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
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.
======================================================
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. ======================================================