Update statistics and shared memory

We are not exactly sure what our problem is, but any help would be greatly appreciated. We are running IDS 7.31.UD3-1 on AIX 4.3.3. We have been experiencing a problem for a number of years, but it had not been one that has caused us any major problems untill recently. Here is the scenerio.. We used to run update stats low on alot of our tables during the week, and then run them high on Sunday morning. This worked well for us for many years. Then last year we noticed our nightly processes taking longer and longer to run. After much investigation, the only culprit we could find was the update stats we run daily. So we stopped them. The nightly processes went back to completing in a more reasonable time. Everything seemed fine until recently. After we would come up from a reboot, every process would run just fine, until our weekly update stats ran. After that, our nightly processes would run longer, until we shut down Informix, and brought it back up. Then everything was fine again, until update stats ran on the weekend. We are not sure, but is seems like shared memory is getting garbaged up, and the system does not know to use the new statistics. Any suggestions/help would be greatly appreciated. Carl Smith Systems Administrator Sidley Austin LLP

Or maybe the update stats aren't giving you optimal statistics and so the overnight processing is taking longer as a consequence... Are you following the Informix guidelines and/or using Art Kagels dostats program? Do you update stats on all tables, or have you identified the ones that have changed significantly? Are you able to identify which bit of the overnight is taking too long? If you can do that it may be the sql that is non-optimal, or at least you'd know which tables to investigate further. Is the daytime work running OK? If it is OK both with and without update stats, why bother with update stats? You mention shared memory. Have you many segments? can you reduce the number of them? Do your other stats look ok? Sorry, no answers, just some things that I'd look at... Good luck, Andy. >From: "CARL SMITH" <carlsmith@sidley.com> >Reply-To: ids@iiug.org >To: ids@iiug.org >Subject: Update statistics and shared memory [7177] >Date: Mon, 31 Jul 2006 14:10:02 -0400 (EDT) > >We are not exactly sure what our problem is, but any help would be greatly >appreciated. > >We are running IDS 7.31.UD3-1 on AIX 4.3.3. We have been experiencing a >problem for a number of years, but it had not been one that has caused us >any >major problems untill recently. Here is the scenerio.. > >We used to run update stats low on alot of our tables during the week, and >then run them high on Sunday morning. This worked well for us for many >years. >Then last year we noticed our nightly processes taking longer and longer to >run. After much investigation, the only culprit we could find was the >update >stats we run daily. So we stopped them. The nightly processes went back to >completing in a more reasonable time. Everything seemed fine until >recently. > >After we would come up from a reboot, every process would run just fine, >until >our weekly update stats ran. After that, our nightly processes would run >longer, until we shut down Informix, and brought it back up. Then >everything >was fine again, until update stats ran on the weekend. > >We are not sure, but is seems like shared memory is getting garbaged up, >and >the system does not know to use the new statistics. > >Any suggestions/help would be greatly appreciated. > >Carl Smith >Systems Administrator >Sidley Austin LLP > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters

We are running Art Kagel's dostats for all tables. Our daytime work is ok, but it is not the same type of work. If we do not run stats at all, all work eventually slows down. We have 1 huge shared memory segment. We have adjusted this over the years so we rarely add any more segments. No problem on the no answers part. They are questions that need to be asked. :)

So, a strange leap takes me to... how about if the update stats process messes up all the pages you've been happily cacheing all day, and then when the real work starts again you've loads of pages that you need to 'purge' before it can get going...?? You could maybe get a handle on that if you were to zero counters after the nightly update stats and then take some SMI snapshots as the nightly process was running. Or try to repopulate the cache with a select * from <suspect_tables> > NULL before the nightly jobs? I'd also try to get some overall system performance stats for the machine too. You say you have a huge shared memory segment. Hopefully there's still enough left for the nightly stuff to run without thrashing? Finally, are you able to 'set explain on' for the nightly jobs? You may see some helpful difference if you could run with/without update stats? Andy. >From: "CARL SMITH" <carlsmith@sidley.com> >Reply-To: ids@iiug.org >To: ids@iiug.org >Subject: Re: RE: Update statistics and shared memory [7185] >Date: Tue, 1 Aug 2006 10:20:46 -0400 (EDT) > >We are running Art Kagel's dostats for all tables. >Our daytime work is ok, but it is not the same type of work. If we do not >run >stats at all, all work eventually slows down. >We have 1 huge shared memory segment. We have adjusted this over the years >so >we rarely add any more segments. > >No problem on the no answers part. They are questions that need to be >asked. >:) > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ The new MSN Search Toolbar now includes Desktop search! http://join.msn.com/toolbar/overview

We suspect that the cache may be causing us the problem. We run the stats on Sunday. Monday night we had the performance problem. Tuesday evening we stopped and restarted informix. The Tuesday night jobs ran with no problems. We are looking at all the parameters for the stats builds, to see if we can not only speed them up, but elimincate this problem at the same time. We are not sure which table is causing us the problem, but we have a few suspects. We will try the select and see if that helps. We have plenty of memory left in the main segment, and lots left for the system to add to informix. We need to builds stats, because our daily processes will eventually slow down if we do not. We are not sure how long we could run between stat runs before we see that happen.

And another thing.... You're not doing something directly after the update stats to make them invalid, are you? Are you running the update stats directly before the nightly jobs? I don't know what these jobs may be, but if they are 'reports', then running them after update stats would seem to be a good idea. If they do lots of insert/delete/updates, then you may be better off doing the update stats afterwards so that the day runs better... You may even need to run update stats before and after... which is where getting to know which tables are involved becomes essential. For a bit more background, how long does update stats take to run? and the nightly jobs? and how big is your nightly maintenance window? Andy. >From: "CARL SMITH" <carlsmith@sidley.com> >Reply-To: ids@iiug.org >To: ids@iiug.org >Subject: Re: RE: Update statistics and shared memory [7194] >Date: Tue, 1 Aug 2006 16:48:40 -0400 (EDT) > >We suspect that the cache may be causing us the problem. We run the stats >on >Sunday. Monday night we had the performance problem. Tuesday evening we >stopped and restarted informix. The Tuesday night jobs ran with no >problems. >We are looking at all the parameters for the stats builds, to see if we can >not only speed them up, but elimincate this problem at the same time. > >We are not sure which table is causing us the problem, but we have a few >suspects. We will try the select and see if that helps. > >We have plenty of memory left in the main segment, and lots left for the >system to add to informix. > >We need to builds stats, because our daily processes will eventually slow >down >if we do not. We are not sure how long we could run between stat runs >before >we see that happen. > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Windows Live™ Messenger has arrived. Click here to download it for free! http://imagine-msn.com/messenger/launch80/?locale=en-gb

On 7/31/06, CARL SMITH <carlsmith@sidley.com> wrote: > > > We are not exactly sure what our problem is, but any help would be greatly > appreciated. > > We are running IDS 7.31.UD3-1 on AIX 4.3.3. We have been experiencing a > problem for a number of years, but it had not been one that has caused us > any > major problems untill recently. [...] You've been getting pretty good help from others - I don't have anything extra to offer on the mechanics of your problem. I do note, though, that you are running an old version of IDS on an out-of-service operating system. You should, ideally, upgrade to AIX 5.3and IDS 10.00. There are definitely things that can help UPDATE STATISTICS in the later versions. Even within the 7.31 family, your version number should be more like UD8 or UD9, I believe, though there's a chance that IDS ports to AIX 4.3.3 is no longer being upgraded and so the most recent version is older (possibly as old as UD3). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/