GBASE Financial Application Guide 9 | Performance Tuning

To help financial institutions select distributed analytical database products and promote successful experience in deploying and applying distributed analytical databases in the financial industry, GBASE preparedGBASE GBase 8a Financial Application Guideunder the guidance of the Beijing FinTech Industry Alliance. The Guide provides an in-depth introduction to the entire deployment process for distributed analytical databases, from selection and planning, development and design specifications, data security and high availability, through to operations and maintenance optimization, and presents representative deployment cases of GBase 8a MPP Cluster at national policy banks and major state-owned banks.

GBASE will successively launch a series of articles to share and interpret the contents of the Guide, with the hope of providing reference and assistance to financial users in database selection and supporting the efficient implementation and high-quality development of fintech. This is the ninth article in the series, introducing performance tuning for database systems.

Performance tuning runs through every stage of a project. The path to optimization begins with planning the logical system architecture, progressing step by step throughlogical system architecture design->data warehouse model design->data model design->SQL development->database parameter adjustment->business scheduling, with optimization at each level from macro to micro. The difficulty of optimization increases from easy to difficult, while the difficulty of adjustment decreases from difficult to easy.

1. Performance Analysis

Usually, after project design and development are completed, testing and trial use are conducted before formal go-live. During this process, cluster monitoring tools can be used to observe whether business execution performance meets requirements. If business SQL performance does not meet requirements, the key points for optimization can be identified by reviewing the execution plan and trace logs of the underperforming SQL statements. Based on these key points, parameter optimization, model tuning, and SQL optimization can be selected to improve performance.

2. Parameter Optimization

GBase 8a provides configuration parameters for each service, enabling users to flexibly tune the cluster based on business execution conditions. Observe the characteristics of the business SQL to be tuned, select a matching category from the parameter categories listed below based on these characteristics, browse the relevant parameter section in the GBase 8a Parameter Manual, and select appropriate parameters for adjustment and testing, such as:

SQL optimization parameters, SQL syntax control parameters, memory-related parameters, thread-related parameters, resource management-related parameters, parameters related to the GBase 8a operating mechanism, data transfer-related parameters, and more.

3. Model Tuning

Model tuning is required if the SQL to be tuned exhibits the following phenomena in the query plan or trace logs:

• The tables involved in the SQL contain large volumes of data that are unevenly distributed across cluster nodes, resulting in data skew

• The intermediate result set from table joins is excessively large

Model tuning includes

• Modifying table types (hash-distributed tables, randomly distributed tables, replicated tables)

• Reselecting hash distribution columns for hash-distributed tables

• Column-store-based wide-table design and preprocessing optimization, namely, preprocessing large-table joins into wide tables in advance, converting original queries based on multi-table joins of large tables into single-table queries based on preprocessed result tables

• Data optimization, loading sorted data into the database

• Data compression optimization, setting an appropriate compression mode

4. SQL Optimization

If the model is found to be reasonable after checking the SQL to be tuned, further SQL tuning can be performed. Generally, SQL tuning can be performed when the following phenomena appear in the query plan or trace logs:

• Numerous implicit data type conversions

• An unreasonable table join order

SQL tuning includes:

• Table structure optimization, such as data type adjustments

• Index optimization

• SQL statement rewriting optimization