postgresql 临时表空间及注意事项-阿里云开发者社区

开发者社区> rudy_gao> 正文

postgresql 临时表空间及注意事项

简介: --查看数据库临时表空间位置 --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/ 目彔,其实是利用内存虚拝出来癿磁盘空间! 讵问速度是非帯快的,但建立在这个目录下的文件在下次开机时就消失了

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【转】Oracle 查看表空间使用率 SQL 脚本
文章转自:http://blog.csdn.net/tianlesoftware/article/details/7619732 1 /* Formatted on 2012/5/31 14:51:13 (QP5 v5.
656 0
MySQL临时表机制的演变(5.6-8.0)
MySQL8.0 临时表 新特性
894 0
PolarDB-X 1.0-SQL 手册-Sequence-Sequence限制及注意事项
限制与注意事项 转换 Sequence 类型时,必须指定 START WITH 起始值; 单元化 Group Sequence 不支持作为源或目标的类型转换,也不支持起始值以外的参数修改; 属于同一个全局唯一数字序列分配空间的每个单元化 Group Sequence,必须指定相同的单元数量和不同的单元索引; 在 PolarDB-X 非拆分模式库(即后端仅关联一个已有的 RDS 物理库)、或拆分模式库中仅有单表(即所有表都是单库单表,且无广播表)的场景下执行 INSERT 时, PolarDB-X 会自动优化并直接下推语句,绕过优化器中分配 Sequence 值的部分。此时 INSERT INT
14 0
重磅|阿里云HBase Ganos全新升级,推空间、时空、遥感一体化基础云服务
Ganos是阿里云时空PaaS服务的自研核心引擎。Ganos已作为云数据库时空引擎与数据库平台融合,建立了以自研云原生数据库POALRDB为基础,联合NoSQL大数据平台(Ali-HBASE和X-Pack Spark)的完整时空地理信息云化管理解决方案。
2097 0
sql创建临时表或视图增加 自增列
select row_number()over(order by [createtime] asc) ida,* from [Opro].[dbo].[DailyReport2010])
745 0
+关注
487
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载