Looking for Opinions on How to Improve INSERT Performance

Greetings All, Our application is currently bottlenecked on INSERTS to the table below. The table has about 1 billion records in a 24x7 OLTP environment with batches being added in the 1-20 million range. We plan on expanding the serial to bigserial soon. The table has a single extent in each fragment and indexes are detached. The summary_id and child_id do have a lot of duplicate entries. The application is an ESQL/C program that reads from multiple tables and subsequently writes to the application_ref table Solaris 10 IDS 10.00.FC5 (soon to be 11.50.FC4) CDSK 2.90.FC1 (soon to be 3.50.FC4) Running on a 4-Way Sun V440 w/16GB RAM, direct attach Fibre Channel disks, RAID 10 with a 32K stripe size. Physical and logical logs are isolated on seperate devices. The OS does not show any slow response times for the LUNs during execution. We have done the following: Use INSERT cursors with FET_BUF_SIZE=32767, flushing when the buffer becomes full Opened seperate 'reader' and 'writer' connections Partioned up to 8 'writers', with each inserting to seperate fragment Rebuilt indexes with a lower FILLFACTOR of 50 Any ideas on what else we might try to 'optimize' our INSERT performance? TIA, Schema below: create table "appdba".application_ref ( application_ref_id serial not null , summary_id integer not null , reference_id smallint not null , child_id integer not null , internal_id integer not null, index_id integer not null ) fragment by expression (mod(application_ref_id , 8 ) = 0 ) in data1dbs , (mod(application_ref_id , 8 ) = 1 ) in data2dbs , (mod(application_ref_id , 8 ) = 2 ) in data3dbs , (mod(application_ref_id , 8 ) = 3 ) in data4dbs , (mod(application_ref_id , 8 ) = 4 ) in data5dbs , (mod(application_ref_id , 8 ) = 5 ) in data6dbs , (mod(application_ref_id , 8 ) = 6 ) in data7dbs , (mod(application_ref_id , 8 ) = 7 ) in data8dbs extent size 3000000 next size 300000 lock mode row; create index "appdba".application_ref_a01 on "appdba".application_ref (summary_id) using btree ; create index "appdba".application_ref_a02 on "appdba".application_ref (child_id) using btree ; create unique index "appdba".application_ref_u01 on "appdba".application_ref (application_ref_id) using btree ; alter table "appdba".application_ref add constraint primary key (application_ref_id) constraint "appdba".application_ref_p01 ;

Your fragmentation by expression may be to blame for some of it; for each insert the engine has to allocate the next serial value and then calculate which dbspace to use. Fragmenting by round robin may work better but with a table of this size in constant use this may not be an option for you. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of the_omegamon@yahoo.com Sent: 03 June 2009 13:47 To: Subject: Looking for Opinions on How to Improve INSERT Performance Greetings All, Our application is currently bottlenecked on INSERTS to the table below. The table has about 1 billion records in a 24x7 OLTP environment with batches being added in the 1-20 million range. We plan on expanding the serial to bigserial soon. The table has a single extent in each fragment and indexes are detached. The summary_id and child_id do have a lot of duplicate entries. The application is an ESQL/C program that reads from multiple tables and subsequently writes to the application_ref table Solaris 10 IDS 10.00.FC5 (soon to be 11.50.FC4) CDSK 2.90.FC1 (soon to be 3.50.FC4) Running on a 4-Way Sun V440 w/16GB RAM, direct attach Fibre Channel disks, RAID 10 with a 32K stripe size. Physical and logical logs are isolated on seperate devices. The OS does not show any slow response times for the LUNs during execution. We have done the following: Use INSERT cursors with FET_BUF_SIZE=32767, flushing when the buffer becomes full Opened seperate 'reader' and 'writer' connections Partioned up to 8 'writers', with each inserting to seperate fragment Rebuilt indexes with a lower FILLFACTOR of 50 Any ideas on what else we might try to 'optimize' our INSERT performance? TIA, Schema below: create table "appdba".application_ref ( application_ref_id serial not null , summary_id integer not null , reference_id smallint not null , child_id integer not null , internal_id integer not null, index_id integer not null ) fragment by expression (mod(application_ref_id , 8 ) = 0 ) in data1dbs , (mod(application_ref_id , 8 ) = 1 ) in data2dbs , (mod(application_ref_id , 8 ) = 2 ) in data3dbs , (mod(application_ref_id , 8 ) = 3 ) in data4dbs , (mod(application_ref_id , 8 ) = 4 ) in data5dbs , (mod(application_ref_id , 8 ) = 5 ) in data6dbs , (mod(application_ref_id , 8 ) = 6 ) in data7dbs , (mod(application_ref_id , 8 ) = 7 ) in data8dbs extent size 3000000 next size 300000 lock mode row; create index "appdba".application_ref_a01 on "appdba".application_ref (summary_id) using btree ; create index "appdba".application_ref_a02 on "appdba".application_ref (child_id) using btree ; create unique index "appdba".application_ref_u01 on "appdba".application_ref (application_ref_id) using btree ; alter table "appdba".application_ref add constraint primary key (application_ref_id) constraint "appdba".application_ref_p01 ; _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

You could be running into the following bug: This is for long checkpoint but would also slow down your inserts. Check onstat -C to see if the btscanner is overactive on this specific table; onmode -C stop <#threads> Check if there are a lot of empty leaf nodes by running this query on sysmaster: select count(*) del_item_pages from syspaghdr where pg_partnum =<idx_partnum> and mod(round(pg_flags / 16), 2) != 0 and pg_nslots = 0; APAR IC56424 -Long checkpoint duration as INSERT into an index loops through empty index pages; btscanner would leave behind lot of leaf nodes with nslots = 0 ; fixed in 11.10.FC3 and 10.00.FC9 On Jun 3, 8:46 am, the_omega...@yahoo.com wrote: > Greetings All, > >    Our application is currently bottlenecked on INSERTS to the table > below. > The table has about 1 billion records in a 24x7 OLTP environment with > batches > being added in the 1-20 million range.  We plan on expanding the > serial to > bigserial soon.  The table has a single extent in each fragment and > indexes > are detached.  The summary_id and child_id do have a lot of duplicate > entries. > The application is an ESQL/C program that reads from multiple tables > and > subsequently writes to the application_ref table > > Solaris 10 > IDS 10.00.FC5  (soon to be 11.50.FC4) > CDSK 2.90.FC1  (soon to be 3.50.FC4) > > Running on a 4-Way Sun V440 w/16GB RAM, direct attach Fibre Channel > disks, RAID 10 > with a 32K stripe size.  Physical and logical logs are isolated on > seperate > devices. > > The OS does not show any slow response times for the LUNs during > execution. > > We have done the following: > >     Use INSERT cursors with FET_BUF_SIZE=32767, flushing when the > buffer becomes full >     Opened seperate 'reader' and 'writer' connections >     Partioned up to 8 'writers', with each inserting to seperate > fragment >     Rebuilt indexes with a lower FILLFACTOR of 50 > > Any ideas on what else we might try to 'optimize' our INSERT > performance? > > TIA, > > Schema below: > > create table "appdba".application_ref >   ( >     application_ref_id serial not null , >     summary_id integer not null , >     reference_id smallint not null , >     child_id integer not null , >     internal_id integer not null, >     index_id integer not null >   ) >   fragment by expression >     (mod(application_ref_id , 8 ) = 0 ) in data1dbs , >     (mod(application_ref_id , 8 ) = 1 ) in data2dbs , >     (mod(application_ref_id , 8 ) = 2 ) in data3dbs , >     (mod(application_ref_id , 8 ) = 3 ) in data4dbs , >     (mod(application_ref_id , 8 ) = 4 ) in data5dbs , >     (mod(application_ref_id , 8 ) = 5 ) in data6dbs , >     (mod(application_ref_id , 8 ) = 6 ) in data7dbs , >     (mod(application_ref_id , 8 ) = 7 ) in data8dbs >   extent size 3000000 next size 300000 lock mode row; > > create index "appdba".application_ref_a01 on "appdba".application_ref > (summary_id) >     using btree ; > create index "appdba".application_ref_a02 on "appdba".application_ref > (child_id) >     using btree ; > create unique index "appdba".application_ref_u01 on > "appdba".application_ref (application_ref_id) >     using btree ; > alter table "appdba".application_ref add constraint primary key > (application_ref_id) >     constraint "appdba".application_ref_p01  ;