Geek Answers Handbook

PostgreSQL DELETE / INSERT bandwidth issue

I have a bandwidth issue with DELETE / INSERT sequences on PostgreSQL 9.0. I am looking for ideas to improve the situation.

In the equipment available to us, I can INSERT new rows into the database at a steady speed of 3000 / s (evenly across 10 tables), far exceeding 1 m rows in each table for which I usually test. However, if I switch to the mode in which we delete the row and reinsert it with different data, the performance drops by more than an order of magnitude to 250 rows / s (again, evenly across 10 tables).

There are no restrictions for any table. Each table has 2 indexed columns with a total index size (1m rows per table) of 1 GB, which is convenient within shared_buffers (2 GB). The total data size (1m rows in the table) is 12 GB, which is much less than the total system RAM. This is a shadow database where we can afford to rebuild in an emergency, so we start with fsync disabled.

It seems that when they were in fill mode, we get a very low disk search time because the data is being added. However, when we switch to update mode, you are looking for a lot (to remove old lines presumably). A random drive is looking for a cost of ~ 8 ms (= ~ 125 per second). Is there a way (without hardware changes) that we can significantly improve the performance of UPDATE / re-INSERT operations?

EDIT1: I am doing the first tests on two different hardware platforms. The figures I previously quoted were obtained from a higher platform. I just completed a test run on the lower specification platform. In this test, I insert new rows as quickly as possible, recording the insertion speed every 10 seconds, until I insert 1 million rows. At this point, my test script switches to updating random strings.

Perf results graph

This graph shows that the measured update rate was ~ 150 updates for all 10 tables per second during the aggregate, and the update rate was <10 updated for all 10 tables per second.

@wildplasser - a machine is a real machine, not a virtual machine. All 10 tables have the following schema.

CREATE TABLE objecti_servicea_item1
(
  iss_scs_id text,
  iss_generation bigint,
  boolattr1 boolean,
  boolattr2 boolean,
  boolattr3 boolean,
  boolattr4 boolean,
  boolattr5 boolean,
  boolattr6 boolean,
  boolattr7 boolean,
  boolattr8 boolean,
  boolattr9 boolean,
  boolattr10 boolean,
  boolattr11 boolean,
  boolattr12 boolean,
  boolattr13 boolean,
  boolattr14 boolean,
  boolattr15 boolean,
  boolattr16 boolean,
  boolattr17 boolean,
  intattr1 bigint,
  intattr2 bigint,
  intattr3 bigint,
  intattr4 bigint,
  intattr5 bigint,
  intattr6 bigint,
  intattr7 bigint,
  intattr8 bigint,
  intattr9 bigint,
  intattr10 bigint,
  intattr11 bigint,
  intattr12 bigint,
  intattr13 bigint,
  intattr14 bigint,
  intattr15 bigint,
  intattr16 bigint,
  intattr17 bigint,
  strattr1 text[],
  strattr2 text[],
  strattr3 text[],
  strattr4 text[],
  strattr5 text[],
  strattr6 text[],
  strattr7 text[],
  strattr8 text[],
  strattr9 text[],
  strattr10 text[],
  strattr11 text[],
  strattr12 text[],
  strattr13 text[],
  strattr14 text[],
  strattr15 text[],
  strattr16 text[],
  strattr17 text[]
)
WITH (
  OIDS=FALSE
);
CREATE INDEX objecti_servicea_item1_idx_iss_generation
  ON objecti_servicea_item1
  USING btree
  (iss_generation );
CREATE INDEX objecti_servicea_item1_idx_iss_scs_id
  ON objecti_servicea_item1
  USING btree
  (iss_scs_id );

Performed "updates" include the following SQL for each of the 10 tables.

DELETE FROM ObjectI_ServiceA_Item1 WHERE iss_scs_id = 'ObjUID39'
INSERT INTO ObjectI_ServiceA_Item1 
VALUES ('ObjUID39', '2', '0', NULL, '0'
, NULL, NULL, NULL, '1', '1', NULL, '0'
, NULL, NULL, NULL, NULL, '0', '1', '1'
, '-70131725335162304', NULL, NULL, '-5241412302283462832'
, NULL, '310555201689715409', '575266664603129486'
, NULL, NULL, NULL, NULL, NULL, NULL
, '-8898556182251816700', NULL, '3325820251460628173'
, '-3434461681822953613'
, NULL
, E'{pvmo2mt7dma37roqpuqjeu4p8b,"uo1kjt1b3eu9g5vlf0d02l6iaq\\\\\\",",45kfns1j80gc7fri0dm29hnrjo}'
, NULL, NULL
, E'{omjv460do8cb7abn8t3eg5b6ki,"a7hrlninbk1rmu6h3rd4787l7f\\\\\\",",24n3ipfua5spma2vrj2aji98g3}'
, NULL
, E'{1821v2n2ermm4jujrucu5tekmm,"ukgst224964uhthkhjj9v189ft\\\\\\",",6dfsaniq9mftvbdr8g1sr8e6as}'
, E'{c2a9gvf0fnd38m8vprlhkp2n74,"ts86vbat12lfr0d7l4tc29k9uk\\\\\\",",32b5j9r5evmrie4h21hi10dpot}'
, E'{18pve4cmcbrjiom9bpvoo1l4n0,"hrqcsane6r0n7u2oj79bj605rh\\\\\\",",32q5n18q3qbkuit605fv47270o}'
, E'{l3bf96shrpnnqgt35m7574t5n4,"cpol4k8296hbdqc9kac79oj0ua\\\\\\",",eqioulmb7vav10lbnc5jg752df}'
, E'{5fai108h163hpjcv0ofgfi7c28,"ci958009ddak3li7bp37slcs8i\\\\\\",",2itstj01tkprlul8f530uhs6s2}'
, E'{ueqfkdold8vc84jllr4b2cakt5,"t5vbea4r7tva091pa8j6886t60\\\\\\",",ul82aovhil1lpd290s14vd0p3i}'
, NULL, NULL, NULL, NULL, NULL)

, DELETE .

@Frank Heikens - , , 10 . , , , .

+5
3

datamodel , DELETE - INSERT. ? iss_generation iss_scs_id UPDATE, HOT update (Heap Overflow Tuple) . UPDATE .

DELETE , , , INSERT. fillfactor UPDATE, DELETE . -. HOT , , .

+3

, fillfactor ?

+1

We have success with deleting / copying from csv in memory.

0
source

More articles:


All Articles