PostgreSQL pg_stat_statements TPCC性能测试

1 背景知识

本文主要使用 BenchMarksql 6.0 对 PostgreSQL 数据库进行 TPC-C 基准测试，并使用 pg_stat_statements 扩展记录统计与信息。

2 BenchMarksql 安装与配置

BenchMarksql 是使用 JAVA 语言实现的，底层使用 JDBC 驱动对数据库进行压力测试。此工具使用模拟客户端模拟：查询、多线程组、多用户和应用线程等操作。

本文主要使用 BenchMarksql 6.0 对 PostgreSQL 数据库进行性能测试，并通过 pg_stat_statements 视图 查看相统计信息。

请完成下面两个步骤。

1. BenchMarksql 6.0 安装与部署。
2. Benchmarksql 6.0 性能测试。

3 清空统计信息

psql -U postgres -d testdb
#postgres> 
SELECT pg_stat_statements_reset();

4 基准测试

1. 使用命令行进行基准测试。

su - postgres 
cd /soft/benchmarksql/target/run
./runBenchmark.sh my.properties

也可以使用 WEB 运行基准测试，选择其一即可。

5 查看性能视图

完成 Benchmarksql 基准测试 之后。请查询查询 pg_stat_statements 视图 ，确定存在的性能瓶颈和性能优化策略方案。

#postgres>
psql -U postgres -d testdb

5.1 查看底层操作系统概览数据

--testdb#
SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;

testdb=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query           | INSERT INTO bmsql_order_line (    ol_o_id, ol_d_id, ol_w_id, ol_number,     ol_i_id, ol_supply_w_id, ol_quantity,     ol_amount, ol_dist_info) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
calls           | 33689
total_exec_time | 2273.0916489999877
rows            | 33689
hit_percent     | 99.9570329500106957
-[ RECORD 2 ]---+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query           | SELECT no_o_id     FROM bmsql_new_order     WHERE no_w_id = $1 AND no_d_id = $2     ORDER BY no_o_id ASC
calls           | 3520
total_exec_time | 1593.8260069999978
rows            | 3164716
hit_percent     | 99.9041905431583784
-[ RECORD 3 ]---+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query           | SELECT o_id, o_entry_d, o_carrier_id     FROM bmsql_oorder     WHERE o_w_id = $1 AND o_d_id = $2 AND o_c_id = $3       AND o_id = (          SELECT max(o_id)               FROM bmsql_oorder               WHERE o_w_id = $4 AND o_d_id = $5 AND o_c_id = $6          )
calls           | 363
total_exec_time | 1297.6159310000016
rows            | 363
hit_percent     | 99.6216619680792026
-[ RECORD 4 ]---+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query           | SELECT s_quantity, s_data,        s_dist_01, s_dist_02, s_dist_03, s_dist_04,        s_dist_05, s_dist_06, s_dist_07, s_dist_08,        s_dist_09, s_dist_10     FROM bmsql_stock     WHERE s_w_id = $1 AND s_i_id = $2     FOR UPDATE
calls           | 33689
total_exec_time | 1146.3766930000077
rows            | 33689
hit_percent     | 89.2813717672474735
-[ RECORD 5 ]---+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query           | SELECT count(*) AS low_stock FROM (    SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = $1 AND s_quantity < $2 AND s_i_id IN (            SELECT ol_i_id                 FROM bmsql_district                 JOIN bmsql_order_line ON ol_w_id = d_w_id                  AND ol_d_id = d_id                  AND ol_o_id >= d_next_o_id - $5                  AND ol_o_id < d_next_o_id                 WHERE d_w_id = $3 AND d_id = $4         )     ) AS L
calls           | 322
total_exec_time | 792.846921
rows            | 322
hit_percent     | 92.8665745666592885

5.2 输出说明

从 pg_stat_statements 视图 中查询 执行时间 消耗最多的前五的SQL 语句。

6 参考连接

更多信息请参考 PostgreSQL BenchMarksql 6.0 性能测试章节。

PostgreSQL: Documentation: 16: F.32. pg_stat_statements — track statistics of SQL planning and execution