Hi,

Software and hardware running postgresql are:
- postgresql92-9.2.3-1.1.1.x86_64
- openSuSE 12.3 x64_86
- 16 GB of RAM
- 2 GB of swap
- 8-core Intel(R) Xeon(R) CPU E5-2407 0 @ 2.20GHz
- ext4 filesystem hold on a hardware Dell PERC H710 RAID10 with 4x4TB SATA HDs.
- 2 GB of RAM are reserved for a virtual machine.

The single database used was created by
CREATE FUNCTION msg_function() RETURNS trigger
    LANGUAGE plpgsql
    AS $_$ DECLARE _tablename text; _date text; _slot timestamp; BEGIN _slot := 
NEW.slot; _date := to_char(_slot, 'YYYY-MM-DD'); _tablename := 'MSG_'||_date; 
PERFORM 1 FROM   pg_catalog.pg_class c JOIN   pg_catalog.pg_namespace n 
ON n.oid = c.relnamespace WHERE  c.relkind = 'r' AND    c.relname = _tablename 
AND    n.nspname = 'public'; IF NOT FOUND THEN EXECUTE 'CREATE TABLE 
public.' || quote_ident(_tablename) || ' ( ) INHERITS (public.MSG)'; EXECUTE 
'ALTER 
TABLE public.' || quote_ident(_tablename) || ' OWNER TO seviri'; EXECUTE 'GRANT 
ALL ON TABLE public.' || quote_ident(_tablename) || ' TO seviri'; EXECUTE 
'ALTER 
TABLE ONLY public.' || quote_ident(_tablename) || ' ADD CONSTRAINT ' || 
quote_ident(_tablename||'_pkey') || ' PRIMARY KEY (slot,msg)'; END IF; EXECUTE 
'INSERT INTO public.' || quote_ident(_tablename) || ' VALUES ($1.*)' USING NEW; 
RETURN NULL; END; $_$;

CREATE TABLE msg (
    slot timestamp(0) without time zone NOT NULL,
    msg integer NOT NULL,
    hrv bytea,
    vis006 bytea,
    vis008 bytea,
    ir_016 bytea,
    ir_039 bytea,
    wv_062 bytea,
    wv_073 bytea,
    ir_087 bytea,
    ir_097 bytea,
    ir_108 bytea,
    ir_120 bytea,
    ir_134 bytea,
    pro bytea,
    epi bytea,
    clm bytea,
    tape character varying(10)
);

Basically, this database consists of daily tables with the date stamp appended 
in their 
names, i.e.
MSG_YYYY-MM-DD and a global table MSG linked to these tables allowing to list 
all 
the records.

A cron script performing a single insert (upsert, see log excerpt below) runs 
every 15 
minutes and
never had any issue.

However, I also need to submit historical records. This is achieved by a bash 
script 
parsing a text file
and building insert commands which are submitted 10 at a time to the database 
using psql through a
temp file in a BEGIN; ...; COMMIT block. When running this script, I noticed 
that the 
INSERT
subprocess can reached around 4GB of memory using htop (see attached 
screenshot). After a while,
the script inevitably crashes with the following messages
psql:/tmp/tmp.a0ZrivBZhD:10: connection to server was lost
Could not submit SQL request file /tmp/tmp.a0ZrivBZhD to database

and the associated entries in the log:
2014-12-15 17:54:07 GMT   LOG:  server process (PID 21897) was terminated by 
signal 9: Killed
2014-12-15 17:54:07 GMT   DETAIL:  Failed process was running: WITH upsert AS 
(update MSG set 
(slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_1
08,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201212032145', 
'YYYYMMDDHH24MI'),2,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01
','\x01','\x7f','LTO5_020') where slot=to_timestamp('201212032145', 
'YYYYMMDDHH24MI') and MSG=2 RETURNING *) insert into MSG 
(slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_1
08,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201212032145', 
'YYYYMMDDHH24MI'),2,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01
','\x01','\x7f','LTO5_020' WHERE NOT EXISTS (SELECT * FROM upsert);
2014-12-15 17:54:07 GMT   LOG:  terminating any other active server processes
2014-12-15 17:54:07 GMT   WARNING:  terminating connection because of crash of 
another server process
2014-12-15 17:54:07 GMT   DETAIL:  The postmaster has commanded this server 
process to roll back the current transaction and exit, because another server 
process 
exited abnormally and possibly corrupted shared memory.
2014-12-15 17:54:07 GMT   HINT:  In a moment you should be able to reconnect to 
the database and repeat your command.
2014-12-15 17:54:07 GMT seviri seviri WARNING:  terminating connection because 
of crash of another server process
2014-12-15 17:54:07 GMT seviri seviri DETAIL:  The postmaster has commanded 
this server process to roll back the current transaction and exit, because 
another 
server process exited abnormally and possibly corrupted shared memory.
2014-12-15 17:54:07 GMT seviri seviri HINT:  In a moment you should be able to 
reconnect to the database and repeat your command.
2014-12-15 17:54:07 GMT   LOG:  all server processes terminated; reinitializing
2014-12-15 17:54:08 GMT   LOG:  database system was interrupted; last known up 
at 2014-12-15 17:49:38 GMT
2014-12-15 17:54:08 GMT   LOG:  database system was not properly shut down; 
automatic recovery in progress
2014-12-15 17:54:08 GMT   LOG:  redo starts at 0/58C1C060
2014-12-15 17:54:08 GMT   LOG:  record with zero length at 0/58C27950
2014-12-15 17:54:08 GMT   LOG:  redo done at 0/58C27920
2014-12-15 17:54:08 GMT   LOG:  last completed transaction was at log time 
2014-12-15 17:53:33.898086+00
2014-12-15 17:54:08 GMT   LOG:  autovacuum launcher started
2014-12-15 17:54:08 GMT   LOG:  database system is ready to accept connections

My postgresql.conf contains the following modified parameters:
listen_addresses = '*'
max_connections = 100
shared_buffers = 96MB  # increased from the default value of 24MB, because 
script 
was failing in the beginning
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Reply via email to