正文
postgresql空间的简单介绍
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
如何扩大postgresql存储空间
1. 概述
cstore_fdw实现了 PostgreSQL 数据库的列式存储。列存储非常适合用于数据分析的场景，数据分析的场景下数据是批量加载的。
这个扩展使用了Optimized Row Columnar (ORC)数据存储格式，ORC改进了Facebook的RCFile格式，带来如下好处:
压缩:将内存和磁盘中数据大小削减到2到4倍。可以扩展以支持不同压缩算法。
列投影:只提取和查询相关的列数据。提升IO敏感查询的性能。
跳过索引:为行组存储最大最小统计值，并利用它们跳过无关的行。
2. 使用
cstore_fdw的安装和使用都非常简单，可以参考官方资料。
thub.com/citusdata/cstore_fdw
注)注意cstore_fdw只支持PostgreSQL9.3和9.4 。
下面做几个简单的性能对比，看看cstore_fdw究竟能带来多大的性能提升。
2.1 数据加载
2.1.1 普通表
CREATE TABLE tb1
(
id int,
c1 TEXT,
c2 TEXT,
c3 TEXT,
c4 TEXT,
c5 TEXT,
c6 TEXT,
c7 TEXT,
c8 TEXT,
c9 TEXT,
c10 TEXT
);
注:要和普通表的全表扫描作对比，所以不建主键和索引。
[postgres@node2 chenhj]$ time psql -p 40382 -At -F, -c "select id,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy tb1 from STDIN with CSV"
COPY 10000000
1.56user 1.00system 6:42.39elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k
776inputs+0outputs (17major+918minor)pagefaults 0swaps
real 6m42.402s
user 0m15.174s
sys 0m14.904s
postgres=# select pg_total_relation_size('tb1'::regclass);
pg_total_relation_size
------------------------
1161093120
(1 row)
postgres=# \timing
Timing is on.
postgres=# analyze tb1;
ANALYZE
Time: 11985.070 ms
插入1千万条记录，数据占用存储大小1.16G，插入耗时6分42秒，分析耗时12秒。
2.1.2 cstore表
$ mkdir -p /home/chenhj/data94/cstore
CREATE EXTENSION cstore_fdw;
CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;
CREATE FOREIGN TABLE cstb1
(
id int,
c1 TEXT,
c2 TEXT,
c3 TEXT,
c4 TEXT,
c5 TEXT,
c6 TEXT,
c7 TEXT,
c8 TEXT,
c9 TEXT,
c10 TEXT
)
SERVER cstore_server
OPTIONS(filename '/home/chenhj/data94/cstore/cstb1.cstore',
compression 'pglz');
[postgres@node2 chenhj]$ time psql -p 40382 -At -F, -c "select id,id::text,id::text,id::text,id::text, id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy cstb1 from STDIN with CSV"
COPY 10000000
1.53user 0.78system 7:35.15elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k
968inputs+0outputs (20major+920minor)pagefaults 0swaps
real 7m35.520s
user 0m14.809s
sys 0m14.170s
[postgres@node2 chenhj]$ ls -l /home/chenhj/data94/cstore/cstb1.cstore
-rw------- 1 postgres postgres 389583021 Jun 23 17:32 /home/chenhj/data94/cstore/cstb1.cstore
postgres=# \timing
Timing is on.
postgres=# analyze cstb1;
ANALYZE
Time: 5946.476 ms
插入1千万条记录，数据占用存储大小390M，插入耗时7分35秒，分析耗时6秒。
使用cstore列存储后，数据占用存储大小降到普通表的3分之1。需要说明的是，由于所有TEXT列填充了随机数据，压缩率不算高，某些实际的应用场景下压缩效果会比这更好。
2.2 Text列的like查询性能对比
2.2.1 普通表
清除文件系统缓存，并重启PostgreSQL
[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart
[root@node2 ~]# free
total used free shared buffers cached
Mem: 2055508 771356 1284152 0 9900 452256
-/+ buffers/cache: 309200 1746308
Swap: 4128760 387624 3741136
[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches
[root@node2 ~]# free
total used free shared buffers cached
Mem: 2055508 326788 1728720 0 228 17636
-/+ buffers/cache: 308924 1746584
Swap: 4128760 381912 3746848
对Text列执行like查询
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.42 0.00 95.40
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.55 330.68 212.08 7351441 4714848
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"
count
--------
100000
(1 row)
real 0m7.051s
user 0m0.001s
sys 0m0.004s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.43 0.00 95.39
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.90 381.53 211.90 8489597 4714956
耗时7.1秒，产生IO读1.14G,IO写108K。
不清文件系统缓存，不重启PostgreSQL，再执行一次。消耗时间降到1.6秒，几乎不产生IO。
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.43 0.00 95.39
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.81 332.20 213.06 7350301 4714364
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"
count
--------
100000
(1 row)
real 0m1.601s
user 0m0.002s
sys 0m0.001s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.43 0.00 95.38
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.80 332.12 213.01 7350337 4714364
2.2.2 cstore表
清除文件系统缓存，并重启PostgreSQL
[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart
[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches
对Text列执行like查询
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.38 0.00 95.45
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.12 376.42 209.04 8492017 4716048
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"
count
--------
100000
(1 row)
real 0m2.786s
user 0m0.002s
sys 0m0.003s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.38 0.00 95.44
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.12 378.75 208.89 8550761 4716048
耗时2.8秒，产生IO读59M,IO写0K。执行时间优化的虽然不是太多，但IO大大减少，可见列投影起到了作用。
不清文件系统缓存，不重启PostgreSQL，再执行一次。消耗时间降到1.4秒，几乎不产生IO。
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.36 0.00 95.47
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.75 376.33 207.58 8550809 4716524
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"
count
--------
100000
(1 row)
real 0m1.424s
user 0m0.002s
sys 0m0.001s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.36 0.00 95.47
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.70 375.96 207.38 8550809 4716588
2.3 对Int列执行=查询
2.3.1 普通表
清除文件系统缓存，并重启PostgreSQL后
[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart
[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches
对Int列执行=查询
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.79 0.00 0.37 3.33 0.00 95.50
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.25 373.21 205.67 8560897 4717624
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"
count
-------
1
(1 row)
real 0m6.844s
user 0m0.002s
sys 0m0.006s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.79 0.00 0.37 3.34 0.00 95.49
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.60 422.57 205.54 9699161 4717708
耗时6.8秒，产生IO读1.14G,IO写84K
不清缓存，再执行一次。消耗时间降到1.1秒，几乎不产生IO。
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.79 0.00 0.37 3.33 0.00 95.50
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.44 421.37 204.97 9699177 4718032
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"
count
-------
相关问答
Q1: postgresql 空间要素数据怎样导出导入
安装postgresql
yum install postgresql postgresql-server
mysql占用端口3306 pgsql是5432
导入整个数据库
psql -U postgres(用户名) 数据库名(缺省时同用户名) /data/dum.sql
导出整个数据库
pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名) /data/dum.sql
导出某个表
pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名) -t table(表名) /data/dum.sql
压缩方法
一般用dump导出数据会比较大，推荐使用xz压缩
压缩方法 xz dum.sql 会生成 dum.sql.xz 的文件
6
xz压缩数据倒数数据库方法
xzcat /data/dum.sql.xz | psql -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)
Q2: 如何在postgresql中创建空间数据库
PersistenceConfig（持久层配置）
我们想要一个配置了所有可用仓库的MONGODB配置。在这个简单的应用中我们只用了一个仓库，所以配置也非常的简单：
@Configuration
class PersistenceConfig {
@Bean
public AccountRepository accountRepository() throws UnknownHostException {
return new MongoAccountRepository(mongoTemplate());
Q3: 如何在postgresql9.3中创建空间数据库
f(isset($_POST['submit'])$_POST['submit']=='提交'){
3 //判断是否是提交过来的
4 $intext = $_POST['intext'];
5 if($intext!=null||$intext!=''){
6 $link = mysql_connect("localhost", "root", "123456");
7 //数据库配置信息 第一个参数数据库位置第二个是用户名第三个是密码
8 mysql_select_db("szn_test");
9 //设置要使用的数据库
10 $sql = "select * from demo where res = '".$intext."'";
Q4: 如何解决postgresql磁盘不足的问题
1）增加磁盘，创建分区，挂载目录
2）目录授权
Chown postgres /data2'
Chmod 777 /data2'
3）创建表空间指向该目录
create tablespace ts_demo location '/data2';
4）设置默认表空间参数值
SET default_tablespace = ts_demo;
select * from pg_settings where name ='default_tablespace';
5）后续创建表会默认存储在该表空间对应的目录
创建表查看表存储路径是否改过来了
select pg_relation_filepath('out_tiqu0')
Postgres数据库有默认的2个表空间
select * from pg_tablespace
Name | Owner | Location
-----------+----------+-----------------------------------
pg_default | postgres |
pg_global | postgres |
创建表如果不指定表空间 那么就会默认使用pg_default，对应的路径为pg安装路径中data文件夹下面，
6）对已存在的表更改表空间
alter table out_831108_1 set tablespace ts_demo;
更改之后再查看是否改过来了
