C-ISAM indexes

I just spoke with tech support and they told me something I'm having problems believing. We have a 7.24UC1 SE database using C-ISAM programs to access the database. For three weeks in a row, we have had performance problems. The first problem showed up on April 20th and lasted through April 21st. To fix that problem, we tried rebuilding the indexes on April 23rd. The problem went away until April 28th. We ran secheck on April 30th. The problem went away until May 5th, even though secheck reported no errors. We didn't do anything this last weekend and the problem has continued through today. The actual problem is that physical IO goes to between 90 and 100 percent, causing some serious response problems. As you may notice, the problem is starting at the end of the week, Thursday on the first week, Friday on the next two. We've looked for all sorts of things. No program has changed, no new cron jobs are running, no extra users, no users running abnormal processes. I called tech support again today and they mention that SE indexes and C-ISAM indexes were separate. An index built using CREATE INDEX in dbaccess was not used by C-ISAM. For an index to be used by C-ISAM, it had to be created using isaddindex(). I have a hard time believing this, because, while I don't know that much about C-ISAM, we've been running this way for more than 6 months. Why would any problem show up now, and why would recreating or sechecking the index fix anything? I would have expected some serious performance problems before now if this were true. Also, why would the call to select the index (isstart, I believe) succeed if C-ISAM couldn't use it? An answer to either problem would be greatly appreciated. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

<mars1972@my-deja.com> wrote in message news:8f93f8$14c$1@nnrp1.deja.com... > I just spoke with tech support and they told me something I'm having > problems believing. > > We have a 7.24UC1 SE database using C-ISAM programs to access the > database. For three weeks in a row, we have had performance problems. > The first problem showed up on April 20th and lasted through April 21st. > To fix that problem, we tried rebuilding the indexes on April 23rd. > The problem went away until April 28th. We ran secheck on April 30th. > The problem went away until May 5th, even though secheck reported no > errors. We didn't do anything this last weekend and the problem has > continued through today. The actual problem is that physical IO goes to > between 90 and 100 percent, causing some serious response problems. As > you may notice, the problem is starting at the end of the week, > Thursday on the first week, Friday on the next two. We've looked for > all sorts of things. No program has changed, no new cron jobs are > running, no extra users, no users running abnormal processes. > > I called tech support again today and they mention that SE indexes and > C-ISAM indexes were separate. An index built using CREATE INDEX in > dbaccess was not used by C-ISAM. For an index to be used by C-ISAM, it > had to be created using isaddindex(). I have a hard time believing > this, because, while I don't know that much about C-ISAM, we've been > running this way for more than 6 months. Why would any problem show up > now, and why would recreating or sechecking the index fix anything? I > would have expected some serious performance problems before now if this > were true. Also, why would the call to select the index (isstart, I > believe) succeed if C-ISAM couldn't use it? > > An answer to either problem would be greatly appreciated. > > -- > # unrm / > ksh: unrm: not found > # man cpio > > > Sent via Deja.com http://www.deja.com/ > Before you buy. Do you insert/update/delete large amounts of data ? Have you tried running update statistics ? Update statistics can have a huge impact on the speed of the database.

No, really, help! In article <8f93f8$14c$1@nnrp1.deja.com>, mars1972@my-deja.com wrote: > I just spoke with tech support and they told me something I'm having > problems believing. > > We have a 7.24UC1 SE database using C-ISAM programs to access the > database. For three weeks in a row, we have had performance problems. > The first problem showed up on April 20th and lasted through April 21st. > To fix that problem, we tried rebuilding the indexes on April 23rd. > The problem went away until April 28th. We ran secheck on April 30th. > The problem went away until May 5th, even though secheck reported no > errors. We didn't do anything this last weekend and the problem has > continued through today. The actual problem is that physical IO goes to > between 90 and 100 percent, causing some serious response problems. As > you may notice, the problem is starting at the end of the week, > Thursday on the first week, Friday on the next two. We've looked for > all sorts of things. No program has changed, no new cron jobs are > running, no extra users, no users running abnormal processes. > > I called tech support again today and they mention that SE indexes and > C-ISAM indexes were separate. An index built using CREATE INDEX in > dbaccess was not used by C-ISAM. For an index to be used by C-ISAM, it > had to be created using isaddindex(). I have a hard time believing > this, because, while I don't know that much about C-ISAM, we've been > running this way for more than 6 months. Why would any problem show up > now, and why would recreating or sechecking the index fix anything? I > would have expected some serious performance problems before now if this > were true. Also, why would the call to select the index (isstart, I > believe) succeed if C-ISAM couldn't use it? > > An answer to either problem would be greatly appreciated. > > -- > # unrm / > ksh: unrm: not found > # man cpio > > Sent via Deja.com http://www.deja.com/ > Before you buy. > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

mars1972@my-deja.com wrote: > I just spoke with tech support and they told me something I'm having > problems believing. > > We have a 7.24UC1 SE database using C-ISAM programs to access the > database. For three weeks in a row, we have had performance problems. > The first problem showed up on April 20th and lasted through April 21st. > To fix that problem, we tried rebuilding the indexes on April 23rd. > The problem went away until April 28th. We ran secheck on April 30th. > The problem went away until May 5th, even though secheck reported no > errors. We didn't do anything this last weekend and the problem has > continued through today. The actual problem is that physical IO goes to > between 90 and 100 percent, causing some serious response problems. As > you may notice, the problem is starting at the end of the week, > Thursday on the first week, Friday on the next two. We've looked for > all sorts of things. No program has changed, no new cron jobs are > running, no extra users, no users running abnormal processes. Something is going haywire. What tools are you using to track where the resources are being used? Is it a runaway SE process, or a runaway C-ISAM process, or is it something unrelated. Something changes -- that sort of thing doesn't happen by accident. > I called tech support again today and they mention that SE indexes and > C-ISAM indexes were separate. An index built using CREATE INDEX in > dbaccess was not used by C-ISAM. For an index to be used by C-ISAM, it > had to be created using isaddindex(). I have a hard time believing > this, Me too! All the SE indexes are created with C-ISAM and the isaddindex() call, so C-ISAM can use any SE index if it is prepared to work out which indexes are available for use. Most C-ISAM programs aren't that sophisticated, but that's a separate issue. There is a converse to this issue, which is probably what's confusing the person you're speaking with. That is, SE cannot use indexes which it did not create. Further, not every C-ISAM file is acceptable to SE -- SE requires the primary key specified with isbuild() has k_nparts = 0. > because, while I don't know that much about C-ISAM, we've been > running this way for more than 6 months. Why would any problem show up > now, and why would recreating or sechecking the index fix anything? It depends on what the trouble really is. You are going to have to establish why you get runaway physical I/O. Until you do that, it will be difficult to guess what's up. > I would have expected some serious performance problems before now if this > > were true. Also, why would the call to select the index (isstart, I > believe) succeed if C-ISAM couldn't use it? C-ISAM can use any SE-created index. SE cannot use all indexes created by C-ISAM. > An answer to either problem would be greatly appreciated. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>

In article <3918EE65.B3FAFF6C@earthlink.net>, Jonathan Leffler <jleffler@earthlink.net> wrote: > mars1972@my-deja.com wrote: > > > I just spoke with tech support and they told me something I'm having > > problems believing. > > > > We have a 7.24UC1 SE database using C-ISAM programs to access the > > database. For three weeks in a row, we have had performance problems. > > The first problem showed up on April 20th and lasted through April 21st. > > To fix that problem, we tried rebuilding the indexes on April 23rd. > > The problem went away until April 28th. We ran secheck on April 30th. > > The problem went away until May 5th, even though secheck reported no > > errors. We didn't do anything this last weekend and the problem has > > continued through today. The actual problem is that physical IO goes to > > between 90 and 100 percent, causing some serious response problems. As > > you may notice, the problem is starting at the end of the week, > > Thursday on the first week, Friday on the next two. We've looked for > > all sorts of things. No program has changed, no new cron jobs are > > running, no extra users, no users running abnormal processes. > > Something is going haywire. What tools are you using to > track where the resources are being used? Is it a runaway > SE process, or a runaway C-ISAM process, or is it something > unrelated. Something changes -- that sort of thing doesn't > happen by accident. > OS tools, top, glance, etc. Our current theory is that while the number of users hasn't changed much, more of them are running nasty programs that do mass scans and updates. There aren't any runaway processes. Well, actually there are, but we've been getting those for better than a year. I haven't been able to keep them from happening. I posted a question about that here several months ago but didn't find much that was useful. > > I called tech support again today and they mention that SE indexes and > > C-ISAM indexes were separate. An index built using CREATE INDEX in > > dbaccess was not used by C-ISAM. For an index to be used by C-ISAM, it > > had to be created using isaddindex(). I have a hard time believing > > this, > > Me too! All the SE indexes are created with C-ISAM and the > isaddindex() call, so C-ISAM can use any SE index if it is > prepared to work out which indexes are available for use. > Most C-ISAM programs aren't that sophisticated, but that's a > separate issue. There is a converse to this issue, which is > probably what's confusing the person you're speaking with. > That is, SE cannot use indexes which it did not create. Further, > not every C-ISAM file is acceptable to SE -- SE requires the > primary key specified with isbuild() has k_nparts = 0. > Ah. That makes sense... > > because, while I don't know that much about C-ISAM, we've been > > running this way for more than 6 months. Why would any problem show up > > now, and why would recreating or sechecking the index fix anything? > > It depends on what the trouble really is. You are going to > have to establish why you get runaway physical I/O. Until > you do that, it will be difficult to guess what's up. > > > I would have expected some serious performance problems before now if this > > > > were true. Also, why would the call to select the index (isstart, I > > believe) succeed if C-ISAM couldn't use it? > > C-ISAM can use any SE-created index. SE cannot use all > indexes created by C-ISAM. > Thanks! At least now I don't have to write some program to recreate all of the indexes on the database using isaddindex... > > An answer to either problem would be greatly appreciated. > > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN > #include <disclaimer.h> > > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.