Re: Looking for Opinions on How to Improve INSERT Performance

- Upgrade to 11.50, non-blocking checkpoints will allow you to reconfigure most of the physical buffer flushes to checkpoint time when they are more efficient. - Use the new BIG_FET_BUF_SIZE which overrides FET_BUF_SIZE and can be set to up to 4MB (it has been implemented in the next release of my dbcopy.ec utility - testing not completed yet). - Use multiple insert threads per fragment. The engine is capable of inserting multiple rows to the same fragment in parallel especially if you have multiple CPU VPs. - Use multiple consumer threads for the SELECT connection. Each thread reads N rows, detaches from the read connection and switches to its own write connection and PUTs the N rows to the INSERT cursor leaving the read connection free for another thread to use. Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jun 3, 2009 at 8:46 AM, <the_omegamon@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 ; > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >