Insert performance for large transaction with multiple COPY FROM
| От | Horst Dehmer |
|---|---|
| Тема | Insert performance for large transaction with multiple COPY FROM |
| Дата | |
| Msg-id | 78A65A52-C49D-4CA4-9A63-1F5F120BC7E7@gmail.com обсуждение исходный текст |
| Ответы |
Insert performance for large transaction with multiple COPY FROM
(Jeff Janes <jeff.janes@gmail.com>)
Re: Insert performance for large transaction with multiple COPY FROM (Claudio Freire <klaussfreire@gmail.com>) |
| Список | pgsql-performance |
Hi!
I see a massive performance drop when writing a large transaction. I'm writing data for 33 tables with COPY FROM directly from streams in Scala/Java. Over all tables there are 2.2M records which are unevenly distributed from 1 record to 315k records in some tables.
For comparison I ran a test where I used UNLOGGED tables, no PK/FK constraints, nor other constraints or indexes and no triggers for all tables. The insert rate for this scenario is well above 105k records/second over all tables (which I think is really cool!)
Turning everything on (but still with UNLOGGED tables), i.e. PK/FK, additional indexes, some column check constraints and a trigger for each table which basically insert one additional record to another table, the rates dropped expectedly to around 6k to 7k records/second.
Except - and that's the wall I'm hitting - for one table which yielded just 75 records/second.
The main 'problem' seem to be the FK constraints. Dropping just them restored insert performance for this table to 6k records/s. The table in question has a composite PK (3 columns), 3 foreign keys and a bunch of indexes (see table obj_item_loc at the end of the mail). Compared to the other 32 tables nothing unusual.
I'd gladly supply more information if necessary.
Dropping and recreating constraints/indexes is (for now) no viable alternative, since I have to write such transaction into an already populated database.
What I'm trying to understand is, which limit it is I'm hitting here. I need some advice how to 'profile' this situation.
Configuration is more or less standard, except WAL settings (which should not be relevant here).
EnterpriseDB One Click installer.
Any hint is really appreciated.
Thanks!
--
Horst Dehmer
"PostgreSQL 9.2.1 on x86_64-apple-darwin, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2336.9.00), 64-bit"
OS X 10.8.2
Mid-2012 MacBook Pro 16 GB, 512 GB SSD
"bytea_output";"escape"
"checkpoint_completion_target";"0.9"
"checkpoint_segments";"32"
"client_encoding";"UNICODE"
"client_min_messages";"notice"
"lc_collate";"en_US.UTF-8"
"lc_ctype";"en_US.UTF-8"
"listen_addresses";"*"
"log_checkpoints";"on"
"log_destination";"stderr"
"log_line_prefix";"%t "
"logging_collector";"on"
"max_connections";"100"
"max_stack_depth";"2MB"
"port";"5432"
"server_encoding";"UTF8"
"shared_buffers";"24MB"
"TimeZone";"Europe/Vienna"
"wal_buffers";"768kB"
/etc/sysctl.conf
kern.sysv.shmmax=1610612736
kern.sysv.shmall=393216
kern.sysv.shmmin=1
kern.sysv.shmmni=256
kern.sysv.shmseg=64
kern.maxprocperuid=512
kern.maxproc=2048
CREATE TABLE obj_item_loc
(
obj_item_id numeric(20,0) NOT NULL,
loc_id numeric(20,0) NOT NULL,
obj_item_loc_ix numeric(20,0) NOT NULL,
ver_acc_dim numeric(12,3),
horz_acc_dim numeric(12,3),
brng_angle numeric(7,4),
brng_acc_angle numeric(7,4),
brng_precision_code character varying(6),
incl_angle numeric(7,4),
incl_acc_angle numeric(7,4),
incl_precision_code character varying(6),
speed_rate numeric(8,4),
speed_acc_rate numeric(8,4),
speed_precision_code character varying(6),
meaning_code character varying(6),
rel_speed_code character varying(6),
rptd_id numeric(20,0) NOT NULL,
creator_id numeric(20,0) NOT NULL,
update_seqnr numeric(15,0) NOT NULL,
rec_id bigint DEFAULT nextval('rec_seq'::regclass),
CONSTRAINT obj_item_loc_pkey PRIMARY KEY (obj_item_id, loc_id, obj_item_loc_ix),
CONSTRAINT obj_item_loc_4fbc75641175ef1757ca310dd34e34ee_fkey FOREIGN KEY (obj_item_id)
REFERENCES obj_item (obj_item_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT obj_item_loc_7895d64f5557b1e382c36d41212a3696_fkey FOREIGN KEY (rptd_id)
REFERENCES rptd (rptd_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT obj_item_loc_8d919243f69bcc599873caca07ac9888_fkey FOREIGN KEY (loc_id)
REFERENCES loc (loc_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT obj_item_loc_brng_acc_angle_ra_check CHECK (br_check_305(brng_acc_angle >= 0::numeric AND brng_acc_angle <= 359.9999, 'obj_item_loc'::text, 'brng_acc_angle'::text, brng_acc_angle::text)),
CONSTRAINT obj_item_loc_brng_angle_ra_check CHECK (br_check_305(brng_angle >= 0::numeric AND brng_angle <= 359.9999, 'obj_item_loc'::text, 'brng_angle'::text, brng_angle::text)),
CONSTRAINT obj_item_loc_brng_precision_code_check CHECK (br_check_305(brng_precision_code::text = ANY (ARRAY['1000MN'::text, '100MN'::text, '100SEC'::text, '10DEG'::text, '10MN'::text, '10SEC'::text, 'DEGREE'::text, 'MIL'::text, 'MINUTE'::text, 'SECOND'::text]), 'obj_item_loc'::text, 'brng_precision_code'::text, brng_precision_code::text)),
CONSTRAINT obj_item_loc_incl_acc_angle_ra_check CHECK (br_check_305(incl_acc_angle >= 0::numeric AND incl_acc_angle <= 359.9999, 'obj_item_loc'::text, 'incl_acc_angle'::text, incl_acc_angle::text)),
CONSTRAINT obj_item_loc_incl_angle_ra_check CHECK (br_check_305(incl_angle >= 0::numeric AND incl_angle <= 359.9999, 'obj_item_loc'::text, 'incl_angle'::text, incl_angle::text)),
CONSTRAINT obj_item_loc_incl_precision_code_check CHECK (br_check_305(incl_precision_code::text = ANY (ARRAY['1000MN'::text, '100MN'::text, '100SEC'::text, '10DEG'::text, '10MN'::text, '10SEC'::text, 'DEGREE'::text, 'MIL'::text, 'MINUTE'::text, 'SECOND'::text]), 'obj_item_loc'::text, 'incl_precision_code'::text, incl_precision_code::text)),
CONSTRAINT obj_item_loc_meaning_code_check CHECK (br_check_305(meaning_code::text = ANY (ARRAY['CEOFMA'::text, 'SHAPE'::text, 'LNBRNG'::text, 'ASSCP'::text, 'COM'::text, 'CMDDET'::text, 'SOUND'::text, 'DSPCTR'::text, 'FRMCTR'::text, 'POSOIM'::text, 'CTRMNB'::text, 'ORGPRL'::text, 'STDPOS'::text, 'ADRPRP'::text]), 'obj_item_loc'::text, 'meaning_code'::text, meaning_code::text)),
CONSTRAINT obj_item_loc_rel_speed_code_check CHECK (br_check_305(rel_speed_code::text = ANY (ARRAY['FAST'::text, 'MEDIUM'::text, 'ZERO'::text, 'SLOW'::text]), 'obj_item_loc'::text, 'rel_speed_code'::text, rel_speed_code::text)),
CONSTRAINT obj_item_loc_speed_precision_code_check CHECK (br_check_305(speed_precision_code::text = ANY (ARRAY['KPH'::text, 'KNOTS'::text, 'MPS'::text]), 'obj_item_loc'::text, 'speed_precision_code'::text, speed_precision_code::text))
)
WITH (
OIDS=FALSE,
autovacuum_enabled=true
);
ALTER TABLE obj_item_loc
OWNER TO postgres;
CREATE INDEX obj_item_loc_ix_rec_id
ON obj_item_loc
USING btree
(rec_id);
CREATE INDEX obj_item_loc_loc_id_idx
ON obj_item_loc
USING btree
(loc_id);
CREATE INDEX obj_item_loc_obj_item_id_idx
ON obj_item_loc
USING btree
(obj_item_id);
CREATE INDEX obj_item_loc_rptd_id_idx
ON obj_item_loc
USING btree
(rptd_id);
CREATE TRIGGER trg_01_obj_item_loc_before_insert
BEFORE INSERT
ON obj_item_loc
FOR EACH ROW
EXECUTE PROCEDURE obj_item_loc_before_insert();
В списке pgsql-performance по дате отправления:
Следующее
От: Jeff JanesДата:
Сообщение: Insert performance for large transaction with multiple COPY FROM