pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [Bizgres-general] Re: faster INSERT with possible
6+ messages / 6 participants
[nested] [flat]
* Re: [Bizgres-general] Re: faster INSERT with possible
@ 2005-07-26 19:30 Luke Lonergan <llonergan@greenplum.com>
0 siblings, 1 reply; 6+ messages in thread
From: Luke Lonergan @ 2005-07-26 19:30 UTC (permalink / raw)
To: Hannu Krosing <hannu@skype.net>; +Cc: John A Meinel <john@arbash-meinel.com>; Dan Harris <fbsd@drivefaster.net>; bizgres-general <bizgres-general@pgfoundry.org>; pgsql-performance
Hannu,
On 7/26/05 11:56 AM, "Hannu Krosing" <hannu@skype.net> wrote:
> On T, 2005-07-26 at 11:46 -0700, Luke Lonergan wrote:
>
>> Yah - that's a typical approach, and it would be excellent if the COPY
>> bypassed WAL for the temp table load.
>
> Don't *all* operations on TEMP tables bypass WAL ?
Good question - do they? We had discussed the bypass as an elective option,
or an automated one for special conditions (no index on table, empty table)
or both. I thought that temp tables was one of those special conditions.
Well - now that I test it, it appears you are correct, temp table COPY
bypasses WAL - thanks for pointing it out!
The following test is on a load of 200MB of table data from an ASCII file
with 1 text column of size 145MB.
- Luke
===================== TEST ===========================
dgtestdb=# create temporary table temp1 (a text);
CREATE TABLE
dgtestdb=# \timing
Timing is on.
dgtestdb=# \i copy.ctl
COPY
Time: 4549.212 ms
dgtestdb=# \i copy.ctl
COPY
Time: 3897.395 ms
-- that's two tests, two loads of 200MB each, averaging 4.2 secs
dgtestdb=# create table temp2 as select * from temp1;
SELECT
Time: 5914.803 ms
-- a quick comparison to "CREATE TABLE AS SELECT", which bypasses WAL
-- on bizgres
dgtestdb=# drop table temp1;
DROP TABLE
Time: 135.782 ms
dgtestdb=# drop table temp2;
DROP TABLE
Time: 3.707 ms
dgtestdb=# create table temp1 (a text);
CREATE TABLE
Time: 1.667 ms
dgtestdb=# \i copy.ctl
COPY
Time: 6034.274 ms
dgtestdb=#
-- This was a non-temporary table COPY, showing the slower performance of 6
secs.
- Luke
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [Bizgres-general] Re: faster INSERT with possible
@ 2005-07-27 16:29 Josh Berkus <josh@agliodbs.com>
parent: Luke Lonergan <llonergan@greenplum.com>
0 siblings, 3 replies; 6+ messages in thread
From: Josh Berkus @ 2005-07-27 16:29 UTC (permalink / raw)
To: pgsql-performance; +Cc: Luke Lonergan <llonergan@greenplum.com>; Hannu Krosing <hannu@skype.net>; John A Meinel <john@arbash-meinel.com>; Dan Harris <fbsd@drivefaster.net>; bizgres-general <bizgres-general@pgfoundry.org>
Luke,
> Well - now that I test it, it appears you are correct, temp table COPY
> bypasses WAL - thanks for pointing it out!
RIght. The problem is bypassing WAL for loading new "scratch" tables which
aren't TEMPORARY tables. We need to do this for multi-threaded ETL, since:
a) Temp tables can't be shared by several writers, and
b) you can't index a temp table.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [Bizgres-general] Re: faster INSERT with possible
@ 2005-07-27 18:28 Kris Jurka <books@ejurka.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 6+ messages in thread
From: Kris Jurka @ 2005-07-27 18:28 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance@postgresql.org, John A Meinel <john@arbash-meinel.com>; bizgres-general <bizgres-general@pgfoundry.org>; Dan Harris <fbsd@drivefaster.net>
On Wed, 27 Jul 2005, Josh Berkus wrote:
> b) you can't index a temp table.
>
jurka# create temp table t (a int);
CREATE
jurka# create index myi on t(a);
CREATE
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [Bizgres-general] Re: faster INSERT with possible
@ 2005-07-27 21:37 Simon Riggs <simon@2ndquadrant.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 6+ messages in thread
From: Simon Riggs @ 2005-07-27 21:37 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance@postgresql.org, John A Meinel <john@arbash-meinel.com>; bizgres-general <bizgres-general@pgfoundry.org>; Dan Harris <fbsd@drivefaster.net>
On Wed, 2005-07-27 at 09:29 -0700, Josh Berkus wrote:
> Luke,
>
> > Well - now that I test it, it appears you are correct, temp table COPY
> > bypasses WAL - thanks for pointing it out!
>
> RIght. The problem is bypassing WAL for loading new "scratch" tables which
> aren't TEMPORARY tables. We need to do this for multi-threaded ETL, since:
> a) Temp tables can't be shared by several writers, and
> b) you can't index a temp table.
The description of "scratch" tables might need some slight
clarification. It kindof makes it sound like temp tables.
I had in mind the extra tables that an application sometimes needs to
operate faster. Denormalisations, pre-joined tables, pre-calculated
results, aggregated data. These are not temporary tables, just part of
the application - multi-user tables that stay across shutdown/restart.
If you have gallons of GB, you will probably by looking to make use of
such tables.
You can use such tables for the style of ETL known as ELT, but that is
not the only use.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [Bizgres-general] Re: faster INSERT with possible
@ 2005-07-27 21:53 PFC <lists@boutiquenumerique.com>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 6+ messages in thread
From: PFC @ 2005-07-27 21:53 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance@postgresql.org, "John A Meinel" <john@arbash-meinel.com>; bizgres-general <bizgres-general@pgfoundry.org>; Dan Harris <fbsd@drivefaster.net>
> I had in mind the extra tables that an application sometimes needs to
> operate faster. Denormalisations, pre-joined tables, pre-calculated
> results, aggregated data. These are not temporary tables, just part of
> the application - multi-user tables that stay across shutdown/restart.
You could also add caching search results for easy pagination without
redoing always entirely on each page the Big Slow Search Query that every
website has...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [Bizgres-general] Re: faster INSERT with possible
@ 2005-07-28 00:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2005-07-28 00:41 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance@postgresql.org, "Luke Lonergan" <llonergan@greenplum.com>; Hannu Krosing <hannu@skype.net>; John A Meinel <john@arbash-meinel.com>; Dan Harris <fbsd@drivefaster.net>; bizgres-general <bizgres-general@pgfoundry.org>
Josh Berkus <josh@agliodbs.com> writes:
> RIght. The problem is bypassing WAL for loading new "scratch" tables which
> aren't TEMPORARY tables. We need to do this for multi-threaded ETL, since:
> a) Temp tables can't be shared by several writers, and
> b) you can't index a temp table.
This may not matter given point (a), but: point (b) is completely wrong.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-07-28 00:41 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-26 19:30 Re: [Bizgres-general] Re: faster INSERT with possible Luke Lonergan <llonergan@greenplum.com>
2005-07-27 16:29 ` Josh Berkus <josh@agliodbs.com>
2005-07-27 18:28 ` Kris Jurka <books@ejurka.com>
2005-07-27 21:37 ` Simon Riggs <simon@2ndquadrant.com>
2005-07-27 21:53 ` PFC <lists@boutiquenumerique.com>
2005-07-28 00:41 ` 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