Is anybody using IDS on HP-UX Itanium, particularly with EMC Clariion?

Would ove to swap stories and compare notes. Please send me a private.

thx
N

It's IDS 10.0FC10W2 on HP-UX 11.23 ia64.

Here's one of the issues I'm looking at: long-running UPDATE STATS.

This index for example takes ages. It has only about 600,000 pages:
cust_id, login_time(LOW)...SUCCESSFUL (3156.161 Secs)

If I run it again immediately, ie without bouncing Informix:
cust_id, login_time(LOW)...SUCCESSFUL (10.322 Secs) # 300 times
faster!!!!!

I can see from onstat -D that the second run did no page reads so this is
clearly due to the IDS cache: the time zooms straight back up to 3,200-odd
seconds if IDS is bounced.

If i unload the table and create a copy:
cust_id, login_time(LOW)...SUCCESSFUL (88.597 Secs)

The index on the original is not *that* fragmented - takes up about 400,000
pages after rebuild as opposed to 600,000 before ...
If I watch the UPDATE STATS I see it crawl on the original table but fly on
the new one.

I should add that for ages I've suspected a disk problem this platform.
But if this is a disk, rather than Informix, problem it's one that allows an
index scan on a recently-built index to fly, and an older one to crawl.

Any thoughts?

Thanks, Neil

The index may not be fragmented, but perhaps the table is!

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

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 Mon, Mar 8, 2010 at 6:37 PM, Neil Truby <neil.truby (AT) ardenta (DOT) com> wrote:

"Art Kagel" <art.kagel (AT) gmail (DOT) com> wrote

The index may not be fragmented, but perhaps the table is!

It doesn;t appear to be, at least not from the oncheck:

Before:
Number of pages used 483601
Number of data pages 483481
Number of pages allocated 221507

After:
Number of pages allocated 484032
Number of pages used 483602
Number of data pages 483482

In any case wouldn;t an UPDATE STATS (LOW) (as executed by dostats of
course!) simply scan the indexes.

I copied the table to another db using onunload/onload, sort of expecting
this too to speed everything up, but in fact it retains the original speed
.....

Hello Neil,

i would try and rule out HP; try it on linux and see if you get the
same results.
NO FILESYSTEM please this may spoil your measurement due to
filesystemcache!!!

On hp i would monitor i/o since you bounce and slow....
sounds like that it is doing one page read a time or something.
sar/iostat may give some light.
When it is reading 1 page at a time (iostat/sar may tell this!!)
the number of requests on a device may be as low as 200

600000/200=3000 secs which comes close to your observation.
When that is the case you could open a case @ ibms and request that
they
make update stats low faster somehow....

How big is your buffer cache;
i would rule bits and pieces out there too; eq decrease buffer cache
so the whole index does not fit in there hmm make it real small say
1000 pages.
then repeat the test; you'll probably get the same results...

If raw and kaio then i would try and switch it off then check using
top or???
maybe you will see a 100% waiting on i/o????.

Superboer

On 9 mrt, 01:51, "Neil Truby" <neil.tr... (AT) ardenta (DOT) com> wrote:

"Superboer" <superboer7 (AT) t-online (DOT) de> wrote

Hello Neil,

Yes, it's the same on Solaris.

In fact, it happens on any composite index where the 2nd or subsequent
column is a DATETIME column. It's not only UPDATE STATISTICS, oncheck -pT
is equally impaired for example.

IBM is heavily involved now, PMR 83917,019,866 refers.

"Neil Truby" <neil.truby (AT) ardenta (DOT) com> wrote

The advice on the PMR is that it is to likely to with a heavy imbalance in
the index B-tree.
This still seems to us like an astonishing impact so we're running some more
tests to try to understand it better.

Can anyone explain why we run update statistics (low) on an index rather
than on the individual columns?

Check John Miller presentation.
UPDATE STATS LOW will collect some information that only makes sense for
indexed columns:

- 2nd greater ans smaller value (syscolumns)
- clustered info (sysindexes)
- number of leaves (sysindexes)
- number of levels (sysindexes - apparently you have a very "tall" index)

I may be missing somethings but documentation and the presentation have more
information.
All this info is used by the optimizer.

Regarding the astonishing impact I really understand your surprise. But in a
recent thread regarding other subject I was also surprised by the effect of
the hardware cache (which is what you're sensing here from my perspective).
the index rebuild). You can assert this by running the query I sent. It will
take a long time (similar to the process itself and the oncheck -pT).
Note that it should not affect the index usage (maybe more if you do big
range scans, but that would lead us to the previous thread I mentioned)

Regards.


On Tue, Mar 16, 2010 at 5:08 PM, Neil Truby <neil.truby (AT) ardenta (DOT) com> wrote:



--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

"Fernando Nunes" <domusonline (AT) gmail (DOT) com> wrote

Query? Is that one you send to Claudio?

cheers

No... The one I included in my email to the list on March 14 (Sunday)
Regards.

On Tue, Mar 16, 2010 at 6:38 PM, Neil Truby <neil.truby (AT) ardenta (DOT) com> wrote:



--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...