# PostgreSQL配置优化参数详解

<script async src="https://www.googletagmanager.com/gtag/js?id=G-P8BDTJ8V1Q"></script>
<script>
  window.dataLayer = window.dataLayer || [];
  function gtag(){dataLayer.push(arguments);}
  gtag('js', new Date());

  gtag('config', 'G-P8BDTJ8V1Q');
</script>

![image.png](/static/img/38b5df9510b43c81310fd20caaf8256b.image.webp)





| 选项                           | 默认值   | 说明                                                             | 是否优化 | 原因                                         |
| ---------------------------- | ----- | -------------------------------------------------------------- | ---- | ------------------------------------------ |
| max_connections              | 100   | 允许客户端连接的最大数目                                                   | 否    | 因为在测试的过程中，100 个连接已经足够                      |
| fsync                        | on    | 强制把数据同步更新到磁盘                                                   | 是    | 系统的 IO 压力很大，为了更好地测试其他配置的影响，将该参数改为 off      |
| shared_buffers               | 24MB  | 决定有多少内存可以被 PostgreSQL 用于缓存数据（推荐内存的 1/4)                        | 是    | 在 IO 压力很大的情况下，提高该值可以减少 IO                  |
| work_mem                     | 1MB   | 使内部排序和一些复杂的查询都在这个 buffer 中完成                                   | 是    | 有助提高排序等操作的速度，并且减少 IO                       |
| effective_cache_size         | 128MB | 优化器假设一个查询可以用的最大内存，和 shared_buffers 无关（推荐内存的 1/2)               | 是    | 设置稍大，优化器更倾向于使用索引扫描而不是顺序扫描                  |
| maintenance_work_mem         | 16MB  | 这里定义的内存仅在 VACUUM 等耗费资源较多的命令调用时使用                               | 是    | 将该值调大，能加快命令的执行                             |
| wal_buffers                  | 768kB | 日志缓存区的大小                                                       | 是    | 可以降低 IO，如果遇上较多的并发短事务，应该和 commit_delay 一起使用 |
| checkpoint_segments          | 3     | 设置 WAL log 的最大数量（一个 log 的大小为 16MB）                             | 是    | 默认的 48MB 的缓存是一个严重的瓶颈，基本上都要设置为 10 以上        |
| checkpoint_completion_target | 0.5   | 表示 checkpoint 的完成时间要在两个 checkpoint 间隔时间的 N% 内完成                | 是    | 能降低平均写入的开销                                 |
| commit_delay                 | 0     | 事务提交后，日志写入 WAL log 到 WAL buffer 写入磁盘的时间间隔。需要配合 commit_siblings | 是    | 能够一次写入多个事务，减少 IO，提高性能                      |
| commit_siblings              | 5     | 设置触发 commit_delay 的并发事务数，根据并发事务多少来配置                           | 是    | 减少 IO，提高性                                  |

PostgreSQL配置优化参数详解

# postgresql配置参数优化



在使用`postgresql`过程当中如果并发比较高，并且数据量也比较大的时候需要对配置项做一些优化

实际的`postgresql`默认配置比较保守，所以如果机器硬件配置还可以的话可以大幅度改动

## 优化配置项

不同版本下的配置默认值是不一样的，具体以官方文档为准  
以下配置是`pg14版本`



#### `max_connections`

- 默认值100
- 允许的最大客户端连接数，通常可以配置上百上千个连接，并发不是太高的话100够用了

#### `shared_buffers`

- 增大数据库内存使用效率
- 设置数据库服务器将使用的共享内存缓冲区量，默认`128MB`
- 决定有多少内存可以被`PostgreSQL`用于缓存数据
- 推荐内存的1/4
- 在磁盘`IO`压力很大的情况下，提高该值可以减少磁盘`IO`

#### `work_mem`

- 加快查询排序
- 默认`4M`
- 设置在写入临时磁盘文件之前查询操作(例如排序或哈希表)可使用的基础最大内存容量
- `ORDER BY`、`DISTINCT`和归并连接都要用到排序操作，哈希连接、基于哈希的聚集以及基于哈希的`IN`子查询处理中都要用到哈希表，
    
    使内部排序和一些复杂的查询都在这个`buffer`中完成，有助提高排序等操作的速度，并且降低`IO`
    
- 这个参数和`max_connections`有关，如果100个查询并发，最坏情况下就很快就达到`400M`的内存使用，
    
    `work_mem` * `max_connections`需要小于内存，推荐乘积可以保持在内存的`1/2`和`3/4`之间
    

#### `effective_cache_size`

- 加快查询
- 默认`4G`，该配置不实际占用内存，优化器假设一个查询可以用的最大内存，保守推荐配置为物理内存的`1/2`，更加推荐配置为内存的`3/4`
- 设置变大，优化器更倾向使用索引扫描而不是顺序扫描

#### `max_wal_size`

- 避免频繁的进行检查点，减少磁盘`IO`
- 默认`1G`
- `wal`全称是`write ahead log`，是`postgresql`中的`online redo log`，保证数据的一致性和事务的完整性
- 在自动 `WAL` 检查点之间允许 `WAL` 增长到的最大尺寸
- 中心思想是先写日志后写数据，即要保证对数据库文件的修改应放生在这些修改已经写入到日志之后
- 使用`wal`可以显著减少磁盘写操作的数量，因为只需要将日志文件刷新到磁盘以确保提交事务，而不是事务更改的每个数据文件，日志文件是按顺序写入的，因此同步日志的成本要比刷新数据页的成本低得多
- 如果在数据库的日志当中看到告警如`consider increasing the configuration parameter "max_wal_size"`，则确实需要增大该参数，在高负载情况下，日志很快就会达到`1G`,推荐把该参数配置为`32G`或者以上

#### `wal_buffers`

- 加快数据更新操作
- 默认`4M`
- 配置`wal`使用的共享内存大小，可以改成`1G`

## 配置方式

修改数据库配置可以通过修改`postgresql.conf`文件或者修改启动命令等方式

#### 修改文件方式

在`postgresql`的`docker`容器当中

`postgresql.conf`的文件位置是`/var/lib/postgresql/data/postgresql.conf`，如果找不到可以执行如下命令搜索一下

$ find / -name "postgresql.conf" 2>/dev/null

在修改完成该配置文件之后直接挂载进去启动容器就行

#### 修改启动命令方式

该方式简单直接

比如修改最大连接数量，配置启动命令为`postgres -c max_connections=500`即可

## 检查配置是否生效

最终经过优化，修改`pg`的启动命令改为

$ postgres -c max_connections=500 -c shared_buffers=1GB -c work_mem=128MB -c max_wal_size=64GB -c wal_buffers=1GB

进入到`psql`环境下面执行`show`命令验证

# show max_connections ;
 max_connections 
-----------------
 500
(1 row)

或者使用`show all` 查看所有配置

## `postgresql`检查点`checkpoint`

产生原因

- 在事务提交的时候，如果直接写入数据文件，必须要先在数据文件中定位到该数据块，然后才能进行数据的更新，写入是随机的，磁盘的随机读写速度比较慢，所以事务提交的时候以追加日志方式顺序写入`wal`日志文件，保证足够的速度
- `wal`会占用大量磁盘，并且故障恢复的时候`redo`的`wal`日志太多导致故障恢复速度变慢

`checkpoint`概述

- 用来保证数据一致性和完整性的一个数据库事务，执行的时候会把检查点之前的脏数据刷新到磁盘，实现数据缩短数据库崩溃恢复时间的目的
- 当数据更新的时候，包含更新数据的数据块在共享缓冲区更新，纪录被保存在`wal`缓冲区，提交后，更改被追加到磁盘上面的`wal`日志，永久生效
- 数据库崩溃的时候，共享缓冲区被清空，`wal`日志中的更改被应用于数据文件
- `checkpoint`执行的时候会识别共享缓冲区的脏页，把数据写入数据文件当中（脏页刷入），然后删除这个检查点位置之前的`wal`文件

触发条件

- 手动执行`checkpoint`命令
- `wal`文件的总大小超过了`max_wal_size`配置的大小
- 距离上一次检查点执行超过了`checkpoint_timeout`配置的超时时间
- 数据库停止或者恢复启动完成
- 执行`pg_start_backup`备份的时候

postgresql配置参数优化

PostgreSQL规约（PG16）

# pg常用查询命令


<script async src="https://www.googletagmanager.com/gtag/js?id=G-P8BDTJ8V1Q"></script>
<script>
  window.dataLayer = window.dataLayer || [];
  function gtag(){dataLayer.push(arguments);}
  gtag('js', new Date());

  gtag('config', 'G-P8BDTJ8V1Q');
</script>

![image.png](/static/img/d1b2c61364c74828f3d8236864db70bf.image.webp)

## 1、查看集群节点的状态

```bash
## pgpool

show pool_nodes;
select client_addr,state,sync_state from pg_stat_replication;

## patroni
[postgres@wtj1vpk8sql01 ~]$ patronictl -c /etc/patroni/patroni.yml list
+ Cluster: pgsql16 (7408451029595073953) -----------+----+-----------+
| Member | Host          | Role         | State     | TL | Lag in MB |
+--------+---------------+--------------+-----------+----+-----------+
| pg01   | 172.17.44.155 | Leader       | running   |  1 |           |
| pg02   | 172.17.44.156 | Replica      | running   |  1 |        16 |
| pg03   | 172.17.44.157 | Sync Standby | streaming |  1 |         0 |
+--------+---------------+--------------+-----------+----+-----------+
```



## 2、查看pg数据库所有schema的大小

```sql
SELECT
     pg_database.datname,
     pg_size_pretty(pg_database_size(pg_database.datname)) AS size
 FROM
     pg_database;
 
  datname  |  size   
-----------+---------
 postgres  | 7628 kB
 template1 | 7708 kB
 template0 | 7473 kB
(3 rows)     
```


## 3、查看核心的几个配置参数

```sql
SELECT  
	name, 
	setting, 
	unit
	-- (unit * 1024) 'bytes'
	FROM pg_settings WHERE name  in
	('shared_buffers',
	'max_connections',
	'wal_buffers',
	'effective_cache_size',
	'work_mem',
	'maintenance_work_mem'
	);

         name         | setting | unit 
----------------------+---------+------
 effective_cache_size | 524288  | 8kB
 maintenance_work_mem | 65536   | kB
 max_connections      | 100     | 
 shared_buffers       | 16384   | 8kB
 wal_buffers          | 512     | 8kB
 work_mem             | 4096    | kB
(6 rows)

```


-- create extension system_stats;

##  4、查看数据库编码字符集

```sql
SHOW server_encoding;

 server_encoding 
-----------------
 UTF8
(1 row)

SELECT current_database(), pg_encoding_to_char(encoding) 
FROM pg_database 
WHERE datname = current_database();

 current_database | pg_encoding_to_char 
------------------+---------------------
 postgres         | UTF8
(1 row)

```


## 5、查看pg数据库的启动时间以及运行时间：
```sql
SELECT
    pg_postmaster_start_time() AS start_time,
    current_timestamp - pg_postmaster_start_time() AS uptime;

	      start_time           |     uptime      
-------------------------------+-----------------
 2024-08-29 14:53:49.014673+08 | 00:40:38.490402
(1 row)
```
    
## 6、查看所有表的占用大小 
```sql
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables order by pg_relation_size(relid) desc;

 relname | pg_size_pretty 
---------+----------------
(0 rows)


```


# 7、查看 PostgreSQL 数据库中哪些表的写入操作（`INSERT`、`UPDATE`、`DELETE`）比较频繁

要查看 PostgreSQL 数据库中哪些表的写入操作（`INSERT`、`UPDATE`、`DELETE`）比较频繁，可以使用内置的统计视图 `pg_stat_user_tables`。该视图记录了自上次服务器启动以来每个表的各种活动统计信息，包括插入、更新和删除操作的计数。

以下是查询表写入频繁程度的 SQL 语句：

```sql
SELECT
    schemaname,
    relname,
    n_tup_ins AS inserts,
    n_tup_upd AS updates,
    n_tup_del AS deletes,
    n_tup_ins + n_tup_upd + n_tup_del AS total_writes
FROM
    pg_stat_user_tables
ORDER BY
    total_writes DESC
LIMIT 10;

```

### 解释：

- `schemaname`: 表所在的模式（schema）。
- `relname`: 表的名称。
- `n_tup_ins`: 自上次服务器启动以来的插入行数。
- `n_tup_upd`: 自上次服务器启动以来的更新行数。
- `n_tup_del`: 自上次服务器启动以来的删除行数。
- `total_writes`: 插入、更新、删除操作的总和，用于衡量表的写入频繁程度。

### 结果分析：

- 查询结果按写入操作总和（`total_writes`）降序排列。结果中，写入频率较高的表会排在前面。
- 如果需要查看更多的表，可以调整 `LIMIT` 子句的值。

通过这个查询，你可以快速识别哪些表在数据库中写入频率较高，从而有助于优化这些表的性能或进一步分析它们对系统资源的影响。

pg常用查询命令

# Github项目创建及代码更新同步


# 一、GitHub 创建新仓库

## github上操作：

GitHub 创建新仓库，不要使用 `README` 、license 或 `gitignore` 文件初始化新存储库。


![image.png](/static/img/63035d9dd2d1b4180fde5c1bbacd3d1d.image.webp)





## 在本地进行操作：

Win + R ，输入 `cmd` 打开命令提示符。  
输入 `D:` 进入 D 盘，依次输入下面命令，会自动在 D 盘创建 【DBPatroller】 文件夹

```bash
git clone https://github.com/vcdog/DBPatroller.git
cd DBPatroller
```


在 GitHub 存储库的页面，单击剪贴板以复制远程存储库 SSH 链接

![image.png](/static/img/cb6fd4f00ee7f9ee40df1c618e8ea564.image.webp)

进入文件夹的根目录【D:\\DBPatroller】,右键git bash here，执行如下命令：

```bash
# git remote add origin SSH链接
 git remote add origin git@github.com:vcdog/DBPatroller.git
 
# 如果报错 error: remote upstream already exists.
# 那么就删除现有远程仓库 
# git remote remove origin

# git remote add origin SSH链接
git remote add origin git@github.com:vcdog/DBPatroller.git
git remote -v
```

# 二、将在GitHub上更改的内容更新到本地

1、在GitHub上修改完后记得保存

2、进入你本地建立的文件夹，右键git bash here

3、输入pwd确认位置后，输入git pull origin

4、查看本地是否更新成功

# 三 、将在本地更新后的代码上传到GitHub

1、更改本地文件

2、在本地文件夹下右键git bash here，

3、输入pwd确认位置

4、输入git add .

5、输入git commit -m ‘update’

6、输入git push （或 git push -u origin main）

7、打开GitHub查看是否成功


要删除远程 Git 仓库中的某个分支，可以使用以下命令：
```bash
$ git push origin --delete <branch-name>


$ git push origin --delete master
To github.com:vcdog/DBPatroller.git
 - [deleted]         master

$ git branch -r
  origin/main

```


```bash
# 创建标签
git tag -a v1.0.1 -m "Release version 1.0.1"

# 推送标签到远程仓库
git push origin v1.0.1

# 或者推送所有标签
git push origin --tags

```

PostgreSQL配置优化参数详解

postgresql配置参数优化

优化配置项

PostgreSQL规约（PG16）

0x00背景

pg常用查询命令

Github项目创建及代码更新同步

一、GitHub 创建新仓库

github上操作：