Re: performance for high-volume log insertion

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Glenn Maynard <glennfmaynard(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: performance for high-volume log insertion
Date: 2009-04-22 21:51:00
Message-ID: 20090422215100.GI8123@tamriel.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

* Glenn Maynard (glennfmaynard(at)gmail(dot)com) wrote:
> >> separate inserts, no transaction: 21.21s
> >> separate inserts, same transaction: 1.89s
> >> 40 inserts, 100 rows/insert: 0.18s
> >> one 40000-value insert: 0.16s
> >> 40 prepared inserts, 100 rows/insert: 0.15s
> >> COPY (text): 0.10s
> >> COPY (binary): 0.10s
> >
> > What about 40000 individual prepared inserts?  Just curious about it.
>
> 40000 inserts, one prepared statement each (constructing the prepared
> statement only once), in a single transaction: 1.68s
>
> I'm surprised that there's any win here at all.

For a single column table, I wouldn't expect much either. With more
columns I think it would be a larger improvement.

> Each row had one integer column. I expect strings to be harder to
> parse, since it's allocating buffers and parsing escapes, which is
> usually more expensive than parsing an integer out of a string. I'd
> expect the difference to be negligible either way, though, and I'd be
> interested in hearing about use cases where binary copying is enough
> of a win to be worth the development cost of maintaining the feature.

I've seen it help, but I was sending everything as binary (I figured,
once I'm doing it, might as well do it all), which included dates,
timestamps, IP addresses, integers, and some text. It may have more of
an impact on dates and timestamps than on simple integers.

Thanks!

Stephen

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Glenn Maynard 2009-04-22 22:16:18 Re: performance for high-volume log insertion
Previous Message Glenn Maynard 2009-04-22 21:48:02 Re: performance for high-volume log insertion