7.30 -> 9.20 Anyone remember this?
A user migrated a database from Informix 7.30.UC2 to 9.20.UC2 (same schema, same ONCONFIG apart from instance-specific settings) and found a large join query ran at ~200 rows/sec instead of ~3000. UPDATE STATISTICS, including high mode, didn't help. Suggestions: compare SET EXPLAIN plans and use optimizer directives to force the 7.30 path, and check onstat -p CPU stats to distinguish an optimizer issue from configuration/disk problems. Another poster reported the same symptom (an unexpected sequential scan) and said setting OPT_GOAL to -1 fixed it. The original poster didn't confirm back.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hey all, I have an existing 7.30.UC2 database where I run a large join query (building a big fact table). I installed 9.20.UC2 into another different INFORMIXDIR and dumped the schema from the 7.30 instance and loaded up into the 9.20 instance for all the tables. I then setup connectivity between the instances and copied the data over with select's. The problem is when I run the same large join query in the 9.20 instance performance is terrible. 200 rows per second vs. 3000 rows a second for the 7.30 instance. Awhile back I had a similar problem that was fixed by relinking some of the files in /usr/lib. The problem with that is the 9.20 files have a 9 in the name and the 7.30 instance files have a 7 in the name. So I'm sure the 9.20 instance is not looking at the wrong files. Anyone remember this or encounter this before? /INFORMIXTMP come into play anywhere? I'm quite sure it's not any of the ONCONFIG settings or indexes, etc. Those are EXACTLY the same with the exception of course of the unique parameters... Thanks, Dave Sent via Deja.com http://www.deja.com/ Before you buy.
dmoeller@geocities.com wrote:
>
> Hey all,
>
> I have an existing 7.30.UC2 database where I run a large join query
> (building a big fact table). I installed 9.20.UC2 into another
> different INFORMIXDIR and dumped the schema from the 7.30 instance and
> loaded up into the 9.20 instance for all the tables.
>
> I then setup connectivity between the instances and copied the data over
> with select's.
>
> The problem is when I run the same large join query in the 9.20
> instance performance is terrible. 200 rows per second vs. 3000 rows a
> second for the 7.30 instance.
>
> Awhile back I had a similar problem that was fixed by relinking some of
> the files in /usr/lib. The problem with that is the 9.20 files have a 9
> in the name and the 7.30 instance files have a 7 in the name. So I'm
> sure the 9.20 instance is not looking at the wrong files.
>
> Anyone remember this or encounter this before? /INFORMIXTMP come into
> play anywhere?
>
> I'm quite sure it's not any of the ONCONFIG settings or indexes, etc.
> Those are EXACTLY the same with the exception of course of the unique
> parameters...
>
Update statistics for the 9.20 instance??
--
John Carlson
Informix DBA
WHSmith USA
#include std_disclaimer.h /* These are my opinions, not my company's
opinion */
In article <3933FB47.36FFAC44@bellsouth.net>,
"Carlson@WHSmith" <carlson1@bellsouth.net> wrote:
> dmoeller@geocities.com wrote:
> >
> > Hey all,
> >
> > I have an existing 7.30.UC2 database where I run a large join query
> > (building a big fact table). I installed 9.20.UC2 into another
> > different INFORMIXDIR and dumped the schema from the 7.30 instance
and
> > loaded up into the 9.20 instance for all the tables.
> >
> > I then setup connectivity between the instances and copied the data
over
> > with select's.
> >
> > The problem is when I run the same large join query in the 9.20
> > instance performance is terrible. 200 rows per second vs. 3000 rows
a
> > second for the 7.30 instance.
> >
> > Awhile back I had a similar problem that was fixed by relinking some
of
> > the files in /usr/lib. The problem with that is the 9.20 files have
a 9
> > in the name and the 7.30 instance files have a 7 in the name. So
I'm
> > sure the 9.20 instance is not looking at the wrong files.
> >
> > Anyone remember this or encounter this before? /INFORMIXTMP come
into
> > play anywhere?
> >
> > I'm quite sure it's not any of the ONCONFIG settings or indexes,
etc.
> > Those are EXACTLY the same with the exception of course of the
unique
> > parameters...
> >
>
> Update statistics for the 9.20 instance??
Yeah a couple thousand variations of the same...
Even tried an Update stats high on the big table which ran for a few
hours.
>
> --
> John Carlson
> Informix DBA
> WHSmith USA
>
> #include std_disclaimer.h /* These are my opinions, not my
company's
> opinion */
>
Sent via Deja.com http://www.deja.com/
Before you buy.
I've been testing performance of v9.20 against that of v7.31, with both
versions running on the same machine, though not concurrently. In general,
performance of v9.20 is better than or equal to that of v7.31.
However, my testing is targeted at OLTP operations. Maybe, the optimizer in
v9.20 is working differently, using an inefficient path to execute your DSS
query.
Check if the "explained" method for executing the query differs. If it
does, use directives to force v9.20 to take the v7.30 path. If performance
is now comparable, the Optimizer is to "blame".
If the Optimizer is behaving, check "onstat -p" stats after running the
query on either instance (make sure you have initialized stats [ onstat -z
] first). If usercpu is vastly different, then there is some problem with
the v9.20 Instance configuration (BUFFERS, possibly).
If usercpu on the 2 instances are approximately the same, then something at
the OS level is inhibiting your v9.20 instance (disks, most likely).
Let us know what you find.
Rudy
dmoeller@geocities.com wrote:
> Hey all,
>
> I have an existing 7.30.UC2 database where I run a large join query
> (building a big fact table). I installed 9.20.UC2 into another
> different INFORMIXDIR and dumped the schema from the 7.30 instance and
> loaded up into the 9.20 instance for all the tables.
>
> I then setup connectivity between the instances and copied the data over
> with select's.
>
> The problem is when I run the same large join query in the 9.20
> instance performance is terrible. 200 rows per second vs. 3000 rows a
> second for the 7.30 instance.
>
> ...Thanks,
>
> Dave
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Yep, just went through the same thing. If you check your query statistics
you will find that the query does a sequential scan.
Optimizer settings fixed it:
OPT_GOAL -1
Richard Lewis
Gerling Global Group of Australia Pty Ltd
<dmoeller@geocities.com> wrote in message
news:8h0qtv$ggs$1@nnrp1.deja.com...
> Hey all,
>
> I have an existing 7.30.UC2 database where I run a large join query
> (building a big fact table). I installed 9.20.UC2 into another
> different INFORMIXDIR and dumped the schema from the 7.30 instance and
> loaded up into the 9.20 instance for all the tables.
>
> I then setup connectivity between the instances and copied the data over
> with select's.
>
> The problem is when I run the same large join query in the 9.20
> instance performance is terrible. 200 rows per second vs. 3000 rows a
> second for the 7.30 instance.
>
> Awhile back I had a similar problem that was fixed by relinking some of
> the files in /usr/lib. The problem with that is the 9.20 files have a 9
> in the name and the 7.30 instance files have a 7 in the name. So I'm
> sure the 9.20 instance is not looking at the wrong files.
>
> Anyone remember this or encounter this before? /INFORMIXTMP come into
> play anywhere?
>
> I'm quite sure it's not any of the ONCONFIG settings or indexes, etc.
> Those are EXACTLY the same with the exception of course of the unique
> parameters...
>
> Thanks,
>
> Dave
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.