tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TDSQL-C for MySQL
    Documentation
    Download PDF

    Binlog Purging Performance Optimization

    Last updated: 2024-11-14 10:42:57
    Download PDF

      Overview

      In a database, binlog is a log file used to record database operations, including addition, deletion, and modification. The binlog files increase over time, occupying a large disk space, so they should be regularly purged. However, binlog purging may conflict with normal binlog rotation and write transactions. Particularly when there are many binlog files, performance fluctuation is easily caused by binlog purging. Through analysis on the scenarios of purging binlog files, the Tencent Cloud kernel team has optimized the binlog purging performance.
      The optimization addresses the LOCK_index conflict between binlog purging and binlog rotation, preventing failure to obtain the lock and continue writing binlogs in binlog rotation.
      Currently, when an index lock is held by binlog purging, if binlog rotation is required after the binlog size reaches 256 MB but the lock cannot be obtained, binlog writing will stop until the index lock is obtained. The main optimization for this issue is that if the index lock cannot be obtained, binlog rotation is not performed and binlogs continue to be written into the current binlog file. Attempts will be made constantly to obtain the index lock and binlog rotation will be performed only after the index lock is obtained.
      The optimization reduces the volume of information read from binlog files to be purged, thereby reducing locks.
      The binlog purging process requires reading the GTID information from all binlogs to be purged and then writing it into the mysql.gtid executed table. Therefore, the exclusive lock of GTID is held during the entire purging period, resulting in that normal transactions cannot obtain the GTID lock. The main optimization for this issue is that GTID information is read only from the last binlog file in the binlog list, reducing the holding time of the GTID lock and decreasing its impact on normal transactions.
      The maximum size of a single binlog file has been increased to 10 GB, significantly reducing the number of times of binlog rotation.
      The default binlog size is 256 MB. Under a load of 5,000 QPS, binlog rotation is performed approximately every 1.4s, each taking 10-20 ms. Currently, the max_binlog_size can be increased from 256 MB to 10 GB (open-source MySQL supports up to 1 GB). Under the same load, rotation is now performed every 56s, reducing the frequency of obtaining the index lock. The max_binlog_size parameter is set to 1 GB in the current policy and can be further adjusted based on business load requirements.

      Supported Versions

      Kernel version 3.1.12 or later for TDSQL-C for MySQL 8.0.
      Kernel version 2.1.12 or later for TDSQL-C for MySQL 5.7.

      Use Cases

      This feature is applicable to scenarios where binlog purging may cause lock contention and block binlog writing in case of many binlog files.

      Use Instructions

      This feature is used with parameters. Set txsql_binlog_rotate_try_lock_index and txsql_binlog_rotate_try_lock_log to ON, and set txsql_binlog_purge_check_file_count to 10 to use this feature. Detailed parameter descriptions are as follows:
      Name
      Global or Not
      Type
      Default Value
      Value Range
      Description
      txsql_binlog_rotate_try_lock_index
      Global
      bool
      ON
      ON/OFF
      When it is set to ON, if the lock of the index file cannot be obtained in binlog rotation, the current rotation will be abandoned.
      txsql_binlog_rotate_try_lock_log
      Global
      bool
      ON
      ON/OFF
      When it is set to ON, if the lock of the binlog file cannot be obtained in binlog rotation, the current rotation will be abandoned.
      txsql_binlog_purge_check_file_count
      Global
      ulonglong
      10
      0-UINT64_MAX
      The binlog purging process requires obtaining the binlog file name from the binlog index file. If there are many binlog files, this operation is time-consuming. Normally, only one binlog file name is required. This parameter restricts the number of file names obtained. 0 indicates no limit. It is generally set to 10.
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy