mysql binlog日志优化及思路_MySQL-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

mysql binlog日志优化及思路_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:41 PM

binaryoptimizationInfluenceDatabase installation

bitsCN.com
mysql binlog日志优化及思路在数据库安装完毕，对于binlog日志参数设置，有一些参数的调整，来满足业务需求或使性能最大化。Mysql日志主要对io性能产生影响，本次主要关注binlog 日志。查一下二进制日志相关的参数 mysql> show variables like '%binlog%';+--------------------------------+----------------------+ | Variable_name | Value | +--------------------------------+----------------------+ | binlog_cache_size | 1048576 | | innodb_locks_unsafe_for_binlog | OFF | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | sync_binlog | 0 | +--------------------------------+----------------------+5 rows in set (0.14 sec) 其中innodb_locks_unsafe_for_binlog 参数是innodb存储引擎特有的与binlog相关的参数，默认是关闭的 Binlog_cache_size:默认大小是37268即32K.我们计算一下上面binlog_cache_size参数大小，1048576/1024/1024 = 1M，根据事务需要调整大小。次参数表示在事务中容纳二进制日志sql语句的缓存大小。那么怎么理解二进制日志缓存，就是服务器支持事务存储引擎并且服务器启用了二进制日志(-log-bin选项)的前提下为每个客户端分配的内存，是每个client都可以分配设置大小的binlog cache空间。我们可以通过以下两个状态变量判断binlog_cache_size的使用情况： 1 binlog_cache_use: Thenumber of transactions that used the temporary binary log cache.（使用二进制日志缓存的事务的数量） 2 Binlog_cache_disk_use: The number oftransactions that used the binary log cache but that exceeded the value of binlog_cache_size andused a temporary file to store changes from the transaction. 使用二进制日志缓存并且值达到了binlog_cache_size设置的值，用临时文件存储来自事务的变化这样的事务数量 Max_binlog_cache_size: 默认值是18446744073709547520，这个值很大，够我们使用的了。此参数和binlog_cache_size相对应，代表binlog所能使用的cache最大使用大小。如果系统中事务过多，而此参数值设置有小，则会报错。Max_binlog_size: 1073741824=1G binlog的最大值，一般设置为512M或1G,一般不能超过1G。此参数不能非常严格控制binlog的大小，特别是在遇到大事务时，而binlog日志又到达了尾部，为了保证事务完整性，不切换日志，把所有sql都写到当前日志。这根oracle redo日志有点不一样（感兴趣的可以google）。Mysql记录的是mysql数据库逻辑变化信息，称之为event，实际上就是引起数据库变化的query语句。 Sync_binlog: 参数不仅影响数据库的性能，而且还影响数据库数据的完整性。Sync_binlog参数选项有如下选择 1 Sync_binlog=0,这意味着当事务提交之后，mysql不做fsync（文件系统同步）之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。2 sync_binlog=n,当每进行n次提交之后，mysql将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。 Sync_binlog=0,这是数据库默认设置，性能也是最好的,也是最危险的。一旦系统crash，在binlog_cache中的所有binlog信息都会被丢失。当synn_binlog=1的时候，是最安全性能消耗也最大，完成一个事务，同步一次。系统crash了，也只是丢掉了未完成的事务信息。没坐过这方面的测试，设置为1和0在性能方面的差距，有做过的说在高并发系统中，两种设置性能可高达5倍甚至更多。这需要我们来做出选择，是要性能最好，还是最安全。资料：《mysql性能调优与架构设计》 http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.htmlhttp://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html 作者 aeolus_pu bitsCN.com

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

When should you use a composite index versus multiple single-column indexes?Apr 11, 2025 am 12:06 AM

In database optimization, indexing strategies should be selected according to query requirements: 1. When the query involves multiple columns and the order of conditions is fixed, use composite indexes; 2. When the query involves multiple columns but the order of conditions is not fixed, use multiple single-column indexes. Composite indexes are suitable for optimizing multi-column queries, while single-column indexes are suitable for single-column queries.

How to identify and optimize slow queries in MySQL? (slow query log, performance_schema)Apr 10, 2025 am 09:36 AM

To optimize MySQL slow query, slowquerylog and performance_schema need to be used: 1. Enable slowquerylog and set thresholds to record slow query; 2. Use performance_schema to analyze query execution details, find out performance bottlenecks and optimize.

MySQL and SQL: Essential Skills for DevelopersApr 10, 2025 am 09:30 AM

MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.

Describe MySQL asynchronous master-slave replication process.Apr 10, 2025 am 09:30 AM

MySQL asynchronous master-slave replication enables data synchronization through binlog, improving read performance and high availability. 1) The master server record changes to binlog; 2) The slave server reads binlog through I/O threads; 3) The server SQL thread applies binlog to synchronize data.

MySQL: Simple Concepts for Easy LearningApr 10, 2025 am 09:29 AM

MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.

MySQL: A User-Friendly Introduction to DatabasesApr 10, 2025 am 09:27 AM

The installation and basic operations of MySQL include: 1. Download and install MySQL, set the root user password; 2. Use SQL commands to create databases and tables, such as CREATEDATABASE and CREATETABLE; 3. Execute CRUD operations, use INSERT, SELECT, UPDATE, DELETE commands; 4. Create indexes and stored procedures to optimize performance and implement complex logic. With these steps, you can build and manage MySQL databases from scratch.

How does the InnoDB Buffer Pool work and why is it crucial for performance?Apr 09, 2025 am 12:12 AM

InnoDBBufferPool improves the performance of MySQL databases by loading data and index pages into memory. 1) The data page is loaded into the BufferPool to reduce disk I/O. 2) Dirty pages are marked and refreshed to disk regularly. 3) LRU algorithm management data page elimination. 4) The read-out mechanism loads the possible data pages in advance.

MySQL: The Ease of Data Management for BeginnersApr 09, 2025 am 12:07 AM

MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.

See all articles