MySQL HA架构下innodb_flush_log_at_trx_commit及sync

Home

Database

Mysql Tutorial

MySQL HA架构下innodb_flush_log_at_trx_commit及sync_binlog参数

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:42 PM

HeartBeat + DRBD以及MySQL replication是很多企业比较普遍使用的方式。对于数据的完整性和一致性的问题，这两种架构需要考虑2个

HeartBeat + DRBD以及MySQL replication是很多企业比较普遍使用的方式。对于数据的完整性和一致性的问题，这两种架构需要考虑2个重要的参数innodb_flush_log_at_trx_commit以及sync_binlog参数。本文主要参考了MySQL 5.6 Reference Manual列出对这2个参数的具体描述。

1、Heartbeat + DRBD or replication

Cost: Additional passive master server (not handing any application traffic) is needed

Performance: To make HA really work on DRBD replication environments, innodb-flush-log-at-trx-commit and sync-binlog must be 1. But these kill write performance

Otherwise necessary binlog events might be lost on the master. Then slaves can’t continue replication, and data consistency issues happen

2、参数innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit参数为全局动态参数，其取值范围为0，1，2，缺省值为0

value

action

With a value of 0, any mysqld process crash can erase the last second of transactions. The log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but no writes are done at a transaction commit.(mysqld 进程crash会导致丢失最后一秒的事务)

The default value of 1 is required for full ACID compliance. With this value, the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file.

With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. The log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. Before MySQL 5.6.6, the flushing on the log file takes place once per second. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. As of MySQL 5.6.6, flushing frequency is controlled by innodb_flush_log_at_timeout instead.( 操作系统crash或电源故障导致丢失最后一秒的事务)

InnoDB's crash recovery works regardless of the value. Transactions are either applied entirely or erased entirely.(Innodb存储引擎存与该参数无关，可以通过crash recovery来解决，要么提交，要么回滚)

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use innodb_flush_log_at_trx_commit =1 and sync_binlog=1 in your master server my.cnf file.

3、参数sync_binlog

sync_binlog为全局动态参数，取值范围为0 .. 18446744073709547520，，缺省值为0。

If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after every sync_binlog writes to the binary log. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise.

The default value of sync_binlog is 0, which does no synchronizing to disk. A value of 1 is the safest choice, because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

本文永久更新链接地址：

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AM

MySQL 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 AM

The 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 AM

MySQL'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 AM

SQL 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 AM

InnoDB 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 AM

MySQL'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 AM

MySQL 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 AM

InnoDB 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.

See all articles