i have 8174 db.
how can i know that i have the db buffer too small?
you have to first make sure your SQL is as good as it gets (reduce the number of
LIO's your sql does).
Then, you need to understand if a sql cache of any size can cache some
percentage of your data (maybe it can, maybe it can't)
Then you can look at ratio to see if you have a satisfactory hit.
But basically, you want to look at what you system is waiting for. Not waiting
on physical IO? then your cache is at least big enough. Waiting on physical
IO? DO NOT INCREASE YOUR BUFFER CACHE FIRST, rather look at your top SQL, if
you have a query that is executed lots and returns like 2 rows but does 100
LIO's to do it -- you need to look at that ( i would hope for single digit IO's
for a popular query that returns 2 rows). do you have a popular query that just
does a ton of LIO's (regardless of the rows) -- why? can you do anything about
it? if so -- fix it.
Then, you might find that your buffer cache is just fine after all (watch the
LIO's and the PIO's will take care of themselves).
Increasing the buffer cache to help a PIO problem is the last step -- you want
to exhaust other avenues first (more efficient sql, better algorithms, quit
running stuff you don't really need to run, that sort of stuff -- they'll give
you the big payoff. increasing the buffer cache doesn't always have the same
potential as those approaches)
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-798857/，如需转载，请注明出处，否则将追究法律责任。