Skip site navigation (1) Skip section navigation (2)

Index slow down insertions...

From: Ioannis Anagnostopoulos <ioannis(at)anatec(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Index slow down insertions...
Date: 2012-07-15 01:14:45
Message-ID: 50021985.2000506@anatec.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-performance
Hello,

Our postgres 9.0 DB has one table (the important one) where the bulk of 
insertions is happening. We are looking more or less at around 15K to 
20K insertions per minute and my measurements give me a rate of 0.60 to 
1 msec per insertion. A summary of the table where the insertions are 
happening is as follows:

-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  msg_id 
bigint NOT NULL DEFAULT 
nextval('feed_all_y2012m07.messages_msg_id_seq'::regclass),
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  msg_type 
smallint NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  obj_id 
integer NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
msg_date_rec timestamp without time zone NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  msg_text 
text NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
msg_expanded boolean NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  msg_time 
time without time zone,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
pos_accuracy boolean NOT NULL DEFAULT false,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  pos_raim 
boolean NOT NULL DEFAULT false,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  pos_lon 
integer NOT NULL DEFAULT (181 * 600000),
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  pos_lat 
integer NOT NULL DEFAULT (91 * 60000),
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
pos_georef1 character varying(2) NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
pos_georef2 character varying(2) NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
pos_georef3 character varying(2) NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
pos_georef4 character varying(2) NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
pos_point geometry,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
ship_speed smallint NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
ship_course smallint NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
ship_heading smallint NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
ship_second smallint NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
ship_radio integer NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
ship_status ais_server.nav_status NOT NULL DEFAULT 
'NOT_DEFINED'::ais_server.nav_status,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
ship_turn smallint NOT NULL DEFAULT 128,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  
ship_maneuver smallint NOT NULL,
   CONSTRAINT ship_a_pos_messages_wk0_pkey PRIMARY KEY (msg_id )

The table is created in table space "Data" while its indexes in table 
space "Index" (a different HD). Now once the database is empty the 
configuration is flying but of course this is not the case always. 5 
days later and around 55,000,000 rows later the insertions are literally 
so slow that the application server has to drop inserts in order to keep 
up. To be precise we are looking now at 1 insertion every 5 to 10, 
sometimes 25 msec!!

After lots of tuning both on the postgres server and the stored procs, 
after installing 18G Ram and appropriately changing the shared_buffers, 
working_mem etc, we realized that our index hard disk had 100% 
utilization and essentially it was talking longer to update the indexes 
than to update the table. Well I took a radical approach and dropped all 
the indexes and... miracle, the db got back in to life, insertion went 
back to a healthy 0.70msec but of course now I have no indexes. It is my 
belief that I am doing something fundamentally wrong with the index 
creation as 4 indexes cannot really bring a database to a halt. Here are 
the indexes I was using:

CREATE INDEX idx_ship_a_pos_messages_wk0_date_pos
   ON feed_all_y2012m07.ship_a_pos_messages_wk0
   USING btree
   (msg_date_rec , pos_georef1 , pos_georef2 , pos_georef3 , pos_georef4 )
TABLESPACE index;

CREATE INDEX idx_ship_a_pos_messages_wk0_date_rec
   ON feed_all_y2012m07.ship_a_pos_messages_wk0
   USING btree
   (msg_date_rec )
TABLESPACE index;

CREATE INDEX idx_ship_a_pos_messages_wk0_object
   ON feed_all_y2012m07.ship_a_pos_messages_wk0
   USING btree
   (obj_id , msg_type , msg_text , msg_date_rec )
TABLESPACE index;

CREATE INDEX idx_ship_a_pos_messages_wk0_pos
   ON feed_all_y2012m07.ship_a_pos_messages_wk0
   USING btree
   (pos_georef1 , pos_georef2 , pos_georef3 , pos_georef4 )
TABLESPACE index;

As I have run out of ideas any help will be really appreciated. For the 
time being i can live without indexes but sooner or later people will 
need to access the live data. I don't even dare to think what will 
happen to the database if I only introduce a spatial GIS index that I 
need. Question: Is there any possibility that I must include the primary 
key into my index to "help" during indexing? If I remember well MS-SQL 
has such a "feature".

Kind Regards
Yiannis


Responses

pgsql-novice by date

Next:From: Bartosz DmytrakDate: 2012-07-15 18:41:19
Subject: Re: Selecting Fields in Union in Subquery
Previous:From: Tom BurnsDate: 2012-07-13 21:49:34
Subject: Selecting Fields in Union in Subquery

pgsql-performance by date

Next:From: Tom LaneDate: 2012-07-15 18:29:27
Subject: Re: [PERFORM] DELETE vs TRUNCATE explanation
Previous:From: Jeff JanesDate: 2012-07-15 00:10:18
Subject: Re: [PERFORM] DELETE vs TRUNCATE explanation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group