From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-performance@postgresql.org
Subject: syslog performance when logging big statements
Date: Tue, 8 Jul 2008 15:24:46 +0300
User-Agent: KMail/1.9.7
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200807081524.47366.achill@matrix.gatewaynet.com>

Hi i have experienced really bad performance on both FreeBSD and linux, with syslog,
when logging statements involving bytea of size ~ 10 Mb.
Consider this scenario:
postgres@dynacom=# \d marinerpapers_atts
                                            Table "public.marinerpapers_atts"
   Column    |           Type           |                                   Modifiers                                   
-------------+--------------------------+--------------------------------------------------------------------------------
 id          | integer                  | not null default nextval(('public.marinerpapers_atts_id_seq'::text)::regclass)
 marinerid   | integer                  | not null
 filename    | text                     | not null
 mimetype    | character varying(50)    | not null
 datecreated | timestamp with time zone | not null default now()
 docsrc      | bytea                    | not null
Indexes:
    "marinerpapers_atts_pkey" PRIMARY KEY, btree (id)
    "marinerpapers_atts_ukey" UNIQUE, btree (marinerid, filename)
    "marinerpapers_atts_marinerid" btree (marinerid)
Foreign-key constraints:
    "$1" FOREIGN KEY (marinerid) REFERENCES mariner(id) ON DELETE CASCADE

The way the insert is done is like
INSERT INTO marinerpapers_atts(marinerid,filename,mimetype,docsrc) VALUES(1,'foo.pdf','aplication/pdf','%PDF-1.3\\0124 0 o....%%EOF\\012');

When someone tries to insert a row in the above table which results to an error (because e.g. violates the 
"marinerpapers_atts_ukey"  constraint), the whole statement is logged to the logging system.

File sizes of about 3M result in actual logging output of ~ 10Mb.
In this case, the INSERT *needs* 20 minutes to return. This is because the logging through syslog seems to severely slow the system.
If instead, i use stderr, even with logging_collector=on, the same statement needs 15 seconds to return.

I am using syslog since like the stone age, and i would like to stick with it, however this morning i was caught by this bad performance
and i am planning moving to stderr + logging_collector.

P.S.
Is there a way to better tune pgsql/syslog in order to work more efficiently in cases like that?
I know it is a corner case, however i thought i should post my experiences.
Thanx

-- 
Achilleas Mantzios