How can we avoid reclustering some indexes ??

Hello, We have a 24/7 OLTP application with very script constraints in terms of response time. We are dealing with complex queries and quite huge tables (in terms of OLTP application). In order to maintain a good response time, one of our table has to be clustered, but after some time, index has to be reclustered due to the fact clustering is not maintained. We are looking for some solutions in order to avoid having to do such reorganisation (which requires some outages ...). More complete description of our application : ============================================== Our Informix application is in Production for a week monthes. This is a 24/7 application. Database size = 8 giga (only used space and not allocated one). Average frequency of the transactions = 3 transactions per second. Our biggest table contains 20Million of records (3 gig of data), and contains 7 indexes (we cannot do less : 4 giga of indexes). We have some very strict performance constraints, and are executing a very heavy query (involving about 20 tables of our model, as well as this "critical table") which should get a reply in less than 800 ms. The index used by this query (when accessing the big table) is clustered (this is a multicolumn index : 4 columns). This allows us to ensure a good response time by guaranteeing some sequential reads of the data pages. When index is "fresh created", response time is fine (<400 ms), after some weeks of activity, response time degrades and reach the limit acceptable. This is normal due to the way index is less and less efficient (clustering is not maintained), and mainly because the number of data pages to read is increasing (records to read not sequential anymore, and spread in N pages). ... I have forgotten to mention that every night, we have some massive batch processes deleting + recreating (but not updating .... :-( ) an average of 800.000 records in the big table. Basically, in 1 month our table of 20M of records is re-generated. Question : ======== We are able to monitor quite well the degradation of the performances by analysing the evolution of : * performances, * disk/buffer read/write activity * growth of the database indexes. When performance is not acceptable anymore, our solution consists on "Re-clustering" the main used index. This obliges us to have outages of our system once every two monthes in average. WE WOULD LIKE TO KNOW HOW IT COULD BE POSSIBLE TO RECLUSTER LESS OFTEN. Any idea ? Additional information : ======================== We have Informix 7.23.UC1.S6 We have HP servers - HP-UX V10 We do not use kaios (pb with our release of IFX). We use EMC disks. Indexes are isolated in separate dbspaces. "Critical table (20M)" is also within a specific dbspace. We cannot restrict the update activity of the system. We are not able to replace the "DELETE+INSERT" by "UPDATE". Our plans : =========== We are currently working on two points : * See if we can fragment our big table in a clever way, in order to force Informix to always ensure some "related" records are always located on the same part of the disk. This is perhaps the first solution we will implement (we are now working on a strategy for splitting in a clever way our big table). * Try to see if we can replace the DELETE+INSERT statements by UPDATE ones (this is a long term solution which will oblige us to change a lot of things in our code). All ideas comments are more than welcomed. Thanks in advance for your help on the subject. Jean-Noel Donadio Amadeus Development Company E-mail: jdonadio@my-dejanews.com -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

jdonadio@my-dejanews.com wrote: > Hello, > > We have a 24/7 OLTP application with very script constraints in terms > of response time. > We are dealing with complex queries and quite huge tables > (in terms of OLTP application). > [SNIP] > Our biggest table contains > 20Million of records (3 gig of data), and contains 7 indexes (we cannot do > less : 4 giga of indexes). > We have some very strict performance constraints, > and are executing a very heavy query (involving about 20 tables of our model, > as well as this "critical table") which should get a reply in less than 800 > ms. Uhmm, Let me get this straight. You are performing a single query that hits 20 tables of your model? I'd start there since you place a high 8/10th of a second response factor. Without going in to more detail, its hard to figure out what you are trying to do. As to the table growth, there are several ways to help manage that. You also seem to indicate that the table size is fairly static once you get started? (Flushing of data.) There are several things you can do to maintain performance at your 4/10th sec response rate. -Mikey

jdonadio@my-dejanews.com wrote: [SNIP Very good description of the problem domain.] I am sorry to say that you are already doing the most likely things to help, ie intelligent fragmentation and UPDATE instead of delete+insert. Upgrading to 7.30/7.31 may gain you 10-30% better performance than the version you have now but that can only delay the reorganizations you need to do now for a little while. Art S. Kagel

Let us think. Cluster index helps you reduce number of phisical disk reads - nothing else - because your application eventually is getting the same information. The most straitforward way to reduce number of diskreads is dramatically encrease buffers cache in the RAM. How big encrease is required to offset the loss of your clustered index depends on the spread of users hits (may be <0.5 Gb, may be several Gb) but investing in the RAM is a sure way to improve your %cached reads and compensate for the index in question. By the way, I belive, Informix recommended 20-25% of RAM for BUFFERS is conservative and ignores client-server reality in which most of us live now (no users' applications on the database box). I came to 60% (but be aware of swapping!) Oleg jdonadio@my-dejanews.com wrote: > > Hello, > > We have a 24/7 OLTP application with very script constraints in terms > of response time. > We are dealing with complex queries and quite huge tables > (in terms of OLTP application). > > In order to maintain a good response time, > one of our table has to be clustered, but after some time, index has to be > reclustered due to the fact clustering is not maintained. > > We are looking for > some solutions in order to avoid having to do such reorganisation (which > requires some outages ...). > > More complete description of our application : > ============================================== > > Our Informix application is > in Production for a week monthes. > This is a 24/7 application. > Database size > = 8 giga (only used space and not allocated one). > Average frequency of the > transactions = 3 transactions per second. > > Our biggest table contains > 20Million of records (3 gig of data), and contains 7 indexes (we cannot do > less : 4 giga of indexes). > We have some very strict performance constraints, > and are executing a very heavy query (involving about 20 tables of our model, > as well as this "critical table") which should get a reply in less than 800 > ms. > > The index used by this query (when accessing the big table) is clustered > (this is a multicolumn index : 4 columns). > This allows us to ensure a good > response time by guaranteeing some sequential reads of the data pages. > > When > index is "fresh created", response time is fine (<400 ms), after some weeks > of activity, response time degrades and reach the limit acceptable. > This is > normal due to the way index is less and less efficient (clustering is not > maintained), and mainly because the number of data pages to read is > increasing (records to read not sequential anymore, and spread in N pages). > > ... I have forgotten to mention that every night, we have some massive batch > processes deleting + recreating (but not updating .... :-( ) an average of > 800.000 records in the big table. > > Basically, in 1 month our table of 20M of > records is re-generated. > > Question : > ======== > > We are able to monitor quite > well the degradation of the performances by analysing the evolution of : > * > performances, > * disk/buffer read/write activity > * growth of the > database indexes. > > When performance is not acceptable anymore, our solution > consists on "Re-clustering" the main used index. > This obliges us to have > outages of our system once every two monthes in average. > > WE WOULD LIKE TO > KNOW HOW IT COULD BE POSSIBLE TO RECLUSTER LESS OFTEN. > > Any idea ? > > Additional information : > ======================== > > We have Informix > 7.23.UC1.S6 > We have HP servers - HP-UX V10 > We do not use kaios (pb with our > release of IFX). > We use EMC disks. > Indexes are isolated in separate dbspaces. > "Critical table (20M)" is also within a specific dbspace. > We cannot restrict > the update activity of the system. > We are not able to replace the > "DELETE+INSERT" by "UPDATE". > > Our plans : > =========== > > We are currently > working on two points : > * See if we can fragment our big table in a > clever way, in order to force Informix to always ensure some "related" > records are always located on the same part of the disk. > This is perhaps the > first solution we will implement (we are now working on a strategy for > splitting in a clever way our big table). > * Try to see if we can > replace the DELETE+INSERT statements by UPDATE ones (this is a long term > solution which will oblige us to change a lot of things in our code). > > All > ideas comments are more than welcomed. > > Thanks in advance for your help on > the subject. > > Jean-Noel Donadio > Amadeus Development Company > E-mail: > jdonadio@my-dejanews.com > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own