IT评测·应用市场-qidao123.com技术社区

标题: PostgreSQL布局 [打印本页]
作者: 曹旭辉    时间: 2025-3-26 22:30
标题: PostgreSQL布局
1.PostgreSQL布局

PostgreSQL 作为一个单机的关系型数据库，与单机Oracle的架构是比较相似的，与MySQL的InnoDB引擎也比较像。据我现在的了解，单机数据库的团体架构都差不太多，都是包含一个主的进程，一些辅助进程，以及一个大的共享内存池。
1.1实例架构


1.2内存布局


1.3数据库进程

主要有3种进程
1.PostgreSQL服务器进程
2.后端进程
3.后台进程
1.4进程布局

PostgreSQL是一个多进程架构的客户端/服务器模式的关系型数据库管理系统。PG数据库中的一系列进程组合进来就是PostgreSQL服务端。这些进程可以细分为以下几大类：


从上图可以看出，PG数据库中有一个主的postgres server进程，针对每个客户端有一个backend postgres进程，另外有一系列的background后台进程（针对差别的功能模块）。所以这些进程都对应一个共享内存shared memory。
  1. ps -ef|grep postgres
  2. memcach+   2244   2211  0 14:37 ?        00:00:00 /opt/gitlab/embedded/bin/postgres -D /var/opt/gitlab/postgresql/data
  3. memcach+   2300   2244  0 14:37 ?        00:00:00 postgres: checkpointer
  4. memcach+   2301   2244  0 14:37 ?        00:00:00 postgres: background writer
  5. memcach+   2302   2244  0 14:37 ?        00:00:00 postgres: walwriter
  6. memcach+   2303   2244  0 14:37 ?        00:00:00 postgres: autovacuum launcher
  7. memcach+   2304   2244  0 14:37 ?        00:00:00 postgres: stats collector
  8. memcach+   2305   2244  0 14:37 ?        00:00:00 postgres: logical replication launcher
复制代码
1.5逻辑架构


\l 检察数据库
  1. postgres=# \l
  2.                                                         数据库列表
  3.    名称    |  拥有者  | 字元编码 |            校对规则            |             Ctype              |       存取权限     
  4. -----------+----------+----------+--------------------------------+--------------------------------+-----------------------
  5. huayu     | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
  6. postgres  | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =Tc/postgres         +
  7.            |          |          |                                |                                | postgres=CTc/postgres
  8. sample_db | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
  9. template0 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
  10.            |          |          |                                |                                | postgres=CTc/postgres
  11. template1 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
  12.            |          |          |                                |                                | postgres=CTc/postgres
  13. (5 行记录)
复制代码
\c 切换数据库
  1. postgres=# \c huayu
  2. 您现在已经连接到数据库 "huayu",用户 "postgres".
  3. huayu=# select user;
  4. current_user
  5. --------------
  6. postgres
  7. (1 行记录)
复制代码
检察对应的oid
  1. huayu=# create table t1(id int);
  2. CREATE TABLE
  3. huayu=# \d pg_class
  4.        数据表 "pg_catalog.pg_class"
  5.         栏位         |   类型    | 修饰词
  6. ---------------------+-----------+--------
  7. relname             | name      | 非空
  8. relnamespace        | oid       | 非空
  9. reltype             | oid       | 非空
  10. reloftype           | oid       | 非空
  11. relowner            | oid       | 非空
  12. relam               | oid       | 非空
  13. relfilenode         | oid       | 非空
  14. reltablespace       | oid       | 非空
  15. relpages            | integer   | 非空
  16. reltuples           | real      | 非空
  17. relallvisible       | integer   | 非空
  18. reltoastrelid       | oid       | 非空
  19. relhasindex         | boolean   | 非空
  20. relisshared         | boolean   | 非空
  21. relpersistence      | "char"    | 非空
  22. relkind             | "char"    | 非空
  23. relnatts            | smallint  | 非空
  24. relchecks           | smallint  | 非空
  25. relhasoids          | boolean   | 非空
  26. relhaspkey          | boolean   | 非空
  27. relhasrules         | boolean   | 非空
  28. relhastriggers      | boolean   | 非空
  29. relhassubclass      | boolean   | 非空
  30. relrowsecurity      | boolean   | 非空
  31. relforcerowsecurity | boolean   | 非空
  32. relispopulated      | boolean   | 非空
  33. relreplident        | "char"    | 非空
  34. relfrozenxid        | xid       | 非空
  35. relminmxid          | xid       | 非空
  36. relacl              | aclitem[] |
  37. reloptions          | text[]    |
  38. 索引：
  39.     "pg_class_oid_index" UNIQUE, btree (oid)
  40.     "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)
  41.     "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
  43. #查看oid
  44. huayu=# select oid from pg_class where relname='t1';
  45.   oid
  46. -------
  47. 49239
  48. (1 行记录)
  50. #查看文件存储位置
  51. huayu=# select pg_relation_filepath('t1');
  52. pg_relation_filepath
  53. ----------------------
  54. base/24578/49239
  55. (1 行记录)
  57. # 查看表结构
  58. huayu=# \d pg_database
  59.   数据表 "pg_catalog.pg_database"
  60.      栏位      |   类型    | 修饰词
  61. ---------------+-----------+--------
  62. datname       | name      | 非空
  63. datdba        | oid       | 非空
  64. encoding      | integer   | 非空
  65. datcollate    | name      | 非空
  66. datctype      | name      | 非空
  67. datistemplate | boolean   | 非空
  68. datallowconn  | boolean   | 非空
  69. datconnlimit  | integer   | 非空
  70. datlastsysoid | oid       | 非空
  71. datfrozenxid  | xid       | 非空
  72. datminmxid    | xid       | 非空
  73. dattablespace | oid       | 非空
  74. datacl        | aclitem[] |
  75. 索引：
  76.     "pg_database_datname_index" UNIQUE, btree (datname), 表空间 "pg_global"
  77.     "pg_database_oid_index" UNIQUE, btree (oid), 表空间 "pg_global"
  78. 表空间："pg_global"
  81. # 查看数据库的oid
  82. huayu=# select oid ,datname from pg_database;
  83.   oid  |  datname
  84. -------+-----------
  85.      1 | template1
  86. 12400 | template0
  87. 24577 | sample_db
  88. 24578 | huayu
  89. 12401 | postgres
  90. (5 行记录)
复制代码
1.6物理架构


默认表空间是base表空间
  1. huayu=# select * from pg_tablespace;
  2.   spcname   | spcowner | spcacl | spcoptions
  3. ------------+----------+--------+------------
  4. pg_default |       10 |        |
  5. pg_global  |       10 |        |
  6. (2 行记录)
复制代码
变更系统设置
  1. huayu=# alter system set authentication_timeout = '80';
  2. ALTER SYSTEM
复制代码
fsm: Free Space Map
vm: Visibility Map
2.死元组

2.1死元组：如何产生

死元组:做DML操作产生的无效数据

  1. huayu=# select * from t1;
  2. id
  3. ----
  4. (0 行记录)
  7. huayu=# insert into t1 values (1);
  8. INSERT 0 1
  9. huayu=# insert into t1 values (2);
  10. INSERT 0 1
  11. huayu=# select * from t1;
  12. id
  13. ----
  14.   1
  15.   2
  16. (2 行记录)
复制代码
  1. huayu=# create extension pageinspect;
  2. CREATE EXTENSION
  3. huayu=# select * from heap_page_items(get_raw_page('t1',0));
  4. 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
  5. ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  6.   1 |   8160 |        1 |     28 |  10080 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  7.   2 |   8128 |        1 |     28 |  10081 |      0 |        0 | (0,2)  |           1 |       2304 |     24 |        |       | \x02000000
  8. (2 行记录)
  10. huayu=# update t1 set id=22 where id =2;
  11. UPDATE 1
  12. # 查看页存储数据  ，因为更新数据，实际两条数据，但是页存储是3条数据，第二条数据无效数据
  13. huayu=# select * from heap_page_items(get_raw_page('t1',0));
  14. 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
  15. ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  16.   1 |   8160 |        1 |     28 |  10080 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  17.   2 |   8128 |        1 |     28 |  10081 |  10083 |        0 | (0,3)  |       16385 |        256 |     24 |        |       | \x02000000
  18.   3 |   8096 |        1 |     28 |  10083 |      0 |        0 | (0,3)  |       32769 |      10240 |     24 |        |       | \x16000000
  19. (3 行记录)
  21. # 这是物理存储文件 8kb
  22. huayu=# select pg_relation_filepath('t1');
  23. pg_relation_filepath
  24. ----------------------
  25. base/24578/49242
  26. (1 行记录)
复制代码
2.2 清除死元组的作用：

1.释放存储资源
2.淘汰无效数据，淘汰磁盘扫描时间，进步文件读取服从
3.vacuum

3.1 vacuum作用

清除死元组
跟踪FM free space map 信息
更新VM visibility map 信息
冻结表中的行
定期更新统计信息
3.2 vacuum处理流程


可见性视图

  1. huayu=# select * from heap_page_items(get_raw_page('t1',0));
  2. 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
  3. ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  4.   1 |   8160 |        1 |     28 |  10080 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  5.   2 |   8128 |        1 |     28 |  10081 |  10083 |        0 | (0,3)  |       16385 |        256 |     24 |        |       | \x02000000
  6.   3 |   8096 |        1 |     28 |  10083 |      0 |        0 | (0,3)  |       32769 |      10240 |     24 |        |       | \x16000000
  7. (3 行记录)
  10. huayu=# select pg_relation_filepath('t1');
  11. pg_relation_filepath
  12. ----------------------
  13. base/24578/49242
  14. (1 行记录)
  17. huayu=# vacuum t1;
  18. VACUUM
  19. huayu=# select * from heap_page_items(get_raw_page('t1',0));
  20. 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
  21. ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  22.   1 |   8160 |        1 |     28 |  10080 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  23.   2 |      3 |        2 |      0 |        |        |          |        |             |            |        |        |       |
  24.   3 |   8128 |        1 |     28 |  10083 |      0 |        0 | (0,3)  |       32769 |      10496 |     24 |        |       | \x16000000
  25. (3 行记录)
复制代码
关系对象的名称、占用的页面数和估计的元组（行）数。
表示关系对象在磁盘上占用的页面数。页面是数据库存储的基本单元，PostgreSQL 中一个页面通常巨细为 8KB。
  1. huayu=# select relname,relpages, reltuples from pg_class where relname ='t1';
  2. relname | relpages | reltuples
  3. ---------+----------+-----------
  4. t1      |        1 |         2
  5. (1 行记录)
复制代码
3.3 vacuum full 清算过程



  1. huayu=# create table t5 as select * from pg_class;
  2. SELECT 349
  3. huayu=# insert into t5 select * from t5;
  4. INSERT 0 349
  5. huayu=# insert into t5 select * from t5;
  6. INSERT 0 698
  7. huayu=# insert into t5 select * from t5;
  8. INSERT 0 1396
  9. huayu=# insert into t5 select * from t5;
  10. INSERT 0 2792
  11. huayu=# insert into t5 select * from t5;
  12. INSERT 0 5584
  13. huayu=# select count(1) from t5;
  14. count
  15. -------
  16. 11168
复制代码
FreeSpaceMap（FSM，空闲空间映射）是PostgreSQL数据库中的一种空间管理机制，它用于跟踪数据库表或索引中的空闲空间
  1. 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');
  2. number of pages | Av.freespace size | Av.freespace ratio
  3. -----------------+-------------------+--------------------
  4.              270 | 57 bytes          |               0.69
  5. (1 行记录)
  8. huayu=# delete from t5 where relfilenode > 10000;
  9. DELETE 5408
  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');
  11. number of pages | Av.freespace size | Av.freespace ratio
  12. -----------------+-------------------+--------------------
  13.              270 | 57 bytes          |               0.69
  14. (1 行记录)
  17. huayu=# vacuum t5;
  18. VACUUM
  19. # 清除死元组  释放空闲空间  ，没有进行块的合并
  20. 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');
  21. number of pages | Av.freespace size | Av.freespace ratio
  22. -----------------+-------------------+--------------------
  23.              267 | 4048 bytes        |              49.42
  24. (1 行记录)
  26. huayu=# vacuum full t5;
  27. VACUUM
  28. 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');
  29. number of pages | Av.freespace size | Av.freespace ratio
  30. -----------------+-------------------+--------------------
  31.              133 | 0 bytes           |               0.00
  32. (1 行记录)
  35. huayu=# insert into t5 select * from t5 limit 10;
  36. INSERT 0 10
  37. 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');
  38. number of pages | Av.freespace size | Av.freespace ratio
  39. -----------------+-------------------+--------------------
  40.              134 | 1 bytes           |               0.01
  41. (1 行记录)
复制代码
3.4 autovacuum


焦点后台进程
周期完成vacuum的工作

autoacuum=on
track_count=on
检察设置文件
  1. huayu=# show autovacuum;
  2. autovacuum
  3. ------------
  4. on
  5. (1 行记录)
  8. huayu=# show track_counts;
  9. track_counts
  10. --------------
  11. on
  12. (1 行记录)
  15. huayu=# show autovacuum_analyze_scale_factor;
  16. autovacuum_analyze_scale_factor
  17. ---------------------------------
  18. 0.1
  19. (1 行记录)
  22. huayu=# show autovacuum_analyze_threshold;
  23. autovacuum_analyze_threshold
  24. ------------------------------
  25. 50
  26. (1 行记录)
  29. huayu=# show autovacuum_vacuum_threshold;
  30. autovacuum_vacuum_threshold
  31. -----------------------------
  32. 50
  33. (1 行记录)
  36. huayu=# show autovacuum_vacuum_scale_factor;
  37. autovacuum_vacuum_scale_factor
  38. --------------------------------
  39. 0.2
  40. (1 行记录)
复制代码
触发条件

计算触发条件
autovacuum vacuum =0.2x1000+50=250;
autovacuum Analyze=0.1x1000+50=150;
触发测试
  1. huayu=# create table t2 (id int,name text);
  2. CREATE TABLE
  3. huayu=# insert into t2 values(generate_series(1,1000),'aaaaa'||generate_series(1,1000));
  4. INSERT 0 1000
  5. huayu=# select count(1) from t2;
  6. count
  7. -------
  8.   1000
  9. (1 行记录)
  12. huayu=# select relname,relpages, reltuples from pg_class where relname ='t2';
  13. relname | relpages | reltuples
  14. ---------+----------+-----------
  15. t2      |        6 |      1000
  16. (1 行记录)
  19. huayu=# update t2 set name='bbb' where id<152;
  20. UPDATE 151
  21. huayu=#
  22. 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';
  23. schemaname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze |       last_autoanalyze
  24. ------------+-----------+-----------+-----------+------------+------------+-------------+-----------------+--------------+-------------------------------
  25. public     |      1000 |       151 |         0 |       1000 |        151 |             |                 |              | 2024-10-24 10:17:26.577289+08
  26. (1 行记录)
  29. huayu=# select relname,relpages, reltuples from pg_class where relname ='t2';
  30. relname | relpages | reltuples
  31. ---------+----------+-----------
  32. t2      |        7 |      1000
  33. (1 行记录)
  36. huayu=# update t2 set name='bbb' where id<121;
  37. UPDATE 120
  38. 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';
  39. schemaname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze |       last_autoanalyze
  40. ------------+-----------+-----------+-----------+------------+------------+-------------+-----------------+--------------+-------------------------------
  41. public     |      1000 |       271 |         0 |       1000 |        271 |             |                 |              | 2024-10-24 10:36:26.854663+08
  42. (1 行记录)
  45. huayu=# select relname,relpages, reltuples from pg_class where relname ='t2';
  46. relname | relpages | reltuples
  47. ---------+----------+-----------
  48. t2      |        7 |      1000
  49. (1 行记录)
  52. huayu=# update t2 set name='bbb' where id<90;
  53. UPDATE 89
  54. 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';
  55. schemaname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum |        last_autovacuum        | last_analyze |       last_autoanalyze
  56. ------------+-----------+-----------+-----------+------------+------------+-------------+-------------------------------+--------------+-------------------------------
  57. public     |      1000 |       360 |         0 |       1000 |         89 |             | 2024-10-24 10:43:26.892215+08 |              | 2024-10-24 10:36:26.854663+08
  58. (1 行记录)
  61. huayu=# select relname,relpages, reltuples from pg_class where relname ='t2';
  62. relname | relpages | reltuples
  63. ---------+----------+-----------
  64. t2      |        7 |      1000
  65. (1 行记录)
复制代码


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/) Powered by Discuz! X3.4