openGauss 6.0 智能优化器与并发控制实践

openGauss 2024-12-23

148

引言

自openGauss社区联合Gauss松鼠会、墨天轮社区共同举办第八届openGauss技术文章征集活动以来，我作为openGauss的忠实用户，一直积极参与其中，分享我的使用心得和技术实践。今天，我想借此机会，与大家分享我与openGauss的故事，以及我在使用过程中的一些技术见解和实践经验。

随着大数据时代的到来，数据库性能和并发处理能力成为了企业和开发者关注的焦点。openGauss 6.0 版本引入了智能优化器，显著提升了数据库的性能和并发处理能力。本文将详细介绍如何利用 openGauss 6.0 的智能优化器进行性能调优和并发控制，帮助读者在实际应用中充分发挥数据库的潜力。

openGauss，作为一款由华为开源的关系型数据库管理系统，自诞生之日起就承载着自主可控与技术创新的重大使命。它基于PostgreSQL研发，专为OLTP场景优化，提供了面向多核架构的极致性能、全链路的业务数据安全、基于AI的调优和高效运维的能力。这些特性使得openGauss在众多开源数据库中脱颖而出，成为了我技术探索的首选目标。

一、智能优化器：性能提升的利器

openGauss的智能优化器是其性能卓越的关键所在。它采用了基于代价的优化策略（Cost-Based Optimization, CBO），能够根据查询语句的复杂度、数据分布、索引情况等多种因素，生成最优的执行计划。

1、创建表测试表

使用SQL语句中的CREATE TABLE命令可以在数据库中创建新表。以下是一个示例：
table_name：要创建的表的名称。
column1, column2, column3等：表中的列名。
datatype：每列的数据类型。
constraint：对列中的数据施加的额外规则，如非空、唯一性、主键等。
[table_constraint]：可选的表级别约束，如主键约束、外键约束等。

openGauss=# CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,        -- 员工ID，主键
    Name VARCHAR(100) NOT NULL,        -- 姓名，非空
    Position VARCHAR(50),              -- 职位
    HireDate DATE,                     -- 入职日期
    SalaropenGauss(# openGauss(# openGauss(# openGauss(# openGauss(# y DECIMAL(10, 2),             -- 薪水
    -- 假设我们有一个表级约束，比如一个唯一约束在Name列上（尽管这通常应该在列级定义）
    CONSTRAINT UniqueName UNIQUE (Name) -- 这是一个表级约束的示例，但实际上Name的唯一性通常直接在列级定义
);openGauss(# openGauss(# openGauss(# 
NOTICE:  CREATE TABLE  PRIMARY KEY will create implicit index "employees_pkey" for table "employees"
NOTICE:  CREATE TABLE  UNIQUE will create implicit index "uniquename" for table "employees"
CREATE TABLE
openGauss=#

2、查看查询计划

在执行查询前，我们可以通过EXPLAIN
命令查看查询的执行计划，了解优化器如何规划查询。

openGauss=# EXPLAIN SELECT * FROM Employees WHERE EmployeeID = 123;
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 [Bypass]
 Index Scan using employees_pkey on employees  (cost=0.00..8.27 rows=1 width=364)
   Index Cond: (employeeid = 123)
(3 rows)


openGauss=#

– - 使用列名进行查询（假设您想查询员工ID为某个值的记录）

这条命令将显示查询的执行计划，包括扫描方式、索引使用情况等，帮助我们分析查询性能。
请注意，已经移除了CONSTRAINT UniqueName UNIQUE (Name)，因为Name列已经在列级定义了UNIQUE约束（尽管在原始示例中这是作为表级约束给出的，但通常我们会在列级直接定义唯一性约束，除非有特殊的复合唯一性需求）。同时，在EXPLAIN语句中，我使用了EmployeeID作为查询条件，因为这是表中实际存在的列名。
执行修正后的EXPLAIN语句，并查看查询计划。

3、统计信息收集

优化器依赖统计信息来做出决策。因此，定期收集统计信息对于保持优化器的准确性至关重要。
在openGauss数据库中，ANALYZE命令用于收集表中数据的统计信息，这些信息对于查询优化器来说是至关重要的，因为它依赖于这些统计信息来选择最优的查询执行计划。

当您执行ANALYZE Employees;命令时，openGauss会扫描Employees表中的数据，并收集关于数据分布、列值频率等的统计信息。这些信息被存储在系统表中，供查询优化器在后续查询中使用。
由于ANALYZE命令通常是一个快速且非阻塞的操作（尽管它可能会锁定表的一些元数据以进行更新），因此您看到的输出非常简洁，只是简单地确认了命令的执行：

openGauss=# ANALYZE Employees;
ANALYZE
openGauss=#

这表示ANALYZE命令已经成功执行，并且没有遇到任何错误。现在，查询优化器可以使用更新后的统计信息来更有效地处理针对Employees表的查询。

这条命令将收集Employees
的统计信息，包括行数、列值的分布等，为优化器提供决策依据。

4、查询优化示例

假设我们有一个复杂的查询，涉及多个表的连接和过滤条件。通过调整查询语句的结构，如使用子查询、联合查询等，并结合索引的使用，我们可以显著提升查询性能。
在openGauss数据库中，查询优化通常涉及对SQL查询语句的重写或使用索引等策略，以提高查询性能。下面，我将通过一个简单的示例来展示如何对针对Employees表的查询进行优化。

4.1 原始查询命令

假设我们有一个Employees表，并且我们想要查询所有职位为"Manager"的员工姓名和薪水。原始查询命令可能如下所示：

openGauss=# 
openGauss=# SELECT Name, Salary
FROM Employees
WHERE Position = 'Manager';openGauss-# openGauss-# 
 name | salary 
------+--------
(0 rows)


openGauss=#

优化前的准备工作
在进行优化之前，我们需要确保以下几点：
统计信息是最新的：执行ANALYZE Employees;以确保Employees表的统计信息是最新的。
检查索引：查看Employees表上是否有针对Position列的索引。如果没有，则可能需要创建一个索引来提高查询性能。

4.2 创建索引（如果尚未创建）

如果Employees表上还没有针对Position列的索引，我们可以通过以下命令创建一个索引：

openGauss=# 
openGauss=# CREATE INDEX idx_position ON Employees(Position);
WARNING:  Session unused timeout.
FATAL:  terminating connection due to administrator command
could not send data to server: Broken pipe
The connection to the server was lost. Attempting reset: Succeeded.
openGauss=#

这个索引将帮助数据库更快地定位到Position列中值为"Manager"的行。

4.3 优化后的查询命令

在创建了索引之后，我们实际上不需要修改原始的查询命令，因为数据库查询优化器会自动利用可用的索引来优化查询。但是，从技术和实践的角度来看，我们可以说“优化后的查询”是那些能够利用索引和其他数据库特性的查询。

因此，优化后的查询命令在语法上与原始查询命令相同，但性能可能会更好，因为数据库现在可以使用索引来加速查询：

openGauss=# 
openGauss=# SELECT Name, Salary
FROM Employees
WHERE Position = 'Manager';openGauss-# openGauss-# 
 name | salary 
------+--------
(0 rows)


openGauss=#

4.4 验证优化效果

要验证优化效果，我们可以使用EXPLAIN命令来查看查询计划。在执行优化后的查询之前，我们可以使用以下命令来查看查询计划：

openGauss=# 
openGauss=# EXPLAIN SELECT Name, Salary FROM Employees WHERE Position = 'Manager';
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on employees  (cost=0.00..12.59 rows=1 width=234)
   Filter: (("position")::text = 'Manager'::text)
(2 rows)


openGauss=#

如果索引被正确使用，查询计划应该会显示索引扫描而不是全表扫描。

注意事项
索引的选择：不是所有的列都适合创建索引。索引会占用额外的存储空间，并且在插入、更新和删除操作时可能会增加额外的开销。因此，应该根据查询模式和性能需求来选择要索引的列。
统计信息的准确性：确保统计信息是准确的非常重要。过时的统计信息可能会导致查询优化器做出不佳的决策。
查询优化器的智能：现代数据库查询优化器非常智能，能够自动利用索引和其他数据库特性来优化查询。但是，了解查询优化器的工作原理和如何编写高效的SQL查询仍然是非常重要的。
综上所述，虽然在这个简单的示例中优化后的查询命令在语法上与原始查询命令相同，但通过创建索引和确保统计信息是最新的，我们可以显著提高查询性能。

通过拆分查询并使用子查询，我们可以减少不必要的数据扫描，提高查询效率。

二、并发调优：保障数据一致性与性能

在并发环境下，数据库需要处理多个事务同时访问同一数据资源的情况。openGauss通过其高效的并发控制机制，确保了数据的一致性和系统的性能。

1、事务隔离级别设置

openGauss支持多种事务隔离级别，如读未提交、读已提交、可重复读和序列化。根据业务需求，我们可以选择合适的事务隔离级别来平衡数据一致性和性能。
在openGauss数据库中，事务隔离级别是确保数据一致性和性能平衡的关键因素。openGauss支持四种标准的事务隔离级别，每种级别提供了不同程度的数据一致性和并发性。以下是对这四种隔离级别的简要说明，以及如何通过SQL语句设置特定的事务隔离级别：

2、读未提交（READ UNCOMMITTED）：

在这种隔离级别下，一个事务可以读取另一个事务尚未提交的数据（脏读）。
可能会导致数据不一致，但在某些情况下可以提高性能。
openGauss默认不支持此隔离级别，因为它违反了事务的基本ACID属性。

3、读已提交（READ COMMITTED）：

一个事务只能读取其他事务已经提交的数据（避免脏读）。
这是一个常用的隔离级别，因为它提供了较好的数据一致性和合理的性能。
在openGauss中，可以通过以下SQL设置：
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

openGauss=# 
openGauss=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET
openGauss=#

4、可重复读（REPEATABLE READ）：

在一个事务内，无论其他事务是否提交了新的数据修改，当前事务总是读取到它开始时看到的相同数据（避免不可重复读）。
提供了比READ COMMITTED更严格的数据一致性，但可能会牺牲一些并发性能。
在openGauss中，可以通过以下SQL设置：

openGauss=# 
openGauss=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
openGauss=#

5、序列化（SERIALIZABLE）：

提供了最高级别的事务隔离，确保事务之间的完全隔离，仿佛它们是串行执行的。
通过锁定和检测冲突来防止幻读和其他并发问题。
可能会显著降低并发性能，但在需要最高数据一致性的场景下非常有用。
在openGauss中，可以通过以下SQL设置：

openGauss=# 
openGauss=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
openGauss=#

注意事项：
在选择事务隔离级别时，需要权衡数据一致性和性能需求。
较高的隔离级别（如SERIALIZABLE）可能会导致更多的锁和等待，从而降低系统吞吐量。
在实际应用中，应根据具体业务场景和性能要求来选择合适的事务隔离级别。
通过正确设置事务隔离级别，可以在openGauss中有效地管理数据一致性和并发性能，从而满足各种业务需求。

三、锁机制探索

在openGauss数据库中，锁机制是管理并发访问、确保数据一致性和完整性的关键组件。通过查询系统视图，我们可以深入了解当前锁的情况，从而进行性能调优和故障排查。以下是对openGauss锁机制的探索，以及如何使用pg_locks系统视图来查询锁信息的详细说明：

查询锁信息：pg_locks系统视图

pg_locks是openGauss中的一个系统视图，它提供了关于当前锁状态的详细信息。通过查询pg_locks，我们可以获取锁的类型、锁定的对象、持有锁的事务以及等待锁的事务等关键信息。
执行以下SQL语句，可以查询pg_locks视图中的所有锁信息：

openGauss=# 
openGauss=# SELECT * FROM pg_locks;
  locktype  | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |       pid 
      |    sessionid    |      mode       | granted | fastpath |      locktag      | global_sessionid 
------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+--------------------+-----------
------+-----------------+-----------------+---------+----------+-------------------+------------------
 relation   |    15743 |    12191 |      |       |        |            |               |         |       |          | 13/29232           | 1400764623
07072 | 140076462307072 | AccessShareLock | t       | t        | 3d7f:2f9f:0:0:0:0 | 0:0#0
 virtualxid |          |          |      |       |        | 13/29232   |               |         |       |          | 13/29232           | 1400764623
07072 | 140076462307072 | ExclusiveLock   | t       | t        | d:7230:0:0:0:7    | 0:0#0
 virtualxid |          |          |      |       |        | 11/14216   |               |         |       |          | 11/14216           | 1400767360
50944 | 140076736050944 | ExclusiveLock   | t       | t        | b:3788:0:0:0:7    | 0:0#0
(3 rows)


openGauss=#

这条命令将显示当前系统中所有的锁信息，包括锁类型、锁模式、锁状态等。通过分析这些信息，我们可以了解并发访问的情况，并及时处理潜在的锁冲突。
关键字段解释
locktype：锁的类型，如行锁（row）、表锁（relation）等。
database：被锁定对象所在的数据库ID。
relation：被锁定对象的OID（对象标识符），如果锁是表级锁，则此字段表示表的OID。
page：被锁定页的页号（如果锁是页级锁）。
tuple：被锁定行的行号（如果锁是行级锁）。
virtualxid：虚拟事务ID，用于表示某些特殊事务（如后台任务）的锁。
transactionid：持有锁的事务ID。
classid、objid、objsubid：这些字段共同标识了被锁定对象的类、ID和子ID，用于更精确地定位被锁定的对象。
virtualtransaction：虚拟事务标识符，用于表示持有锁的事务。
pid：持有锁的进程ID。
mode：锁的模式，如AccessShareLock（共享读锁）、RowExclusiveLock（行排他锁）等。
granted：如果锁已被授予，则为true；如果锁正在等待，则为false。
fastpath：如果锁是通过快速路径获取的（即不经过完整的锁管理机制），则为true。

四、常见性能优化

调整Linux系统参数和openGauss数据库配置以优化性能的基本方法。下面是对这些步骤的详细解释和补充：
为了充分发挥openGauss的性能，可以进行一些基本的性能优化配置。

1、调整共享内存：

编辑/etc/sysctl.conf文件，增加共享内存的大小，这是Linux系统用于配置内核参数的文件。
添加了两个参数kernel.shmmax和kernel.shmall来增加系统共享内存的大小。这些参数的值应该根据您的系统内存大小和openGauss的需求来设置。

[root@worker3 single_node]# vim /etc/sysctl.conf

添加以下内容：

kernel.shmmax = 1073741824
kernel.shmall = 268435456

kernel.shmmax：这是系统能够分配的最大共享内存段的大小。可能需要根据您的具体需求进行调整。
kernel.shmall：这是系统能够使用的共享内存段的总数（以页为单位）。设置的值是一个相对较大的值，但同样需要根据您的系统内存来确定。

应用配置：

[root@worker3 single_node]# sysctl -p

使用sysctl -p命令应用更改是正确的。这将使您所做的更改立即生效，而无需重启系统。

2、调整openGauss的postgresql.conf配置：

编辑postgresql.conf文件，postgresql.conf是openGauss（以及PostgreSQL）用于配置数据库参数的文件。根据实际需求调整参数。

[gauss@worker3 ~]$ cd opt/software/openGauss/data/single_node
[gauss@worker3 single_node]$ ls
asp_data         gs_profile          pg_clog       pg_ident.conf  pg_multixact  pg_snapshots  pg_xlog               postmaster.pid.lock
base             gswlm_userinfo.cfg  pg_csnlog     pg_llog        pg_notify     pg_stat_tmp   postgresql.conf       sql_monitor
gaussdb.state    mem_log             pg_ctl.lock   pg_location    pg_perf       pg_tblspc     postgresql.conf.lock  undo
global           mot.conf            pg_errorinfo  pg_log         pg_replslot   pg_twophase   postmaster.opts
gs_gazelle.conf  pg_audit            pg_hba.conf   pg_logical     pg_serial     PG_VERSION    postmaster.pid
[gauss@worker3 single_node]$ vim postgresql.conf

2.1 增加shared_buffers和work_mem的值：

shared_buffers = 1GB
work_mem = 64MB

shared_buffers：这是数据库服务器用于缓存表和索引数据的内存区域。您设置的值（1GB）是一个常见的起点，但应该根据您的系统内存大小和数据库的工作负载来调整。通常，建议将shared_buffers设置为系统总内存的25%左右，但这也取决于其他因素，如操作系统和其他应用程序的内存需求。
work_mem：这是用于排序和哈希操作的内存量。您设置的值（64MB）对于大多数查询来说是一个合理的起点。然而，对于需要处理大量数据的复杂查询，您可能需要增加这个值。

2.2 增加 maintenance_work_mem 的值：

maintenance_work_mem：调整维护操作（如 VACUUM 和 CREATE INDEX）使用的内存量。

maintenance_work_mem = 512MB

2.3 增加 max_connections 的值：

max_connections：调整最大连接数。根据系统负载和硬件资源调整。

max_connections = 200

2.4 增加 effective_cache_size 的值：

effective_cache_size：调整操作系统缓存的估计大小。建议设置为系统内存的 50% 到 75%。

effective_cache_size = 2GB

2.4 增加 checkpoint_completion_target 的值：

checkpoint_completion_target：调整检查点完成的目标时间。设置为 0.9 可以减少 I/O 压力。

checkpoint_completion_target = 0.9

2.5 增加 wal_buffers 的值：

wal_buffers：调整 WAL 缓冲区的大小。建议设置为 shared_buffers 的 3% 到 5%。

wal_buffers = 16MB

2.6 调整 synchronous_commit 的值：

synchronous_commit：调整同步提交的模式。设置为 off 可以提高性能，但可能会降低数据安全性。

synchronous_commit = off

保存并退出编辑器。

3、重启数据库：

在更改postgresql.conf文件中的参数后，您需要重启数据库以使更改生效。这通常涉及停止数据库服务，然后重新启动它。
使配置生效。

[gauss@worker3 single_node]$ gs_ctl restart -D $GAUSSHOME/data/single_node -Z single_node
[2024-11-16 13:14:59.000][99725][][gs_ctl]: gs_ctl restarted ,datadir is opt/software/openGauss/data/single_node 
waiting for server to shut down... done
server stopped
[2024-11-16 13:15:00.009][99725][][gs_ctl]: waiting for server to start...
.0 LOG:  [Alarm Module]can not read GAUSS_WARNING_TYPE env.


0 LOG:  [Alarm Module]Host Name: worker3 


0 LOG:  [Alarm Module]Host IP: worker3. Copy hostname directly in case of taking 10s to use 'gethostbyname' when etc/hosts does not contain <HOST IP>


0 LOG:  [Alarm Module]Cluster Name: dbCluster

4、验证配置

重新连接到数据库，验证调优配置是否生效：

[gauss@worker3 single_node]$ gsql -d postgres -p 5432 -C
gsql ((openGauss 6.0.0 build aee4abd5) compiled at 2024-09-29 18:38:08 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.


openGauss=#

查询配置参数以确保它们已正确应用：

openGauss=# 
openGauss=# SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW max_connections;
SHOW effective_cache_size;
SHOW checkpoint_completion_target;
SHOW wal_buffers;
SHOW synchronous_commit; shared_buffers 
----------------
 1GB
(1 row)


openGauss=#  work_mem 
----------
 64MB
(1 row)


openGauss=#  maintenance_work_mem 
----------------------
 512MB
(1 row)


openGauss=#  max_connections 
-----------------
 200
(1 row)


openGauss=#  effective_cache_size 
----------------------
 2GB
(1 row)


openGauss=#  checkpoint_completion_target 
------------------------------
 0.9
(1 row)


openGauss=#  wal_buffers 
-------------
 16MB
(1 row)


openGauss=#

五、监控和日常维护

1、监控性能：

1.1 使用 pg_stat_activity 视图监控数据库的性能和活动情况。

SELECT * FROM pg_stat_activity;

openGauss=# SELECT * FROM pg_stat_activity;
 datid | datname  |       pid       |    sessionid    | usesysid | usename |    application_name    | client_addr | client_hostname | client_port |  
       backend_start         |          xact_start           |          query_start          |         state_change          | waiting | enqueue | st
ate  | resource_pool |     query_id     |                 query                  |                                                        connection_
info                                                         | unique_sql_id | trace_id 
-------+----------+-----------------+-----------------+----------+---------+------------------------+-------------+-----------------+-------------+--
-----------------------------+-------------------------------+-------------------------------+-------------------------------+---------+---------+---
-----+---------------+------------------+----------------------------------------+-------------------------------------------------------------------
-------------------------------------------------------------+---------------+----------
 15743 | postgres | 140093858772736 | 140093858772736 |       10 | gauss   | JobScheduler           |             |                 |             | 2
024-11-16 13:15:00.599797+08 |                               |                               | 2024-11-16 13:17:44.782991+08 | f       |         | ac
tive | default_pool  |                0 |                                        |                                                                   
                                                             |             0 | 
 15743 | postgres | 140093692049152 | 140093692049152 |       10 | gauss   | TxnSnapCapturer        |             |                 |             | 2
024-11-16 13:15:00.537115+08 |                               |                               | 2024-11-16 13:15:00.598714+08 | f       |         | id
le   | default_pool  |                0 |                                        |                                                                   
                                                             |             0 | 
 15743 | postgres | 140093668980480 | 140093668980480 |       10 | gauss   | CfsShrinker            |             |                 |             | 2
024-11-16 13:15:00.541403+08 |                               |                               | 2024-11-16 13:15:00.598549+08 | f       |         | id
le   | default_pool  |                0 |                                        |                                                                   
                                                             |             0 | 
 15743 | postgres | 140093743429376 | 140093743429376 |       10 | gauss   | ApplyLauncher          |             |                 |             | 2
024-11-16 13:15:00.554036+08 |                               |                               | 2024-11-16 13:15:00.598832+08 | f       |         | id
le   | default_pool  |                0 |                                        |                                                                   
                                                             |             0 | 
 15743 | postgres | 140093582866176 | 140093582866176 |       10 | gauss   | Asp                    |             |                 |             | 2
024-11-16 13:15:00.598612+08 |                               |                               | 2024-11-16 13:17:44.514556+08 | f       |         | ac
tive | default_pool  |                0 |                                        |                                                                   
                                                             |             0 | 
 15743 | postgres | 140093645383424 | 140093645383424 |       10 | gauss   | PercentileJob          |             |                 |             | 2
024-11-16 13:15:00.599883+08 |                               |                               | 2024-11-16 13:17:40.764122+08 | f       |         | ac
tive | default_pool  |                0 |                                        |                                                                   
                                                             |             0 | 
 15743 | postgres | 140093455005440 | 140093455005440 |       10 | gauss   | workload               |             |                 |             | 2
024-11-16 13:15:00.589007+08 | 2024-11-16 13:15:00.599907+08 | 2024-11-16 13:15:00.599907+08 | 2024-11-16 13:15:00.600787+08 | f       |         | ac
tive | default_pool  |                0 | WLM fetch collect info from data nodes |                                                                   
                                                             |             0 | 
 15743 | postgres | 140093556061952 | 140093556061952 |       10 | gauss   | statement flush thread |             |                 |             | 2
024-11-16 13:15:00.602016+08 |                               |                               | 2024-11-16 13:15:00.60203+08  | f       |         | id
le   | default_pool  |                0 |                                        |                                                                   
                                                             |             0 | 
 15743 | postgres | 140093360699136 | 140093360699136 |       10 | gauss   | WorkloadMonitor        |             |                 |             | 2
024-11-16 13:15:00.595012+08 |                               |                               | 2024-11-16 13:15:00.602517+08 | f       |         | id
le   | default_pool  |                0 |                                        |                                                                   
                                                             |             0 | 
 15743 | postgres | 140093341296384 | 140093341296384 |       10 | gauss   | WLMArbiter             |             |                 |             | 2
024-11-16 13:15:00.600827+08 |                               |                               | 2024-11-16 13:15:00.603745+08 | f       |         | id
le   | default_pool  |                0 |                                        |                                                                   
                                                           |             0 |

1.2 使用 pg_stat_database 视图监控数据库的性能和活动情况。

SELECT * FROM pg_stat_database;

openGauss=# SELECT * FROM pg_stat_database;
 datid |  datname  | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup
_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_read_time | blk_write_time |          stats_reset          
-------+-----------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+----
---------+-----------+------------+------------+-----------+---------------+----------------+-------------------------------
     1 | template1 |           0 |           0 |             0 |         0 |        0 |            0 |           0 |            0 |           0 |    
       0 |         0 |          0 |          0 |         0 |             0 |              0 | 
 16384 | school    |           0 |        8389 |             0 |      1511 |    98656 |       357238 |       15172 |          301 |          10 |    
       0 |         0 |          0 |          0 |         0 |             0 |              0 | 2024-11-15 14:38:11.198468+08
 16420 | finance   |           0 |        8391 |             0 |      1513 |    99497 |       363076 |       16010 |          291 |          11 |    
       0 |         0 |          0 |          0 |         0 |             0 |              0 | 2024-11-15 14:38:11.947408+08
 15738 | template0 |           0 |           0 |             0 |         0 |        0 |            0 |           0 |            0 |           0 |    
       0 |         0 |          0 |          0 |         0 |             0 |              0 | 
 16485 | testdb    |           0 |         210 |             0 |       400 |     3938 |         8525 |        1092 |            0 |           0 |    
       0 |         0 |          0 |          0 |         0 |             0 |              0 | 2024-11-16 12:45:10.165171+08
 15743 | postgres  |          11 |      116343 |             7 |      2928 |   256484 |       383225 |       42624 |        30090 |         590 |    
      19 |         0 |          0 |          0 |         0 |             0 |              0 | 2024-11-15 14:35:07.151423+08
(6 rows)


openGauss=#

2、定期维护：

定期执行 VACUUM 操作，保持数据库的最佳性能。
VACUUM FULL;

openGauss=# VACUUM FULL;
NOTICE:  skipping "pg_type" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_column_keys_args" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_authid" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_statistic" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_statistic_ext" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_user_mapping" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_subscription" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_db_role_setting" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_tablespace" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_class" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_attribute" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_proc" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_pltemplate" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_auth_members" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_shdepend" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_shdescription" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_ts_config" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_ts_config_map" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_ts_dict" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_ts_parser" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_ts_template" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_extension" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_obsscaninfo" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_foreign_data_wrapper" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_foreign_server" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pgxc_class" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pgxc_node" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pgxc_group" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_resource_pool" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_workload_group" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_app_workloadgroup_mapping" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_foreign_table" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_rlspolicy" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_default_acl" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_seclabel" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_package" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_partition" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_attrdef" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_constraint" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_inherits" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_index" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_operator" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_opfamily" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_opclass" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_am" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_amop" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_amproc" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_language" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_largeobject_metadata" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_aggregate" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_rewrite" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_trigger" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_event_trigger" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_description" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_cast" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_enum" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_set" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_namespace" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_conversion" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_depend" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_database" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_largeobject" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_shseclabel" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_collation" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_range" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_encrypted_columns" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_column_keys" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_client_global_keys" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_client_global_keys_args" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_encrypted_proc" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_job" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_asp" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_job_proc" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_object" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_synonym" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_obsscaninfo" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_directory" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_hashbucket" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_global_chain" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_global_config" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "streaming_stream" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "streaming_cont_query" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "streaming_reaper_status" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_matview" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_matview_dependency" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pgxc_slice" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_opt_model" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_dependencies" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_dependencies_obj" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_model_warehouse" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_recyclebin" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_txn_snapshot" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_job_argument" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_job_attribute" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_uid" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_db_privilege" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_replication_origin" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_publication" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_publication_rel" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_sql_patch" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_subscription_rel" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_proc_ext" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_user_status" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_auth_history" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_extension_data_source" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_auditing_policy" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_auditing_policy_access" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_auditing_policy_filters" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_auditing_policy_privileges" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_policy_label" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_masking_policy" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_masking_policy_actions" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_masking_policy_filters" --- use xc_maintenance_mode to VACUUM FULL it
VACUUM
openGauss=#

3、其他性能优化建议：

调整检查点参数：checkpoint_segments、checkpoint_timeout等参数控制数据库检查点的频率和大小。这些参数对性能有重要影响，应该根据您的工作负载和存储系统的特性进行调整。
优化磁盘I/O：确保您的数据库文件位于具有足够I/O性能的磁盘上。考虑使用RAID阵列、SSD或其他高性能存储解决方案。
使用连接池：对于高并发环境，使用连接池可以减少数据库连接的建立和断开次数，从而提高性能。
监控和调优查询：定期监控数据库性能，识别并优化慢查询。使用EXPLAIN等工具分析查询计划，并根据需要调整索引和查询逻辑。
请记住，性能优化是一个持续的过程，需要不断地监控、分析和调整。根据您的具体需求和工作负载，您可能需要尝试不同的配置和调优策略。

总结

openGauss的智能优化器与并发控制机制为其卓越的性能和高安全性提供了有力保障。通过实操命令行展示，我们深入了解了这两个方面的技术实践。无论是查询优化还是并发控制，openGauss都展现出了出色的表现。作为开发者或数据库管理员，我们应该充分利用这些特性，不断提升数据库的性能和稳定性。

点击阅读原文跳转作者文章

opengauss position 数据库系统命令模式

文章转载自openGauss，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。