Re: [PERFORM] insertion of bytea
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
[PERFORM] 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] insertion of bytea
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
Re: [PERFORM] insertion of bytea
Chris Mair [EMAIL PROTECTED] 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
Re: [PERFORM] insertion of bytea
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
Re: [PERFORM] insertion of bytea
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
