Re: insert performance riddle

On Wednesday August 10 2005 6:03 pm, Michael Fuhr wrote:
> On Wed, Aug 10, 2005 at 05:02:46PM -0600, Ed L. wrote:
> > I have two identical servers giving abysmal INSERT
> > performance in pgsql 7.3.4, 7.4.8, and 8.1devel under no
> > load or I/O contention at all (no dumps, no vacuums, no
> > apps, etc). Any suggested investigations appreciated...
> >
> > Results: The slow servers are doing 6...count'em,
> > SIX...that's right, I said S-I-X inserts per second (QPS) on
> > average.
>
> Have you done any tests that eliminate the client code and
> client-server communcation? Something like the following:
>
> CREATE FUNCTION insert_foo(integer) RETURNS void AS '
> DECLARE
> i integer;
> BEGIN
> FOR i IN 1 .. $1 LOOP
> INSERT INTO foo (msg) VALUES (''this is a test
> message''); END LOOP;
>
> RETURN;
> END;
> ' LANGUAGE plpgsql VOLATILE STRICT;
>
> EXPLAIN ANALYZE SELECT insert_foo(1000);

Michael, you seem to have nailed it. The local inserts (via Unix
domain sockets?) that were running at 6 QPS ran at 6800 to 41000
QPS in a PL/pgSQL function.

So, given the same DBI/DBD::Pg code is performing 2 orders of
magnitude better on other similar systems, where would you look
next? The local socket system call code? Where else? My next
thought is to gprof the client code to identify the slowdown...

Thanks,
Ed