trying tro understand, when the optimizer select to use MRU or LRU strategies

[unknown]

Hi All,
In a select statement, is it a normal behavior of the
optimizer to select indexes pages of a non-clustered index
using 16K buffer pulls with a MRU stategy? why?
I understand very well the fetch of data using 16K buffer
pool and MRU strategy, but in the cases of indexes;
especially the non-clustered ones; it is not at all clear
why such a choice is made. If one select 16K of indexe pages
of a non-clustered indexes into the cache and get rid of
them by MRU strategy, this is not a mistake? to me it is, as
most probably some of the pages which are flashed out of
memory will be needed again and must be fetched again, this
means a considerable increase in the number of physical I/O,
no?
I would like to understand the mechanism behind such a
choice, the sybase documenation describe very well the two
mechanism but I do not see any explaination as to why one
strategy is chosen while one expect the other.
Why by forcing the stategy from MRU to LRU for the indexes;
one obtains a better response time?
All help and suggestions, comments are well appreciated.
By the way, our Sybase ASE server is version 12.5.03 and the
tables are huge(70M rows and more).
tartampion

[Derek Asirvadem]

T

Ok, you seem to have gotten your head around the Sybase cache strategy
(LRU/MRU issue); and you understand it; you agree with most of it; but
you disagree with the use of MRU for scanning NCI. Right ? That is
the way it is. The Sybase engineers came up with it a long time ago,
and it is tried and tested. There has been no effort from the user
base to change this. I (and I would think most of us) definitely do
NOT want NCI LRU scanned (or LRU set for NCI scans as the default), the
whole idea of MRU is to avoid a large table (or large index) icing a
warm or hot cache when it is scanned into the cache. Note the table
might be "huge" but the NCI is not. Note MRU is used for SCANS, where
a large portion of the table or index has to be read in.

The correct way to go about addressing your issue is (in order, to
minimise overall negative effect on the server, rather than focussing
on one object):

1 Create a data cache for some or all your NCIs. With a large 8k or
16k pool (I find 8k better than 16k as a general rule). This should be
the fourth standard cache anyway. Set the APF percent to 20 or
something reasonable.

2 If you do not get a substantial and immediate improvement in
performance, or you do not like that idea, then change the cache
strategy on a per-statement basis, to check that you obtain the desired
effect, and catch the stats:
SELECT ... FROM table (INDEX indexname LRU):

3 If you are happy with those results, then set LRU for chosen (or
all) NCIs by:
sp_cachestrategy dbname, tablename, indexname, mru , "off"
Although this is done on a per-index basis, you can script the lot.
Watch for falling trees (operations other than NCI scans slow down).

(in [2] you can set the prefetch "off" as well - "on" is default)
(in [3] you can set the prefetch size as well)

(Get onto 12.5.4, at least ESD#5) asap.)
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2007 Software Gems Pty Ltd
This is an inappropriate medium to confer tone, feeling, etc. None
intended, none taken.
"Patient, normalise thyself"

Post by unknown
Hi All,
In a select statement, is it a normal behavior of the
optimizer to select indexes pages of a non-clustered index
using 16K buffer pulls with a MRU stategy? why?
I understand very well the fetch of data using 16K buffer
pool and MRU strategy, but in the cases of indexes;
especially the non-clustered ones; it is not at all clear
why such a choice is made. If one select 16K of indexe pages
of a non-clustered indexes into the cache and get rid of
them by MRU strategy, this is not a mistake? to me it is, as
most probably some of the pages which are flashed out of
memory will be needed again and must be fetched again, this
means a considerable increase in the number of physical I/O,
no?
I would like to understand the mechanism behind such a
choice, the sybase documenation describe very well the two
mechanism but I do not see any explaination as to why one
strategy is chosen while one expect the other.
Why by forcing the stategy from MRU to LRU for the indexes;
one obtains a better response time?
All help and suggestions, comments are well appreciated.
By the way, our Sybase ASE server is version 12.5.03 and the
tables are huge(70M rows and more).
tartampion

[Derek Asirvadem]

Just read your related post under P&T re query plan diffs between dev
and prod. It appears you have some consternation re why ASE uses MRU
and largest poolsize when scanning NCI. Index leaf-level reads (not
scans) use 2k buffers. One of the principles "built-in" to the
Optimiser. is to try and service a query by using indexes only. The
underlying code structures try to consistently perform this.

When there is a big diff between dev & prod, it is usually due to stats
not being updated. In your case, it seems to be the 2k vs 16k and
LRU/MRU issue, which is a result of making decisions based on different
resources and stats.

In any case, you can make production "match" dev, by using one of the
suggestions in my prev post.

Cache
No question, it is well and truly trashed. You have to do some work
and set up a few data caches, there is a fair amount of info around for
this.

[unknown]

Derek
Thank you very much for your excellent communications, What
you propose is a guideline.I am trying to understand you and
apply what you propose, I shall surely be very happy,
relieved and especially thankful to you when I can resolve
this problem. This is the type of problem which does not
happen every day, but when it comes in, it is an excellent
event, resolving it, will help me to have a better
understanding of our Sybase.

Thanks again.
tartampion

Post by Derek Asirvadem
T
Ok, you seem to have gotten your head around the Sybase
cache strategy (LRU/MRU issue); and you understand it;
you agree with most of it; but you disagree with the use
of MRU for scanning NCI. Right ? That is the way it is.
The Sybase engineers came up with it a long time ago,
and it is tried and tested. There has been no effort from
the user base to change this. I (and I would think most
of us) definitely do NOT want NCI LRU scanned (or LRU set
for NCI scans as the default), the whole idea of MRU is
to avoid a large table (or large index) icing a warm or
hot cache when it is scanned into the cache. Note the
table might be "huge" but the NCI is not. Note MRU is
used for SCANS, where a large portion of the table or
index has to be read in.
The correct way to go about addressing your issue is (in
order, to minimise overall negative effect on the server,
1 Create a data cache for some or all your NCIs. With a
large 8k or 16k pool (I find 8k better than 16k as a
general rule). This should be the fourth standard cache
anyway. Set the APF percent to 20 or something
reasonable.
2 If you do not get a substantial and immediate
improvement in performance, or you do not like that idea,
then change the cache strategy on a per-statement basis,
to check that you obtain the desired effect, and catch
3 If you are happy with those results, then set LRU for
sp_cachestrategy dbname, tablename, indexname, mru ,
"off" Although this is done on a per-index basis, you can
script the lot. Watch for falling trees (operations
other than NCI scans slow down).
(in [2] you can set the prefetch "off" as well - "on" is
default) (in [3] you can set the prefetch size as well)
(Get onto 12.5.4, at least ESD#5) asap.)
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2007 Software Gems Pty Ltd
This is an inappropriate medium to confer tone, feeling,
etc. None intended, none taken.
"Patient, normalise thyself"

Post by unknown
Hi All,
In a select statement, is it a normal behavior of the
optimizer to select indexes pages of a non-clustered
index using 16K buffer pulls with a MRU stategy? why?
I understand very well the fetch of data using 16K
buffer pool and MRU strategy, but in the cases of
indexes; especially the non-clustered ones; it is not at
all clear why such a choice is made. If one select 16K
of indexe pages of a non-clustered indexes into the
cache and get rid of them by MRU strategy, this is not a
mistake? to me it is, as most probably some of the pages
which are flashed out of memory will be needed again and
must be fetched again, this means a considerable
increase in the number of physical I/O, no?
I would like to understand the mechanism behind such a
choice, the sybase documenation describe very well the
two mechanism but I do not see any explaination as to
why one strategy is chosen while one expect the other.
Why by forcing the stategy from MRU to LRU for the
indexes; one obtains a better response time?
All help and suggestions, comments are well appreciated.
By the way, our Sybase ASE server is version 12.5.03 and
the tables are huge(70M rows and more).
tartampion

[Derek Asirvadem]

Post by unknown
Derek
Thank you very much for your excellent communications, What
you propose is a guideline.I am trying to understand you and
apply what you propose, I shall surely be very happy,
relieved and especially thankful to you when I can resolve
this problem. This is the type of problem which does not
happen every day, but when it comes in, it is an excellent
event, resolving it, will help me to have a better
understanding of our Sybase.
Thanks again.
tartampion

1 I am not sure you understand. My suggestions are proactive. If you
wait for the "problem" before you do anyting, you will not be able to
(reactively) do anything to resolve it.

2 If the problem does not happen every day, then the MRU strategy for
NCI scans must be correct for the vast majority of the day (when the
problem does not happen). This appears to contradict your original
post, in which you display a strong belief that that strategy is wrong
(always) and you are wondering why it has been chosen.

Anyway, it now appears you only have to deal with those few occurrences
where that strategy is proving to be incorrect, if and when they can be
identified.

[unknown]

Derek,
thank you very much.
I have not surely expressed myself properly, the situation
that I described is repetitive right now every day on the
server that I talked about. I have arrived on a site and I
have inherited the situation and unfortunately I could not
be proactive on it.

tartampion

Post by Derek Asirvadem

Post by unknown
Derek
Thank you very much for your excellent communications,
What you propose is a guideline.I am trying to
understand you and apply what you propose, I shall
surely be very happy, relieved and especially thankful
to you when I can resolve this problem. This is the type
of problem which does not happen every day, but when it
comes in, it is an excellent event, resolving it, will
help me to have a better understanding of our Sybase.
Thanks again.
tartampion

1 I am not sure you understand. My suggestions are
proactive. If you wait for the "problem" before you do
anyting, you will not be able to (reactively) do anything
to resolve it.
2 If the problem does not happen every day, then the MRU
strategy for NCI scans must be correct for the vast
majority of the day (when the problem does not happen).
This appears to contradict your original post, in which
you display a strong belief that that strategy is wrong
(always) and you are wondering why it has been chosen.
Anyway, it now appears you only have to deal with those
few occurrences where that strategy is proving to be
incorrect, if and when they can be identified.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2007 Software Gems Pty Ltd
This is an inappropriate medium to confer tone, feeling,
etc. None intended, none taken.
"Patient, normalise thyself"

[Derek Asirvadem]

Post by unknown

Post by Derek Asirvadem

Post by unknown
Derek
Thank you very much for your excellent communications,
What you propose is a guideline.I am trying to
understand you and apply what you propose, I shall
surely be very happy, relieved and especially thankful
to you when I can resolve this problem. This is the type
of problem which does not happen every day, but when it
comes in, it is an excellent event, resolving it, will
help me to have a better understanding of our Sybase.
Thanks again.
tartampion

1 I am not sure you understand. My suggestions are
proactive. If you wait for the "problem" before you do
anyting, you will not be able to (reactively) do anything
to resolve it.
2 If the problem does not happen every day, then the MRU
strategy for NCI scans must be correct for the vast
majority of the day (when the problem does not happen).
This appears to contradict your original post, in which
you display a strong belief that that strategy is wrong
(always) and you are wondering why it has been chosen.
Anyway, it now appears you only have to deal with those
few occurrences where that strategy is proving to be
incorrect, if and when they can be identified.
--
Cheers
Derek

Derek,
thank you very much.
I have not surely expressed myself properly, the situation
that I described is repetitive right now every day on the
server that I talked about. I have arrived on a site and I
have inherited the situation and unfortunately I could not
be proactive on it.
tartampion

Right. In that case, Tell them you will give them relief in two
stages. Execute (1) immediately. You may as well do a decent cache
set up once and for all, and set up caches for the usual hogs (read
this and the P&T forum). That will keep the natives quiet for a while,
it will get you instant credibility, and you will have time to examine
(2) and (3) and identify the objects that need address. Ensure you are
getting 10 to 16 hours of daily sp_sysmons and put your feet up.