|
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. |
Attachment:
postgres.png
Description: PNG image
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
