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
0o....%%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
slowthe 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
thisbad 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