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
