In addition to providing various system parameter templates for you to choose from, TencentDB for MySQL also allows you to create custom parameter templates to set parameters in batches according to your business scenarios.
You can use the parameter template to configure and manage the parameters of a database engine. A template is like a container of the values of database engine parameters, which can be applied to one or more TencentDB instances.
Parameter templates support the following features.
Parameter templates support default parameter templates, including the high-stability template (recommended) and high-performance template.
Parameter templates support creating templates based on default parameters. With minor modifications, you can create a custom solution for parameter optimization.
Parameter templates support importing the TencentDB for MySQL configuration file (my.conf) to generate a template.
Parameter templates support saving configurations as templates from the parameter settings page.
Parameter templates support applying existing templates to instances when parameters are configured for one or multiple instances.
Comparison between two parameter templates is supported.
Note:
If the parameters in the template are updated, the instance parameters are not updated unless they are manually re-applied to the instances.
Introduction of the System Default Template
TencentDB for MySQL provides different default templates tailored to varying system requirements for database performance and stability.
High-Stability Template (Hot)
It emphasizes data security and operation stability, resulting in a slower running speed compared to the high-performance parameter template.
High-Performance Template
It emphasizes operation performance, providing a faster running speed compared to the high-stability parameter template.
Parameters involved are as follows:
Note:
Some parameters in the parameter template support setting formulas. For details about the parameter formulas and the parameters that support them, see Parameter Formula.
Name of Parameter with Difference
Default Template (Deprecated)
High-Performance Parameter Template
High-Stability Parameter Template
innodb_read_io_threads
12
{MAX(DBInitCpu/2,4)}
{MAX(DBInitCpu/2,4)}
innodb_write_io_threads
12
{MAX(DBInitCpu/2,4)}
{MAX(DBInitCpu/2,4)}
max_connections
10000
{MIN(DBInitMemory/4+500,100000)}
{MIN(DBInitMemory/4+500,100000)}
table_definition_cache
768
{MAX(DBInitMemory*512/1000,2048)}
{MAX(DBInitMemory*512/1000,2048)}
table_open_cache
2000
{MAX(DBInitMemory*512/1000,2048)}
{MAX(DBInitMemory*512/1000,2048)}
table_open_cache_instances
16
{MIN(DBInitMemory/1000,16)}
{MIN(DBInitMemory/1000,16)}
innodb_disable_sort_file_cache
OFF
OFF
ON
innodb_log_compressed_pages
ON
OFF
ON
innodb_print_all_deadlocks
OFF
OFF
ON
sync_binlog
0
1000
1
thread_handling
one-thread-per-connection
pool-of-threads
one-thread-per-connection
innodb_flush_redo_using_fdatasync
TRUE
TRUE
TRUE
innodb_fast_ahi_cleanup_for_drop_table
ON
ON
ON
innodb_adaptive_hash_index
FALSE
TRUE
FALSE
innodb_table_drop_mode
ASYNC_DROP
ASYNC_DROP
ASYNC_DROP
innodb_flush_log_at_trx_commit
2
2
1
Core parameter description:
Parameter
Value
Description
innodb_flush_log_at_trx_commit
1
When a transaction is committed, the transaction log is written from the cache to the log file and immediately written to the disk.
2
When a transaction is committed, the transaction log is written from the cache to the log file, but it may not be immediately written to the disk. The log file is written to the disk every second; if the system crashes before the write operation, the logs from the last second may be lost.
innodb_flush_redo_using_fdatasync
true
Use fdatasync to refresh redo log file data from the operating system cache to the disk. Compared to fsync, fdatasync does not update the metadata portion, causing a slight performance improvement.
false
Use fsync to refresh the redo log file data from the operating system cache to the disk. Compared to fdatasync, it causes a slight performance degradation.
sync_binlog
1
After a transaction is committed, the binary log file is written to the disk and refreshed immediately. This is equivalent to writing to the disk synchronously, without going through the system cache.
1000
A risk of data loss is posed by writing data to the disk and refreshing after every 1000 write operations to the system cache.
thread_handling
one-thread-per-connection
Each connection is handled by a backend thread. When there are a large number of sessions, it can lead to significant resource preemption, and a high level of thread scheduling and cache invalidation can also lead to performance degradation.
pool-of-threads
Different from the previous approach, where each connection is handled by a backend thread, all connections are now managed by a shared thread pool, which improves performance in high-concurrency scenarios.
innodb_table_drop_mode
SYNC_DROP
InnoDB will complete the table deletion before the DROP TABLE operation ends.
ASYNC_DROP
InnoDB will rename the ibd file to innodb_async_drop_tmp_dir and place it in the backend for asynchronous deletion.
table_definition_cache
400 - 524288
Number of table definitions that can be stored in the definition cache. If a large number of tables are used, a large table definition cache can be created to speed up table opening. Unlike the normal table cache, the table definition cache occupies less space and does not use file descriptors.
table_open_cache
1 - 524288
Number of open tables across all threads. Increasing this value will increase the number of file descriptors required by mysqld.
Creating a Parameter Template
You can create a parameter template, modify the parameter values, and apply the template to instances.
1. Log in to the TencentDB for MySQL console, select Parameter Templates on the left sidebar, and click Create Template.
2. In the pop-up dialog box, specify the following configurations, and click Create and Set Parameters.
Parameter
Description
Template name
Enter a name for the parameter template. It cannot exceed 60 characters and supports only digits, uppercase and lowercase letters, Chinese characters, and special characters: _-./()（）[]+=：:@. The parameter template name should be unique.
Database version
Select a database version. After selection, the parameter template can only apply to instances with the same database version.
Engine
Select a database version. After selection, the parameter template can only apply to instances with the same engine.
Referenced Template
Select a default template as the original one for creating a parameter template. After creation, you can perform parameter adjustment based on this original template.
Template Description
Enter a brief explanation for the created parameter template. This item is optional.
For the above configuration items, the optional engines and original template types corresponding to different database versions are as follows.
Database Version
Engine
Original Template Type
MySQL 5.5
InnoDB
High-stability parameter template
LibraDB
MySQL 5.6
InnoDB
High-stability parameter template
High-performance template
LibraDB
MySQL 5.7
or
MySQL 8.0
InnoDB
High-stability parameter template
High-performance template
LibraDB
RocksDB
Default parameter template
Note:
The original template type of the RocksDB engine does not support the high-stability or high-performance template. Only the default parameter template provided by the system can be selected.
3. After creation, you will automatically be redirected to the details page of parameter template creation. You can perform operations such as modifying, importing, or exporting parameters on the parameter template details page.
2. In the parameter template list, locate the desired template and click Apply to Instance in the Operation column.
3. On the pop-up page, complete the following configurations, and click Submit.
Parameter
Description
Execution Method
The default value is immediate execution. If you set it to during the maintenance period, the parameter change tasks for the selected instances are executed and take effect during the maintenance period of the instances.
Region
Select the region where the instance is located.
MySQL Instance
Select an instance. Filtering instances quickly based on instance ID/name is supported. In addition, selecting a single, multiple, or all instances is supported.
Note:
When a parameter template is applied to multiple instances, confirm whether the template parameters are suitable for these instances. Proceed with caution.
Parameter comparison
Under this item, you can preview the parameter comparison details before and after the parameter template is applied to the selected instances. This item can only be displayed after instances are selected.
Note:
You can enable the preview only modified parameters feature. Once enabled, only modified parameters are displayed.
If the parameter value is null, it will be marked with NULL.
If an instance does not support setting parameters in the corresponding parameter template, the parameter value is marked as (N/A) and changes are ignored.
If parameter comparison results are marked in orange, it indicates that applying template parameters will restart the database instances.
Copying a Parameter Template
To include most of the custom parameters and values of an existing parameter template in a new template, you can copy the existing template.
Method 1. Copying an existing parameter template
1. Log in to the MySQL console, choose the Parameter Template page in the left sidebar, and click the TemplateID or View Details in the Operation column to go to the template details page.
2. On the template details page, click Save as Template.
3. In the pop-up dialog box, specify the following configurations:
Template Name: enter a unique template name.
Template Description: enter a brief description of the parameter template.
4. After confirming that everything is correct, click Save.
Method 2. Saving parameters of an instance as a parameter template
1. Log in to the MySQL console, choose the Instance List page in the left sidebar, and click the Instance ID to go to the management page.
2. Choose the Database Management > Parameter Setting page.
3. Click
next to the default template, and then click Save as Template.
4. In the pop-up dialog box, specify the following configurations:
Template Name: enter a unique template name.
Template Description: enter a brief description of the parameter template.
5. After confirming that everything is correct, click Create and Save.
Modifying Parameter Values in a Parameter Template
1. Log in to the MySQL console, choose the Parameter Template page in the left sidebar, and click the Template ID to go to the template details page.
2. On the template details page, click Batch Modify Parameters, or click the edit icon in the Current Parameter Value column to modify the parameter values.
Importing a Local Parameter Template File to Overwrite the Current Parameter Template
1. Log in to the MySQL console, choose the Parameter Template page in the left sidebar, and click the Template ID to go to the template details page.
2. Click Import Parameters.
3. In the pop-up dialog box, select a file to upload and click Import and Overwrite Original Parameters.
Note：
Select a file in .cnf format with a size of no more than 20 KB. If the format does not match, it leads to import failures.
