mysql中的cache和buffer_理解和调整BUFFER CACHE 以及 DBWR(database writer)

Understanding and Tuning Buffer Cache and DBWR (Doc ID 62172.1)

理解和调整BUFFER CACHE 以及 DBWR(database writer)

当我们的数据库出现一下等待事件的时候，说明数据库性能已经受到了影响：

--1)Latch contention for the 'cache buffers lru chain' or the "cache buffer chain" latch

--2)Large "Average Write Queue" length

--3)Lots of time spent waiting for "write complete waits"

--4)Lots of time spent waiting for "free buffer waits"? or "Buffer busy waits"

BUFFER CACHE缓冲区缓存

Oracle在SGA的一个区域中保存数据库块的副本，称为缓冲区缓存。

缓存可以从不同的时间点保存一个块的多个副本，并且可能包含“脏”块，即已更新但尚未刷新到磁盘的块。数据库write/ S(DBWR或DBWn进程)是负责写脏数据块到磁盘，而任何用户会话可以读取数据块写入缓存。

缓冲区高速缓存中的所有块在一个LRU(最近最少使用)列表-当一个进程需要一个空闲的缓冲时。它会扫描从这个列表的LRU端非脏缓冲区，它可以使用。The 'cache buffers lru chain' latch/es serialize operations on the LRU list/s.(在Oracle8i起用于LRU列表的算法是更早的版本不同而影响因素保持不变)。

Evaluating Buffer? cache Activity

对缓冲区缓存的活性评价

缓冲区缓存命中率度量内存中所需块的多少倍，而不是在磁盘上执行昂贵的读操作以获得块。

查询V$SYSSTAT视图是可能获得的统计信息用于调整缓冲区高速缓存。为了计算这个比例，你必须考虑你正在运行的Oracle版本。建议在增加缓冲区缓存大小之前，命中率高于80%。

how to calculate this ratio on each Oracle version.

怎么样计算每个Oracle版本的命中率。

“缓存命中率”是一个派生的统计数据最多的手册和文章。

存在的缓存命中的定义不止一个

命中率是用来指示各种情况的频率。

访问数据缓冲区的进程查找Oracle缓冲区中的块。

高速缓存。命中率的精确值比

有能力监控它随着时间的推移，以注意任何重大变化的

数据库上活动的概要。

important：

命中率很高(接近100%)不一定是好的。

原因是后来解释的。

Calculation:

~~~~~~~~~~~~

The most common formula in circulation for the hit ratio for the buffer cache

for Oracle7/8 is:

hit ratio =   1 -           ( physical reads )

-----------------------------------

( consistent gets + db block gets )

A better formula in Oracle8i/9i is:

hit ratio =

1 -  ( physical reads - (physical reads direct + physical reads direct (lob)) )

--------------------------------------------------------------------------

( db block gets + consistent gets - (physical reads direct + physical reads direct (lob)) )

每个池的命中率可以是使用V buffer_pool_statistics看到：

SELECT name, 1-(physical_reads / (consistent_gets + db_block_gets ) )  "HIT_RATIO"

FROM V$BUFFER_POOL_STATISTICS

WHERE ( consistent_gets + db_block_gets ) !=0

;

The "Miss Ratio"

~~~~~~~~~~~~~~~~

Occasionally you may see reference to the "miss ratio". This is just

Miss ratio =  100% - Hit Ratio (expressed as a percentage)

Notes about the Hit Ratio

~~~~~~~~~~~~~~~~~~~~~~~~~

A good hit ratio is expected for OLTP type systems but decision support type

systems may have much lower hit ratios.  Use of parallel query will make the

hit ratio less meaningful if using the first form of calculation based on

"physical reads" only.

A  hit ratio close to 100% does not mean the application is good. It is quite

possible to get an excellent hit ratio by using a very unselective index in a

heavily used SQL statement.

Eg: Consider a statement like:

SELECT * FROM employee WHERE empid=1023 AND gender='MALE';

If EMPLOYEE is a large table and this statement always uses the GENDER index

rather than the EMPID index then you scan LOTS of blocks (from the GENDER

index) and find nearly all of them in the cache as everyone is scanning this

same index over and over again. The hit ratio is very HIGH but performance

is very BAD.  A common 'variation' on an "unselective" index is a heavily

skewed index where there are a large number of entries with one particular

value (eg: a workflow status code of CLOSED) - the index may perform well for

some queries and very poorly for the most common value.

如果员工是大表，这个语句总是使用性别索引。

而不是工号索引然后你扫描很多块(从性别索引)并在缓存中找到几乎所有的缓存，因为每个人都在扫描这个。

同一索引一遍又一遍。命中率很高，但性能很糟糕。一个共同的“变化”的“选择性”指数是一个重有一个特殊条目的大量索引。

值(例如：关闭的工作流状态代码)-该索引可能执行得很好。

有些查询和最常见的值很差。

下面的这个命中率公式适用于所有版本的Oracle：

A few comments:

~~~~~~~~~~~~~~~

- The "good" hit ratio is generally considered to be one >80%

There is probably still scope for tuning if it is <90% *BUT*

note that the hit ratio is not the best measure of performance.

- The ratio can be artificially high in applications making

poor use of an UNSELECTIVE index.

- In Oracle8.1 onwards "physical reads direct" are recorded

- Some documentation incorrectly reports hit ratio to be:

Hit Ratio = Logical Reads / ( Logical Reads + Physical Reads )

this is incorrect for any version of Oracle.

一个好的命中率一般是大于80%的，如果它小于90%，也可能还有调整的范围，因为命中率高并不代表性能就一定好。