pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedinsertion of bytea
6+ messages / 3 participants
[nested] [flat]
* insertion of bytea
@ 2005-10-25 13:44 Chris Mair <list@1006.org>
0 siblings, 2 replies; 6+ messages in thread
From: Chris Mair @ 2005-10-25 13:44 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have the following test setup:
* PG 8.0.4 on Linux (Centos 4) compiled from source.
* DB schema: essentially one table with a few int columns and
one bytea column that stores blobs of 52000 bytes each, a
primary key on one of the int columns.
* A test client was written in C using libpq to see what rate
can be reached (inserting records). The client uses a
prepared tatement and bundles n inserts into a single
transaction (n is variable for testing).
* Hardware: different setups tested, in particular a
single-opteron box with a built in SATA disk and also an
array of SATA disks connected via FC.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: insertion of bytea
@ 2005-10-25 14:05 Michael Stone <mstone+postgres@mathom.us>
parent: Chris Mair <list@1006.org>
1 sibling, 2 replies; 6+ messages in thread
From: Michael Stone @ 2005-10-25 14:05 UTC (permalink / raw)
To: Chris Mair <list@1006.org>; +Cc: pgsql-performance
On Tue, Oct 25, 2005 at 03:44:36PM +0200, Chris Mair wrote:
>Is there a better, faster way to do these inserts?
COPY is generally the fastest way to do bulk inserts (see
PQputCopyData).
Mike Stone
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: insertion of bytea
@ 2005-10-25 14:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Mair <list@1006.org>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2005-10-25 14:23 UTC (permalink / raw)
To: Chris Mair <list@1006.org>; +Cc: pgsql-performance
"Chris Mair" <list@1006.org> writes:
> Am I correct in assuming that even though I'm passing my 52000
> bytes as a (char *) to PQexecPrepared(), encoding/decoding is
> happening (think 0 -> \000) somewhere in the transfer?
Are you specifying it as a text or binary parameter? Have you looked to
see if the stored data is what you expect?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: insertion of bytea
@ 2005-10-25 21:36 Chris Mair <list@1006.org>
parent: Michael Stone <mstone+postgres@mathom.us>
1 sibling, 0 replies; 6+ messages in thread
From: Chris Mair @ 2005-10-25 21:36 UTC (permalink / raw)
To: mstone+postgres@mathom.us; +Cc: list@1006.org; pgsql-performance
>>Is there a better, faster way to do these inserts?
>
> COPY is generally the fastest way to do bulk inserts (see
> PQputCopyData).
Thanks :)
I'll give that I try and report the results here later.
Bye, Chris.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: insertion of bytea
@ 2005-10-25 21:41 Chris Mair <list@1006.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Chris Mair @ 2005-10-25 21:41 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: list@1006.org; pgsql-performance
>> Am I correct in assuming that even though I'm passing my 52000
>> bytes as a (char *) to PQexecPrepared(), encoding/decoding is
>> happening (think 0 -> \000) somewhere in the transfer?
>
> Are you specifying it as a text or binary parameter? Have you looked to
> see if the stored data is what you expect?
I'm specifying it as binary (i.e. one's in PQexecPrepared's
format parameter). The stored data is correct.
I'll try "copy from stdin with binary" tomorrow and see what
I get...
Thanks & Bye, Chris.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: insertion of bytea
@ 2005-10-27 13:40 Chris Mair <list@1006.org>
parent: Michael Stone <mstone+postgres@mathom.us>
1 sibling, 0 replies; 6+ messages in thread
From: Chris Mair @ 2005-10-27 13:40 UTC (permalink / raw)
To: mstone+postgres@mathom.us; +Cc: pgsql-performance
> On Tue, Oct 25, 2005 at 03:44:36PM +0200, Chris Mair wrote:
>>Is there a better, faster way to do these inserts?
>
> COPY is generally the fastest way to do bulk inserts (see
> PQputCopyData).
Hi,
I've rewritten the testclient now to use COPY, but I'm getting
the exact same results as when doing bundled, prepared inserts.
I'm CPU-bound with an I/O well below what my disks could do :(
Bye, Chris.
PS1: someone off-list suggested using oprofile, which I will do.
PS2: in case somebody is iterested, the test client is here:
http://www.1006.org/tmp/20051027/
pgclient-1.1.c is prepared inserts, 2.0 is binary copy.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-10-27 13:40 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-25 13:44 insertion of bytea Chris Mair <list@1006.org>
2005-10-25 14:05 ` Michael Stone <mstone+postgres@mathom.us>
2005-10-25 21:36 ` Chris Mair <list@1006.org>
2005-10-27 13:40 ` Chris Mair <list@1006.org>
2005-10-25 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-25 21:41 ` Chris Mair <list@1006.org>
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