Help Center/ GaussDB(for MySQL)/ User Guide/ Parameter Management/ Viewing Suggestions on GaussDB(for MySQL) Parameter Tuning
Updated on 2024-11-06 GMT+08:00
View PDF
Share

Viewing Suggestions on GaussDB(for MySQL) Parameter Tuning

Parameters are key configuration items in a database system. Improper parameter settings may adversely affect database performance. This section describes some important parameters for your reference. For details, see MySQL official website.

For details on how to modify GaussDB(for MySQL) parameters on the console, see Modifying Parameters of a DB Instance.

Performance Parameters

  • innodb_flush_log_at_trx_commit

    Default value: 1

    Function: Controls the balance between strict ACID compliance for commit operations and higher performance.

    1: InnoDB writes data in the log buffer to log files and then flushes the data to distributed storage at each transaction commit. The ACID properties of transactions are ensured.

    0: InnoDB writes data in the log buffer to log files and then flushes the data to distributed storage every second.

    2: InnoDB writes data in the log buffer to the file system cache at each transaction commit, and flushes the data to distributed storage every second.

    Impact: If this parameter is not set to 1, data security is not guaranteed. One second of transactions can be lost in a crash.

    Recommended value for PoC: 0. This setting can significantly improve write performance in low concurrency scenarios.

  • rds_global_sql_log_bin

    Default value: OFF

    Function: Controls whether to enable or disable binlog. GaussDB(for MySQL) uses a shared storage architecture. The primary node and read replicas in a DB instance do not depend on binlogs for data synchronization. You can disable binlog as required.

    OFF: Binlog is disabled. The setting is applied to both existing and new connections without an instance reboot.

    ON: Binlog is enabled. The setting is applied to both existing and new connections without an instance reboot.

    Impact: Enabling or disabling it does not affect your instance.

    Recommended value for PoC: OFF. This setting can improve write performance.

    • In 2.0.42.230601 and earlier versions, binlog is enabled by default. To enable or disable it, you need to configure the log-bin parameter and then reboot your instance.
    • In 2.0.45.230900 and later versions, binlog is disabled by default. To enable or disable it, you need to configure the rds_global_sql_log_bin parameter.
  • rds_plan_cache

    Default value: OFF

    Function: Controls whether to cache the execution plan of a PREPARE statement.

    ON: The execution plan of the PREPARE statement is cached. The cached execution plan can be reused in the next execution, improving query performance.

    Impact: The query performance of the PREPARE statement is greatly improved, and the select_random_ranges test model of sysbench is significantly enhanced.

    Recommended value for PoC: ON. This setting can improve query performance.

    • This feature can be enabled in 2.0.51.240300 and later versions.
    • rds_plan_cache uses the memory allocated by the stmt mem memory area instead of the innnodb_buffer memory.
    • rds_plan_cache_allow_change_ratio: Table data change rate caused by query operations such as DML. If the change rate exceeds this parameter value, plan caches become invalid. If this parameter is set to 0, plan caches are not affected by the table data change ratio. They are always valid.
Parent Topic: Parameter Management