From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-performance@postgresql.org
Subject: syslog performance when logging big statements
Date: Tue, 8 Jul 2008 15:24:46 +0300
Message-ID: <200807081524.47366.achill@matrix.gatewaynet.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: syslog performance when logging big statements
In-Reply-To: <200807081524.47366.achill@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox