pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance of temporary vs. regular tables
15+ messages / 6 participants
[nested] [flat]
* performance of temporary vs. regular tables
@ 2010-05-25 07:59 Joachim Worringen <joachim.worringen@iathh.de>
0 siblings, 1 reply; 15+ messages in thread
From: Joachim Worringen @ 2010-05-25 07:59 UTC (permalink / raw)
To: pgsql-performance
Greetings,
in
http://archives.postgresql.org/message-id/1056648218.7041.11.camel@jester,
it is stated that the performance of temporary tables is "the same as a
regular table but without
WAL on the table contents.".
I have a datamining-type application which makes heavy use of temporary
tables to stage (potentially large amounts of) data between different
operations. WAL is write-ahead
To effectively multi-thread this application, I (think I) need to switch
from temporary to regular tables, because
- the concurrent threads need to use different connections, not cursors,
to effectively operate concurrently
- temporary tables are not visible across connections (as they are
across cursors of the same connection)
Thus, I wonder how much this will affect performance. Access on the
temporary table is inserting (millions of) rows once in a single
transaction, potentially update them all once within a single
transaction, then select on them once or more.
Of course, eventually loosing the data in these tables is not a problem
at all. The threads are synchronized above the SQL level.
Thanks for any input on how to maximize performance for this applicaiton.
Joachim
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-05-25 08:49 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Joachim Worringen <joachim.worringen@iathh.de>
0 siblings, 1 reply; 15+ messages in thread
From: Grzegorz Jaśkiewicz @ 2010-05-25 08:49 UTC (permalink / raw)
To: Joachim Worringen <joachim.worringen@iathh.de>; +Cc: pgsql-performance
temporary tables are handled pretty much like the regular table. The
magic happens on schema level, new schema is setup for connection, so
that it can access its own temporary tables.
Temporary tables also are not autovacuumed.
And that's pretty much the most of the differences.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-05-25 09:00 Joachim Worringen <joachim.worringen@iathh.de>
parent: Grzegorz Jaśkiewicz <gryzman@gmail.com>
0 siblings, 2 replies; 15+ messages in thread
From: Joachim Worringen @ 2010-05-25 09:00 UTC (permalink / raw)
To: Grzegorz Jaśkiewicz <gryzman@gmail.com>; +Cc: pgsql-performance
Am 25.05.2010 10:49, schrieb Grzegorz Jaśkiewicz:
> temporary tables are handled pretty much like the regular table. The
> magic happens on schema level, new schema is setup for connection, so
> that it can access its own temporary tables.
> Temporary tables also are not autovacuumed.
> And that's pretty much the most of the differences.
Thanks. So, the Write-Ahead-Logging (being used or not) does not matter?
And, is there anything like RAM-only tables? I really don't care whether
the staging data is lost on the rare event of a machine crash, or
whether the query crashes due to lack of memory (I make sure there's
enough w/o paging) - I only care about performance here.
Joachim
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-05-25 09:15 Thom Brown <thombrown@gmail.com>
parent: Joachim Worringen <joachim.worringen@iathh.de>
1 sibling, 1 reply; 15+ messages in thread
From: Thom Brown @ 2010-05-25 09:15 UTC (permalink / raw)
To: Joachim Worringen <joachim.worringen@iathh.de>; +Cc: Grzegorz Jaśkiewicz <gryzman@gmail.com>; pgsql-performance
2010/5/25 Joachim Worringen <joachim.worringen@iathh.de>:
> Am 25.05.2010 10:49, schrieb Grzegorz Jaśkiewicz:
>>
>> temporary tables are handled pretty much like the regular table. The
>> magic happens on schema level, new schema is setup for connection, so
>> that it can access its own temporary tables.
>> Temporary tables also are not autovacuumed.
>> And that's pretty much the most of the differences.
>
> Thanks. So, the Write-Ahead-Logging (being used or not) does not matter?
>
> And, is there anything like RAM-only tables? I really don't care whether the
> staging data is lost on the rare event of a machine crash, or whether the
> query crashes due to lack of memory (I make sure there's enough w/o paging)
> - I only care about performance here.
>
> Joachim
>
I think can create a tablespace on a ram disk, and create a table there.
Thom
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-05-25 09:32 Joachim Worringen <joachim.worringen@iathh.de>
parent: Thom Brown <thombrown@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Joachim Worringen @ 2010-05-25 09:32 UTC (permalink / raw)
To: pgsql-performance
Am 25.05.2010 11:15, schrieb Thom Brown:
> 2010/5/25 Joachim Worringen<joachim.worringen@iathh.de>:
>> And, is there anything like RAM-only tables? I really don't care whether the
>> staging data is lost on the rare event of a machine crash, or whether the
>> query crashes due to lack of memory (I make sure there's enough w/o paging)
>> - I only care about performance here.
>>
>> Joachim
>>
>
> I think can create a tablespace on a ram disk, and create a table there.
True, but I think this makes the database server configuration more
complex (which is acceptable), and may add dependencies between the
server configuration and the SQL statements for the selection of
tablespace name (which would be a problem)?
But I am a tablespace-novice and will look into this "workaround".
thanks, Joachim
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-05-25 09:38 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Joachim Worringen <joachim.worringen@iathh.de>
0 siblings, 1 reply; 15+ messages in thread
From: Grzegorz Jaśkiewicz @ 2010-05-25 09:38 UTC (permalink / raw)
To: Joachim Worringen <joachim.worringen@iathh.de>; +Cc: pgsql-performance
WAL does the same thing to DB journaling does to the FS.
Plus allows you to roll back (PITR).
As for the RAM, it will be in ram as long as OS decides to keep it in
RAM cache, and/or its in the shared buffers memory.
Unless you have a lot of doubt about the two, I don't think it makes
too much sens to setup ramdisk table space yourself. But try it, and
see yourself.
Make sure that you have logic in place, that would set it up, before
postgresql starts up, in case you'll reboot, or something.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-05-25 09:52 Joachim Worringen <joachim.worringen@iathh.de>
parent: Grzegorz Jaśkiewicz <gryzman@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Joachim Worringen @ 2010-05-25 09:52 UTC (permalink / raw)
To: pgsql-performance
Am 25.05.2010 11:38, schrieb Grzegorz Jaśkiewicz:
> WAL does the same thing to DB journaling does to the FS.
> Plus allows you to roll back (PITR).
>
> As for the RAM, it will be in ram as long as OS decides to keep it in
> RAM cache, and/or its in the shared buffers memory.
Or until I commit the transaction? I have not completely disabled
sync-to-disk in my setup, as there are of course situations where new
data comes into the database that needs to be stored in a safe manner.
> Unless you have a lot of doubt about the two, I don't think it makes
> too much sens to setup ramdisk table space yourself. But try it, and
> see yourself.
> Make sure that you have logic in place, that would set it up, before
> postgresql starts up, in case you'll reboot, or something.
That's what I thought about when mentioning "increased setup
complexity". Simply adding a keyword like "NONPERSISTENT" to the table
creation statement would be preferred...
Joachim
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-05-25 10:41 Andres Freund <andres@anarazel.de>
parent: Joachim Worringen <joachim.worringen@iathh.de>
1 sibling, 1 reply; 15+ messages in thread
From: Andres Freund @ 2010-05-25 10:41 UTC (permalink / raw)
To: pgsql-performance; +Cc: Joachim Worringen <joachim.worringen@iathh.de>; Grzegorz Jaśkiewicz <gryzman@gmail.com>
On Tuesday 25 May 2010 11:00:24 Joachim Worringen wrote:
> Am 25.05.2010 10:49, schrieb Grzegorz Jaśkiewicz:
> > temporary tables are handled pretty much like the regular table. The
> > magic happens on schema level, new schema is setup for connection, so
> > that it can access its own temporary tables.
> > Temporary tables also are not autovacuumed.
> > And that's pretty much the most of the differences.
>
> Thanks. So, the Write-Ahead-Logging (being used or not) does not matter?
It does matter quite significantly in my experience. Both from an io and a cpu
overhead perspective.
Andres
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-05-26 16:03 Joachim Worringen <joachim.worringen@iathh.de>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 2 replies; 15+ messages in thread
From: Joachim Worringen @ 2010-05-26 16:03 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: pgsql-performance@postgresql.org, Grzegorz Jaśkiewicz <gryzman@gmail.com>
Am 25.05.2010 12:41, schrieb Andres Freund:
> On Tuesday 25 May 2010 11:00:24 Joachim Worringen wrote:
>> Thanks. So, the Write-Ahead-Logging (being used or not) does not matter?
> It does matter quite significantly in my experience. Both from an io and a cpu
> overhead perspective.
O.k., looks as if I have to make my own experience... I'll let you know
if possible.
Joachim
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-05-26 16:47 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Joachim Worringen <joachim.worringen@iathh.de>
1 sibling, 0 replies; 15+ messages in thread
From: Grzegorz Jaśkiewicz @ 2010-05-26 16:47 UTC (permalink / raw)
To: Joachim Worringen <joachim.worringen@iathh.de>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-performance
WAL matters in performance. Hence why it is advisable to have it on a
separate drive :)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-05-28 11:04 Joachim Worringen <joachim.worringen@iathh.de>
parent: Joachim Worringen <joachim.worringen@iathh.de>
1 sibling, 2 replies; 15+ messages in thread
From: Joachim Worringen @ 2010-05-28 11:04 UTC (permalink / raw)
To: pgsql-performance
On 05/26/2010 06:03 PM, Joachim Worringen wrote:
> Am 25.05.2010 12:41, schrieb Andres Freund:
>> On Tuesday 25 May 2010 11:00:24 Joachim Worringen wrote:
>>> Thanks. So, the Write-Ahead-Logging (being used or not) does not matter?
>> It does matter quite significantly in my experience. Both from an io
>> and a cpu
>> overhead perspective.
>
> O.k., looks as if I have to make my own experience... I'll let you know
> if possible.
As promised, I did a tiny benchmark - basically, 8 empty tables are
filled with 100k rows each within 8 transactions (somewhat typically for
my application). The test machine has 4 cores, 64G RAM and RAID1 10k
drives for data.
# INSERTs into a TEMPORARY table:
[joachim@testsrv scaling]$ time pb query -d scaling_qry_1.xml
real 3m18.242s
user 1m59.074s
sys 1m51.001s
# INSERTs into a standard table:
[joachim@testsrv scaling]$ time pb query -d scaling_qry_1.xml
real 3m35.090s
user 2m5.295s
sys 2m2.307s
Thus, there is a slight hit of about 10% (which may even be within
meausrement variations) - your milage will vary.
Joachim
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-05-28 17:07 Rob Wultsch <wultsch@gmail.com>
parent: Joachim Worringen <joachim.worringen@iathh.de>
1 sibling, 0 replies; 15+ messages in thread
From: Rob Wultsch @ 2010-05-28 17:07 UTC (permalink / raw)
To: Joachim Worringen <joachim.worringen@iathh.de>; +Cc: pgsql-performance
On Fri, May 28, 2010 at 4:04 AM, Joachim Worringen
<joachim.worringen@iathh.de> wrote:
> On 05/26/2010 06:03 PM, Joachim Worringen wrote:
>>
>> Am 25.05.2010 12:41, schrieb Andres Freund:
>>>
>>> On Tuesday 25 May 2010 11:00:24 Joachim Worringen wrote:
>>>>
>>>> Thanks. So, the Write-Ahead-Logging (being used or not) does not matter?
>>>
>>> It does matter quite significantly in my experience. Both from an io
>>> and a cpu
>>> overhead perspective.
>>
>> O.k., looks as if I have to make my own experience... I'll let you know
>> if possible.
>
> As promised, I did a tiny benchmark - basically, 8 empty tables are filled
> with 100k rows each within 8 transactions (somewhat typically for my
> application). The test machine has 4 cores, 64G RAM and RAID1 10k drives for
> data.
>
> # INSERTs into a TEMPORARY table:
> [joachim@testsrv scaling]$ time pb query -d scaling_qry_1.xml
>
> real 3m18.242s
> user 1m59.074s
> sys 1m51.001s
>
> # INSERTs into a standard table:
> [joachim@testsrv scaling]$ time pb query -d scaling_qry_1.xml
>
> real 3m35.090s
> user 2m5.295s
> sys 2m2.307s
>
> Thus, there is a slight hit of about 10% (which may even be within
> meausrement variations) - your milage will vary.
>
> Joachim
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
I think it would be interesting to create a ram disk and insert into
it. In the MySQL community even thought MyISAM has fallen out of use
the Memory table (based on MyISAM) is still somewhat used.
--
Rob Wultsch
wultsch@gmail.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-06-02 10:03 Pierre C <lists@peufeu.com>
parent: Joachim Worringen <joachim.worringen@iathh.de>
1 sibling, 1 reply; 15+ messages in thread
From: Pierre C @ 2010-06-02 10:03 UTC (permalink / raw)
To: Joachim Worringen <joachim.worringen@iathh.de>; pgsql-performance
> As promised, I did a tiny benchmark - basically, 8 empty tables are
> filled with 100k rows each within 8 transactions (somewhat typically for
> my application). The test machine has 4 cores, 64G RAM and RAID1 10k
> drives for data.
>
> # INSERTs into a TEMPORARY table:
> [joachim@testsrv scaling]$ time pb query -d scaling_qry_1.xml
>
> real 3m18.242s
> user 1m59.074s
> sys 1m51.001s
>
> # INSERTs into a standard table:
> [joachim@testsrv scaling]$ time pb query -d scaling_qry_1.xml
>
> real 3m35.090s
> user 2m5.295s
> sys 2m2.307s
>
> Thus, there is a slight hit of about 10% (which may even be within
> meausrement variations) - your milage will vary.
Usually WAL causes a much larger performance hit than this.
Since the following command :
CREATE TABLE tmp AS SELECT n FROM generate_series(1,1000000) AS n
which inserts 1M rows takes 1.6 seconds on my desktop, your 800k rows
INSERT taking more than 3 minutes is a bit suspicious unless :
- you got huge fields that need TOASTing ; in this case TOAST compression
will eat a lot of CPU and you're benchmarking TOAST, not the rest of the
system
- you got some non-indexed foreign key
- some other reason ?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-06-02 12:54 Joachim Worringen <joachim.worringen@iathh.de>
parent: Pierre C <lists@peufeu.com>
0 siblings, 1 reply; 15+ messages in thread
From: Joachim Worringen @ 2010-06-02 12:54 UTC (permalink / raw)
To: Pierre C <lists@peufeu.com>; +Cc: pgsql-performance
Am 02.06.2010 12:03, schrieb Pierre C:
> Usually WAL causes a much larger performance hit than this.
>
> Since the following command :
>
> CREATE TABLE tmp AS SELECT n FROM generate_series(1,1000000) AS n
>
> which inserts 1M rows takes 1.6 seconds on my desktop, your 800k rows
> INSERT taking more than 3 minutes is a bit suspicious unless :
>
> - you got huge fields that need TOASTing ; in this case TOAST
> compression will eat a lot of CPU and you're benchmarking TOAST, not the
> rest of the system
> - you got some non-indexed foreign key
> - some other reason ?
Yes, the "other" reason is that I am not issueing a single SQL command,
but import data from plain ASCII files through the Pyhton-based
framework into the database.
The difference between your measurement and my measurent is the upper
potential of improvement for my system (which has, on the other hand,
the advantage of being a bit more powerful and flexible than a single
SQL statement....;-) )
Joachim
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: performance of temporary vs. regular tables
@ 2010-06-06 22:37 Pierre C <lists@peufeu.com>
parent: Joachim Worringen <joachim.worringen@iathh.de>
0 siblings, 0 replies; 15+ messages in thread
From: Pierre C @ 2010-06-06 22:37 UTC (permalink / raw)
To: Joachim Worringen <joachim.worringen@iathh.de>; +Cc: pgsql-performance
> Yes, the "other" reason is that I am not issueing a single SQL command,
> but import data from plain ASCII files through the Pyhton-based
> framework into the database.
>
> The difference between your measurement and my measurent is the upper
> potential of improvement for my system (which has, on the other hand,
> the advantage of being a bit more powerful and flexible than a single
> SQL statement....;-) )
Ah, in that case ... ;)
You could give pypy a try, sometimes it's a lot slower, sometimes it's a
lot faster.
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2010-06-06 22:37 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-25 07:59 performance of temporary vs. regular tables Joachim Worringen <joachim.worringen@iathh.de>
2010-05-25 08:49 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2010-05-25 09:00 ` Joachim Worringen <joachim.worringen@iathh.de>
2010-05-25 09:15 ` Thom Brown <thombrown@gmail.com>
2010-05-25 09:32 ` Joachim Worringen <joachim.worringen@iathh.de>
2010-05-25 09:38 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2010-05-25 09:52 ` Joachim Worringen <joachim.worringen@iathh.de>
2010-05-25 10:41 ` Andres Freund <andres@anarazel.de>
2010-05-26 16:03 ` Joachim Worringen <joachim.worringen@iathh.de>
2010-05-26 16:47 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2010-05-28 11:04 ` Joachim Worringen <joachim.worringen@iathh.de>
2010-05-28 17:07 ` Rob Wultsch <wultsch@gmail.com>
2010-06-02 10:03 ` Pierre C <lists@peufeu.com>
2010-06-02 12:54 ` Joachim Worringen <joachim.worringen@iathh.de>
2010-06-06 22:37 ` Pierre C <lists@peufeu.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