PostgreSQL多版本并发控制 MVCC

- 多版本并发控制MVCC
- - MVCC读写隔离
  - 使用锁实现数据一致性
  - 为什么使用MVCC
- 不同的MVCC机制
- - PG的mvcc模式
- MVCC涉及的概念
- - 事务状态分为四类
  - 数据修改的过程
  - PG的MVCC优势
  - PostgreSQL MVCC总结
  - 优缺点
- 表膨胀冻结
- - 什么是表膨胀
  - 表膨胀危害
  - 为什么会有表膨胀
  - postrgreSQL MVCC优缺点
  - vacuum
  - - vacuum full
  - 表膨胀原因
  - 表膨胀优化
- 基于pg_repack工具处理表，处理索引膨胀
- - pg_repack原理
  - pg_repack安装
- 冻结炸弹
- 事务id的分类
- - 事务id冻结1
  - 表年龄
  - 数据库年龄
  - 事务id冻结2
- autovacuum优化
- - 触发autovacuum消耗
- 惰性模式
- 急切模式
- pg_squeeze维护年龄
- - 配置文件
- 数据库监控
- - 自动收集
  - 手动收集
  - 收集过程
  - 谁来更新统计信息
  - 何时触发统计
  - 收集进程及目录存放
  - 收集维度配置
- 视图
- - pg_stat_statements
  - 时间消耗
  - 何时触发统计信息
  - pgmetrics
  - smem
  - iostat
  - vmstat
  - sar
  - free
  - top

多版本并发控制MVCC

在这里插入图片描述

MVCC读写隔离

在这里插入图片描述
会话1

会话2

使用锁实现数据一致性

ACCESS EXCLUSIVE 访问独占

在这里插入图片描述
会话1

为什么使用MVCC

在这里插入图片描述

不同的MVCC机制

在这里插入图片描述

PG的mvcc模式

多版本并发控制，通过在数据库中创建多个版本的数据来实现并发的读写操作。每个数据库事务都能够看到一个逻辑上一致的数据库快照，当一个事务修改了数据库中的数据时，他不会直接修改原始数据，而是创建一个新的数据版本，并将修改后的数据写入新的数据版本中，这样其他事务依然可以读取到旧的数据版本，不会被修改的事务所影响。通过mvcc，pg可以并发的处理多个读写操作而不会发生冲突。当事务提交时，它的修改才会对其他事物可见。

MVCC涉及的概念

在这里插入图片描述

事务状态分为四类

在这里插入图片描述

数据修改的过程

在这里插入图片描述

PG的MVCC优势

在这里插入图片描述

数据库回滚段的数据不会永久保留，当该事务不再需要这些回滚数据时（例如在事务提交或回滚后，并且超过了一定的保留时间），回滚段的数据才会被释放

在数据库中，回滚段（Undo Segments）的主要作用是支持事务的回滚操作，以及在一致性读取（Consistent Read）时提供旧版本的数据。当事务对数据库进行修改时，数据库系统会将这些修改前的数据存储在回滚段中，以便在需要时可以撤销这些修改（例如，事务失败时）。

关于回滚段的数据保留时间：
事务结束时：当一个事务正常结束时（即提交或回滚），该事务在回滚段中使用的空间确实可以被释放。然而，并不是立即释放，而是当没有其他事务需要访问这些回滚数据时，它们才会被逐步清理和释放。
保留时间：数据库系统通常会设置一些参数来控制回滚段数据的保留时间。例如，Oracle数据库中的UNDO_RETENTION参数用于指定回滚数据保留的时间窗口，以确保在这个时间窗口内，可以执行一致性读取和回滚操作。
系统需要：数据库系统会根据当前的负载和需要，动态管理回滚段的空间。如果系统检测到回滚段空间不足，它可能会尝试释放一些不再需要的回滚数据，或者请求更多的空间。
因此，虽然事务结束时回滚段的数据理论上可以被释放，但实际上它们会根据数据库系统的策略和需要，在一段时间内被保留，以确保数据库的一致性和可恢复性。

会话1
在这里插入图片描述
会话2
查看行状态是不是运行版本，如果是运行状态，就需要查看上一个

会话1

会话2

PostgreSQL MVCC总结

在这里插入图片描述

优缺点

在这里插入图片描述

表膨胀冻结

什么是表膨胀

在这里插入图片描述

表膨胀危害

在这里插入图片描述

为什么会有表膨胀

在这里插入图片描述

postrgreSQL MVCC优缺点

在这里插入图片描述

vacuum

在这里插入图片描述

vacuum full

在这里插入图片描述

postgres=# select oid ,datname from pg_database;oid  |  datname
-------+-----------12401 | postgres1 | template112400 | template024577 | sample_db24578 | huayu
(5 行记录)postgres=# select relname,oid,relfilenode from pg_class where relname='emp';relname |  oid  | relfilenode
---------+-------+-------------emp     | 40988 |       40988
(1 行记录)postgres=# vacuum full emp;
VACUUM
postgres=# select relname,oid,relfilenode from pg_class where relname='emp';relname |  oid  | relfilenode
---------+-------+-------------emp     | 40988 |       49210
(1 行记录)

在这里插入图片描述

表膨胀原因

在这里插入图片描述

在这里插入图片描述

表膨胀

postgres=# create table test(id int,info text);
CREATE TABLE
postgres=# insert into test(id,info)values(1,'test');
INSERT 0 1
postgres=# insert into test(id,info)values(2,'test');
INSERT 0 1postgres=# begin;
BEGIN
postgres=# select pg_backend_pid();pg_backend_pid
----------------30700
(1 行记录)postgres=# select txid_current();txid_current
--------------641
(1 行记录)postgres=# \dt+ test关联列表架构模式 | 名称 |  类型  |  拥有者  | 大小  | 描述
----------+------+--------+----------+-------+------public   | test | 数据表 | postgres | 16 kB |
(1 行记录)postgres=# select ctid,xmin,xmax,cmin,cmax,id from test;ctid  | xmin | xmax | cmin | cmax | id
-------+------+------+------+------+----(0,1) |  639 |    0 |    0 |    0 |  1(0,2) |  640 |    0 |    0 |    0 |  2
(2 行记录)

D:\SoftWare\PostgreSQL\bin>psql -U postgres -h localhost -p 5432 -d postgres
用户 postgres 的口令：
psql (9.6.21)
输入 "help" 来获取帮助信息.postgres=# select txid_current();txid_current
--------------642
(1 行记录)#  每0.01秒更新一次数据
postgres=# update test set info='c1c2';\watch 0.01

查看表空间数据大小

postgres=# \dt+ test关联列表架构模式 | 名称 |  类型  |  拥有者  |  大小  | 描述
----------+------+--------+----------+--------+------public   | test | 数据表 | postgres | 856 kB |
(1 行记录)postgres=# select ctid,xmin,xmax,cmin,cmax,id from test;ctid    | xmin  | xmax | cmin | cmax | id
-----------+-------+------+------+------+----(101,126) | 10050 |    0 |    0 |    0 |  1(101,127) | 10050 |    0 |    0 |    0 |  2

postgres=# vacuum verbose test;
信息:  正在清理 (vacuum)  "public.test"
信息:  "test": 在超出102页的102中找到可删除版本号0, 不可删除的版本号18812
描述:  18810的死亡行版本还不能被移除。
有0个未用的项指针。
由于缓冲区占用而跳过0个页面。
0 个页面完全为空。
CPU 0.00s/0.00u sec elapsed 0.00 sec
信息:  正在清理 (vacuum)  "pg_toast.pg_toast_49213"
信息:  索引"pg_toast_49213_index"在1个页中包含了行版本号0
描述:  索引行版本0被删除.
0个索引页已经被删除,0当前可重用.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
信息:  "pg_toast_49213": 在超出0页的0中找到可删除版本号0, 不可删除的版本号0
描述:  0的死亡行版本还不能被移除。
有0个未用的项指针。
由于缓冲区占用而跳过0个页面。
0 个页面完全为空。
CPU 0.00s/0.00u sec elapsed 0.00 sec
VACUUM

对应的膨胀未解决；
commit提交事务之后，执行vacuum full

vacuum full test;

执行之后查看表空间大小

postgres=# \dt+ test;关联列表架构模式 | 名称 |  类型  |  拥有者  | 大小  | 描述
----------+------+--------+----------+-------+------public   | test | 数据表 | postgres | 16 kB |
(1 行记录)postgres=# select ctid,xmin,xmax,cmin,cmax,id from test;ctid  | xmin  | xmax | cmin | cmax | id
-------+-------+------+------+------+----(0,1) | 10050 |    0 |    0 |    0 |  1(0,2) | 10050 |    0 |    0 |    0 |  2
(2 行记录)

表膨胀优化

在这里插入图片描述

postgres=# show autovacuum;autovacuum
------------on

postgres=# \d pg_stat_activity;视图 "pg_catalog.pg_stat_activity"栏位       |           类型           | 修饰词
------------------+--------------------------+--------datid            | oid                      |datname          | name                     |pid              | integer                  |usesysid         | oid                      |usename          | name                     |application_name | text                     |client_addr      | inet                     |client_hostname  | text                     |client_port      | integer                  |backend_start    | timestamp with time zone |xact_start       | timestamp with time zone |query_start      | timestamp with time zone |state_change     | timestamp with time zone |wait_event_type  | text                     |wait_event       | text                     |state            | text                     |backend_xid      | xid                      |backend_xmin     | xid                      |query            | text                     |postgres=# select * from pg_stat_activity;datid | datname  |  pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | wait_event_type | wait_event |        state        | backend_xid | backend_xmin |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              query                                                                                                                                                                                                                                                                                                       
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+------------+---------------------+-------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 12401 | postgres | 30700 |       10 | postgres | psql             | 127.0.0.1   |                 |       52032 | 2024-10-16 17:45:23.494555+08 | 2024-10-17 16:33:42.598489+08 | 2024-10-17 12401 | postgres | 31296 |       10 | postgres | Navicat          | 127.0.0.1   |                 |       52151 | 2024-10-16 17:21:30.187508+08 |                               | 2024-10-16 17:21:33.151036+08 | 2024-10-16 17:21:33.170048+08 |                 |            | idle                |             |              | SELECT c.oid, n.nspname AS schemaname, c.relname AS tablename, c.relacl, pg_get_userbyid(c.relowner) AS tableowner, obj_description(c.oid) AS description, c.relkind, ci.relname As cluster, c.relhasoids AS hasoids, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, t.spcname AS tablespace, c.reloptions AS param, c.relhastriggers AS hastriggers, c.relpersistence AS unlogged, ft.ftoptions, fs.srvname, c.reltuples, ((SELECT count(*) FROM pg_inherits WHERE inhparent = c.oid) > 0) AS inhtable, i2.nspname AS inhschemaname, i2.relname AS inhtablename FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace LEFT JOIN (pg_inherits i INNER JOIN pg_class c2 ON i.inhparent = c2.oid LEFT JOIN pg_namespace n2 ON n2.oid = c2.relnamespace) i2 ON i2.inhrelid = c.oid LEFT JOIN pg_index ind ON(ind.indrelid = c.oid) and (ind.indisclustered = 't') LEFT JOIN pg_class ci ON ci.oid = ind.indexrelid LEFT JOIN pg_foreign_table ft ON ft.ftrelid = c.oid LEFT JOIN pg_for12401 | postgres | 18856 |       10 | postgres | psql             | 127.0.0.1   |                 |       62311 | 2024-10-17 16:04:07.400937+08 | 2024-10-17 16:47:53.528622+08 | 2024-10-17(3 行记录)postgres=#

基于pg_repack工具处理表，处理索引膨胀

在这里插入图片描述

pg_repack原理

对表执行repack

在这里插入图片描述
对索引执行repack

pg_repack安装

在这里插入图片描述

postgres=# select pg_relation_filepath('test');pg_relation_filepath
----------------------base/12401/49225
(1 行记录)

这条SQL命令用于查询数据库中名为test的表的文件路径
在这里插入图片描述

冻结炸弹

在这里插入图片描述

事务id的分类

在这里插入图片描述

事务id冻结1

在这里插入图片描述

表年龄

在这里插入图片描述

select relfrozenxid,age(relfrozenxid) from pg_class where relname='test';

在这里插入图片描述

数据库年龄

在这里插入图片描述

事务id冻结2

在这里插入图片描述

autovacuum优化

在这里插入图片描述

触发autovacuum消耗

在这里插入图片描述

惰性模式

在这里插入图片描述

急切模式

在这里插入图片描述

pg_squeeze维护年龄

在这里插入图片描述

配置文件

在这里插入图片描述

数据库监控

在这里插入图片描述

自动收集

在这里插入图片描述

手动收集

在这里插入图片描述

收集过程

在这里插入图片描述

谁来更新统计信息

在这里插入图片描述

postgres=# explain verbose select * from test;QUERY PLAN
---------------------------------------------------------------Seq Scan on public.test  (cost=0.00..32.00 rows=2200 width=9)Output: id, info
(2 行记录)postgres=# select count(*) from test;count
-------2
(1 行记录)# 手动提交统计信息
postgres=# analyze test;
ANALYZE
postgres=# explain select * from test;QUERY PLAN
----------------------------------------------------Seq Scan on test  (cost=0.00..1.02 rows=2 width=9)
(1 行记录)