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.
