PolarDB Series (4): Physical and Logical Logs

Physical Logs vs. Logical Logs

The log organization can be divided into physical log and logical log. The physical log describes the changes in a way that is more partial to the underlying data block operation. Logical logs tend to describe changes in the form of a record mirror or SQL statement. Transaction engines typically use the mode of the physical log to log the underlying operation of a transaction, while non-transaction engines generally use logical logs.

Challenges of MySQL Logs

The physical log, due to its relatively low-level format, is difficult to create a read-only instances and switching from a read-only instance to a read-write instance takes a long time. Looking at the historical development of the Oracle database, it has never supported read-only standby database. It takes a relatively long period of time to change the standby database into the primary database. It is difficult to automate, and it is not easy to meet the demand for Internet read throughput expansion.

Generating Logical Logs

It is difficult to generate logical logs directly from the storage engine layer. For data consistency, the XA (2 PC) mechanism is introduced between the physical log and the logical log, resulting in major limitations and challenges to stability and performance. This results in a large gap in terms of transaction handling performance compared to traditional business databases, while based on the physical log, the gap is extremely small.

Logs for Large Transactions

The MySQL logical log for the same transaction needs to be written in sequence, so larger transaction operations cannot be supported. Too large a transaction can cause the operation to fail. Based on physical logs, logs for the same operation can be split into separate (transaction start, operation 1, operation 2, transaction commit) writes so that large transaction operations can be supported.

Generating Logs

The existing logical log of MySQL holds the before and after images of the entire record. The result is a large increase in IO pressure due to logical log writes, which is liable to result in reduced performance and jitter. The physical log records only the changed fields in a compact format to reduce the total log volume, with better IO performance. It is not liable to cause performance degradation and jitter, and definitely offers higher performance and stability.

Log Execution Path

MySQL logical log needs to re-pass the SQL Layer Code upon playback; with a relatively long execution path and with difficulty in parallel processing, it is liable to cause standby database delays — that is, the logical log generation speed exceeds the playback speed; the physical log contains complete transaction information, better facilitates transaction consistency for parallel playback, can greatly improve the recovery speed of the standby database, achieving ms-level delay under high pressure for primary and backups. As shown in the following figure:

Logical Log Information

MySQL logical log, does not contain transaction information, cannot support continuity test, can begin recovery from any point. Non-familiar unprofessional operation is liable to cause problems; the physical log contains complete transaction information, supports continuity test. It will automatically identify the last breakpoint, reducing manual judgment in operation, can effectively prevent human errors in operation.


Therefore, MySQL, based on logical replication, offers a poor experience for operations to add fields, and indexes to large tables. PolarDB, fully recognizing the advantages and disadvantages of MySQL logical replication, chose to implement physical copies as the basis for realizing duplicate nodes (Replica), thus enhancing the efficiency and experience of primary and backup replication. It provides customers with stable, reliable and high performance read-only nodes, leading the development of the next generation of replication technologies.



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com