Cong LiIn today's data-driven era, optimizing database performance is key for enterprises to enhance their competitiveness. This article will explore how to perform performance tuning on the GBase 8c database based on the TPCH (Transaction Processing Performance Council Benchmark H) model, providing readers with a scientifically rigorous, insightful, and well-organized tuning method.
I. Introduction to the TPCH Model
What is TPCH?
TPC-H, established by the Transaction Processing Performance Council (TPC) in 1994, is a standard benchmark designed for decision support systems in retail business scenarios. It is a tool to measure the capabilities of OLAP/HTAP databases and is based on a typical snowflake schema, consisting of 8 tables and 22 queries, adhering to the SQL92 standard.
The size of the data volume directly impacts query performance. TPC-H uses SF (Scale Factor) to describe data volume, where 1 SF corresponds to 1GB. The SF values increase in the following order: 1, 10, 30, 100, 300, 1000, 3000, 10000. 1 SF refers to the total data volume of the 8 tables, excluding indexes.
Table 1. TPCH 22 SQL Interpretations
| SQL | Name | Business Context | Statement Characteristics | Standard Requirements |
|---|---|---|---|---|
| Q1 | Price Statistics Report Query | This is a pricing summary report querying the lineItems. It involves statistical analysis of various types of goods over a specific period, such as billed, shipped, discounted, and taxed items, including information on business volume billing, shipping, discount, tax, and average price. | A single table query with grouping, sorting, and aggregation operations. This query can result in reading 95% to 97% of the rows in the table. | |
| Q2 | Minimum Cost Supplier Query | Finds the supplier with the minimum cost. It identifies which supplier within a given region can supply a specified part (of a particular type and size) at the lowest price, allowing for decision-making in order placement. | A multi-table query with sorting, aggregation, and subquery operations. | The query does not syntactically limit the number of tuples returned, but TPC-H standards require that only the top 100 rows be returned (usually implemented by the application). |
| Q3 | Shipping Priority Query | Retrieves the top 10 revenue-generating orders that have not yet been shipped. It provides the shipping priorities and potential revenues (sum of l_extendedprice * (1-l_discount)) of the highest revenue orders not shipped before a given date. | A three-table query with grouping, sorting, and aggregation operations. | The query does not syntactically limit the number of tuples returned, but TPC-H standards require that only the top 10 rows be returned (usually implemented by the application). |
| Q4 | Order Priority Query | Calculates the number of orders within a given three-month period, where each order has at least one line item received by the customer after its commit date. | A single-table query with grouping, sorting, aggregation, and correlated subquery operations. | |
| Q5 | Revenue from a Specific Region Supplier Query | Retrieves the revenue statistics (calculated as sum(l_extendedprice * (1 - l_discount))) generated by suppliers from a specific region. This can be used to decide whether to establish a local distribution center in the given area. | A multi-table join query with grouping, sorting, aggregation, and subquery operations. | |
| Q6 | Revenue Change Prediction Query | Obtains the incremental revenue generated by changing discounts over a year. It is a typical "what-if" analysis used to find ways to increase revenue. The query considers all shipped orders in a specified year with discounts between "DISCOUNT-0.01" and "DISCOUNT+0.01" and calculates the total revenue increase by eliminating discounts on orders with l_quantity less than a specified quantity. | A single-table query with aggregation operations, using the BETWEEN-AND operator, which can be optimized in some databases. | |
| Q7 | Shipping Profit Query | Retrieves the profit from sales between the supplier's country and the country where goods are sold. This query determines the volume of goods shipped between two countries to aid in renegotiating shipping contracts. | A multi-table query with grouping, sorting, aggregation, and subquery operations. The parent query does not have other query objects, making the subquery format relatively simple. | |
| Q8 | National Market Share Query | Retrieves changes in the market share of a given part type in a specific country's region over the past two years. | A query with grouping, sorting, aggregation, and subquery operations. The parent query has no other query objects, making it relatively simple. However, the subquery itself is a multi-table join query. | |
| Q9 | Product Type Profit Estimation Query | Retrieves the total profit for all parts ordered per country per year. | A query with grouping, sorting, aggregation, and subquery operations. The parent query has no other query objects, making it relatively simple. However, the subquery itself is a multi-table join query. The subquery uses the LIKE operator, which some query optimizers may not support for optimization. | |
| Q10 | Problematic Shipping Query | Retrieves customers and losses associated with problematic shipments within a country over a three-month period. | A multi-table join query with grouping, sorting, and aggregation operations. | The query does not syntactically limit the number of tuples returned, but TPC-H standards require that only the top 10 rows be returned (usually implemented by the application). |
| Q11 | Inventory Value Query | Retrieves the value of parts supplied by a particular country in the inventory. | A multi-table join query with grouping, sorting, aggregation, and subquery operations. The subquery is in the HAVING condition of the grouping operation. | |
| Q12 | Shipping Mode and Order Priority Query | Obtains shipping modes and order priorities. It helps decide whether selecting cheaper shipping modes will result in more deliveries after the contract date, negatively impacting urgent orders. | A two-table join query with grouping, sorting, and aggregation operations. | |
| Q13 | Customer Order Quantity Query | Retrieves the order quantity for customers, including those who have no past or present order records. | A query with grouping, sorting, aggregation, subquery, and left outer join operations. | The TPC-H standard defines equivalent variations of the Q13 statement. The transformed SQL converts the subquery portion into a view, and then the view is queried, as some databases do not support. |
| Q14 | Promotion Effect Query | Retrieves the percentage of revenue from promotional parts in a given month. It helps monitor the market response to promotions. | A query with grouping, sorting, aggregation, subquery, and left outer join operations. | The TPC-H standard defines an equivalent variant of the Q14 statement, which is structurally similar to the above query, with different expressions for target columns. |
| Q15 | Top Supplier Query | Retrieves information about the top revenue-contributing supplier (ranked first) within a specific period. This can be used to decide which top suppliers to reward, give more orders, special certification, encouragement, or incentives. | A join query between regular tables and views with grouping, sorting, aggregation, and aggregate subquery operations. | The TPC-H standard defines an equivalent variation of the Q15 statement. Unlike the above query statement, which first defines a view and then joins the table and view, the transformed SQL defines a WITH statement, then joins the WITH object and the table. |
| Q16 | Query for Suppliers of Parts with Specific Attributes | Obtains the number of suppliers who can supply parts under specified conditions. This can be used to determine whether there are enough suppliers when order volumes are high and tasks are urgent. | A two-table join query with grouping, sorting, aggregation, deduplication, and NOT IN subquery operations. | |
| Q17 | Small Order Revenue Query | Retrieves small batch orders lower than 20% of the average supply volume. It determines the average item count for parts of a specified brand and packaging type in all orders over seven years (past and pending). It calculates the annual revenue loss if small orders are no longer accepted. This can be used to estimate the average annual revenue loss without small orders, as bulk shipments will reduce administrative costs. | A two-table join query with aggregation and aggregate subquery operations. | |
| Q18 | Large Order Customer Query | Retrieves supplier information for orders larger than a specified supply quantity. This can be used to verify if there are enough suppliers when order volumes are high and tasks are urgent. | A three-table join query with grouping, sorting, aggregation, and IN subquery operations. | The query does not syntactically limit the number of tuples returned, but TPC-H standards require that only the top 100 rows be returned (usually implemented by the application). |
| Q19 | Discount Revenue Query | Retrieves total discount revenue for all orders for three different types of parts transported by air or by hand, considering specific brand, packaging, and size ranges. This is an example of data mining tools generating formatted code. | A three-table join query with grouping, sorting, aggregation, and IN subquery operations. | |
| Q20 | Supplier Competitiveness Query | Identifies suppliers who can offer more competitive prices for a particular part within a specified year. A supplier is considered competitive if they have excess supply, with over 50% of a part's shipment to a specified country being supplied by that supplier in a given year. | A two-table join query with sorting, aggregation, IN subquery, and regular subquery operations. | |
| Q21 | Late Delivery Supplier Query | Retrieves suppliers who fail to deliver on time. | A four-table join query with grouping, sorting, aggregation, EXISTS subquery, and NOT EXISTS subquery operations. | The query does not syntactically limit the number of tuples returned, but TPC-H standards require that only the top 100 rows be returned (usually implemented by the application). |
| Q22 | Global Sales Opportunity Query | Retrieves the geographic distribution of potential consumer purchases. It calculates the number of consumers in specified countries who are more positive than average but have not placed orders in seven years, reflecting general consumer attitudes, i.e., purchase intentions. | A four-table join query with grouping, sorting, aggregation, EXISTS subquery, and NOT EXISTS subquery operations. |
II. Testing Process
GBase 8c, a multi-modal database, can use the TPCH model to measure its performance. Below is the TPCH testing process for the GBase 8c database.
1. Data Generation
Generate 100G data:
Compile dbgen:
cp makefile.suite makefile
make clean
make
Generate data:
./dbgen -s 100 &
2. Table Creation
Assume the database has been installed, deployed, and configured according to the TPCH business model.
gsql -p 5432 -d tpch -f tpch.sql
The tpch.sql script can be obtained from the official TPC website.
3. Load Data into the Database
For example, loading data into the GBase 8c distributed database:
sh load.sh /data1/tpch/dbgen/data100/supplier/ 5432 tpch nation
sh load.sh /data1/tpch/dbgen/data100/supplier/ 5432 tpch supplier
sh load.sh /data1/tpch/dbgen/data100/part/ 5432 tpch part
sh load.sh /data1/tpch/dbgen/data100/customer/ 5432 tpch customer
sh load.sh /data1/tpch/dbgen/data100/partsupp/ 5432 tpch partsupp
sh load.sh /data1/tpch/dbgen/data100/orders/ 5432 tpch orders
sh load.sh /data1/tpch/dbgen/data100/lineitem/ 5432 tpch lineitem
Sample load.sh script:
#!/bin/bash
# Parameter check
if [ "$#" -ne 3 ]; then
echo "Usage: $0 <data_directory> <port> <database_name>"
exit 1
fi
DATA_DIR=$1
PORT=$2
DB_NAME=$3
# Load data into the database
for table in nation supplier part customer partsupp orders lineitem; do
TABLE_FILE="${DATA_DIR}/${table}.tbl"
if [ -f "$TABLE_FILE" ]; then
echo "Loading ${table}..."
psql -h localhost -p $PORT -d $DB_NAME -c "\copy ${table} FROM '${TABLE_FILE}' WITH DELIMITER '|' NULL AS ''"
else
echo "File ${TABLE_FILE} not found."
fi
done
echo "Data loading completed."
4. Check Data Volume in Tables
| Table Name | Number of Records |
|---|---|
| customer | 15,000,000 |
| lineitem | 600,037,902 |
| nation | 25 |
| orders | 150,000,000 |
| part | 20,000,000 |
| partsupp | 80,000,000 |
| region | 5 |
| supplier | 1,000,000 |
5. Execute Performance Tests
gsql -p 5432 -d tpch -f tpch.sql
The total time obtained represents the TPCH performance metric. During execution, adjust parameters such as work_memory and cstore_buffers based on the execution plan and resource usage to achieve minimal response time.
III. Performance Tuning
Database performance directly affects application response time and user experience. Performance tuning based on the TPCH model for GBase 8c can significantly improve system processing capabilities and stability. The basic principles of tuning are as follows:
- Understand the System Architecture: Gain an in-depth understanding of GBase 8c components, including the storage engine and query optimizer.
- Data Modeling and Index Optimization: Design data models effectively and create indexes to accelerate queries.
- Hardware Resource Allocation: Allocate CPU, memory, and storage resources based on system load.
- SQL Optimization: Write efficient SQL statements to avoid unnecessary calculations and data access.
- Monitoring and Analysis: Continuously monitor system performance, analyze bottlenecks, and make targeted optimizations.
Tuning Steps:
- Baseline Testing: Conduct baseline performance tests on GBase 8c using the TPCH model.
- Problem Identification: Use monitoring tools to identify performance bottlenecks, such as CPU usage and I/O wait times.
- Parameter Adjustment: Adjust database configuration parameters, such as cache size and concurrency control, based on bottlenecks.
- Index Optimization: Analyze query patterns and create or adjust indexes to improve query efficiency.
- SQL Tuning: Optimize SQL statements to reduce resource consumption and execution time.
- Hardware Upgrades: Upgrade hardware as needed, such as adding more memory or using faster storage media.
- Repeat Testing: After adjustments, rerun TPCH tests to verify performance improvements.
Through performance tuning of the GBase database based on the TPCH model, we have not only improved system processing capabilities but also gained a deeper understanding of performance tuning principles and methods. As technology continues to advance, performance tuning will remain a critical aspect of database management.
