Re: IDS 9.4 Slower then IDS 7.31
A user reported that queries ran about ten times slower after migrating from IDS 7.31 to 9.4, even though query plans were identical to the old version and UPDATE STATISTICS (low/medium/high) had been run; large result sets suffered most, while disk/RAID layout, fragmentation and extents were unchanged or improved. Suggestions included checking query plans, update statistics for procedures, and onmode -C threshold. One poster offered a likely cause: after onmode -BC 2, pages still stored in the old on-disk format must be converted when read into the buffer pool, slowing scans; the workaround is to update one row per page to force rewriting in the new format (the poster planned a tool to generate such SQL, also useful for completing in-place alters). No confirmation from the original poster that this resolved it is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
idsexpert@gmail.com wrote:
> Please run UPDATE STATISTICS LOW on your databases and report back if
> you still run into performance issues.
UPDATE STATISTICS LOW has been ran, and is re-run on each table on aperiodic basis. Along with HIGH on columns involved with an index or
otherwise used in a join. Medium on all others.
Preformance is still very poor.
On 6 Apr 2006 07:32:14 -0700, "cgates" <cgates@adt.com> wrote:
>
>idsexpert@gmail.com wrote:
>> Please run UPDATE STATISTICS LOW on your databases and report back if
>> you still run into performance issues.
>
>UPDATE STATISTICS LOW has been ran, and is re-run on each table on a>periodic basis. Along with HIGH on columns involved with an index or
>otherwise used in a join. Medium on all others.
>
>Preformance is still very poor.
How do the query plans look?
JWC
update statistics for procedure ??
Give this a try (just the first shot):
$ onmode -C threshold 20000
The stange thing is the query plans look identical to when they are ran on 7.31. Thats what makes this so frustrating. If the engine was getting a different query plan you should be able to play with update stats and optomizer directives to correct it. But in this case its getting the exact same query plan but taking ten-times as long to execute it. And this is occuring for numerous queries against several of our core tables. Queries that return smaller record sets don't suffer this problem as badly.
On 7 Apr 2006 07:49:05 -0700, "cgates" <cgates@adt.com> wrote: >The stange thing is the query plans look identical to when they are ran >on 7.31. Thats what makes this so frustrating. If the engine was >getting a different query plan you should be able to play with update >stats and optomizer directives to correct it. But in this case its >getting the exact same query plan but taking ten-times as long to >execute it. And this is occuring for numerous queries against several >of our core tables. Queries that return smaller record sets don't >suffer this problem as badly. Disk layout is the same? No changes to RAID levels? JWC
Database all lives in an IBM ESS. Four 1gig fiber cards, tera-byte of cache in the ESS. So the disk should still be as responsive as it was before. We never see any I/O wait. We did a full migration to perform the upgrade. But the fragmentation strategies are the same and tables now have only one or two extents compared to the dozens they had before. Index pages are a bit more dense, but that should help performance not hurt. Update stats low and high have been ran on all the tables.
On 7 Apr 2006 12:05:51 -0700, "cgates" <cgates@adt.com> wrote: >Database all lives in an IBM ESS. Four 1gig fiber cards, tera-byte of >cache in the ESS. So the disk should still be as responsive as it was >before. We never see any I/O wait. We did a full migration to perform >the upgrade. But the fragmentation strategies are the same and tables >now have only one or two extents compared to the dozens they had >before. Index pages are a bit more dense, but that should help >performance not hurt. Update stats low and high have been ran on all >the tables. And medium . . . . . . .. ?? JWC
Having just done the internals course...
If you have onmode -BC 2 done on the instance then all pages are held
in large chunk format in memory.
The on-disk version stays in the old format and hence has to be
converted by reading into an area (in the virtual portion??)
and then converted into the new format as it is copied into the buffer
pool. This can slow down scans..
Answer.... update 1 row per page to convert it to the new format since
after onmode -BC2 each page is WRITTEN in the
new format...
I now have enough knowledge to right a tool to do this but it may be a
while before I complete this...!!!
When I write the tool to update one row page something similar can be
use to complete
outstanding in-place alters prior to an upgrade. This is because
updating one row on a page causes
the whole page to be converted to the latest schema version...
I know people have been asking how to complete in-place alters prior to
an upgrade...
the nice thing is that this tool would generate sql to do
update table set <1st col> = <1st col>
- where rowid = N for non-fragmented tables or fragmented tables
with rowids
- where unique index cols = (X,Y,Z)
- where (index cols) = (X,Y,Z) for table with no unique index but
using the most unique index from optimizer stats.
This could update multiple rows but not many.
1/ The scan to generate sql would need to run with the engine paused
(onmode -c block) perhaps??
2/ The updates do not change anything so are safe and can be checked
before hand anyway.
3/ The updates can be run online since they are just sql and update
single rows at a time ( a commit -interval will be provided).
David.