Performance problems with large SELECTS in 7.3x ? 
Author Message
 Performance problems with large SELECTS in 7.3x ?


> 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

Wed, 18 Jun 1902 08:00:00 GMT
 [ 1 post ] 

 Relevant Pages 

1. Performance problems with large SELECTS in 7.3x ?

2. Performance of select for a large table..Urgent

3. Performance Problem with Large Tables

4. Critical performance problems on large databases

5. Critical performance problems on large databases

6. Performance problem with large insert statements

7. Performance problems with JDBC & large result sets

8. Performance problems with large table

9. ODBC/Large column performance problem

10. performance problems on large joins??

11. Performance problems when large jobs are run

Powered by phpBB® Forum Software