Batch job slows down

Hi ! IDS 10.00.FC8 on HP-UX 11.31, EMC SAN We have been running the same batch job (transactions updating a number of lagre tables 20 million rows) for a number of years. This batch usually takes about 1 hour, now it takes up to 5 hours. Number of transactions are the same, roughly. The batch does a number of selects and based on them a a number of insert/updates/deletes. We have not changed the batch program or any settings in IDS. One idea is that the problem is connected to the statistics for the tables. We where using "dostats" once a week, we ran into the problem, did a new "dostats" and the problem vanished. The theory was the that the update statistics somehow failed sometimes so we stopped using "dostats". After some major changes of values for indexed rows we decided to run dostats again (it takes about 3 hours to run). We now hit the problem again, no error messages from dostats. We did a manual update statistics (script created by Server Studio), problem remains. I have done a "set explain on" for the batch program, nothing that sticks out. Any ideas are appreciated. Another try with "dostats" ? Rebuild indexes ?

Ulf wrote: > I have done a "set explain on" for the batch program, nothing that > sticks out. I'm guessing here, but that explain plan is telling you that all the queries you expect to use indexes, are using indexes. But are they using the correct indexes? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

Is it possible that the tables that are being accessed have become fragmented and need to be reorged? Why did/do you think that dostats has anything to do with the problem? What do you mean 'did a new "dostats" and the problem vanished'? If a manual update stats script created by Server Studio didn't improve or hurt performance why not continue to use dostats or the script? To really diagnose this, I'd want to connect to the server and poke around. We'll try our best by remote control, but... Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jan 26, 2010 at 4:49 AM, Ulf <ulf.akerberg@gmail.com> wrote: > Hi ! > > IDS 10.00.FC8 on HP-UX 11.31, EMC SAN > > We have been running the same batch job (transactions updating a > number of lagre tables 20 million rows) for a number of years. This > batch usually takes about 1 hour, now it takes up to 5 hours. Number > of transactions are the same, roughly. The batch does a number of > selects and based on them a a number of insert/updates/deletes. > > We have not changed the batch program or any settings in IDS. > > One idea is that the problem is connected to the statistics for the > tables. We where using "dostats" once a week, we ran into the problem, > did a new "dostats" and the problem vanished. The theory was the that > the update statistics somehow failed sometimes so we stopped using > "dostats". > > After some major changes of values for indexed rows we decided to run > dostats again (it takes about 3 hours to run). We now hit the problem > again, no error messages from dostats. We did a manual update > statistics (script created by Server Studio), problem remains. > > I have done a "set explain on" for the batch program, nothing that > sticks out. > > Any ideas are appreciated. > > Another try with "dostats" ? > > Rebuild indexes ? > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Thank you for your answer and the questions, I will try to answer them: 1 They tables may need a reorg but as a new update statistics solved the problem (using dostats) I don't think so 2. Dostats itself does not cause the problem, I suspect that the update statistics done by dostats somehow fails sometimes 3. When we first hit the problem we did a new dostats and then the problem was solved until we now two months later runs dostats again and the problem appears again I know that it is not dostats that causes the problem, I mentioned it because the type of update statistics it produces is known to a number of people

Hi, Another shot in the dark... Maybe the reason dostats is failing and the batch job is taking longer are caused by the same underlying problem. Have you check I/O stats and waits on the SAN? Could there be a slowly degrading disk problem or disk contention? Regards - Lester Ulf wrote: > > Thank you for your answer and the questions, I will try to answer > them: > > 1 They tables may need a reorg but as a new update statistics solved > the problem (using dostats) I don't think so > > 2. Dostats itself does not cause the problem, I suspect that the > update statistics done by dostats somehow fails sometimes> > 3. When we first hit the problem we did a new dostats and then the > problem was solved until we now two months later runs dostats again > and the problem appears again > > I know that it is not dostats that causes the problem, I mentioned it > because the type of update statistics it produces is known to a number > of people > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- ______________________________________________________________________ Lester Knutsen lester@advancedatatools.com Advanced DataTools Corporation Voice: 703-256-0267 x102 Visit our Web page: http://www.advancedatatools.com ______________________________________________________________________

We have are looking at the disksystem and SAN , we have tested it using 'dd' and it seems OK. EMC will have a look as well

See my notes below: Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jan 26, 2010 at 9:47 AM, Ulf <ulf.akerberg@gmail.com> wrote: > > > Thank you for your answer and the questions, I will try to answer > them: > > 1 They tables may need a reorg but as a new update statistics solved > the problem (using dostats) I don't think so > OK, so by 'new update statistics' and 'new dostats' you mean rerunning the update statistics commands or rerunning dostats fixed the problem. Howoften do you normally run dostats/update statistics? > > 2. Dostats itself does not cause the problem, I suspect that the > update statistics done by dostats somehow fails sometimes> If the dostats run was failing in any way it would be noted in the output. If you are capturing stderr you would see any error messages. Dostats checks for, traps, and reports any errors it encounters whether while gathering intelligence or actually running the update statistics commands. > > 3. When we first hit the problem we did a new dostats and then the > problem was solved until we now two months later runs dostats again > and the problem appears again > If the problem recurs, run an dbschema -d <database> -hd all and redirect it to a file. Then run the dostats (or other equivalent update stats run) and if all is well, run the dbschema again as above to another file and compare the two files. That may tell you what's been happening. If you can't figure it out from there yourself, feel free to send me the two files (and indicate what tables are specifically having problems) or call IBM, open a PMR, and have the case engineer attach the two output files to the case to see what IBM thinks. > > I know that it is not dostats that causes the problem, I mentioned it > because the type of update statistics it produces is known to a number > of people > No harm no foul! Just trying to understand. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Ulf wrote: > Hi ! > > IDS 10.00.FC8 on HP-UX 11.31, EMC SAN > > We have been running the same batch job (transactions updating a > number of lagre tables 20 million rows) for a number of years. This > batch usually takes about 1 hour, now it takes up to 5 hours. Number > of transactions are the same, roughly. The batch does a number of > selects and based on them a a number of insert/updates/deletes. > > We have not changed the batch program or any settings in IDS. > > One idea is that the problem is connected to the statistics for the > tables. We where using "dostats" once a week, we ran into the problem, > did a new "dostats" and the problem vanished. The theory was the that > the update statistics somehow failed sometimes so we stopped using > "dostats". > > After some major changes of values for indexed rows we decided to run > dostats again (it takes about 3 hours to run). We now hit the problem > again, no error messages from dostats. We did a manual update > statistics (script created by Server Studio), problem remains. > > I have done a "set explain on" for the batch program, nothing that > sticks out. > > Any ideas are appreciated. > > Another try with "dostats" ? > > Rebuild indexes ? A blind guess. You talked about major changes in indexed values. Check your btscanner activity and configuration. Maybe your indexes are giving too much work for the index cleaners. 10.00.FC8 is, to the best of my knowledge, a good version. previous versions (FC6 and FC7 if I recall correctly) had serious issues. Besides that, take a look at any kind of lock waiting issues... Maybe other jobs cause troubles to that one?... Regards.

As Art says, it would be nice to be able to poke around. So take what you read with a grain of Salt. OTC is right. Take a look at your batch jobs and see if the indexes match the queries. I think it was Lester who said to take a look at your san. Disk failures don't just happen. There are usually signs that a disk is going to fail before they do. (Although I've seen some drive go poof without any signs). If you've been running this on a SAN for the length of time you said you have, unless you've been replacing drives, you could have some drive issues. Because the SAN uses raid, you may have a problem and not notice it. Having said all of that, here's a couple of ideas... First, consider migrating the data to a different part of the SAN. I mean create a new table space and a new index space on new disks and then rebuild the table. Second, idea. Drop and rebuild the indexes. If they are not detached, detach them and rebuild them in a different table space. If the number of rows in the table haven't really changed over the years, meaning you have roughly 20 million rows that are being updated, or replaced, what do you expect to happen when you run update statistics. I mean sure it doesn't hurt, but your stats aren't really changing. I would agree with Lester that it could be the SAN and you have disk issues. It could also be a hardware issue too. I'd say drop and rebuild your indexes would be a good place to start. But hey! What do I know? My head is in the clouds. :-P -G > From: ulf.akerberg@gmail.com > Subject: Batch job slows down > Date: Tue, 26 Jan 2010 01:49:53 -0800 > To: informix-list@iiug.org > > Hi ! > > IDS 10.00.FC8 on HP-UX 11.31, EMC SAN > > We have been running the same batch job (transactions updating a > number of lagre tables 20 million rows) for a number of years. This > batch usually takes about 1 hour, now it takes up to 5 hours. Number > of transactions are the same, roughly. The batch does a number of > selects and based on them a a number of insert/updates/deletes. > > We have not changed the batch program or any settings in IDS. > > One idea is that the problem is connected to the statistics for the > tables. We where using "dostats" once a week, we ran into the problem, > did a new "dostats" and the problem vanished. The theory was the that > the update statistics somehow failed sometimes so we stopped using > "dostats". > > After some major changes of values for indexed rows we decided to run > dostats again (it takes about 3 hours to run). We now hit the problem > again, no error messages from dostats. We did a manual update > statistics (script created by Server Studio), problem remains. > > I have done a "set explain on" for the batch program, nothing that > sticks out. > > Any ideas are appreciated. > > Another try with "dostats" ? > > Rebuild indexes ? > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. http://clk.atdmt.com/GBL/go/196390709/direct/01/