淘客网站后台怎么做,seo有哪些优缺点?,wordpress数据库邮箱,永州市规划建设局网站知识要点1 对象标识OID
在PostgreSQL内部#xff0c;所有的数据库对象都通过相应的对象标识符#xff08;object identifier#xff0c;oid#xff09;进行管理#xff0c;这些标识符是无符号的4字节整型。数据库对象与相应oid 之间的关系存储在对应的系统目录中#xf…知识要点1 对象标识OID
在PostgreSQL内部所有的数据库对象都通过相应的对象标识符object identifieroid进行管理这些标识符是无符号的4字节整型。数据库对象与相应oid 之间的关系存储在对应的系统目录中依具体的对象类型而异。例如数据库和堆表对象的 oid 分别存储在pg_database和pg_class中因此当你希望找出oid时可以执行以下查询 sampledb# SELECT datname, oid FROM pg_database WHERE datname sampledb;
sampledb# SELECT relname, oid FROM pg_class WHERE relname sampletbl;
OID不变但是relfilenode在进行ddl操作后会发生变化。
知识要点2 软件物理结构
1 PGBASE,PGDATA,CONF文件。 4个配置文件 pg_hba.conf 控制PG数据库客户端认证 pg_ident.conf控制用户名映射。 postgresql.conf配置参数 postgresql.auto.conf存储使用alter system调整的参数 表空间文件结构布局
select pg_relation_filepath(sampletbl);
postgres# select pg_relation_filepath(pg_class); base/13593/1259
PG中表空间是基础PGDATA目录之外的附加数据区域。8.0版本引入该功能。
本身的初始化数据目录要对外面的表空间有一个管理那么pg_tblspc就实现了功能。
create tablespace tbs1 location /home/postgres/datafile/tbs1;
[postgreslocalhost pg_tblspc]$ pwd /home/postgres/PGDATA/pg_tblspc [postgreslocalhost pg_tblspc]$ ls -la lrwxrwxrwx 1 postgres postgres 28 May 4 15:34 16388 - /home/postgres/datafile/tbs1 [postgreslocalhost pg_tblspc]$
create table test02 (id int) tablespace tbs1;
insert into test02 select 3;
postgres# select oid,spcname from pg_tablespace; oid | spcname ------------------- 1663 | pg_default 1664 | pg_global 16388 | tbs1 (3 rows)
postgres# select oid,reltablespace,relfilenode,relowner from pg_class where relnametest02; oid | reltablespace | relfilenode | relowner --------------------------------------------- 16392 | 16388 | 16392 | 10 (1 row)
[postgreslocalhost 13593]$ pwd /home/postgres/datafile/tbs1/PG_12_201909212/13593 --这个目录是个啥 [postgreslocalhost 13593]$ ls 16392
postgres# 表文件路径查询
select pg_relation_filepath(test02); [postgreslocalhost 13593]$ psql psql (12.18) Type help for help. postgres# select pg_relation_filepath(test02); pg_relation_filepath --------------------------------------------- pg_tblspc/16388/PG_12_201909212/13593/16392 (1 row) postgres# 执行 CREATE TABLESPACE语句会在指定的目录下创建表空间。在该目录下还会创建版本特定的子目录例如PG_9.4_201409291。版本特定的命名方式为 PG_主版本号_目录版本号 例如在/pg/data/ 目录中创建表空间 new_TBLSPC 对应的OID为 16384则会在表空间下创建一个名为 pg_version_banben的字目录。
cd /pg/data/pg_11.2_20220121/16384
如果在表空间中创建新表则新表对应的OID值 创建在/pg/data/pg_11.2_20220121/16384中而且对应的数据段大小可以在初始化时制定大小超过大小后分裂为OID.1,OID.2文件热点快数据优化。还有表及其他对象对应的管理文件空闲空间映射可见性映射等fsm和vm文件
$PGDATA/pg_tblspc/16384 ------------- /pg/data/
堆表文件的内部布局
数据文件包括堆表索引空间空间映射可见性空间映射文件 内部被划分为固定大小的页
或者叫做区块 8KB。从0开始编号叫做区块号。 为了识别表中的元组数据库内部会使用元组标识符tuple identifier TID
TID由一组值组成分别为元组所属页面的区块号和指向元组的行指针偏移号TID典型应用为索引。大小超过2kb的堆元组会使用toast 超大属性存储技术。
PG进程和内存架构
C/S架构客户端/服务器风格
pgserver实际上是一些列协同工作的进程集合。
[postgreslocalhost ~]$ ps -ef|grep postgres |grep -v grep|grep 1* postgres 30957 1 0 May03 ? 00:00:00 /home/postgres/postgresql/bin/postgres -D /home/postgres/PGDATA postgres 30958 30957 0 May03 ? 00:00:00 postgres: logger postgres 30960 30957 0 May03 ? 00:00:00 postgres: checkpointer postgres 30961 30957 0 May03 ? 00:00:00 postgres: background writer postgres 30962 30957 0 May03 ? 00:00:00 postgres: walwriter postgres 30963 30957 0 May03 ? 00:00:00 postgres: autovacuum launcher postgres 30964 30957 0 May03 ? 00:00:00 postgres: stats collector postgres 30965 30957 0 May03 ? 00:00:00 postgres: logical replication launcher [postgreslocalhost ~]$
本地内存区域和共享内存区域。
[postgreslocalhost ~]$ ipcs -ma|grep postgres 0x0052e2c1 1376262 postgres 600 56 6 [postgreslocalhost ~]$
本地内存区域由每个后端进程分配供自己使用 类似pga。 如何去查看进程占用的内存
linux用top 1 PG时CS架构采用多进程架构。
2 PG服务进程是所有进程的父进程。postgres server process.
3 后端进程backed process负责处理客户端发出的查询语句。 重写器重写器会根据存储在pg_rules中的规则对查询进行转换。pg不支持提示hint。 4 各种后台进程background process 负责执行各种数据库管理任务例如清理过程和存档过程
5 各种复制进程replication associate process负责复制流。
6 pg_ctl start会启动postgres server process父进程它会在内存中分配共享内存区域启动各种后台进程。如果有必要还要启动replication并等待客户端的连接请求。有客户端连接它就会启动一个后端进程然后由后端进程处理该客户端的所有查询请求。oracle 1-1的专享连接模式
7 PG没有原生的资源池可以采用池化中间件pgbouncer pgpool-II PG后台进程解释
登录pg服务器使用ps -ef|grep postgres可以发现有很对pg进程以下简要解释下各个后台进程的含义。
background writer 本进程负责吧共享池中的脏页刷新到磁盘中。
checkpointer负责检查点。
autoavcuum launcher自动清理死元组清理工作
WAL writer 预写日志管理。
statistic collector收集统计信息例如pg_STAT_ACTIVITY,PG_STAT_DATABASE等。
logging collector日志采集统计
archiver wal日志归档。
backed process例如10.228.11.1:577423 progres格式。
单表查询的代价估计
所有被执行的操作都有着相应的代价函数
cost_seq_scan顺序扫描
cost_index索引扫描
对于costpg定义为3种1 启动代价运行代价总代价。
启动代价在读取到第一条元组前花费的代价。索引扫描读取目标表索引页获取第一个元组的代价。
运行代价获取全部元组的代价。
总代价前面2个的和。explain命令只显示启动代价和总代价。
postgres# explain select * from test02; QUERY PLAN ------------------------------------------------------ Seq Scan on test02 (cost0.00..1.01 rows1 width4) (1 row)
启动代价为0.00总代价为1.01
顺序扫描代价评估
postgres# create table tbl(id int primary key,data int); CREATE TABLE postgres# create index tbl_data_idx on tbl(data); CREATE INDEX postgres# insert into tbl select generate_series(1,10000),generate_series(1,10000); INSERT 0 10000 postgres# analyze; ANALYZE postgres# \d tbl Table public.tbl Column | Type | Collation | Nullable | Default ----------------------------------------------- id | integer | | not null | data | integer | | | Indexes: tbl_pkey PRIMARY KEY, btree (id) tbl_data_idx btree (data)
postgres#
postgres# select relpages,reltuples from pg_class where relnametbl; relpages | reltuples --------------------- 45 | 10000 (1 row)
postgres#
run_costcpu_tuple_coscpu_operator_cost*Ntuplesseq_page_cost*Npages
而3个cost值在配置文件postgresql.conf中都是可以设置的存在默认值的。
0.01/0.0025/1.0
0.010.0025*10001.0*45170
postgres# explain select * from tbl where id8000; QUERY PLAN -------------------------------------------------------- Seq Scan on tbl (cost0.00..170.00 rows7999 width8) Filter: (id 8000)-----表级别过滤谓词并不影响扫描的page数。 (2 rows)
postgres# 索引扫描代价评估
postgres# select relpages,reltuples from pg_class where relnametbl_data_idx; relpages | reltuples --------------------- 30 | 10000 (1 row)
事务标识并发控制-一致性和隔离性
ACID 原子性一个事务要不全成功要不全失败。 一致性 隔离性 持久性wal日志 每种技术都有不同的变体在MVCC中每个写操作都会创建一个新版本的数据项并保留其就版本undopage保持标记等当其他事务读取数据对象时系统会选择一个版本让它读取确保事务间相互隔离。读不阻塞写写不阻塞读MVCC PG的MVCC是怎么样的那
快照隔离。snapshot isolation SI
像oracle mysql都是用undo来实现SI。而PG采用的是新数据被插入到相关的表页中读取对象时postgresql根据可见性规则mysqlread viewundo为每个事务选择合适的对象版本作为响应。
每个事务开始时事务管理器会为其分配一个事务标识 tid,最大值42亿32位无符号整型。
select txid_current();--查看当前事务的txid。012表示预留的txid。
txid可以互相比较大小例如txid100 则小于100的属于过去大于100的属于未来。
因为txid在逻辑上是无限的而实际系统中的txid空间不足4B整型的取值空间大小约42亿因此PostgreSQL将txid空间视为一个环。对于某个特定的txid其前约21亿个txid属于过去其后约21亿个txid属于未来 提交日志PG_XACT
postgresql在提交日志中CLOG中保存事务的状态提交日志分配在内存中并用于事务处理的全过程
事务状态
postgresql定义了4种事务状态in_processcommitedaborted和sub commited。
提交日志如何工作
提交日志是一个数组在共享内存中一些列8K页面组成。数组的序列号代表的事务的标识tid其内容则是事务的状态 T1txid 200提交txid 200的状态从IN_PROGRESS变为COMMITTED。 T2txid 201中止txid 201的状态从IN_PROGRESS变为ABORTED。 txid 不断前进当 CLOG空间耗尽无法存储新的事务状态时就会追加分配一个新的页面。 当需要获取事务的状态时PostgreSQL将调用相应内部函数读取CLOG并返回所请求事务的状态。 5.4.3 提交日志的维护 当PostgreSQL关机或执行存档过程时CLOG数据会写入pg_clog子目录下的文件中注意在10.0版本中pg_clog被重命名为pg_xact。这些文件被命名为00000001等。文件的最大尺寸为256 KB。例如当CLOG使用8个页面时从第1页到第8页的总大小为64 KB这些数据会写入文件000064 KB中而当CLOG使用37个页面时296 KB数据则会写入0000和0001两个文件中其大小分别为256 KB和40 KB。 当PostgreSQL启动时会加载存储在pg_clogpg_xact中的文件用其数据初始化CLOG。 CLOG的大小会不断增长因为只要CLOG一填满就会追加新的页面。但并非所有数据都是必要的。
clog是如何删除的那
CLOG存储着事务的状态。当更新pg_database.datfrozenxid时 PostgreSQL会尝试删除不必要的CLOG文件。注意相应的CLOG页面也会被删除。 图 6.7 给出了一个例子。如果 CLOG 文件 0002 中包含最小的 pg_database.datfro zenxid则可以删除旧文件0000 和0001因为存储在这些文件中的所有事务在整个数据库集簇中已经被视为冻结了。 元组
读取
两种典型的读取方式顺序扫描索引扫描。
通过扫描每一页中的行指针依次读取所有页面的数据。
B树扫描 索引文件包含 索引元组索引元组由一对健值组成健值和TID。 pg还支持TID扫描 位图扫描 仅索引扫描。TID扫描时一种通过索引元祖直接获取数据的扫描。
或者例如使用select语句指定。 select * from test02 where ctid(0,1)---第0个页面的第一个元组信息。 postgres# select * from test02 where ctid(0,1); id ---- 3 (1 row) postgres# explain select * from test02 where ctid(0,1); QUERY PLAN ------------------------------------------------------ Seq Scan on test02 (cost0.00..1.01 rows1 width4) Filter: (ctid (0,1)::tid) (2 rows) postgres# explain select * from test02 where ctid(0,2); QUERY PLAN ------------------------------------------------------ Seq Scan on test02 (cost0.00..1.01 rows1 width4) Filter: (ctid (0,2)::tid) (2 rows) postgres# select * from test02 where ctid(0,2); id ---- (0 rows) postgres# 元祖包含的隐藏列
txid xmin xmax ctid 数据。前面只显示核心的4个字段。 postgres# select txid_current(),xmin,xmax,ctid from pg_class limit 10; 488 | 36 | 0 | (0,46) 488 | 273 | 0 | (0,47) 488 | 1 | 0 | (1,19) 488 | 1 | 0 | (1,20) 488 | 1 | 0 | (1,21) 488 | 1 | 0 | (1,22) 488 | 1 | 0 | (1,23) 488 | 1 | 0 | (1,24) 488 | 1 | 0 | (1,25) 488 | 1 | 0 | (1,26) postgres#
postgres#
元组的增删改FSM用于插入和更新元组的自由空间映射。
通常不需要的元组在POSTgres中被称为死元组。
增
在插入中新元组直接插入目标表的page中如图所示 Tuple_1 · t_xmin设置为99因为此元组由txid99的事务所插入。 · t_xmax设置为0因为此元组尚未被删除或更新。 · t_cid设置为0因为此元组是由txid99的事务所执行的第一条命令插入的。 · t_ctid设置为(0,1)指向自身因为这是该元组的最新版本。 pageinspect PostgreSQL自带了一个第三方贡献的扩展模块pageinspect可用于检查数据库页面的具体内容。 testdb# CREATE EXTENSION pageinspect;
CREATE EXTENSION
testdb# CREATE TABLE tbl (data text);
CREATE TABLE
testdb# INSERT INTO tbl VALUES(A);
INSERT 0 1
testdb# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid
FROM heap_page_items(get_raw_page(tbl, 0));
tuple | t_xmin | t_xmax | t_cid | t_ctid
--------------------------------------
1 | 99 | 0 | 0 | (0,1)
(1 row) 12.18环境 postgres# CREATE EXTENSION pageinspect;
ERROR: could not open extension control file /home/postgres/postgresql/share/extension/pageinspect.control: No such file or directory postgres-# 实验
postgres# create table test(id int); CREATE TABLE
postgres# insert into test select 1; INSERT 0 1
postgres# select txid_current(),xmin,xmax,ctid from test limit 10; txid_current | xmin | xmax | ctid --------------------------------- 491 | 490 | 0 | (0,1)
postgres# insert into test select 2; INSERT 0 1 postgres# select txid_current(),xmin,xmax,ctid from test limit 10; txid_current | xmin | xmax | ctid --------------------------------- 493 | 490 | 0 | (0,1) ----490事务插入 493 | 492 | 0 | (0,2) ----492事务插入
postgres# update test set id10; UPDATE 2
postgres# select txid_current(),xmin,xmax,ctid from test limit 10; txid_current | xmin | xmax | ctid --------------------------------- 495 | 494 | 0 | (0,3) 495 | 494 | 0 | (0,4) --全部被494事务更新update。
删
在删除操作中目标元组只是在逻辑上被标记为删除。目标元组的t_xmax字段将被设置成delete命令事务的txid。 假设tuple_1被事务111删除在这种情况下Tuple_1的首部字段被t_xmax设置成111如果事务txid111已经提交就不一定要tuple_1元组通常不需要的元组此时已经成为死元组。
死元组最终将从页面中被移除清除死元组的过程称为VACUUM。
改
在更新操作中PG在逻辑上实际执行的是删除最新的元组并插入一条新的元组。 假设由txid99的事务插入的行被txid100的事务更新两次。 当执行第一条UPDATE命令时Tuple_1的t_xmax被设为txid 100在逻辑上被删除然后Tuple_2被插入接下来重写Tuple_1的t_ctid以指向Tuple_2。Tuple_1和Tuple_2的头部字段设置如下。 Tuple_1 · t_xmax被设置为100。 · t_ctid从(0,1)被改写为(0,2)。 Tuple_2 · t_xmin被设置为100。 · t_xmax被设置为0。 · t_cid被设置为0。 · t_ctid被设置为(0,2)。 当执行第二条UPDATE命令时和第一条UPDATE命令类似Tuple_2被逻辑删除Tuple_3被插入。Tuple_2和Tuple_3的首部字段设置如下。 Tuple_2 · t_xmax被设置为100。 · t_ctid从(0,2)被改写为(0,3)。 Tuple_3 · t_xmin被设置为100。 · t_xmax被设置为0。 · t_cid被设置为1。 · t_ctid被设置为(0,3)。 与删除操作类似如果txid100的事务已经提交那么Tuple_1和Tuple_2就成了死元组而如果txid100的事务中止Tuple_2和Tuple_3就成了死元组。 事务快照
select txid_current_snapshot();
xminxmaxxip_list。 postgres# select txid_current_snapshot(); 489:489: postgres# 事务快照是一个数据集存储某个特定事务在某个特定时间所看到的事务状态信息。哪些事务处于活跃状态事务正在进行或者还没开始。事务快照在PostgreSQL内部的文本表示格式为100100
100100 意味着txid100的事务处于非活跃状态txid100的事务处于活跃状态。 清理过程VACUUM
为了移除死元组清理过程有另种模式分别为并发清理与完整清理清理过程会删除表文件每个页面的死元组而其他事务可以在运行时继续读取该表。
完整清理不仅移除死元组还会对活的元组进行碎片整理此时表不可访问。
在8.0以前需要手动清理直到出现autovacuum守护进程实现自动化。
由于清理过程需要全表扫描因此代价过于高昂。 可见性映射提高了VM移除死元组的效率并在后期的版本中VM增强。 postgresql FDW
需要进行配置扩展。
