Optimizing Database Performance

This section describes how to optimize database performance based on specific suggestions and use cases.

Optimizing Database Parameters
Database parameters should be designed based on specific workload needs. Huawei Cloud provides default parameters for all its database cloud services to meet the most common workload needs.

Huawei Cloud provides multiple database services. The optimization methods used and precautions taken depend on the services. You are advised to use Huawei Cloud professional services to optimize your database parameters.
Monitoring Performance Metrics
Performance monitoring helps you keep appraised of application and system loads and resource usages in real time. You can set alarm rules to automatically generate alarms for abnormal load, so that you can better use and maintain your cloud database system. Take GeminiDB as an example. You can check GeminiDB Redis API metrics on the console.
Setting Data Partitions
GaussDB supports range partitioned tables, list partitioned tables, and hash partitioned tables. Partitioned tables can improve database query performance, enhance availability, facilitate maintenance, and balance I/O.

To convert an ordinary table to a partitioned table, you need to create a partitioned table and import data to it from the ordinary table. When you design tables, plan whether to use partitioned tables based on workload requirements.
Rewriting GaussDB SQL Statements
SQL statement rewriting allows databases to execute SQL statements faster while still ensuring accurate results. Adhering to specific rules, such as using UNION ALL instead of UNION, adding not null to the join columns, and converting NOT IN to NOT EXISTS, can significantly enhance query efficiency.
Tuning GaussDB Statement Pushdown
Currently, there are three ways the GaussDB optimizer can develop statement execution policies in a distributed framework: generating a statement pushdown plan, a distributed execution plan, or a distributed execution plan for submitting statements. The third method sends many intermediate results from DNs to the CN for further execution. In this case, the CN performance bottleneck (in bandwidth, storage, and compute) is caused by statements that cannot be pushed down to DNs. So, you are not advised to use the query statements where only the third method applies.

Statements cannot be pushed down if they have functions that do not support pushdown or syntax that does not support pushdown.

In this case, you can rewrite these statements.

Typical scenarios for statement pushdown include single-table query pushdown and multi-table query pushdown. In some special scenarios, for example, a statement containing the with recursive clause or a column-store table cannot be pushed down.
Tuning GaussDB Subqueries
When an application runs a SQL statement to query the database, a large number of subqueries are used because they are more clear than a table join. Especially in complex query statements, subqueries have more complete and independent semantics, which makes SQL statements clearer and easy to understand. In GaussDB, subqueries are classified as subqueries and sublinks depending on where they are located in the SQL statement.
Tuning GaussDB Operators
A query goes through many steps to produce its final results. Often, the whole query slows down because just one of the steps takes too long. This slow step is called a bottleneck. To fix this, you can run the EXPLAIN ANALYZE or PERFORMANCE command to find the bottleneck.
Enabling Read/Write Splitting for a TaurusDB Instance
Read/write splitting enables read and write requests to be automatically routed through a proxy address.

You can create a proxy instance after a TaurusDB instance is created. Using the IP address of the proxy instance, write requests are automatically routed to the primary node and read requests are routed to read replicas and the primary node by user-defined weights or active connections.

When creating a proxy instance, you need to select the nodes (including the primary node and read replicas) to be associated with the proxy instance.

For more information, see Enabling Read/Write Splitting.
What Should I Do If Locks on Long Transactions Block the Execution of Subsequent Transactions in TaurusDB?
Kill the long transactions.
How Do I Handle a Large Number of Temporary Tables Being Generated for Long Transactions and High Memory Usage in TaurusDB?
Upgrade the instance specifications to keep the memory usage within a reasonable range, preventing a sudden increase in traffic from causing an OOM crash. Alternatively, optimize slow SQL statements as needed.
How Do I Handle Slow SQL Statements Caused by Inappropriate Composite Index Settings in TaurusDB?
If the slow query was caused by insufficient CPU resources, upgrade the specifications. If it was caused by poor table design, missing indexes, or incorrect index settings, tune the statements.
Using INSTANT to Add Columns Quickly in TaurusDB
TaurusDB is compatible with open-source MySQL 8.0.22, so you can use ALGORITHM=INSTANT to quickly add columns, preventing lock waiting from affecting workloads or SQL statement execution timeout.
Using DAS Data Diagnosis
Intelligent O&M checks instance health using operational data analytics and intelligent algorithms and provides diagnosis results and suggestions.

Monitoring Performance Metrics

The following uses GaussDB as an example.

**Table 1** Performance metrics
Metric ID	Metric Name	Description
rds001_cpu_util	CPU Usage	CPU usage of the monitored object
rds002_mem_util	Memory Usage	Memory usage of the monitored object
rds003_bytes_in	Data Write Volume	Average number of bytes sent by the VM of the monitored object in a measurement period
rds004_bytes_out	Outgoing Data Volume	Average number of bytes received by the VM of the monitored object in a measurement period
iops_usage	IOPS Usage	Percentage of IOPS capacity in use.
rds007_instance_disk_usage	Instance Disk Usage	Real-time data disk usage of the monitored instance
rds010_disk_usage	Disk Usage	Real-time data disk usage of the monitored node