SEO

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.

From the test run it appears that the insert rate here is
essentially CPU bound. I'm getting about 11 MB/s net transfer,
regardless if I use the built in disk or the much faster
array and regardless various settings (like n, shared_mem).

vmstat says that disk bo is about 30MB/s (the array can do much
better, I tried with dd and sync!) while the CPU is maxed out
at about 90% us and 10% sy. The client accounts for just 2% CPU,
most goes into the postmaster.

The client inserts random data. I found out that I can improve
things by 35% if I use random sequences of bytes that are
in the printable range vs. full range.


Question 1:
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?


Question 2:
Is there a better, faster way to do these inserts?
I'm unsure about large objects. I'm planning to use some
custom server side functions to do computations on the bytes
in these records and the large objects API doesn't appear
to be well suited for this.


Sidequestion:
I've tried to profile the server using CFLAGS="-p -DLINUX_PROFILE".
I'm getting profiling output but when I look at it using
"gprof bin-somewhere/postgres $PGDATA/gmon.out" I'm only seeing
what I think are the calls for the server startup. How can I profile
the (forked) process that actually performs all the work on
my connection?


Sorry for the long post
Bye,
Chris.




---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

>>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.




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

>> 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.



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> 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.





---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings