pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestions about 2 databases.
6+ messages / 5 participants
[nested] [flat]
* Questions about 2 databases.
@ 2005-03-11 18:54 jelle <jellej@pacbell.net>
0 siblings, 2 replies; 6+ messages in thread
From: jelle @ 2005-03-11 18:54 UTC (permalink / raw)
To: pgsql-performance
Hello All,
I have a couple of questions about running 2 databases:
1) on a single 7.4.6 postgres instance does each database have it own WAL
file or is that shared? Is it the same on 8.0.x?
2) what's the high performance way of moving 200 rows between similar
tables on different databases? Does it matter if the databases are
on the same or seperate postgres instances?
Background:
My web app does lots of inserts that aren't read until a session is
complete. The plan is to put the heavy insert session onto a ramdisk based
pg-db and transfer the relevant data to the master pg-db upon session
completion. Currently running 7.4.6.
Individual session data is not as critical as the master pg-db so the risk
associated with running the session pg-db on a ramdisk is acceptable.
All this is to get past the I/O bottleneck, already tweaked the config
files, run on multiple RAID-1 spindles, profiled the queries, maxed
the CPU/ram. Migrating to 64bit fedora soon.
Thanks, this mailing list has been invaluable.
Jelle
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Questions about 2 databases.
@ 2005-03-11 20:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: jelle <jellej@pacbell.net>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2005-03-11 20:33 UTC (permalink / raw)
To: jellej@pacbell.net; +Cc: pgsql-performance
jelle <jellej@pacbell.net> writes:
> 1) on a single 7.4.6 postgres instance does each database have it own WAL
> file or is that shared? Is it the same on 8.0.x?
Shared.
> 2) what's the high performance way of moving 200 rows between similar
> tables on different databases? Does it matter if the databases are
> on the same or seperate postgres instances?
COPY would be my recommendation. For a no-programming-effort solution
you could just pipe the output of pg_dump --data-only -t mytable
into psql. Not sure if it's worth developing a custom application to
replace that.
> My web app does lots of inserts that aren't read until a session is
> complete. The plan is to put the heavy insert session onto a ramdisk based
> pg-db and transfer the relevant data to the master pg-db upon session
> completion. Currently running 7.4.6.
Unless you have a large proportion of sessions that are abandoned and
hence never need be transferred to the main database at all, this seems
like a dead waste of effort :-(. The work to put the data into the main
database isn't lessened at all; you've just added extra work to manage
the buffer database.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Questions about 2 databases.
@ 2005-03-11 20:51 Richard_D_Levine@raytheon.com
0 siblings, 0 replies; 6+ messages in thread
From: Richard_D_Levine@raytheon.com @ 2005-03-11 20:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: jellej@pacbell.net; pgsql-performance; pgsql-performance-owner@postgresql.org
> this seems
> like a dead waste of effort :-(. The work to put the data into the main
> database isn't lessened at all; you've just added extra work to manage
> the buffer database.
True from the view point of the server, but not from the throughput in the
client session (client viewpoint). The client will have a blazingly fast
session with the buffer database. I'm assuming the buffer database table
size is zero or very small. Constraints will be a problem if there are
PKs, FKs that need satisfied on the server that are not adequately testable
in the buffer. Might not be a problem if the full table fits on the RAM
disk, but you still have to worry about two clients inserting the same PK.
Rick
Tom Lane
<tgl@sss.pgh.pa.us> To: jellej@pacbell.net
Sent by: cc: pgsql-performance@postgresql.org
pgsql-performance-owner@pos Subject: Re: [PERFORM] Questions about 2 databases.
tgresql.org
03/11/2005 03:33 PM
jelle <jellej@pacbell.net> writes:
> 1) on a single 7.4.6 postgres instance does each database have it own WAL
> file or is that shared? Is it the same on 8.0.x?
Shared.
> 2) what's the high performance way of moving 200 rows between similar
> tables on different databases? Does it matter if the databases are
> on the same or seperate postgres instances?
COPY would be my recommendation. For a no-programming-effort solution
you could just pipe the output of pg_dump --data-only -t mytable
into psql. Not sure if it's worth developing a custom application to
replace that.
> My web app does lots of inserts that aren't read until a session is
> complete. The plan is to put the heavy insert session onto a ramdisk
based
> pg-db and transfer the relevant data to the master pg-db upon session
> completion. Currently running 7.4.6.
Unless you have a large proportion of sessions that are abandoned and
hence never need be transferred to the main database at all, this seems
like a dead waste of effort :-(. The work to put the data into the main
database isn't lessened at all; you've just added extra work to manage
the buffer database.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Questions about 2 databases.
@ 2005-03-11 21:43 jelle <jellej@pacbell.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: jelle @ 2005-03-11 21:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Fri, 11 Mar 2005, Tom Lane wrote:
[ snip ]
> COPY would be my recommendation. For a no-programming-effort solution
> you could just pipe the output of pg_dump --data-only -t mytable
> into psql. Not sure if it's worth developing a custom application to
> replace that.
I'm a programming-effort kind of guy so I'll try COPY.
>
>> My web app does lots of inserts that aren't read until a session is
>> complete. The plan is to put the heavy insert session onto a ramdisk based
>> pg-db and transfer the relevant data to the master pg-db upon session
>> completion. Currently running 7.4.6.
>
> Unless you have a large proportion of sessions that are abandoned and
> hence never need be transferred to the main database at all, this seems
> like a dead waste of effort :-(. The work to put the data into the main
> database isn't lessened at all; you've just added extra work to manage
> the buffer database.
The insert heavy sessions average 175 page hits generating XML, 1000
insert/updates which comprise 90% of the insert/update load, of which 200
inserts need to be transferred to the master db. The other sessions are
read/cache bound. I hoping to get a speed-up from moving the temporary
stuff off the master db and using 1 transaction instead of 175 to the disk
based master db.
Thanks,
Jelle
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Questions about 2 databases.
@ 2005-03-12 02:15 PFC <lists@boutiquenumerique.com>
parent: jelle <jellej@pacbell.net>
1 sibling, 0 replies; 6+ messages in thread
From: PFC @ 2005-03-12 02:15 UTC (permalink / raw)
To: jellej@pacbell.net; pgsql-performance
> My web app does lots of inserts that aren't read until a session is
> complete. The plan is to put the heavy insert session onto a ramdisk
> based pg-db and transfer the relevant data to the master pg-db upon
> session completion. Currently running 7.4.6.
From what you say I'd think you want to avoid making one write
transaction to the main database on each page view, right ?
You could simply store the data in a file, and at the end of the session,
read the file and do all the writes in one transaction.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Questions about 2 databases.
@ 2005-03-14 10:43 Mirko Zeibig <mirko-individual@zeibig.net>
parent: jelle <jellej@pacbell.net>
0 siblings, 0 replies; 6+ messages in thread
From: Mirko Zeibig @ 2005-03-14 10:43 UTC (permalink / raw)
To: pgsql-performance
jelle wrote:
> The insert heavy sessions average 175 page hits generating XML, 1000
> insert/updates which comprise 90% of the insert/update load, of which
> 200 inserts need to be transferred to the master db. The other
> sessions are read/cache bound. I hoping to get a speed-up from moving
> the temporary stuff off the master db and using 1 transaction
> instead of 175 to the disk based master db.
Just a thought:
Wouldn't it be sufficient to have the "temporary", fast session-table
in a RAM-disk? I suspect you could do this rather easily using a TABLESPACE.
All the indices could be in this TABLESPACE as well (at least after
having a quick look at the short help for CREATE TABLE and assuming you are
using PostgreSQL >= 8.0).
Regards
Mirko
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-03-14 10:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-11 18:54 Questions about 2 databases. jelle <jellej@pacbell.net>
2005-03-11 20:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-11 21:43 ` jelle <jellej@pacbell.net>
2005-03-14 10:43 ` Mirko Zeibig <mirko-individual@zeibig.net>
2005-03-12 02:15 ` PFC <lists@boutiquenumerique.com>
2005-03-11 20:51 Re: Questions about 2 databases. Richard_D_Levine@raytheon.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