Save 
Join IIUG
 for   
 

Informix News
29 July 09 - Wall Street Journal - IBM to Acquire SPSS, Adding to Acquisitions... Read
11 February 09 - InformationWeek - IBM Drifts Slowly Toward Mainstream Cloud Computing... Read
11 February 09 - CNNMoney.com - IBM to Deliver Software via Cloud Computing With Amazon Web Services... Read
07 January 09 - Huliq News - IBM Power Servers Helps Bank of Chengdu Build Up Core Banking System.. Read
04 December 08 - Steeleye - Multicarta achieves 99.997% uptime for its Informix-based Credit Card Authorization Centre... Read
16 October 08 - VendorRate - Informix Earns Top Customer Satisfaction Score on VendorRate in Q3... Read
14 August 08 - IIUG.org - Sellout Expected for the 2009 IIUG Informix Conference... Read
29 April 08 - IntelligentEnterprise.com - IBM Informix Upgrade Enhances Clustering, Database Management... Read
29 April 08 - itweek.com - IBM 'Cheetah 2' mauls data costs... Read
28 April 08 - eWeek.com - IBM Uncages Cheetah 2 Data Server... Read
28 April 08 - CNNMoney.com - IBM Helps Clients Reduce Data Management Costs With New Informix Dynamic Server... Read
09 April 08 - CNNMoney.com - MediaSpan Embeds IBM Informix Dynamic Server Software for Delivering News to Print, Web and Wireless Devices... Read
08 April 08 - IT-Director.com - Informix seeks developers... Read
18 February 08 - marketwire.com - Icarus Studios Partners With IBM to Upgrade Performance, Availability for Its Online Games... Read
17 January 08 - eWeek.com - IBM Adds Mac Support to IDS for Higher Education... Read
17 January 08 - informationweek.com - Lotus Notes For iPhone Signals Closer Ties Between IBM, Apple... Read
16 January 08 - marketwire.com - IBM Informix Dynamic Server to Deliver Support for Mac OS X... Read
16 January 08 - internetnews.com - IBM's IDS to Support Mac Platform... Read
28 June 07 - REG Developer - IBM and Informix tie down Cheetah... Read
27 June 07 - CBRonline.com - IBM corrects its own Informix customer figures... Read
14 June 07 - vnunet.com - IBM changes spots with Informix 'Cheetah' database... Read
14 June 07 - eChannelLine - IBM expands scope for IDS... Read
14 June 07 - Resellernews - IBM: Informix database alive and kicking... Read
13 June 07 - DB2 Magazine - Cheetah is now out of the gate... Read
12 June 07 - IBM - IDS 11 release announcement (pdf)... Read
12 June 07 - ChannelWeb Network - IBM Uncages IDS 11, Aka Cheetah, Database... Read
12 June 07 - eWeek.com - IBM's 'Cheetah' Ready to Pounce... Read
12 June 07 - InformationWeek - IBM Unleashes 'Cheetah' Database... Read
12 June 07 - WebWire - IBM Strengthens Database Portfolio With New Informix Dynamic Server... Read
12 June 07 - Intelligent Enterprise - IBM Unveils Informix Upgrade... Read
12 June 07 - ComputerWeekly.com - IBM's Cheetah IDS makes leap to better data centre clustering... Read
12 June 07 - ebiz - IBM Unveils Next Generation Informix Dynamic Server... Read
12 June 07 - computerworld.com - Will 'Cheetah' help IBM's Informix chase down market share?... Read
12 June 07 - Internetnews.com - No Data Can Outrun This 'Cheetah'... Read
12 June 07 - de.internet.com - IBM neuer Datenbank-Server mit Codenamen Cheetah ist fertig... Read
12 June 07 - verifox.de - IBM stärkt Datenbank-Portfolio mit neuem Informix Dynamic Server... Read
12 June 07 - golem.de - Informix 11 vorgestellt... Read
12 June 07 - Computerwoche.de - IBM stellt neue Informix-Version vor... Read
12 June 07 - IBM.de - IBM stärkt Datenbank-Portfolio mit neuem Informix Dynamic Server... Read
12 June 07 - Heise - IBM gibt Informix 11 frei... Read
25 May 07 - Taiwan.CNET.com - Local Taiwan Informix user group established... (Chinese language) ... Read
18 May 07 - ChannelWeb Network - IBM Musters Partners For Cheetah Release... Read
18 May 07 - eWeek.com - IBM Looks to 'Cheetah' to Speed Up Blade Servers... Read
7 May 07 - DB2 Magazine - SQL Shortcuts - Use these tricks to generate IDS SQL scripts... Read




End of Support Dates
IDS 7.31 - 30 Sep 2009
IDS 9.40 - 30 Apr 2009
IDS 10.00 - 30 Sep 2010
Subscribe to notifications

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum

Re: Question about sysindexes.clust

Posted By: Eric Rowell
Date: Tuesday, 18 November 2008, at 12:45 p.m.

In Response To: Re: Question about sysindexes.clust (Obnoxio The Clown)

Wish it was that easy... Both tried and checked...

Update stats has been run many times with no change to runtime.
Rebuilding the index then running update stats does nothing to change the
runtime or the "clust" values.

What it appears to be is that the physical data in the table is in such
a different order then the index (many years/inserts/deletes and at least
one reload since the table was built) that it is slowing everything down.

When the data is sorted by (or clustered) by the table the performance is
great. The only things that change are the number of pgreads and the
"clust" value goes down .

Note:

Current Run Time after update stats: 5hr 47min
Total Rows in the table: 69234768
sysindex.clust values for indexes:
ix_pn_sl 43574071
ix_sk 18992828
ix_sl 24324296
ix_vi_ed_sl 29010585

Run time after reload/index/update stats of table: 9hr 54m
Total Rows in the table: 69234768 sysindex.clust values for indexes:
ix_pn_sl 62861388
ix_sk 47732317
ix_sl 54720284
ix_vi_ed_sl 56854170

Run time after sorted reload/index/update stats of table: 4hr 45m
Total Rows in the table: 69234768 sysindex.clust values for indexes:
ix_pn_sl 16681107
ix_sk 3867459
ix_sl 2695472
ix_vi_ed_sl 6089056

So again does anyone watch this value to know about the health of a table?
I don't think I ever have.

On Tue, Nov 18, 2008 at 12:22 PM, Obnoxio The Clown <obnoxio@serendipita.com
> wrote:

> Eric Rowell wrote:
> > To the very wise watchers of this group. I have a question which I would
> > like your input.
> >
> > Background:
> > Currently we are running IDS 10.00.FC8 on AIX 5.3 TL6 on an IBM P570
> > (6-way) using an IBM DS4500 SAN.
> >
> > We have seen some slowdowns within the system and have had a heck of a
> time
> > pairing down to what has changed and is causing the problem.
> >
> > The main table we are currently working with is fragmented and has
> approx.
> > 69million rows. While we are testing index/table rebuilds we have noted
> > that our performance is dropping through the floor and the only values
> that
> > change are the sysindexes.clust values for the indexes, pgreads for the
> > table, and the over all run time. With some testing we have found a
> > fragmentation schema which appears to work but...
> >
> > I haven't found much on performance monitoring and expectations of the
> > sysindexes.clust value. How many of you great DBA's, DBSA's, and Informix
> > Guru's use the sysindexes.clust to determine when it is time to rebuild a
> > table and if the rebuild was in the best form?
>
> Personally, I've no idea why you think this should be an issue. I'd
> first ask the obvious "have you run UPDATE STATISTICS" and then I'd say
> "B-Tree cleaner".
>
> --
> Cheers,
> Obnoxio The Clown
>
> http://obotheclown.blogspot.com
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Eric B. Rowell

Messages In This Thread

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum is maintained by Administrator with WebBBS 5.12.