重做产生于PGA，再由各个session的服务器进程将重做记录拷贝到SGA 的log buffer中，再由LGWR进程刷新到redo log文件中
重做产生于PGA，再由各个session的服务器进程将重做记录拷贝到SGA 的log buffer中，再由LGWR进程刷新到redo log文件中
涉及到的三个latch：
Redo copy latch
Redo allocation latch
Redo writing latch
Redo copy latch
redo copy latch的数量可以有多个，可以通过_log_simultaneous_copies参数来设定，，缺省值是两倍CPU的个数，
此latch保护日志缓存中的信息，主要用于从PGA拷贝重做到log buffer中，但是不允许对重做记录一边进行修改，
一边将重做记录写入磁盘。所以LGWR工作的时候，必须等待持有redo copy latch 的前台进程将要刷新的重做记录拷贝完毕
这里也就是说，LGWR从redo log buffer写到文件的时候，是无法写正在copy的redo log buffer，但是可以写不持有
Redo copy latch的log buffer。
Redo allocation latch
前台进程和LGWR都将持有该latch
Oracle把向log buffer中写缓存这样一个操作分做两个步骤：
1． 是先在log buffer中分配一块空间
2． 是向这块空间中实际的写入重做信息
当前台进行分配空间的时候，必须先持有该latch，但是该阶段该latch只有一个，所以前台进程这个时候会相互阻塞。
当LGWR进行刷新缓存时，持有该latch，当确定刷新的范围后，那么就会写到磁盘，写磁盘前会释放该latch
Redo writing latch
当日志缓存没空间分配时，前台进程必须通知LGWR刷新日志缓存，只有第一个得到此latch的进程通知LGWR，
用来阻止其他进程通知LGWR，通知后，马上释放该latch，不会一直占用。LGWR得到通知，持有该latch，
写入磁盘文件前释放该latch。
重做产生的流程：
1.先在PGA中生成重做记录,并计算出重做记录大小
2.由服务器进程申请redo copy latch如果成功的话继续
3.再去申请redo allocation,成功分配空间后
4.释放redo allocation
5.开始把PGA中的重做记录写往log buffer
6.记录写完后,释放redo copy latch
_log_io_size:如果使用的log buffer大小等于或者大于该值，那么就触发LGWR写磁盘，缺省大小为log buffer的1/3，上限值为1M
redo buffer等待事件：
LOG BUFFER SPACE：
redo copy的速度快于LGWR，造成free log buffer总是不够用
原因：
LOG BUFFER太小，总没有空间copy
LOG BUFFER太大，但是录入的太频繁
提高LGWR写的效率，以及磁盘的IO性能
log file parallel write
此等待事件是LGWR将log buffer写到在线日志文件，重用log buffer。
解决方法：
减少日志的生成(NOLOGGING)
减少日志组成员数
避免在备份模式下做大量的事务
尽量用最小的辅助日志模式(Supplemental Logging),如在LOGMINER下分析日志.
日志组成员分布在不同的物理磁盘上
不要将在线日志存放在RAID5上
尽量使用裸设备
Log file sync
事物提交时，一个进程创建一个重做记录，LGWR从log buffer写到磁盘，当再次发出commit，前面的LGWR还没有完成，会造成log file sync等待
原因：
过度频繁的提交
CPU使用过度
bug
如果log file sync接近log file parallel write，那么冲突可能是日志IO问题，如果远大于，则IO不是主要问题
Log file switch(checkpoint incomplete)
当日志切换的时候，要覆盖一个检查点未完成的的日志造成的等待
解决办法：
IO有严重问题，增加DBWR的效率，提高磁盘IO性能
增大日志文件
增加日志组
Log file switch (archiving needed)
如果是归档模式存在此等待，那就是归档的速度慢，可以调整归档日志所在磁盘的性能，调整log_archive_max_processes。
log file sequential read
当redo进行归档时，会顺序读取redo日志，会造成此等待
How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AMMySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.
MySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AMThe MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.
Real-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AMMySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.
SQL Commands in MySQL: Practical ExamplesApr 14, 2025 am 12:09 AMSQL commands in MySQL can be divided into categories such as DDL, DML, DQL, DCL, etc., and are used to create, modify, delete databases and tables, insert, update, delete data, and perform complex query operations. 1. Basic usage includes CREATETABLE creation table, INSERTINTO insert data, and SELECT query data. 2. Advanced usage involves JOIN for table joins, subqueries and GROUPBY for data aggregation. 3. Common errors such as syntax errors, data type mismatch and permission problems can be debugged through syntax checking, data type conversion and permission management. 4. Performance optimization suggestions include using indexes, avoiding full table scanning, optimizing JOIN operations and using transactions to ensure data consistency.
How does InnoDB handle ACID compliance?Apr 14, 2025 am 12:03 AMInnoDB achieves atomicity through undolog, consistency and isolation through locking mechanism and MVCC, and persistence through redolog. 1) Atomicity: Use undolog to record the original data to ensure that the transaction can be rolled back. 2) Consistency: Ensure the data consistency through row-level locking and MVCC. 3) Isolation: Supports multiple isolation levels, and REPEATABLEREAD is used by default. 4) Persistence: Use redolog to record modifications to ensure that data is saved for a long time.
MySQL's Place: Databases and ProgrammingApr 13, 2025 am 12:18 AMMySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen
MySQL: From Small Businesses to Large EnterprisesApr 13, 2025 am 12:17 AMMySQL is suitable for small and large enterprises. 1) Small businesses can use MySQL for basic data management, such as storing customer information. 2) Large enterprises can use MySQL to process massive data and complex business logic to optimize query performance and transaction processing.
What are phantom reads and how does InnoDB prevent them (Next-Key Locking)?Apr 13, 2025 am 12:16 AMInnoDB effectively prevents phantom reading through Next-KeyLocking mechanism. 1) Next-KeyLocking combines row lock and gap lock to lock records and their gaps to prevent new records from being inserted. 2) In practical applications, by optimizing query and adjusting isolation levels, lock competition can be reduced and concurrency performance can be improved.
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.
MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.
SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment
