Thread: insertion of bytea

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.

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

"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

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

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

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

> I'm CPU-bound with an I/O well below what my disks could do :(
> [...]
>
> PS1: someone off-list suggested using oprofile, which I will do.

I've used oprofile and found out that with my test client (lots of
bytea inserts) the server burns a lot of CPU time in pglz_compress.

I'm using random data and my production data will be closed to random
(due to noise!), so compression is of course pointless.

By using
alter table dbtest alter img set storage external;
I can tell the server not to compress.

On a test box this brought net insert rate up by 50%,
which is enough to meet the requirements.

Thanks again :)

Bye, Chris.