IDS Forum

Re: IDS 11.5 - Mystery improvement in performance

Posted By: Art Kagel
Date: Tuesday, 12 April 2016, at 8:46 a.m.

In Response To: IDS 11.5 - Mystery improvement in performance (JOHN MURTARI)

The only thing I can think of is that the additional rows caused the
optimizer to change its query plan. However, I would not expect that to
happen unless update statistics were run to rebuild the data distributions
that control the optimizer and you say that did not happen. If you happen
to have a query plan (SET EXPLAIN output) from before last night you could
run the improved query under SET EXPLAIN now to see the difference if any.

There are no automated structure changes that the server makes to your
tables and the only changes that are applied to indexes is when the BTREE
Cleaner threads merge index pages when two adjacent pages are both more
than 50% empty. Inserts will split index nodes in half if they fill up.
Large numbers of inserts would tend to split many index node pages that are
nearly full (oddly known as compression which is a different compression
than "index compression" available in v12.10) which might cause three
nearly full pages to be combined into two full pages after splitting and
compressing once the BTREE Cleaner threads woke. But I'm thinking that over
all even this process should have resulted in an index that had about the
same percentage of unused node slots as before the insert run.

So, over all, I have no clue what happened.

Art

Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com

Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on 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 Tue, Apr 12, 2016 at 7:31 AM, JOHN MURTARI <jm5903@att.com> wrote:

> Folks,
>
> Overnight we saw a dramatic improvement in query performance against one
> large
> table (>400,000 rows). We'd been working on tuning prior to this and had an
> application instrumented to record query times. Within a period of about
> two
> minutes the execution time dropped from 4 seconds to 2 seconds in the
> middle
> of the night. The query does a select on an indexed column followed by an
> 'ORDER BY' which currently returns over 90,000 rows.
>
> We thanks our lucky stars that we'd been visited by the IDS Performance
> Fairy
> overnight!!! Glad to see IBM still allows her to visit older releases!
>
> We had a meeting and confirmed no operator actions had been taken and no
> CRON
> activity was scheduled in that time frame. We'd really like to know what
> special "Fairy Dust" was sprinkled on the server so we can use it on some
> other problems.
>
> Our current best explanation is this. As part of other work we did have an
> application continually inserting new rows in the table (about 1000/hour)
> and
> which would also result in more query results to be sorted by the 'ORDER
> BY' I
> mention above. We think the table crossed a data/index size threshold (only
> has one primary index based on two columns) and Informix changed the
> layout on
> disk as it crossed some threshold.
>
> Just looking for some confirmation that internal IDS actions could have
> caused
> this. What config parameters would we check for when the change was
> triggered?
> Thanks for any help!
> John
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--089e0139fddc2849ba0530490d0f

Messages In This Thread

• IDS 11.5 - Mystery improvement in performance
  JOHN MURTARI -- Tuesday, 12 April 2016, at 7:31 a.m.
  • Re: IDS 11.5 - Mystery improvement in performance
    Fernando Nunes -- Tuesday, 12 April 2016, at 7:40 a.m.
  • Re: IDS 11.5 - Mystery improvement in performance
    Art Kagel -- Tuesday, 12 April 2016, at 8:46 a.m.
  • Re: IDS 11.5 - Mystery improvement in performance
    JOHN MURTARI -- Tuesday, 12 April 2016, at 1:15 p.m.
    • Re: IDS 11.5 - Mystery improvement in performance
      Madison Pruet -- Tuesday, 12 April 2016, at 1:27 p.m.
    • Re: IDS 11.5 - Mystery improvement in performance
      Fernando Nunes -- Tuesday, 12 April 2016, at 7:06 p.m.
      • Re: IDS 11.5 - Mystery improvement in performance
        MICHAEL HOFFMAN -- Tuesday, 19 April 2016, at 2:52 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.