postgresql 临时表空间及注意事项

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: --查看数据库临时表空间位置--temp_tablespaces是表空间名称的列表,当列表中有一个以上名称时, PostgreSQL 每次临时对象被创建时选择一个列表中的随机数;--除了在一个事务中之外,先后创建临时对象放置在列表连续的表空间中。
--查看数据库临时表空间位置
--temp_tablespaces是表空间名称的列表,当列表中有一个以上名称时, PostgreSQL 每次临时对象被创建时选择一个列表中的随机数;
--除了在一个事务中之外,先后创建临时对象放置在列表连续的表空间中。 
--如果列表中选定的元素是一个空字符串, PostgreSQL 会自动使用当前数据库的缺省表空间
 select * from pg_settings where name = 'temp_tablespaces';
 
 --可以把pg的临时表空间建立在/dev/shm下面提高性能
 -- 新建目录结构.
  mkdir /dev/shm/tbs_tmp
 
 -- 创建表空间
 create tablespace tbs_tmp location '/dev/shm/tbs_tmp';
 
 --如果把目录建在/dev/shm目录下,需要改变目录权限,默认为root用户
  chown postgres.postgres /dev/shm

--设置session级别的临时表空间 
set session temp_tablespaces='tbs_tmp';




 --创建测试表
 postgres=#  create temp table test2 (id int);
CREATE TABLE
postgres=# \d+ test2
                       Table "pg_temp_5.test2"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 
Has OIDs: no
Tablespace: "tbs_tmp"

--插入数据
postgres=#  insert into test2 select generate_series(1,1000000);
INSERT 0 1000000

--可发现此目录明显增大,说明临时表的数据已经进到此目录
35M     /dev/shm/tbs_tmp/

--由于是临时表,故session退出后此数据就会被清空,临时表目录大小变为0
0       /dev/shm/tbs_tmp/




--如果要想让临时表空间的设置对所有的session永久生效,则需要修改配置文件
[postgres@staging27 tmp]$ vim /db/pgsql/data/postgresql.conf 
temp_tablespaces = 'tbs_tmp'
[postgres@staging27 tmp]$ pg_ctl reload -D /db/pgsql/data



--执行压力测试,进行大量的排序,需要使用到临时表空间
\setrandom id 140 12345678
\setrandom id2 1470 3023
\setrandom id3 1570 3213

select snapid,dbid,tbl,attnum,date,name,type,stattarget,storage,isnotnull,isdropped,avg_width,n_distinct,correlation from statsrepo.column order by name desc limit 11 offset :id;
select count(*) from statsrepo.index where name=(select name from statsrepo.index limit 1 offset :id ) ;
select snapid,dbid,tbl,attnum,date,name,type,stattarget,storage,isnotnull,isdropped,avg_width,n_distinct,correlation from statsrepo.column where snapid=:id2 order by name desc limit 11 offset :id2;
select count(*) from statsrepo.index where name=(select name from statsrepo.index limit 1 offset :id3+14 ) ;
select snapid,dbid,tbl,attnum,date,name,type,stattarget,storage,isnotnull,isdropped,avg_width,n_distinct,correlation from statsrepo.column where snapid=:id3 order by name desc limit 11 offset :id3;
select count(*) from statsrepo.index where name=(select name from statsrepo.index limit 1 offset :id2+1 ) ;


--执行测试,可知当临时表空间用完后,pg将会kill掉需要临时表空间的进程(不需要临时表空间的不受影响)
[postgres@staging27 tmp]$ pgbench -M prepared -f ./test.sql -n -r -c 6 -j 2 -T 1800 statsrepo > /tmp/pgbench_report_2.txt
Client 3 aborted in state 3: ERROR:  could not write block 102208 of temporary file: 设备上没有空间
HINT:  Perhaps out of disk space?
Client 1 aborted in state 3: ERROR:  could not write block 68958 of temporary file: 设备上没有空间

--监控其大小
while true; do du -sh /dev/shm/tbs_tmp/ /db/pgsql/data/base/pgsql_tmp/ ; sleep 1 ; done;

3.8G    /dev/shm/tbs_tmp/
3.9G    /dev/shm/tbs_tmp/
1.2G    /dev/shm/tbs_tmp/
0       /dev/shm/tbs_tmp/



--由于/dev/shm 空间有限,此时可以建立一个磁盘文件的临时表空间
 create tablespace disk_tbs_tmp location '/db/pgsql/data/base/pgsql_tmp';
 --修改配置文件
 temp_tablespaces='tbs_tmp,disk_tbs_tmp';
 
--进行压力测试后可知,如果磁盘文件不足,pg还是会kill掉需要临时表空间的进程
3.7G    /dev/shm/
5.6G    /db/pgsql/data/base/pgsql_tmp/
3.7G    /dev/shm/
5.6G    /db/pgsql/data/base/pgsql_tmp/
3.8G    /dev/shm/
5.6G    /db/pgsql/data/base/pgsql_tmp/
1.0G    /dev/shm/
4.6G    /db/pgsql/data/base/pgsql_tmp/
1.0G    /dev/shm/
606M    /db/pgsql/data/base/pgsql_tmp/
1.0G    /dev/shm/

 
 --dev/shm/ 目彔,其实是利用内存虚拝出来癿磁盘空间! 讵问速度是非帯快的,但建立在这个目录下的文件在下次开机时就消失了

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
8月前
|
关系型数据库 分布式数据库 数据库
PolarDB对比X-Engine与InnoDB空间效率
本实验带您体验创建X-Engine和InnoDB两种不同的表存储引擎,通过Sysbench模拟数据注入的过程对比俩种表引擎的空间效率。
548 0
|
7天前
|
分布式计算 关系型数据库 大数据
MaxCompute产品使用合集之怎么才可以将 PostgreSQL 中的 geometry 空间类型字段同步到 MaxCompute 或另一个 PostgreSQL 数据库
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
9月前
|
安全 关系型数据库 数据库
创建 PostgreSQL 表空间时没有指定空间的总大小
创建 PostgreSQL 表空间时没有指定空间的总大小
99 1
|
12月前
|
关系型数据库 定位技术 数据库
PostgreSQL技术大讲堂 - 第17讲:Vacuum空间管理工具
PostgreSQL从小白到专家,技术大讲堂 - 第17讲:Vacuum空间管理工具
152 0
|
并行计算 算法 Cloud Native
PolarDB 开源版 使用PostGIS 数据寻龙点穴(空间聚集分析)- 大数据与GIS分析解决线下店铺选址问题
寻龙点穴是风水学术语。古人说:三年寻龙,十年点穴。意思就是说,学会寻龙脉要很长的时间,但要懂得点穴,并且点得准则难上加难,甚至须要用“十年”时间。 但是,若没正确方法,就是用百年时间,也不能够点中风水穴心聚气的真点,这样一来,寻龙的功夫也白费了。 准确地点正穴心,并不是一件容易的事,对初学者来说如此,就是久年经验老手,也常常点错点偏。 寻龙点穴旨在寻找龙气聚集之地,而现实中,我们也有类似需求,比如找的可能是人气聚集之地。 PolarDB 开源版 使用PostGIS 数据寻龙点穴(空间聚集分析)- 大数据与GIS分析解决线下店铺选址问题
485 0
PolarDB 开源版 使用PostGIS 数据寻龙点穴(空间聚集分析)- 大数据与GIS分析解决线下店铺选址问题
|
并行计算 关系型数据库 数据库
|
算法 关系型数据库 定位技术
【重新发现PostgreSQL之美】- 25 最强大脑题目 泰森多边形(空间战略布局问题)
大家好,这里是重新发现PostgreSQL之美 - 25 最强大脑题目 泰森多边形(空间战略布局问题)
|
SQL 存储 NoSQL
AnalyticDB for PostgreSQL 空间数据分析实战
数字经济时代,数据是其关键的生产资料,而空间信息作为一重要属性集和模型特征集在业界形成广泛共识。政府层面,美国911之后,通信运营商为政府相关部门(如公安、交通、应急指挥等)提供手机定位信息受法律保护;社会部分行业,尤其涉及GIS、交通、物流、吃住行游、自动驾驶等,无不与空间信息强相关。由此,空间数据的存储、空间查询与分析等特性成为数据库的标配。本文主要介绍如何利用AnalyticDB for PostgreSQL对空间数据进行管理和分析应用。
589 0
AnalyticDB for PostgreSQL 空间数据分析实战
|
SQL 存储 NoSQL
AnalyticDB for PostgreSQL 空间数据分析实战
数字经济时代,数据是其关键的生产资料,而空间信息作为一重要属性集和模型特征集在业界形成广泛共识。政府层面,美国911之后,通信运营商为政府相关部门(如公安、交通、应急指挥等)提供手机定位信息受法律保护;社会部分行业,尤其涉及GIS、交通、物流、吃住行游、自动驾驶等,无不与空间信息强相关。由此,空间数据的存储、空间查询与分析等特性成为数据库的标配。本文主要介绍如何利用AnalyticDB for PostgreSQL对空间数据进行管理和分析应用。
2096 0
AnalyticDB for PostgreSQL 空间数据分析实战