Index Size, On Table, Off Table, Reindexing Online......

Hello All, Couple of questions: 1. If I have a table with 1.8GB of data how big would my ontable index be expected to be (I am assuming there is an algorithm to calculate this based on the number of columns in the index)? 2. For high performance OLTP systems, am I better placing my indexes off table or on table? 3. Why does it seem to take forever to reindex a table (not baiting here, but I thought IFX was a very performant system. Reindexing on SQL Server or Oracle seems to take much less time and can be done in an online manner, not removing my table from use??)? Any further tips you may have on idexing strategies would be welcome. Thank you all in advance, Tam.

Tam The version of Informix is essential with this question. 1. The index size = index length * table size / table length 2. If using Informix 9 or 10 the index is in its own tblspace. It depends upon what you are trying to optimise - try it in your test environment and see if there is any difference. 3. Why do you reindex? We have never found it helpful and have larger tables than you describe. You can drop and create indexes "online" in Informix 10. MW -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Tam OShanter Sent: Friday, 22 September 2006 9:52 a.m. To: informix-list@iiug.org Subject: Index Size, On Table, Off Table, Reindexing Online...... Hello All, Couple of questions: 1. If I have a table with 1.8GB of data how big would my ontable index be expected to be (I am assuming there is an algorithm to calculate this based on the number of columns in the index)? 2. For high performance OLTP systems, am I better placing my indexes off table or on table? 3. Why does it seem to take forever to reindex a table (not baiting here, but I thought IFX was a very performant system. Reindexing on SQL Server or Oracle seems to take much less time and can be done in an online manner, not removing my table from use??)? Any further tips you may have on idexing strategies would be welcome. Thank you all in advance, Tam. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

Thanks for the help. As to why we reindex, I'm not sure I understand the question... I'm assuming that indexes get "disheveled" so to speak if data is constantly being inserted and updated on a given table causing fragmentation. You are saying that reindexing does not really do anything? The version we are using is 10UC4. An addendum to my question would be: Given that analysis has determined that "old" data that could be archived as it's not needed) ) makes up in excess of 45% of the data in a given table, hit constantly in an OLTP environment, wouldn't removing this data lead to a performance increase? Also, it seems as though deleting large amounts of data from this table results in locking and performance degradation which is unacceptable. Is it possible to execute a large delete while maintaining system uptime? If yes, what steps should I take to ensure this goes smoothly? Also, after removing a large amount of data from a heavily indexed table, wouldn't reindexing be in order? Thanks friends, Tam. <ifxmaillist@quanta.co.nz> wrote in message news:mailman.532.1158876895.20706.informix-list@iiug.org... > Tam > > The version of Informix is essential with this question. > > 1. The index size = index length * table size / table length > > 2. If using Informix 9 or 10 the index is in its own tblspace. It > depends upon what you are trying to optimise - try it in your test > environment and see if there is any difference. > > 3. Why do you reindex? We have never found it helpful and have larger > tables than you describe. > You can drop and create indexes "online" in Informix 10. > > MW > > -----Original Message----- > From: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org] > On Behalf Of Tam OShanter > Sent: Friday, 22 September 2006 9:52 a.m. > To: informix-list@iiug.org > Subject: Index Size, On Table, Off Table, Reindexing Online...... > > Hello All, > Couple of questions: > > 1. If I have a table with 1.8GB of data how big would my ontable index be > expected to be (I am assuming there is an algorithm to calculate this > based > on the number of columns in the index)? > > 2. For high performance OLTP systems, am I better placing my indexes off > table or on table? > > 3. Why does it seem to take forever to reindex a table (not baiting here, > but I thought IFX was a very performant system. Reindexing on SQL Server > or > Oracle seems to take much less time and can be done in an online manner, > not > removing my table from use??)? > > Any further tips you may have on idexing strategies would be welcome. > > Thank you all in advance, > > Tam. > > > > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > >

Tam See embedded On 22/09/06, Tam OShanter <tam@oshanter.com> wrote: > Thanks for the help. > > As to why we reindex, I'm not sure I understand the question... > > I'm assuming that indexes get "disheveled" so to speak if data is constantly > being inserted > and updated on a given table causing fragmentation. Not really, the indexes tend to keep themselves tidy, especially if the inserts are random. The indexes may get a bit 'lop-sided' if data is consistantly added to 'the end', but personally I have not found a problem. > > You are saying that reindexing does not really do anything? > > The version we are using is 10UC4. An addendum to my question would be: > > Given that analysis has determined that "old" data that could be archived > as it's not needed) ) makes up in excess of 45% of the data in a given > table, hit constantly in an OLTP environment, wouldn't removing this data > lead to a performance increase? Of course removing old data would lead to a performance improvement and even more so if the table is reorganised and the 'empty' space released back to the engine. > > Also, it seems as though deleting large amounts of data from this table > results in locking and performance degradation which is unacceptable. > > Is it possible to execute a large delete while maintaining system uptime? If > yes, what steps should I take to ensure this goes smoothly? Set the table to row level locking (should be for an OLTP environmnet anyway) and remove the data in small transactions (under 500 records at a time) also allows the process to be interrupted and restarted more easily. > > Also, after removing a large amount of data from a heavily indexed table, > wouldn't reindexing be in order? > > Thanks friends, > > Tam. > > <ifxmaillist@quanta.co.nz> wrote in message > news:mailman.532.1158876895.20706.informix-list@iiug.org... > > Tam > > > > The version of Informix is essential with this question. > > > > 1. The index size = index length * table size / table length > > > > 2. If using Informix 9 or 10 the index is in its own tblspace. It > > depends upon what you are trying to optimise - try it in your test > > environment and see if there is any difference. > > > > 3. Why do you reindex? We have never found it helpful and have larger > > tables than you describe. > > You can drop and create indexes "online" in Informix 10. > > > > MW > > > > -----Original Message----- > > From: informix-list-bounces@iiug.org > > [mailto:informix-list-bounces@iiug.org] > > On Behalf Of Tam OShanter > > Sent: Friday, 22 September 2006 9:52 a.m. > > To: informix-list@iiug.org > > Subject: Index Size, On Table, Off Table, Reindexing Online...... > > > > Hello All, > > Couple of questions: > > > > 1. If I have a table with 1.8GB of data how big would my ontable index be > > expected to be (I am assuming there is an algorithm to calculate this > > based > > on the number of columns in the index)? > > > > 2. For high performance OLTP systems, am I better placing my indexes off > > table or on table? > > > > 3. Why does it seem to take forever to reindex a table (not baiting here, > > but I thought IFX was a very performant system. Reindexing on SQL Server > > or > > Oracle seems to take much less time and can be done in an online manner, > > not > > removing my table from use??)? > > > > Any further tips you may have on idexing strategies would be welcome. > > > > Thank you all in advance, > > > > Tam. > > > > > > > > > > > > > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > > > > > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Tam OShanter wrote: > Thanks for the help. > > As to why we reindex, I'm not sure I understand the question... > > I'm assuming that indexes get "disheveled" so to speak if data is constantly > being inserted > and updated on a given table causing fragmentation. Informix rebalances and cleans index pages constantly using threads called Btree Scanners or Btree Cleaners depending on the version. After massive deletes this online incremental reorganization may take time and yes performance may be sub-optimal until it completes, but it does happen. Sometimes if you will be doing massive numbers of deletes or key column updates dropping an index and rebuilding it will resolve the performance issues faster. Mostly I find it unneccessary. > You are saying that reindexing does not really do anything? Does something, but something that will happen without a reindexing anyway. > The version we are using is 10UC4. An addendum to my question would be: > > Given that analysis has determined that "old" data that could be archived > as it's not needed) ) makes up in excess of 45% of the data in a given > table, hit constantly in an OLTP environment, wouldn't removing this data > lead to a performance increase? Some, but in an OLTP environment most record accesses are by primary key so the effect of extraneous data on performance is minimal. Informix's B+Tree indexes are very efficient, especially for unique keys. > Also, it seems as though deleting large amounts of data from this table > results in locking and performance degradation which is unacceptable. Locking problems? After the delete's been committed? Shouldn't be. Performance problems after massive deletes? Yes until the btree scanners have finished compressing and rebalancing the indexes. See my other comments above. > Is it possible to execute a large delete while maintaining system uptime? If > yes, what steps should I take to ensure this goes smoothly? As far as concurrency issues: 1. Your applications should all be using 'SET LOCK MODE TO WAIT <nsecs>;' to avoid receiving errors from transient locks. This will minimize the impact of the typical OLTP lock which lives for only a fraction of a second. 2. Delete larger numbers of rows using smaller transactions to avoid long transaction rollbacks, lock contention, and other similar problems. You can get my dbdelete utility to do this for you. Dbdelete is in the package utils2_ak in the IIUG Software Repository. > Also, after removing a large amount of data from a heavily indexed table, > wouldn't reindexing be in order? Yes and no. See comments above. Index build performance is dependent on several factors: 1. Environment var and/or SQL SET parameter PDQPRIORITY controls the resources available to sorts needed for the index build including parallel sorting and MGM managed memory to reduce disk IO for temporary sort-work files. 2. Environment var DBSPACETEMP can contain a list of dbspaces to be used for sort-work temp files/tables. 3. Env var PSORT_DBTEMP can contain a list of filesystems to use for sort-work files instead of the DBSPACETEMP dbspaces. These files are so short lived that taking advantage of the system OS buffer cache by using filesystem files instead of dbspace chunks (which are always opened in OSYNC mode bypassing the cache even if they are COOKED) can improve sort performance dramatically if parallel sorting is enabled using PDQPRIORITY and PSORT_NPROCS. 4. Environment variable PSORT_NPROCS controls the number of sort threads the IDS engine will use during parallel sorting (default 1). The docs say the max is 10 but setting this to 20 or 40 will increase the number of threads used beyond 10 (to involved to go into now). 5. The ONCONFIG parameters DS_MAX_QUERIES controls the number of PDQ (Parallel) queries that can execute simultaneously and DS_TOTAL_MEMORY controls the amount of MGM memory allocatable to all DS_MAX_QUERIES. So setting DS_TOTAL_MEMORY 10000 and DS_MAX_QUERIES 10 allows up to 1MB of MGM memory for PDQ processes to use for things like in-memory sorting. If you are building a 100MB index this setting would require at least 100 sort-work files to be written and merged. Changing DS_TOTAL_MEMORY 1000000 will allow the entire index sort to happen in memory assuming PDQPRIORITY > 1. If you also had PSORT_NPROCS=10 then the data would be divided into 10 10MB in-memory sorts all sorted in parallel resulting in a VERY fast index build indeed. Read the Performance Guide for more information. Art S. Kagel > Thanks friends, > > Tam. > > <ifxmaillist@quanta.co.nz> wrote in message > news:mailman.532.1158876895.20706.informix-list@iiug.org... > >>Tam >> >>The version of Informix is essential with this question. >> >>1. The index size = index length * table size / table length >> >>2. If using Informix 9 or 10 the index is in its own tblspace. It >>depends upon what you are trying to optimise - try it in your test >>environment and see if there is any difference. >> >>3. Why do you reindex? We have never found it helpful and have larger >>tables than you describe. >>You can drop and create indexes "online" in Informix 10. >> >>MW >> >>-----Original Message----- >>From: informix-list-bounces@iiug.org >>[mailto:informix-list-bounces@iiug.org] >>On Behalf Of Tam OShanter >>Sent: Friday, 22 September 2006 9:52 a.m. >>To: informix-list@iiug.org >>Subject: Index Size, On Table, Off Table, Reindexing Online...... >> >>Hello All, >>Couple of questions: >> >>1. If I have a table with 1.8GB of data how big would my ontable index be >>expected to be (I am assuming there is an algorithm to calculate this >>based >>on the number of columns in the index)? >> >>2. For high performance OLTP systems, am I better placing my indexes off >>table or on table? >> >>3. Why does it seem to take forever to reindex a table (not baiting here, >>but I thought IFX was a very performant system. Reindexing on SQL Server >>or >>Oracle seems to take much less time and can be done in an online manner, >>not >>removing my table from use??)? >> >>Any further tips you may have on idexing strategies would be welcome. >> >>Thank you all in advance,