RE: Slow inserts

Are the inserts in PK order? If not, the index keys will have to be inserted inbetween existing keys causing index page splits and the corresponding overhead of index page management. If Informix ever had an online index rebuild ability as 'Orrible' has this is an area where it would be extremely useful Regards Colin There are 10 types of people in the world, those that understand binary and those that don't >From: Ben Thompson <ben@nomonitorsoftspam.com> >Reply-To: Ben Thompson <ben@nomonitorsoftspam.com> >To: informix-list@iiug.org >Subject: Slow inserts >Date: Mon, 22 Aug 2005 12:01:15 +0100 > >We have a problem with inserts into a particularly large table being >slow for no obvious reason (500ms per row instead of under 10ms) and the >whole system being slow in general when this occurs. Analysis of the >sessions running and the SQL statements they're running never reveals >anything untoward. This table is in its own chunk and "onstat -z" and >various "onstat -g iof" commands show that this chunk is being accessed >heavily (400io/sec). Typically this slow running starts at random times, >lasts about 20 minutes and then stops again. The frequency of this is >maybe 3 to 4 times a week. > >Recently I looked at the size of the primary key index on this table >which is a segmented key covering 5 columns (we aim to redesign the >database to sort this out). It is approaching 1Gb (I got this using >oncheck -pe and looked at the allocated extent for the index). Our>machine has approximately 1.5Gb BUFFERs and other large tables to deal >with as well. Could it be that the index is too large to be buffered and >that the engine is having to go to disc to work out whether the insert >violates the primary key and thus hitting system performance hard? >Thoughts please. > >Ben. sending to informix-list