Binlog_cache_size | Binlog_stmt_cache_size

MySQL binary log cache parameters:

Binlog_cache_size//Transaction Cache size

Binlog_cahce_use//Transaction Cache usage

Binblog_cache_disk_use//Transaction cache disk usage (memory cache settings too small enough)

Binlog_stmt_cache_size//non-transactional statement cache size

Binlog_stmt_cache_use//non-transactional statement cache usage times

Binlog_stmt_cache_disk_use//Non-transactional statements disk cache usage

We know that the InnoDB storage engine supports transactions. The implementation transaction relies on log technology, and for performance, the log encoding is in binary format.

So, when do we record the logs? Write directly to disk when you have a log? But because the efficiency of the disk is very low, if you have used Nginx, the general Nginx output access log is to buffer the output. Therefore, when logging binary logs, we also need to consider the use of in-memory caching. However, because the cache is not directly persisted, it is faced with the security problem of not being able to flash the disk in time when the system is down. As a result, the cache needs to be weighed to reduce disk I/O to meet performance requirements, and to ensure that there is no residue in the memory cache, timely persistence, to meet security requirements.

The parameter binlog_cache_size is used to control it, when a transaction is not committed (uncommitted), the resulting log is recorded in the cache, and the log is persisted to disk when the transaction commit (committed) needs to be committed. It is important to note that Binlog_cache is not global, but is allocated exclusively in session, meaning that when a thread starts a transaction, MySQL assigns a binlog_cache to the session. The system default binlog_cache_size is only 32k. When we submit a long transaction, such as bulk import data, and the cache does not fit in the time, MySQL will have committed transactions recorded in a temporary file, and so on when the submission of the log, but the temporary file performance is significantly lower than memory. The size of the solid needs to be weighed to see the current cache size using:

Mysql> Show session variables like ' binlog_cache% ';
+-------------------+----------+
| Variable_name    | Value   |
  +-------------------+----------+
  | Binlog_cache_size | 67108864 |
  +-------------------+----------+
  1 row in Set (0.00 sec)

To view the global cache size:
Mysql> show global variables like ' binlog_cache% ';
+-------------------+----------+
| Variable_name| Value|
+-------------------+----------+
| Binlog_cache_size | 67108864 |
+-------------------+----------+
1 row in Set (0.00 sec)

To view the configuration file:

grep ' Binlog_cache '/etc/my.cnf

binlog_cache_size = 64M #系统默认配置大小
Max_binlog_cache_size = 128M
Max_binlog_size = 200

See if the value meets your needs:

Show status like "binlog_%";

  +-------------------------+-------------+
| Variable_name     | Value   |
+-------------------------+-------------+
| Binlog_cache_disk_use | 0   |
| Binlog_cache_use  | 120402264 |
+-------------------------+-------------+
2 rows in Set (0.00 sec)

Binlog_cache_disk_use indicates that when the cache is out of service, the number of disk temp files is used, which means 0 is unused, so the current cache size is sufficient. If there is no long transaction long_trascation, it means that the value is too large.

Binlog_stmt_cache_size is similar to Binlog_cache_size, and the opposite is binlog_stmt_cache_disk_use.

Binlog_cache_size | Binlog_stmt_cache_size