Very Computer

I seem to remember reading something a week or so ago about some
strange performance problems in Online 7.1x select. Supposedly,
the problems were fixed in subsequent releases. Maybe so, but it looks
like we found another problem. So far, Informix Tech Support is stumped
(even though the problem is easily and dependably reproduceable).

This is the problem:

We have INFORMIX-Online Dynamic Server 7.11.UC1 running on
HP-UX v.10.01.

table_x (30 columns, 22000 rows), index on (column1,field2,field3)

SELECT * FROM table_x ORDER BY 1,2,3 (works fine)

SELECT * FROM table_x ORDER BY 1 (works fine)

SELECT * FROM table_x ORDER BY 3 (*EXTREMELY* long time)

SELECT * FROM table_x ORDER BY 15 (*EXTREMELY* long time)

SELECT column1,column, column3 ORDER BY 3 (works fine)

What do I mean by "*EXTREMELY* long time? On a dedicated 4-processor
HP K400, the query completes in approximately 10 minutes!

Even wierder: Move these queries into a distributed environment and
the *EXTREMELY* long query completely shuts down the TCP/IP pipe
between two systems -- Users can rlogin to the database server...

Has anyone seen anything like this, and most importantly, does anyone
have any suggestions?

OBComment: We already took aspirin.

--

o - o o     AEC NYC ARES, New York County
o -         Deputy Radio Operator, NYC RACES, New York County
- o - -     Start --> http://www.panix.com/clay

<<<< Stuff deleted >>>>

  Clay,
    My experience, too. When you use the last part of a concatenated
  key, i.e. 3 of index(1,2,3), the performance is a doggie.
  So things hum when you use all three, or the first two, or the
  first one, and I suspect you're getting good results when you
  select on all three columns for the same reason, but to get a
  clear picture of what's happening,

     set explain on;
     select .... , etc.

  then read the explain.out diagnostic file in the current directory.

  Off the top of my head, y'all forgive me for shooting my bandwith
  off, a kluge might be
    select x
      from your_table
    where col1 is not null  (if it's key it can't be, but so what)
      and col2 is not null
      and col3 = something;

  Anyway, don't give up. There's usually a way.

  Yours,
    Nick

  expedient was

Quote:

>   Clay,
>     My experience, too. When you use the last part of a concatenated
>   key, i.e. 3 of index(1,2,3), the performance is a doggie.

There's a performance dog, but a select that takes over ten
minutes on a dedicated 4-processor HP with 512MB of memory and
completely shuts down the TCP pipe is not what I call a dog --
I call it "broken". :)

Quote:>   So things hum when you use all three, or the first two, or the
>   first one, and I suspect you're getting good results when you
>   select on all three columns for the same reason, but to get a
>   clear picture of what's happening,

>      set explain on;
>      select .... , etc.

>   then read the explain.out diagnostic file in the current directory.

We looked at this. The select that kills system is a sequential
scan, and I believe a temp table is created, but still -- 10 minutes?

Quote:>   Off the top of my head, y'all forgive me for shooting my bandwith
>   off, a kluge might be
>     select x
>       from your_table
>     where col1 is not null  (if it's key it can't be, but so what)
>       and col2 is not null
>       and col3 = something;

>   Anyway, don't give up. There's usually a way.

I appreciate your comments. I'll give this select a try and see
what happens. Thanks.

--
Clay Irving, N2VKG

http://www.panix.com/clay

For those two no index is being used :

  SELECT * FROM table_x ORDER BY 3 (*EXTREMELY* long time)

  SELECT * FROM table_x ORDER BY 15 (*EXTREMELY* long time)

Temp table has to be created for the order by , run the set explain.
When doing an order by on a large selection , there should be
an index coresponding for that order by.

The only reason this one works well is that you selecting the
3 index columns , so only the index is being read.

SELECT column1,column, column3 ORDER BY 3 (works fine)

Mariusz Malogrosz

: I seem to remember reading something a week or so ago about some
: strange performance problems in Online 7.1x select. Supposedly,
: the problems were fixed in subsequent releases. Maybe so, but it looks
: like we found another problem. So far, Informix Tech Support is stumped
: (even though the problem is easily and dependably reproduceable).

: This is the problem:

: We have INFORMIX-Online Dynamic Server 7.11.UC1 running on
: HP-UX v.10.01.

: table_x (30 columns, 22000 rows), index on (column1,field2,field3)

: SELECT * FROM table_x ORDER BY 1,2,3 (works fine)

: SELECT * FROM table_x ORDER BY 1 (works fine)

: SELECT * FROM table_x ORDER BY 3 (*EXTREMELY* long time)

: SELECT * FROM table_x ORDER BY 15 (*EXTREMELY* long time)

: SELECT column1,column, column3 ORDER BY 3 (works fine)

: What do I mean by "*EXTREMELY* long time? On a dedicated 4-processor
: HP K400, the query completes in approximately 10 minutes!

: Even wierder: Move these queries into a distributed environment and
: the *EXTREMELY* long query completely shuts down the TCP/IP pipe
: between two systems -- Users can rlogin to the database server...

: Has anyone seen anything like this, and most importantly, does anyone
: have any suggestions?

: OBComment: We already took aspirin.

: --

: o - o o     AEC NYC ARES, New York County
: o -         Deputy Radio Operator, NYC RACES, New York County
: - o - -     Start --> http://www.panix.com/clay

Quote:Clay Irving writes:

|> This is the problem:
|>
|> We have INFORMIX-Online Dynamic Server 7.11.UC1 running on
|> HP-UX v.10.01.
|> table_x (30 columns, 22000 rows), index on (column1,field2,field3)
|> SELECT * FROM table_x ORDER BY 1,2,3 (works fine)
|> SELECT * FROM table_x ORDER BY 1 (works fine)
This is perfectly reasonable, as the query can do an indexed read for both
of these in order to return the ordered set.  More importantly, it does not
have to wait until everything is read before returning the first row.  Did
you time it to the last row or to the first row returned??

|> SELECT * FROM table_x ORDER BY 3 (*EXTREMELY* long time)
|> SELECT * FROM table_x ORDER BY 15 (*EXTREMELY* long time)
It is certainly reasonable that this would take longer, as it can no longer
use the index to do the ordering, so it must read the entire table
sequentially, and sort all the resulting data, and can't return the first
row until the entire set has been sorted.  Whether or not 10 minutes is
reasonable for this is another matter, which gets into questions of tuning
(most importantly, how much memory is available in the virtual segments,
where sorting is done, versus the resident segment, where raw data is simply
cached).

|> SELECT column1,column, column3 ORDER BY 3 (works fine)
Here you would be doing a key-only select, so again, the index provides your
speedup.  

You might improve the performance of the two poor performers by reducing
the number columns selected (which means more tuples can fit into the
sort buffers).

|> What do I mean by "*EXTREMELY* long time? On a dedicated 4-processor
|> HP K400, the query completes in approximately 10 minutes!
This may well be understandable if you have a limited virtual memory in
online, which would force the sort to be done in runs that have to be written
out to disk. 22000 rows of 30 columns (how many bytes?) can chew up a
whole lot of memory.

|> Even wierder: Move these queries into a distributed environment and
|> the *EXTREMELY* long query completely shuts down the TCP/IP pipe
|> between two systems -- Users can rlogin to the database server...

That, on the other hand, sounds like a bug.  

Dave Kosenko
Disclaimer: All opinions expressed in this message are well-reasoned and
insightful; needless to say, they are not those of Informix Software, its
partners or lackeys.  Anyone who says otherwise is itching for a fight.
****************************************************************************
"I look back with some satisfaction on what an idiot I was when I was 25,
 but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney

Have you executed each query with the set explain on option?  What does
the explain results say is happening?

I suspect that the first two queries are using the index and that last
two are doing full table scans?

Have you run onperf to see if you have high cpu usage and/or IO bound
to a single disk?  Is the table fragmented?  Is the PDQ priority set to
allow parallel scans?

Good luck...

Quote:Clay Irving writes:

|> Those two queries taking forever to run are not using the index, and
|> a temp table is built for the sequential scan. <snip>

Glad you got your problem solved.  Just wanted to point out that a temp
table is never built for sequential scans.  What I think you mean is that
temp space is used for the ORDER BY, since no index was available to do
the ordering.  Also note that the temp space is not for a TEMP TABLE per se;
rather it is used for writing out sort runs (the server will attempt to do
as much of the sort as possible in memory, but when memory overflows it will
sort as much as it can  in memory and write the temporary results out to disk.
When all the source data has been sorted, those temporary files are read and
merged, and the results passed back to the application.), so is really a
TEMP *FILE*.  The difference is really a technical nit, but I think it worth
pointing out.

Also note that you might speed things up further if you had multiple temp
dbspaces located on different disks, then  listed them all in DBSPACETEMP
in your config file.  This would allow temp i/o to be done in parallel when
doing the sorting.

Dave Kosenko
Disclaimer: All opinions expressed in this message are well-reasoned and
insightful; needless to say, they are not those of Informix Software, its
partners or lackeys.  Anyone who says otherwise is itching for a fight.
****************************************************************************
"I look back with some satisfaction on what an idiot I was when I was 25,
 but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney