Excessive memory used for INSERT - Mailing list pgsql-performance

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_108,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_108,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

together with the /etc/sysctl.conf settings:

sys.kernel.shmmax = 268435456

sys.kernel.shmall = 268435456

Am I doing something wrong, either in my database definition (function, upsert) or in the parameters

used (shared buffers) ? Or is such memory consumption usual ?

Many thanks,

Alessandro.