SQL slowdown. Index rebuild?
After upgrading from IDS 10 to 11.50.FC8, a user saw query run times degrade; reverting OPTCOMPIND from 2 to 0 helped slightly, but the big win came from dropping and recreating indexes, prompting the question of whether rebuilds are needed after an upgrade. John Miller (IBM) pointed to the B-tree cleaner rather than the upgrade: check onstat -C, add BTSCANNER threads (they can be started/stopped with onmode -C), ensure large, heavily updated tables use detached indexes, and note ALICE auto-raises its mode (or set it to 10 up front). No confirmation of the final outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
About a month ago, we upgrade all of our clients from IDS10 to IDS11.5 FC8 and iSQL from 7.32 to 7.5. It did not happen immediately after the upgrade (and therefore, maybe it is not even related) but the run times of a number of SQL queries on most of our clients have plumeted. I have been through the onconfig files and noticed that OPTCOMPIND was changed from 0 to 2 during the upgrade. I changed this back, bounced the engine and go some slightly better results. The biggest improvement I have achieved, resulted from dropping and re-creating the indexes for the tables referenced in the queries. My question is, should we be dropping and re-creating all indexes after upgrading iSQL or IDS, or do you think these improvements are co-incidental? I am continuing to investigate and have opened a case with Informix Tech support but maybe somebody else has come across sililar issues. Any help is appreciated. Thanks Paul Ridding Tourism Technology
Sorry, forgot to mention that update statistics drop distributions was done and a re-creation of the statistics LOW and MEDIUM for the tables and HIGH for the indexes.
G'day:
I would suggestion a few things.
1. It sounds like the btree cleaner might not be tuned correctly. You
can
use onstat -C to view the status of the btree cleaner. You might
need
to increase the number of btree cleaners.
2. One issues is that most indexes in newer version are detached, in
version 7
that was not always the case. You can greatly assist the performance
of
btree cleaner and your system if you ensure all the large indexes are
detached.
Below is a query which will find these indexes and tables.
select T.*
from sysmaster:sysptnhdr P , sysmaster:systabnames T
where nrows > 1 and nkeys>1
and T.partnum = P.partnum
and T.tabname not matches "sys*"
3. You can use the sqltrace feature in version 11 to see what has changed
in the
queries.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 10/11/2010 09:23:06 PM:
> From:
>
> "PAUL RIDDING" <pridding@tt.com.au>
>
> To:
>
> ids@iiug.org
>
> Date:
>
> 10/11/2010 09:23 PM
>
> Subject:
>
> SQL slowdown. Index rebuild? [21635]
>
> Sent by:
>
> ids-bounces@iiug.org
>
> About a month ago, we upgrade all of our clients from IDS10 to
> IDS11.5 FC8 and
> iSQL from 7.32 to 7.5. It did not happen immediately after the upgrade
(and
> therefore, maybe it is not even related) but the run times of a number of
SQL
> queries on most of our clients have plumeted.
>
> I have been through the onconfig files and noticed that OPTCOMPIND
> was changed
> from 0 to 2 during the upgrade.
>
> I changed this back, bounced the engine and go some slightly better
results.
>
> The biggest improvement I have achieved, resulted from dropping and
> re-creating the indexes for the tables referenced in the queries.
>
> My question is, should we be dropping and re-creating all indexes after
> upgrading iSQL or IDS, or do you think these improvements are
co-incidental?
>
> I am continuing to investigate and have opened a case with Informix Tech
> support but maybe somebody else has come across sililar issues.
>
> Any help is appreciated.
>
> Thanks
> Paul Ridding
> Tourism Technology
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hi John,
Thanks for the reply. Your suggestion regarding mis-configured BTSCANNER
threads seems to be pointing me in the right direction.
After looking at the onmode -C output and reading a bunch of info on IIUG,
Informix Docs and other sites, I am still none the wiser regarding some
sensible values.
I will monitor the hot list to try and determine if more threads are required.
There seems to be a lot of info out there saying that the default config
(which is what we are currently using) is way off.
BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default
Any recommendations for THRESHOLD and RANGESIZE? How about 50000 and 10000?
The indexes on the tables that have a lot of updates are in the 500MB to 1GB
size.
Also, there is very little documentation on alice settings. Is 6 acceptable?
They seem to be the preferred scans.
Thanks and regards,
Paul
Hi John,
Thanks for the reply. Your suggestion regarding mis-configured BTSCANNER
threads seems to be pointing me in the right direction.
After looking at the onmode -C output and reading a bunch of info on IIUG,
Informix Docs and other sites, I am still none the wiser regarding some
sensible values.
I will monitor the hot list to try and determine if more threads are required.
There seems to be a lot of info out there saying that the default config
(which is what we are currently using) is way off.
BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default
Any recommendations for THRESHOLD and RANGESIZE? How about 50000 and 10000?
The indexes on the tables that have a lot of updates are in the 500MB to 1GB
size.
Also, there is very little documentation on alice settings. Is 6 acceptable?
Paul:
Please make sure that your highly updated tables, which are
large have detached indexes (data and indexes are not in the same
partition). This goes a long way for increasing the efficency of the
btree cleaner
ALICE will automatically adjust the mode higher if it finds that the
cleaning is not efficient. If you want the efficiency to be high right out
of the box then you can set the mode to 10. This will use about
approx 1-2KB more per index (the actual amount depends on the size
of the index).
I would probably error at first in having to many btree cleaner threads,
because if there is not work for them to do they just sit idle. You can
start
and stop them dynamically using onmode -C start # and onmode -C stop #
The ideal number does depend on how fast the btree cleaners process
the hot list.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 10/12/2010 04:47:04 PM:
> From:
>
> "PAUL RIDDING" <pridding@tt.com.au>
>
> To:
>
> ids@iiug.org
>
> Date:
>
> 10/12/2010 04:47 PM
>
> Subject:
>
> Re: SQL slowdown. Index rebuild? [21665]
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Hi John,
>
> Thanks for the reply. Your suggestion regarding mis-configured BTSCANNER
> threads seems to be pointing me in the right direction.
>
> After looking at the onmode -C output and reading a bunch of info on
IIUG,
> Informix Docs and other sites, I am still none the wiser regarding some
> sensible values.
>
> I will monitor the hot list to try and determine if more threads
arerequired.
> There seems to be a lot of info out there saying that the default config
> (which is what we are currently using) is way off.
>
> BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default
>
> Any recommendations for THRESHOLD and RANGESIZE? How about 50000 and
10000?
> The indexes on the tables that have a lot of updates are in the 500MB to
1GB
> size.
>
> Also, there is very little documentation on alice settings. Is 6
acceptable?
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>