IDS Forum

Re: IDS 11.5 - Mystery improvement in performance

Posted By: Fernando Nunes
Date: Tuesday, 12 April 2016, at 7:06 p.m.

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

If you had recreated the index and saw no significant performance
improvement, that proves the Btree cleaner could not have caused the
"miracle".
The following won't help you, as it is more like a "politic" position, but
I honestly think we in IT spend too much time and effort in things like
"finding out what changed".
Many times it looks as the shortest path, but in most cases it serves only
to distract us...

If you have a slow query or process, you have work to do and benefits to
gain once you understand what's holding it back. ..
Finding out what's wrong with the process, the data model, the database or
the system may well help you understand what happened on that "good" period.

Now that I have preached, a couple more ideas... but they'd be acceptable
only if the performance is still good...

1- Indexes on timestamps can be tricky... and as time passes by and
assuming statistics are stalled the optimizer may change it's options. The
situation I see frequently is of a table with and index on customer_number
(or similar field) and an index on a datetime value that typically if
filled with "CURRENT...". If a query includes filters on both fields,
typically it chooses the customer_num. But when we update statistics in
moment T0, we're telling the optimizer that we don't have records with
datetime_column > T0.
If the query is repeated during T0 through T0 + N, the bigger the N, the
most probable it is for the optimizer to choose this index. In the cases I
usually see that's not a good thing, but in different situations this could
eventually help

2- On a query that joins a local and a remote table the optimizer must
choose if it reads the local table and does a nested loop, by sending the
same query with different values to the other side, or if it decides to
bring the whole remote table to the local engine and then does whatever
it's best. The decision depends on the size of the tables... as they grow
the decision may change. But this in theory would require new statistics

Regards

On Tue, Apr 12, 2016 at 6:15 PM, JOHN MURTARI <jm5903@att.com> wrote:

> Thanks for the responses so far. To answer some questions:
>
> There weren't any other processes active on the server. This is a lab test
> server with limited access. The only process running for the last week was
> a
> test script that added about 500-1000 rows/hour to the DB -- this was part
> of
> our tuning effort.
>
> We did not run any statistics update, backups, etc.. near that time. The
> sudden change was logged on a Saturday evening and no one was on the
> server.
> It was never as fast inserting data as it got after the "Informix Fairy"
> visited.
>
> I like the answer about the index cleaner thread optimizing the B-trees and
> we'll explore that further. During this long duration insert run, we had at
> times stopped the inserts, dropped and recreated the index -- but hadn't
> seen
> any noticeable performance change.
>
> Any other thoughts are welcome and I'll certainly post if we find a
> definite
> cause.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Fernando Nunes
Portugal

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

--001a113ee868ad7794053051b64f

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.