On 16/07/2012 11:24, Ioannis Anagnostopoulos wrote:
> 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
>
>
Hello again, sorry for topping up the thread but I think that the more
information I provide you the more likely it is to get an answer. So as
I go along, I have stripped completely the database from additional
indexes, those that possible delay the insertion process, of course
maintaining the pkey and 2 or three absolutely mandatory indexes for my
select queries. As a result I have a sleek and steady performance of
around 0.70 msec per insertion. However I have now closed a full circle
as I have a fast database but when I try to "select", making optimum
usage of the left over indexes, the insertion process slows down. Yes my
selections are huge (they are not slow, just huge as it is about
geographical points etc) but I am asking if there is anyway that I can
"prioritise" the insertions over the "selections". These "selections"
are happening anyway as batch process during night so I don't really
mind if they will take 2 or 5 hours, as long as they are ready at 9.00am
next day. Again any advice will be highly appreciated.
Kind Regards
Yiannis