syslog performance when logging big statements

From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: syslog performance when logging big statements
Date: 2008-07-08 12:24:46
Message-ID: 200807081524.47366.achill@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(at)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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2008-07-08 14:35:16 Re: syslog performance when logging big statements
Previous Message Markus Wanner 2008-07-08 09:49:36 Re: Fusion-io ioDrive