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.

The default setting of 1 is required for full ACID compliance. Data in the log buffer is written to log files and then flushed to distributed storage at each transaction commit.

If this parameter is set to 0, InnoDB writes data in the log buffer to log files and flushes the data to distributed storage every second.

If this parameter is set to 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 low-concurrency write performance.
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 do not depend on binlogs for data synchronization. You can disable binlog as required.

If this parameter is set to OFF (default value), binlog is disabled. The setting is applied without an instance reboot and applied to both existing and new connections.

If this parameter is set to ON, binlog is enabled. The setting is applied without an instance reboot and applied to both existing and new connections.

Impact: Binlog can be disabled if it is not required. This does not affect the 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. You can set the log-bin parameter to disable or enable binlog. Modifying this parameter requires an instance reboot.
- In 2.0.45.230900 and later versions, binlog is disabled by default. You can set the rds_global_sql_log_bin parameter to enable or disable binlog.
rds_plan_cache
Default value: OFF

Function: The Plan Cache feature caches the execution plan of the PREPARE statement to improve query performance by reducing the need for the optimizer to regenerate execution plans.

If rds_plan_cache is set to 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 improved.

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 always take effect.