[rsyslog] [PERFORM] performance for high-volume log insertion (fwd)

[david at lang.hm]

from the postgres performance mailing list, relative speeds of different 
ways of inserting data.

I've asked if the 'seperate inserts' mode is seperate round trips or many 
inserts in one round trip.

based on this it looks like prepared statements make a difference, but not 
so much that other techniques (either a single statement or a copy) aren't 
comparable (or better) options.

David Lang

---------- Forwarded message ----------
Date: Wed, 22 Apr 2009 15:33:21 -0400
From: Glenn Maynard <glennfmaynard at gmail.com>
To: pgsql-performance at postgresql.org
Subject: Re: [PERFORM] performance for high-volume log insertion

On Wed, Apr 22, 2009 at 8:19 AM, Stephen Frost <sfrost at snowman.net> wrote:
> Yes, as I beleive was mentioned already, planning time for inserts is
> really small.  Parsing time for inserts when there's little parsing that
> has to happen also isn't all *that* expensive and the same goes for
> conversions from textual representations of data to binary.
>
> We're starting to re-hash things, in my view.  The low-hanging fruit is
> doing multiple things in a single transaction, either by using COPY,
> multi-value INSERTs, or just multiple INSERTs in a single transaction.
> That's absolutely step one.

This is all well-known, covered information, but perhaps some numbers
will help drive this home.  40000 inserts into a single-column,
unindexed table; with predictable results:

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

Of course, real workloads will change the weights, but this is more or
less the magnitude of difference I always see--batch your inserts into
single statements, and if that's not enough, skip to COPY.

-- 
Glenn Maynard

-- 
Sent via pgsql-performance mailing list (pgsql-performance at postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance