What is a high-performance parameter template (8.0) - PolarDB

PolarDB for MySQL provides the high-performance parameter template feature. This topic describes the parameter settings for a high-performance parameter template of PolarDB for MySQL and how to enable the high-performance parameter template feature. This topic also provides a comparison of performance before and after the feature is enabled.

Overview

PolarDB has a large number of parameters, which can lead to complexity in parameter configurations for specific scenarios. To help simplify database optimization, PolarDB provides the high-performance parameter template feature. You can use a template out of the box by directly applying the template to an existing cluster, or implement deeper optimizations on top of the parameter template. In most cases, high-performance parameter templates help improve the performance of databases.

A high-performance parameter template of PolarDB for MySQL includes the parameters described in the following table.

Table 1. Parameters in the high-performance parameter template of PolarDB for MySQL 5.6
Parameter	Description	Value in the high-performance parameter template	Default value
innodb_flush_log_at_trx_commit	Specifies the disk write policy of the database. If you set this parameter to 0, data is written to and refreshed in the database once per second.	0	1
query_cache_type	Specifies whether to enable the fast query cache feature. For more information, see Fast Query Cache. This feature improves the query performance of the database.	1	0

Table 2. Parameters in the high-performance parameter template of PolarDB for MySQL 5.7
Parameter	Description	Value in the high-performance parameter template	Default value
innodb_flush_log_at_trx_commit	Specifies the disk write policy of the database. If you set this parameter to 0, data is written to and refreshed in the database once per second.	0	1
loose_innodb_primary_purge_max_id_lag	If this parameter is set to 18446744073709551104, the limits on the primary node due to the read views on read-only nodes are removed.	18446744073709551104	N/A

Table 3. Parameters in the high-performance parameter template of PolarDB for MySQL 8.0
Parameter	Description	Value in the high-performance parameter template	Default value
innodb_flush_log_at_trx_commit	Specifies the disk write policy of the database. If you set this parameter to 0, data is written to and refreshed in the database once per second.	0	1
loose_innodb_lock_sys_rec_partition	The number of shards for the transaction lock. InnoDB uses shards to manage transaction locks, including row locks and table locks. This parameter reduces the competition overhead introduced by transaction lock management.	64	1
loose_query_cache_type	Specifies whether to enable the fast query cache feature. For more information, see Fast Query Cache. This feature improves the query performance of the database.	ON	OFF

Limits

This feature is available in PolarDB Standard Edition. A PolarDB for MySQL cluster must run one of the following versions to support this feature:

A PolarDB for MySQL 5.6 cluster whose revision version is 5.6.1.0.30 or later.
A PolarDB for MySQL 5.7 cluster whose revision version is 5.7.1.0.11 or later.
A PolarDB for MySQL 8.0.1 cluster whose revision version is 8.0.1.1.21 or later.
A PolarDB for MySQL 8.0.2 cluster whose revision version is 8.0.2.2.6.1 or later.

For information about how to view the version of your cluster, see Query the engine version.

Scenarios and risks

In most cases, high-performance parameter templates help improve the performance of databases. However, after a high-performance template is applied, its pursuit for performance may pose risks to databases:

Data durability is deteriorated. If the database crashes, the data that is not written to the disk within one second may be lost because the innodb_flush_log_at_trx_commit parameter is set to 0.
The latency of primary/secondary replication may increase. The latency of primary/secondary physical replication may be one second because the innodb_flush_log_at_trx_commit parameter is set to 0.

Note

To prevent the preceding risks, you can reset the innodb_flush_log_at_trx_commit parameter to 1.

If your business can tolerate the trade-off between database performance and durability, you can apply the high-performance parameter template to improve database performance.

Important

Before you apply a high-performance parameter template, we recommend that you submit a ticket first to the Alibaba Cloud technical team for consultation.

Apply a high-performance parameter template

You can use one of the following methods to apply a high-performance parameter template to your cluster.

Warning

After you apply the template, you must restart the database for the template to take effect. During the restart, some intermittent service interruptions may occur. We recommend that you modify parameters during off-peak hours and make sure that your application is configured to automatically reconnect to your cluster.

Method 1:

Log on to the PolarDB console.
In the upper-left corner of the console, select the region where the cluster is deployed.
In the left-side navigation pane, click Parameter Templates.
On the Parameter Templates page, click System Parameter Templates.
Select the mysql_innodb_5.6_standard_high, mysql_innodb_5.7_standard_high, or mysql_innodb_8.0_standard_high template and click Apply to Instance in the Actions column.
In the Apply to Instance panel, select the cluster to which you want to apply the template and click > to add the cluster to the Selected Instances list.
After you select a cluster, you can view the differences between the current values of the cluster parameters and the values in the high-performance parameter template in the Parameter Comparison section.
Click OK.
Note
After you apply the template, you must restart the database for the template to take effect.

Method 2:

Log on to the PolarDB console.
In the upper-left corner of the console, select the region where the cluster is deployed.
Find the cluster and click the cluster ID.
In the left-side navigation pane, choose Settings and Management > Parameters.
Click Apply Template.
On the Apply Template page, select the high-performance parameter template from the Template Name drop-down list.
After you select a template, you can view the differences between the current values of the cluster parameters and the values in the high-performance parameter template in the Parameter Comparison section.
Click OK.
Note
After you apply the template, you must restart the database for the template to take effect.

Performance comparison

This section compares the performance between the default parameters and the high-performance parameters on a PolarDB for MySQL 8.0 cluster. The Sysbench and TPC-C benchmark suites are used to test the cluster performance before and after the high-performance parameter template is used.

Note

Sysbench is a modular, cross-platform, and multi-threaded benchmark tool that can be used to evaluate the performance of a heavily loaded database system based on core metrics.
TPC-C is a benchmark that is widely used to evaluate the online transaction processing (OLTP) capabilities of databases. TPC-C is developed and released by Transaction Processing Performance Council (TPC).
The TPC-C performance tests described in this topic are implemented based on the TPC-C benchmark test but do not meet all requirements of the TPC-C benchmark test. The test results described in this topic cannot be compared with the published results of the TPC-C benchmark test.

Test environment:
- PolarDB for MySQL cluster:
  - Cluster specification: 88 cores and 720 GB of memory.
  - Revision version: 8.0.1.1.21 or later.
- Stress test environment:
  - The latency between the ECS instance used for the stress test and the PolarDB cluster is approximately 1 ms.
  - The ECS instance used for the stress test has sufficient computing and network resources.
Sysbench test:
- Test sets: read_write and write_only.
- Raw data volume: 25 tables × 25,000 rows of data.
- Performance metric – queries per second (QPS): the number of SQL statements that are executed per second in the database when 1, 8, 16, 32, 64, 128, 256, 512, and 1,024 concurrent requests are made. The SQL statements include INSERT, SELECT, UPDATE, and DELETE statements.
TPC-C test:
- Test tool: TPCC-MySQL.
- Raw data volume: 1,000 warehouses.
- Performance metric – transactions per minute (TPM): the number of transactions that are committed per minute in the database when 1, 8, 16, 32, 64, 128, 256, 512, and 1,024 concurrent requests are made. This test can measure the maximum qualified throughput (MQTh) of the database.
Test results:
- Sysbench test
- TPC-C test
Test conclusions: The preceding Sysbench and TPC-C test results show that the high-performance parameter template improves the performance of the cluster under intensive workloads. With 256 or higher concurrent requests, the high-performance parameter template improves the performance of the cluster by twice or more.