IDS 11.5 - Mystery improvement in performance
A user reported that an ORDER BY query on a ~400,000-row table on IDS 11.5 suddenly dropped from 4 seconds to 2 seconds overnight on a quiet lab server, with no operator action, cron jobs, update statistics or backups, and asked what internal server activity could explain it. Suggestions included btree cleaner/index page merging, new extents on faster disks, an optimizer plan change as row counts grew, a cheaper sort, and network/connection contention being lower at weekends. The poster noted dropping and recreating the index had made no difference, which argued against the btree cleaner, and no definite cause was identified in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
The only "fairy" I can think of is the btree cleaner/scanner. But I highly doubt this could have caused what you describe. I don't believe you'll be able to find out what happened if what you have is what you described. Other things that could hapenn.... new extents were allocated in better performing dbspaces/disks? A backup has finished? Some other CPU consuming, or heavy disk usage has finished. Do you have auto update statistics running? ... On Tue, Apr 12, 2016 at 12:31 PM, 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. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f2d2ad0c6e80530481e6c
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
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.
Maybe the order by and the new row inserts coordinated such that the sort required by the order by became a more trivial sort. Madison Pruet Retired and Loving it On Tuesday, April 12, 2016 12:16 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.
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
The timing of the "miracle" raised one flag for me, and it was sort of addressed in the question about other processes, but Saturday evening would suggest a period of minimal usage on your servers and networks. Are you using shared memory or TCP connections for these processes? If TCP, then each call to the database will utilize your network and compete with other traffic. Saturday night, the lines are empty and no competition, so faster communication. I bring this up only because we recently had issues with slowness that traced directly back to our remote connections and how Informix was handling them (CPU v NET VPs) and overall traffic on our networks. Maybe this helps.... Michael Hoffman