On 15/07/2012 02:14, Ioannis Anagnostopoulos wrote:
> 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
>
>
Some more information regarding this "problem". I start to believe that
the problem is mainly due to the autovacum that happens to prevent
wraparound. As our database is heavily used with inserts, wraparounds
are happing very often. The vacuums that are triggered to deal with the
situation have an adverse effect on the index HD. In essence as the
database covers 12 months of data an autovacuum to prevent wrap around
is more or less constantly present starving the actual data insertion
process from index HD resources (especially when those indexes are quite
a lot as I said in my previous post). Now, given the fact that only the
"current" month is updated with inserts while the previous months are
essentially ready-only(static) I think that moving the indexes of the
past months to an archive HD or dropping those that are not necessary
any more would probably solve the problem. Does my theory hold any water?
Kind Regards
Yiannis