Buffer cache Experiment 7:buffer busy waits-complete

1.buffer Busy Waits production principle analysis:

The process of acquiring and releasing a CBC latch lock and a buffer pin lock at one logical reading is as follows:

1. Add Latch X

2. Enter the hash chain, add the buffer pin S (0-->1) on the corresponding BH

3. Release latch X

4. Logical reading-that is, through the buffer Adderss in BH to find the real location of the data block in memory---if 1MS read

5. Add Latch X

6. Release buffer pin S (1-->0) 0: No lock 1: Shared lock-select 2: Exclusive lock-DML

7. Release latch X

It can be found and inferred from this process that, for example, when process A has a buffer pin exclusive lock on BH, the CBC latch has been released,

In a high concurrency environment, at this time if there is a process B to obtain CBC latch, in the hash chain find BH, ready to add locks, found BH has been added exclusive lock, the buffer busy occurred waits

Note that the shared lock on the buffer pin does not block the exclusive lock, blocking the exclusive lock-that is, the current read, and the process is currently being read. Exclusive locks also block shared locks-that is, current read blocking consistency read

In the experiment, it can be found that the read process also blocks the write process, but the number of times is less than the write process blocking the read process. This is because the block contention for the rollback segment causes the buffer BUSY WAITS

The construction of CR blocks at read time requires a consistent read block in the undo block, and multiple DML statements for a transaction may correspond to the same undo. The read operation uses the Undo block to construct the CR block, which may cause a buffer BUSY WAITS. This can be considered: multiple DML statements in the same transaction are submitted multiple times, reducing the undo block competition--can be committed by adding an if mod (a,5000) =0;commit to the judgment every 5 K or 1W times.

Of course in BH with a buffer pin lock and release is a quick process-ns class bar; Here's just an ideal hypothesis.

The same transaction is the same rollback block until the rollback block is full and the next one is used.

Different transactions, using different rollback blocks.

The same rollback block can have only one transaction at a time.

10G, there are buffer busy waits with read by

Common scenarios for 2.buffer busy waits contention:

Hot block of a table--multiple sessions concurrently accessing the same block of data,

ASSM the contention of the lower section head--The contention of the L1 block of the segment head for high concurrent inserts--see: Http://blog.csdn.net/haibusuanyun/article/details/18776657#t2

The frequent change of block idle state caused by unreasonable pctfree value, L1 block contention-see: Http://blog.csdn.net/haibusuanyun/article/details/18776657#t2

MSSM management more easily in the large concurrent insert due to the section of the first contention for the generation of buffer busy waits, the principle and ASSM high concurrent insert on the L1 block contention principle is similar, but now with less MSSM, unknown said.

The undo header, which can be used to resolve the buffer problem by increasing the rollback segment (rollback segment). If you wait on the undo block, we may need to check for related applications, reduce the mass of consistent reads appropriately, or reduce the data density in the table of consistent read (consistent read) or increase db_cache_size, or increase the number of commits in multiple DML statements for a transaction. ---is also OLTP in the small transaction can reduce the buffer busy waits wait.

Another upset is that theIMU model helps reduce the buffer BUSY waits wait--reducing the CR block from the buffer caceh to reduce latch competition.

The hot block will also require the CBC LATCH to be acquired as a result of concurrent access due to the waits of the buffer busy. possible situation already has session acquired a buffer pin lock, at this time session 1 is obtained CBC LATCH, but cannot obtain the buffer pin lock, at this time produces the buffer busy waits.

CBC latch will also appear if there are other sessions that want to access other hash chain under this CBC latch or access and session 1 of the same BH.

3.read by other sessions waiting

It is common for one or more sessions to access blocks that do not exist in the buffer cache and find that the data block is being read by another session. At this point, the session that is reading the data block appears: DB file sequential read/db file scattered read;

Other sessions appear: read by others session

4. Solve the buffer busy waits contention method is:

Temporary methods: collect relevant data, kill session, temporary solution, let the system first recovery-but it may be normal and soon again.

palliative approach: identify the SQL that involves hot block contention, optimize SQL, or adjust the application.

5. Hot block is the solution of the data block on the ordinary table:

1. Increase the pctfree or use a smaller block size

2. Using small data blocks -2k-4k

3. Use hash Table partitioning

3. Reverse Indexing

The hot block is the L1 block of the segment head:The contention of the lower segment of the-ASSM--the contention of the L1 block of the segment head for high concurrent inserts-see: Http://blog.csdn.net/haibusuanyun/article/details/18776657#t2

The hot block is the Undo header: You can resolve the buffer problem by increasing the rollback segment (rollback segment).

hot blocks are on the undo block: We may need to check for related applications, appropriately reduce the mass of consistent reads, or reduce the density of data in a table with consistent read (consistent read) or increase db_cache_size.

4. Frequently operated small table consider put into keep POOL

5. Adjust hidden parameter _spin_count, increase the possibility that the process obtains latch successfully, this method should use cautiously, increase _spin_count will increase CPU load and may cause negative effect