is this normal?

hey guys! :) I am using Informix Dynamic Server Version 7.31.UC2 -- On-Line on solaris 2.7. I have a database with about 15 tables in it. They are relational and goes about 4 levels deep based on foreign keys. All those keys are also on delete cascade. The database is made with buffered logging on. The whole database is about a quarter million records on the primary table, no more than a gig in size the whole thing. Here is my problem. I use perl's dbi lib to connect to it and update records nightly. Every night I have about12000 records to update/add. Due to the complexity of the records, everytime if I need to update a record, all I do is to delete it and readd it again. Now, everytime when it added around 300 records, which is about a minute and half, it goes into a 45 seconds checkpoint. Which takes me almost a whole hour to finish updating. All I want to know is that A. is this normal and B. can I make any improvments or am I missing something here? thanks a lot guys. yan

Yan Zhu wrote: > I am using Informix Dynamic Server Version 7.31.UC2 -- On-Line on > solaris 2.7. > > I have a database with about 15 tables in it. They are relational > and goes about 4 levels deep based on foreign keys. All those keys are > also on delete cascade. The database is made with buffered logging on. > The whole database is about a quarter million records on the primary > table, no more than a gig in size the whole thing. > > Here is my problem. I use perl's dbi lib to connect to it and update > records nightly. Every night I have about 12000 records to update/add. > Due to the complexity of the records, everytime if I need to update a > record, all I do is to delete it and readd it again. Now, everytime > when it added around 300 records, which is about a minute and half, > it goes into a 45 seconds checkpoint. Which takes me almost a whole > hour to finish updating. All I want to know is that A. is this normal > and B. can I make any improvments or am I missing something here? 1. Is it really a good idea to delete and insert the records to update them? 200 records a minute isn't all that fast, though it isn't all that slow; it depends on how big they are and what else is going on. 2. A 45 second checkpoint is always dubious; I'd regard it as abnormal. 3. You're probably going to want to look hard at your ONCONFIG file. I'd probably decrease LRU_MIN_DIRTY and LRU_MAX_DIRTY a lot (eg 5 and 10, or even lower; in extremis, 0 and 1). I'd look at how much write activity is occurring at the checkpoint versus in between, and try to increase the amount of intermediate writing so there's less to do at the checkpoint. Your overall processing speed may partly be affected by the Perl and DBI and DBD::Informix, but it probably isn't the principal bottle- neck. I'd be looking hard at the referential integrity stuff to see what is really going on behind the scenes for these operations. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Take a look at what is triggering the checkpoint. I assume your checkpoint duration is more than three minutes, which implies that the checkpoint is triggered by the physical log reaching it's trigger percentage. Check your writes (onstat -F I think) and see if you are doing all chunk writes. If so, by increasing the LRU writes you can reduce the checkpoint duration. A previous reply addressed the LRU settings you need. Also, you mentioned foreign keys and cascade deletes. The cascade deletes are expensive and are best avoided. It's much faster to handle the deletes in a 4GL or similar program. More info on tuning is in the IDS Performance Tuning manual. Yan Zhu <yan.zhu@infinity-insurance.com> wrote in message news:7l2ueo$rlo$1@news.xmission.com... > > hey guys! :) > > I am using Informix Dynamic Server Version 7.31.UC2 -- On-Line on > solaris 2.7. > > I have a database with about 15 tables in it. They are relational > and goes about 4 levels deep based on foreign keys. All those keys are > also on delete cascade. The database is made with buffered logging on. > The whole database is about a quarter million records on the primary > table, no more than a gig in size the whole thing. > > Here is my problem. I use perl's dbi lib to connect to it and update > records nightly. Every night I have about12000 records to update/add. > Due to the complexity of the records, everytime if I need to update a > record, all I do is to delete it and readd it again. Now, everytime when > it added around 300 records, which is about a minute and half, it goes > into a 45 seconds checkpoint. Which takes me almost a whole hour to > finish updating. All I want to know is that A. is this normal and B. can > I make any improvments or am I missing something here? > > thanks a lot guys. > > yan > >