Re: Performance problems with large SELECTS in 7.3x ?

Stefan Weideneder wrote: > > Hi, > > I tried to select some pages of a table that was a bit larger > than my buffer pool. I estimated that my statement will take > about 5 seconds but it took 12 seconds. > I found out, that the server spent approx. 7 seconds for > "buffer waits". Now I added a few buffers to the buffer pool > so that the buffer pool was a bit larger than my table and started > the Select twice. The 2nd Select took less than 1 second. > So I'm sure it's not the regular buffer access where I lost > my 7 seconds. I know that starting with version 7.3x Informix > is using 4 priority levels in the LRU-queues and in my opinion > this new feature costs a lot of time, because I always spent > the additional 7 seconds when the pages had to be loaded into > the buffer pool. > > Any comments ? > > I ran the test on a HP-UX 10.20 / IDS 7.30UC2. Is there anybody > who has made similiar experiences ? > > ( When I set LIGHT_SCANS=FORCE, the Select statement took > really 5 seconds. So I think my estimated duration time was correct ). The only problem with the new buffer priority scheme is that there are fewer buffers from a particular pool size available for ordinary data pages. This is not necessarily a bad thing since data pages are medium-low priority (system catalog pages and the like are LOW, index pages are MEDIUM-HIGH and resident table pages are HIGH) so the pages that take priority over them are index pages which have a high probability of being needed again and resident table pages, if any, which are critical or they would not be resident. In the current 7.30 code higher priority buffers COULD steal buffers from lower priority pages if needed and there is not good mechanism for the lower priority data getting those pages back. New priority aging code is in development but actually noone has reported any problems like this yet, despite my own fears. Anyway, if you are doing queries that are data-page intensive in 7.30 you may need to increase the size of the buffer cache to prevent buffer thrashing that would not have occurred in 7.2x because older index pages' buffers would have been recycled. In 7.30 data pages cannot force index buffers to be reused. You can turn off buffer priority by setting the environment variable NOLRUPRIO=1 in the environment when the engine is started up. Art S. Kagel