Informix 11.70.FC5
After upgrading from Informix 11.50.FC5 to 11.70.FC5 on AIX 6.1, a user found queries taking minutes on the test box instead of seconds, with huge read counts pointing at sequential scans. More buffers and readahead tuning helped little. Suggestions: compare hardware/ONCONFIG, check onstat -p and onstat -g ppf seqsc counts, review plans, and force index use via optimizer directives (which worked, but plans reverted otherwise). Art Kagel noted 11.70 UPDATE STATISTICS skips current distributions unless FORCE is used, advising 'delete from sysdistrib' first (or dostats --clean-distributions --force-run). The poster planned to try this; no confirmed outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We're trying to upgrade to the latest Informix version but are having some serious performance issues. Our Test Box is running AIX v6.1 and we're upgrading from Informix 11.50.FC5. Our Development box did alright with it getting query times after tuning of similar performance to 11.50.FC5. Currently we're seeing queries that run in just a couple of seconds on our dev box take upwards of 10 or 11 minutes on our Test box. I've noticed that we've got a couple of Informix daemon threads with well over 60 million reads and 0 writes leading me to believe that this is a readahead thread. I've tried a lot of different settings with readahead that seemed to work great in DEV but horribly in TEST. We're adding more buffers tonight to bring that up to 200000 4k buffers. We're currently trying to run with 100000 buffers. Any performance tuning tips? or knowledge on what these daemon threads are and what they're doing?
> We're trying to upgrade to the latest Informix version but are having some serious performance issues. Our Test Box is running AIX v6.1 and we're upgrading from Informix 11.50.FC5. Our Development box did alright with it getting query times after tuning of similar performance to 11.50.FC5. Currently we're seeing queries that run in just a couple of seconds on our dev box take upwards of 10 or 11 minutes on our Test box. I've noticed that we've got a couple of Informix daemon threads with well over 60 million reads and 0 writes leading me to believe that this is a readahead thread. I've tried a lot of different settings with readahead that seemed to work great in DEV but horribly in TEST. We're adding more buffers tonight to bring that up to 200000 4k buffers. We're currently trying to run with 100000 buffers. Any performance tuning tips? or knowledge on what these daemon threads are and what they're doing? > 1. Do the TEST and DEV boxes have exactly the same hardware, AIX 6.1 and instance? 2. Are the onconfig params in both boxes exactly the same? 3. Have you examined the 11.70 release notes and installation guide. I would also examine "Migration Guide" and "Installation" in this link: http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp
1. Do the TEST and DEV boxes have exactly the same hardware, AIX 6.1 and instance? The TEST box has more hardware than DEV does. The AIX versions are the same the instance is the same as well. 2. Are the onconfig params in both boxes exactly the same? Now the buffers in TEST are more but other than that everything else is the same. 3. Have you examined the 11.70 release notes and installation guide. Yeah I've gone over it numerous times. I don't see any caveat in it that I think I could be running into. After adding more buffers yesterday it's been a little faster but not like I would expect it to be. Thanks
Nate
Have up updated statistics following the upgrade ?? To me a high number of
reads
would point towards sequential scans. Are these increasing rapidly
(onstat -p), are
they occurring on some tables (onstat -g ppf) and check seqsc column. Is the
issue on particular tables or all ? If only some then look at some
execution plans
to see if you are missing some indexes (or need to update stats).
Keith
On 19 July 2012 13:14, NATE HICKS <nathaniel.hicks@trnswrks.com> wrote:
> 1. Do the TEST and DEV boxes have exactly the same hardware, AIX 6.1 and
> instance?
>
> The TEST box has more hardware than DEV does. The AIX versions are the same
> the instance is the same as well.
>
> 2. Are the onconfig params in both boxes exactly the same?
>
> Now the buffers in TEST are more but other than that everything else is the
> same.
>
> 3. Have you examined the 11.70 release notes and installation guide.
>
> Yeah I've gone over it numerous times. I don't see any caveat in it that I
> think I could be running into.
>
> After adding more buffers yesterday it's been a little faster but not like I
> would expect it to be.
>
> Thanks
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Have up updated statistics following the upgrade ??
Yes I updated stats directly following the upgrade.
To me a high number of reads
would point towards sequential scans.
After reading this there was at least one daemon job no longer using the index
for the job. looking into this now. It uses it in 11.5 but nowhere we have
11.70.FC5 at.
Are these increasing rapidly
(onstat -p), are
they occurring on some tables (onstat -g ppf) and check seqsc column. Is the
issue on particular tables or all ?
Definitely goes up a lot on one particular table. That's what pointed to the
daemon.
If only some then look at some
execution plans
to see if you are missing some indexes (or need to update stats)
I didn't see any failures when I ran stats after the upgrade but we're going
to re-run them and cross our fingers.
Even after updating stats again it's refusing to use the same index that 11.50 did for the same query. This is quite confusing.
Wait, did you drop all of the distributions before running UPDATE stats to
replace them? Under 11.70 if the distributions are current, update
statistics doesn't actually do anything unless you added the FORCE option.
Run this command in the database THEN run your update statistics
script/suite:
delete from sysdistrib;
That will get rid of all of the existing distributions so that update
statistics will actually create new ones. Make sure that you do the
recommended suite of commands:
- HIGH on columns that lead index keys with DISTRIBUTIONS ONLY (you can
gang all leading columns into a single command)
- MEDIUM on every other column (this can be a single command for each table)
- LOW on each full index key
Of just use dostats with the --clean-distributions and --force-run options
which will take care of it all for you.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
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 Thu, Jul 19, 2012 at 1:40 PM, NATE HICKS
<nathaniel.hicks@trnswrks.com>wrote:
> Even after updating stats again it's refusing to use the same index that
> 11.50
> did for the same query. This is quite confusing.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae93404457b4e5c04c5326b98
Have you tried an optimizer directive to use that index? Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "NATE HICKS" <nathaniel.hicks@trnswrks.com> To: ids@iiug.org Date: 07/19/2012 01:40 PM Subject: Re: Informix 11.70.FC5 [27715] Sent by: ids-bounces@iiug.org Even after updating stats again it's refusing to use the same index that 11.50 did for the same query. This is quite confusing. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Under 11.70 if the distributions are current, update statistics doesn't actually do anything unless you added the FORCE option. Run this command in the database THEN run your update statistics Did not realize that. That's the next try though. Thank you. I just got dostats the other day too but we didn't use it on this...yet. Thank you
I did try using optimizer directives. That way I could force it to use the index but if I don't do that it just does a sequential scan.