PostgreSQL布局
1.PostgreSQL布局PostgreSQL 作为一个单机的关系型数据库，与单机Oracle的架构是比较相似的，与MySQL的InnoDB引擎也比较像。据我现在的了解，单机数据库的团体架构都差不太多，都是包含一个主的进程，一些辅助进程，以及一个大的共享内存池。
1.1实例架构
https://i-blog.csdnimg.cn/direct/2be4900130f4423dad84311cf66ac3a9.png
1.2内存布局
https://i-blog.csdnimg.cn/direct/8c2cbe3c925e4167897c97a720aa2039.png
1.3数据库进程
主要有3种进程
1.PostgreSQL服务器进程
2.后端进程
3.后台进程
1.4进程布局
PostgreSQL是一个多进程架构的客户端/服务器模式的关系型数据库管理系统。PG数据库中的一系列进程组合进来就是PostgreSQL服务端。这些进程可以细分为以下几大类：
[*]postgres server进程 -是PG数据库中全部进程的父进程。
[*]backend进程 - 每个客户端对于一个backend进程，处于这个客户端中的全部请求。
[*]background进程 - 包含多个后台进程，好比做脏块刷盘的BACKGROUND
WRITER进程，做垃圾清算的AUTOVACUUM进程，做查抄点的CHECKPOINTER进程等。
[*]replication相关进程 - 处理流复制的进程。
[*]background workder进程 - PG9.3版本增加，执行由用户自定义开发的逻辑。
https://i-blog.csdnimg.cn/direct/86ceb34a969040b7864dd078a4bc1a82.png
从上图可以看出，PG数据库中有一个主的postgres server进程，针对每个客户端有一个backend postgres进程，另外有一系列的background后台进程（针对差别的功能模块）。所以这些进程都对应一个共享内存shared memory。
ps -ef|grep postgres
memcach+ 2244 22110 14:37 ? 00:00:00 /opt/gitlab/embedded/bin/postgres -D /var/opt/gitlab/postgresql/data
memcach+ 2300 22440 14:37 ? 00:00:00 postgres: checkpointer
memcach+ 2301 22440 14:37 ? 00:00:00 postgres: background writer
memcach+ 2302 22440 14:37 ? 00:00:00 postgres: walwriter
memcach+ 2303 22440 14:37 ? 00:00:00 postgres: autovacuum launcher
memcach+ 2304 22440 14:37 ? 00:00:00 postgres: stats collector
memcach+ 2305 22440 14:37 ? 00:00:00 postgres: logical replication launcher
1.5逻辑架构
https://i-blog.csdnimg.cn/direct/6e47e046788d44fa9efbb3670a1dc322.png
\l 检察数据库
postgres=# \l
数据库列表
名称 |拥有者| 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------
huayu | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
postgres| postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =Tc/postgres +
| | | | | postgres=CTc/postgres
sample_db | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
template0 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 行记录)
\c 切换数据库
postgres=# \c huayu
您现在已经连接到数据库 "huayu",用户 "postgres".
huayu=# select user;
current_user
--------------
postgres
(1 行记录)
检察对应的oid
huayu=# create table t1(id int);
CREATE TABLE
huayu=# \d pg_class
数据表 "pg_catalog.pg_class"
栏位 | 类型 | 修饰词
---------------------+-----------+--------
relname | name | 非空
relnamespace | oid | 非空
reltype | oid | 非空
reloftype | oid | 非空
relowner | oid | 非空
relam | oid | 非空
relfilenode | oid | 非空
reltablespace | oid | 非空
relpages | integer | 非空
reltuples | real | 非空
relallvisible | integer | 非空
reltoastrelid | oid | 非空
relhasindex | boolean | 非空
relisshared | boolean | 非空
relpersistence | "char" | 非空
relkind | "char" | 非空
relnatts | smallint| 非空
relchecks | smallint| 非空
relhasoids | boolean | 非空
relhaspkey | boolean | 非空
relhasrules | boolean | 非空
relhastriggers | boolean | 非空
relhassubclass | boolean | 非空
relrowsecurity | boolean | 非空
relforcerowsecurity | boolean | 非空
relispopulated | boolean | 非空
relreplident | "char" | 非空
relfrozenxid | xid | 非空
relminmxid | xid | 非空
relacl | aclitem[] |
reloptions | text[] |
索引：
"pg_class_oid_index" UNIQUE, btree (oid)
"pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)
"pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
#查看oid
huayu=# select oid from pg_class where relname='t1';
oid
-------
49239
(1 行记录)
#查看文件存储位置
huayu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/24578/49239
(1 行记录)
# 查看表结构
huayu=# \d pg_database
数据表 "pg_catalog.pg_database"
栏位 | 类型 | 修饰词
---------------+-----------+--------
datname | name | 非空
datdba | oid | 非空
encoding | integer | 非空
datcollate | name | 非空
datctype | name | 非空
datistemplate | boolean | 非空
datallowconn| boolean | 非空
datconnlimit| integer | 非空
datlastsysoid | oid | 非空
datfrozenxid| xid | 非空
datminmxid | xid | 非空
dattablespace | oid | 非空
datacl | aclitem[] |
索引：
"pg_database_datname_index" UNIQUE, btree (datname), 表空间 "pg_global"
"pg_database_oid_index" UNIQUE, btree (oid), 表空间 "pg_global"
表空间："pg_global"
# 查看数据库的oid
huayu=# select oid ,datname from pg_database;
oid|datname
-------+-----------
1 | template1
12400 | template0
24577 | sample_db
24578 | huayu
12401 | postgres
(5 行记录)
1.6物理架构
https://i-blog.csdnimg.cn/direct/50770c463bb5436ca86f8e3bef8bba38.png
默认表空间是base表空间
huayu=# select * from pg_tablespace;
spcname | spcowner | spcacl | spcoptions
------------+----------+--------+------------
pg_default | 10 | |
pg_global| 10 | |
(2 行记录)
变更系统设置
huayu=# alter system set authentication_timeout = '80';
ALTER SYSTEM
fsm: Free Space Map
vm: Visibility Map
2.死元组
2.1死元组：如何产生
死元组:做DML操作产生的无效数据
https://i-blog.csdnimg.cn/direct/7cd4cbd9332e4cdeb4222d8f8520ac20.png
huayu=# select * from t1;
id
----
(0 行记录)
huayu=# insert into t1 values (1);
INSERT 0 1
huayu=# insert into t1 values (2);
INSERT 0 1
huayu=# select * from t1;
id
----
1
2
(2 行记录)
huayu=# create extension pageinspect;
CREATE EXTENSION
huayu=# select * from heap_page_items(get_raw_page('t1',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 |10080 | 0 | 0 | (0,1)| 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 |10081 | 0 | 0 | (0,2)| 1 | 2304 | 24 | | | \x02000000
(2 行记录)
huayu=# update t1 set id=22 where id =2;
UPDATE 1
# 查看页存储数据，因为更新数据，实际两条数据，但是页存储是3条数据，第二条数据无效数据
huayu=# select * from heap_page_items(get_raw_page('t1',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 |10080 | 0 | 0 | (0,1)| 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 |10081 |10083 | 0 | (0,3)| 16385 | 256 | 24 | | | \x02000000
3 | 8096 | 1 | 28 |10083 | 0 | 0 | (0,3)| 32769 | 10240 | 24 | | | \x16000000
(3 行记录)
# 这是物理存储文件 8kb
huayu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/24578/49242
(1 行记录)
2.2 清除死元组的作用：
1.释放存储资源
2.淘汰无效数据，淘汰磁盘扫描时间，进步文件读取服从
3.vacuum
3.1 vacuum作用
清除死元组
跟踪FM free space map 信息
更新VM visibility map 信息
冻结表中的行
定期更新统计信息
3.2 vacuum处理流程
https://i-blog.csdnimg.cn/direct/d3a9176e788d45299a6da5faf55e650c.png
可见性视图
https://i-blog.csdnimg.cn/direct/4701ad5538ab4af3b16bd248cd0e727a.png
huayu=# select * from heap_page_items(get_raw_page('t1',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 |10080 | 0 | 0 | (0,1)| 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 |10081 |10083 | 0 | (0,3)| 16385 | 256 | 24 | | | \x02000000
3 | 8096 | 1 | 28 |10083 | 0 | 0 | (0,3)| 32769 | 10240 | 24 | | | \x16000000
(3 行记录)
huayu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/24578/49242
(1 行记录)
huayu=# vacuum t1;
VACUUM
huayu=# select * from heap_page_items(get_raw_page('t1',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 |10080 | 0 | 0 | (0,1)| 1 | 2304 | 24 | | | \x01000000
2 | 3 | 2 | 0 | | | | | | | | | |
3 | 8128 | 1 | 28 |10083 | 0 | 0 | (0,3)| 32769 | 10496 | 24 | | | \x16000000
(3 行记录)
关系对象的名称、占用的页面数和估计的元组（行）数。
表示关系对象在磁盘上占用的页面数。页面是数据库存储的基本单元，PostgreSQL 中一个页面通常巨细为 8KB。
huayu=# select relname,relpages, reltuples from pg_class where relname ='t1';
relname | relpages | reltuples
---------+----------+-----------
t1 | 1 | 2
(1 行记录)
3.3 vacuum full 清算过程
https://i-blog.csdnimg.cn/direct/b65a0bf7b3df465dbf09f85dbc449a1f.png
https://i-blog.csdnimg.cn/direct/8d1ce6d728284dd19d32d3b02092c2c2.png
huayu=# create table t5 as select * from pg_class;
SELECT 349
huayu=# insert into t5 select * from t5;
INSERT 0 349
huayu=# insert into t5 select * from t5;
INSERT 0 698
huayu=# insert into t5 select * from t5;
INSERT 0 1396
huayu=# insert into t5 select * from t5;
INSERT 0 2792
huayu=# insert into t5 select * from t5;
INSERT 0 5584
huayu=# select count(1) from t5;
count
-------
11168
FreeSpaceMap（FSM，空闲空间映射）是PostgreSQL数据库中的一种空间管理机制，它用于跟踪数据库表或索引中的空闲空间
huayu=# select count(*) as "number of pages", pg_size_pretty(cast(avg(avail) as bigint)) as "Av.freespace size",round(100 * avg(avail)/8192,2)as "Av.freespace ratio" from pg_freespace('t5');
number of pages | Av.freespace size | Av.freespace ratio
-----------------+-------------------+--------------------
270 | 57 bytes | 0.69
(1 行记录)
huayu=# delete from t5 where relfilenode > 10000;
DELETE 5408
huayu=# select count(*) as "number of pages", pg_size_pretty(cast(avg(avail) as bigint)) as "Av.freespace size",round(100 * avg(avail)/8192,2)as "Av.freespace ratio" from pg_freespace('t5');
number of pages | Av.freespace size | Av.freespace ratio
-----------------+-------------------+--------------------
270 | 57 bytes | 0.69
(1 行记录)
huayu=# vacuum t5;
VACUUM
# 清除死元组释放空闲空间，没有进行块的合并
huayu=# select count(*) as "number of pages", pg_size_pretty(cast(avg(avail) as bigint)) as "Av.freespace size",round(100 * avg(avail)/8192,2)as "Av.freespace ratio" from pg_freespace('t5');
number of pages | Av.freespace size | Av.freespace ratio
-----------------+-------------------+--------------------
267 | 4048 bytes | 49.42
(1 行记录)
huayu=# vacuum full t5;
VACUUM
huayu=# select count(*) as "number of pages", pg_size_pretty(cast(avg(avail) as bigint)) as "Av.freespace size",round(100 * avg(avail)/8192,2)as "Av.freespace ratio" from pg_freespace('t5');
number of pages | Av.freespace size | Av.freespace ratio
-----------------+-------------------+--------------------
133 | 0 bytes | 0.00
(1 行记录)
huayu=# insert into t5 select * from t5 limit 10;
INSERT 0 10
huayu=# select count(*) as "number of pages", pg_size_pretty(cast(avg(avail) as bigint)) as "Av.freespace size",round(100 * avg(avail)/8192,2)as "Av.freespace ratio" from pg_freespace('t5');
number of pages | Av.freespace size | Av.freespace ratio
-----------------+-------------------+--------------------
134 | 1 bytes | 0.01
(1 行记录)
3.4 autovacuum
[*]主要作用：
焦点后台进程
周期完成vacuum的工作
[*]开启参数
autoacuum=on
track_count=on
检察设置文件
huayu=# show autovacuum;
autovacuum
------------
on
(1 行记录)
huayu=# show track_counts;
track_counts
--------------
on
(1 行记录)
huayu=# show autovacuum_analyze_scale_factor;
autovacuum_analyze_scale_factor
---------------------------------
0.1
(1 行记录)
huayu=# show autovacuum_analyze_threshold;
autovacuum_analyze_threshold
------------------------------
50
(1 行记录)
huayu=# show autovacuum_vacuum_threshold;
autovacuum_vacuum_threshold
-----------------------------
50
(1 行记录)
huayu=# show autovacuum_vacuum_scale_factor;
autovacuum_vacuum_scale_factor
--------------------------------
0.2
(1 行记录)
触发条件
https://i-blog.csdnimg.cn/direct/63ccd2a29af2468695250fe4bd106f80.png
计算触发条件
autovacuum vacuum =0.2x1000+50=250;
autovacuum Analyze=0.1x1000+50=150;
触发测试
huayu=# create table t2 (id int,name text);
CREATE TABLE
huayu=# insert into t2 values(generate_series(1,1000),'aaaaa'||generate_series(1,1000));
INSERT 0 1000
huayu=# select count(1) from t2;
count
-------
1000
(1 行记录)
huayu=# select relname,relpages, reltuples from pg_class where relname ='t2';
relname | relpages | reltuples
---------+----------+-----------
t2 | 6 | 1000
(1 行记录)
huayu=# update t2 set name='bbb' where id<152;
UPDATE 151
huayu=#
huayu=# SELECT schemaname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_user_tables where relname='t2';
schemaname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
------------+-----------+-----------+-----------+------------+------------+-------------+-----------------+--------------+-------------------------------
public | 1000 | 151 | 0 | 1000 | 151 | | | | 2024-10-24 10:17:26.577289+08
(1 行记录)
huayu=# select relname,relpages, reltuples from pg_class where relname ='t2';
relname | relpages | reltuples
---------+----------+-----------
t2 | 7 | 1000
(1 行记录)
huayu=# update t2 set name='bbb' where id<121;
UPDATE 120
huayu=# SELECT schemaname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_user_tables where relname='t2';
schemaname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
------------+-----------+-----------+-----------+------------+------------+-------------+-----------------+--------------+-------------------------------
public | 1000 | 271 | 0 | 1000 | 271 | | | | 2024-10-24 10:36:26.854663+08
(1 行记录)
huayu=# select relname,relpages, reltuples from pg_class where relname ='t2';
relname | relpages | reltuples
---------+----------+-----------
t2 | 7 | 1000
(1 行记录)
huayu=# update t2 set name='bbb' where id<90;
UPDATE 89
huayu=# SELECT schemaname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_user_tables where relname='t2';
schemaname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
------------+-----------+-----------+-----------+------------+------------+-------------+-------------------------------+--------------+-------------------------------
public | 1000 | 360 | 0 | 1000 | 89 | | 2024-10-24 10:43:26.892215+08 | | 2024-10-24 10:36:26.854663+08
(1 行记录)
huayu=# select relname,relpages, reltuples from pg_class where relname ='t2';
relname | relpages | reltuples
---------+----------+-----------
t2 | 7 | 1000
(1 行记录)
https://i-blog.csdnimg.cn/direct/c97ef029a8444caa9ef151a3ecbd860e.png
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]