1 Postgresql安装
1.1 获取postgresql源码
下载postgresql-9.5.10.tar.gz (https://www.postgresql.org/ftp/source/v9.5.10/)
解压 tar -xzf postgresql-9.5.10.tar.gz
进入postgresql-9.5.10文件夹
1.2 编译postgresql源码
$ ./configure --prefix=/usr/pgsql-9.5 --enable-debug --enable-cassert
其中/usr/pgsql-9.5是安装路径,可以自定义
可能报错:
通过rpm -qa | grep readline命令查看可以发现系统默认是自带readline包
通过yum search readline搜索相关readline包,发现有个包readline-devel
安装readline-devel
$ yum -y install -y readline-devel
- 构建源代码
# 查看逻辑CPU的个数
$ cat /proc/cpuinfo| grep "processor"| wc -l
依据逻辑CPU的个数设置并行数进行编译
$ make -j 32
$ make install
其中32代表并行数量
- 设置环境变量
$ vi /etc/profile
export PATH=$PATH:/usr/pgsql-9.5/bin
刷新权限
source /etc/profile
- 确保pg_config是可运行的,其路径是正确的。
这一步的设置很重要,因为PG_strom是跟postgresql共享配置文件的
$ which pg_config
$ pg_config --pgxs
如果此处不对,多半是环境变量设置问题。
1.3 创建linux用户
useradd postgres
1.4 初始化数据库
- 创建数据库目录和日志目录
$ mkdir /usr/pgsql-9.5/data
$ mkdir -p /usr/pgsql-9.5/log
$ touch /usr/pgsql-9.5/log/pgsql.log
- 赋予目录postgres用户组权利
chown -R postgres:postgres /usr/pgsql-9.5/data
chown -R postgres:postgres /usr/pgsql-9.5/log
chown -R postgres:postgres /usr/pgsql-9.5/log/pgsql.log
- 初始化数据库
$ su - postgres
$ initdb -D /usr/pgsql-9.5/data
可以看到/usr/pgsql-9.5/data目录下产生了大量的文件
1.5 修改配置文件
- 设置监听
$ vi /usr/pgsql-9.5/data/postgresql.conf
改#listen_addresses = 'localhost' 为 listen_addresses='*'
- 信任远程连接
$ vi /usr/pgsql-9.5/data/pg_hba.conf
修改如下内容,信任指定服务器连接(设置免密)
# IPv4 local connections:
host all all 0.0.0.0/0 trust
1.6 启动postgresql
添加软链接
$ ln -s /usr/pgsql-9.5/bin/psql
启动
$ pg_ctl start -D /usr/pgsql-9.5/data
查看是否启动,出现下面界面代表启动成功
$ ps -ef | grep postgres
停止
$ pg_ctl stop -D /usr/pgsql-9.5/data
2 Postgresql使用
2.1 postgresql表操作参考
# 创建新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
# 插入数据
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');
# 选择记录
SELECT * FROM user_tbl;
# 更新数据
UPDATE user_tbl set name = '李四' WHERE name = '张三';
# 删除记录
DELETE FROM user_tbl WHERE name = '李四' ;
# 添加栏位
ALTER TABLE user_tbl ADD email VARCHAR(40);
# 更新结构
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
# 更名栏位
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
# 删除栏位
ALTER TABLE user_tbl DROP COLUMN email;
# 表格更名
ALTER TABLE user_tbl RENAME TO backup_tbl;
# 删除表格
DROP TABLE IF EXISTS backup_tbl;
# 查看表
\dt+
# 查看数据库
\l
2.2 生成表数据
/home/users/chenzhuo/create.py文件(192.168.6.175)
生成data1.csv(1亿行)、data2.csv(100w)与data3.csv(100w)(数据放在192.168.6.174的/opt目录下)
2.3 postgresql使用
1)运行,进入postgres用户
su - postgres
2)连接postgres数据库
psql
3)生成表和导入数据:
CREATE TABLE mytable(name VARCHAR(20), time VARCHAR(20), phone VARCHAR(10));
COPY mytable FROM '/opt/data2.csv' WITH CSV HEADER;
CREATE TABLE youtable(name VARCHAR(20), time VARCHAR(20), phone VARCHAR(10));
COPY youtable FROM '/opt/data2.csv' WITH CSV HEADER;
4)查询操作:
1.关联join
\timing on
select count(*) from mytable my inner join youtable you on my.name=you.name and my.time=you.time and my.phone =you.phone;
2.查询Group by
3.子查询
可能报错:
出现中文编码问题,加入
\encoding GBK
3 PG-strom安装
3.1 PG_strom源码下载
下载地址:https://github.com/heterodb/pg-strom
代码说明:最新版本是2.0,但是由于还不稳定,存在一些bug,一开始就用最新版本,导致运行的时候出现一些莫名其妙的错误。建议下载1.x的版本。
下载后上传到centos系统,解压后,进入pg-strom文件夹。
3.2 PG_strom源码编译
$ make
$ make install
可能报错:
1 pg_config was not found
检查postgresql的PATH环境变量是否配置正确
2 CUDA related files were not found
检查CUDA安装是否正确
3.3 修改配置文件
$ vi /usr/pgsql-9.5/data/postgresql.conf
shared_preload_libraries = '$libdir/pg_strom'
shared_buffers = 10GB
work_mem = 10GB
max_wal_size = 32GB
4 PG_strom使用
1)启动pg-strom
$ su - postgres
$ pg_ctl -D /usr/pgsql-9.5/data start
出现以上红色框,说明PG_strom已经安装好了
启动可能报错:
在CUDA安装下查找库的动态链接器
$ sudo sh -c "echo /usr/local/cuda/lib64 > /etc/ld.so.conf.d/cuda-x86_64.conf"
$ sudo ldconfig
$ bash
其他可能报错:
NVIDIA的MPS is not running
启动 mps-control
export CUDA_VISIBLE_DEVICES=0
export CUDA_MPS_PIPE_DIRECTORY=/tmp/nvidia-mps
export CUDA_MPS_LOG_DIRECTORY=/tmp/nvidia-log
nvidia-cuda-mps-control -d
关闭mps-control
echo quit | nvidia-cuda-mps-control
执行完了,可以查看log 文件
2)添加插件
# psql
# CREATE EXTENSION pg_strom;
使用表操作与postgresql一样。
5 citus安装
5.1 获取citus源码
下载citus5.2.2版本,并上传至服务器任意目录
$ tar -xzvf citus-5.2.2.tar.gz
5.2 编译、安装
$ cd citus-5.2.2
$ ./configure
$ make
$ sudo make install
5.3 更改配置
- 添加插件
$ cd /usr/pgsql-9.5/data
$ vi postgresql.conf
# 在 shared_preload_libraries 参数中添加 'citus' 并放在第一个
shared_preload_libraries = 'citus'
5.4 测试插件是否安装成功
重启postgresql后,
$ su - postgres
$ psql
$ create extension citus;
5.5 citus集群安装
在多台服务器上执行安装步骤3.1-3.4,并将其中一台当做master,另外其他的为worker。
以下操作均在master上执行(hadoop3):
- 添加所有worker节点信息
$ echo " 172.22.13.222 5432" | sudo -u postgres tee -a /usr/pgsql-9.5/data/pg_worker_list.conf
$ echo " 172.22.13.223 5432" | sudo -u postgres tee -a /usr/pgsql-9.5/data/pg_worker_list.conf
- 重新加载主数据库设置
$ pg_ctl reload -D /usr/pgsql-9.5/data
- 验证安装是否成功
$ sudo -i -u postgres psql -c "SELECT * FROM master_get_active_worker_nodes();"
6 citus使用
以下操作均在master上执行:
- 连接psql
su – postgres
psql
- 建表
\timing on
create table t01(id int, id2 int, t text);
create table t02(id int, id2 int, t text);
- 定义为分布式表
SELECT master_create_distributed_table('t01', 'id2', 'hash');
SELECT master_create_distributed_table('t02', 'id2', 'hash');
- 创建分片
每个表创建16个分片,1个副本
SELECT master_create_worker_shards('t01', 16, 1);
SELECT master_create_worker_shards('t02', 16, 1);
- 制造数据
citus不支持insert 后面的SELECT另一张表的这种语法,支持下面的语法:
copy (select id, id, lpad(id::text, 5, id::text) from generate_series(1,100000000) as t(id)) to '/tmp/t01.txt';
copy t01 from '/tmp/t01.txt';
copy t02 from '/tmp/t01.txt';
- 执行
explain select a.t, b.t from t01 a, t02 b where a.id2=b.id2 and a.id2=5;
- 如果join时不使用分布键需进行
Set citus.task_executor_type to "task-tracker";
explain select a.t, b.t from t01 a, t02 b where a.id=b.id2 and a.id2=5;
- 查看分片:
SELECT * from pg_dist_shard;
- 查看分片分布:
SELECT * from pg_dist_shard_placement order by shardid;
- 查看GPU使用情况:
watch -n 1 nvidia-smi
7 简单性能测试
7.1 postgresql
set pg_strom.enabled=off;
1)创建测试表
create unlogged table test1 (id int, c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, info text, crt_time timestamp);
create unlogged table test2 (id int, c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, info text, crt_time timestamp);
2)写入测试数据
copy ( select
id,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
md5(random()::text),
clock_timestamp()
from generate_series(1,10000000)
t(id)) to '/tmp/test1.txt';
copy ( select
id,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
md5(random()::text),
clock_timestamp()
from generate_series(1,100000000)
t(id)) to '/tmp/test4.txt';
copy test1 from '/tmp/test1.txt';
copy test02 from '/tmp/test2.txt';
3)聚合函数
explain (analyze,verbose,timing,costs,buffers) select count(*) from test2;
13869ms
4)join
explain (analyze,verbose,timing,costs,buffers) select count(*) from test1 join test2 on test1.id = test2.id;
37693ms
4)group by
explain (analyze,verbose,timing,costs,buffers) select test1.c1,count(*) from test1 join test2 using (id) group by 1;
41698ms
5)子查询
explain (analyze,verbose,timing,costs,buffers) select * from test1 where c1 = (select c1 from test2 where c1>500 order by c1 desc limit 1);
18902ms
explain (analyze,verbose,timing,costs,buffers) select * from (select id,c1 from test2 order by c1 desc) a limit 30;
23319ms
7.2 pg-strom单机
1)聚合函数
explain (analyze,verbose,timing,costs,buffers) select count(*) from test2;
9401ms
2)join
explain (analyze,verbose,timing,costs,buffers) select count(*) from test1 join test2 on test1.id = test2.id;
45232ms
3)group by
explain (analyze,verbose,timing,costs,buffers) select test1.c1,count(*) from test1 join test2 using (id) group by 1;
37968ms
4)子查询
explain (analyze,verbose,timing,costs,buffers) select * from test1 where c1 = (select c1 from test2 where c1>500 order by c1 desc limit 1);
20260ms
explain (analyze,verbose,timing,costs,buffers) select * from (select id,c1 from test2 order by c1 desc) a limit 30;
21944ms
7.3 pg-strom分布式
1)创建测试表
create unlogged table test1 (id int, c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, info text, crt_time timestamp);
create unlogged table test2 (id int, c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, info text, crt_time timestamp);
2)指定为分布式表
SELECT master_create_distributed_table('test1', 'id', 'hash');
SELECT master_create_distributed_table('test2', 'id', 'hash');
3)创建分片
每个表创建16个分片,1个副本
SELECT master_create_worker_shards('test1', 16, 1);
SELECT master_create_worker_shards('test2', 16, 1);
4)创建数据(不支持insert into … select)
copy ( select
id,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
md5(random()::text),
clock_timestamp()
from generate_series(1,1000)
t(id)) to '/tmp/test1.txt';
copy ( select
id,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
(random()*1000)::integer,
md5(random()::text),
clock_timestamp()
from generate_series(1,1000)
t(id)) to '/tmp/test2.txt';
copy test1 from '/tmp/test1.txt';
copy test2 from '/tmp/test2.txt';
5)聚合函数
explain (analyze,verbose,timing,costs,buffers) select count(*) from test2;
5486ms
6)join
explain (analyze,verbose,timing,costs,buffers) select count(*) from test1 join test2 on test1.id = test2.id;
8542ms
7)group by
explain (analyze,verbose,timing,costs,buffers) select test1.c1,count(*) from test1 join test2 using (id) group by 1;
9781ms
8)子查询
explain (analyze,verbose,timing,costs,buffers) select * from test1 where c1 = (select c1 from test2 where c1>500 order by c1 desc limit 1);
不支持=子查询
explain (analyze,verbose,timing,costs,buffers) select * from (select id,c1 from test2 order by c1 desc) a limit 30;
不支持limit子查询、order by子查询。
7.4 对比
查询 | Postgres(ms) | Pg-strom单机(ms) | Pg-strom分布式(ms) |
聚合函数 | 13869 | 9401 | 5486 |
join | 37693 | 45232 | 8542 |
Group by | 41698 | 37968 | 9781 |
子查询 | 18902 | 20260 | 某些不支持 |
子查询 | 23319 | 21944 | 某些不支持 |
性能分析:postgres与pg-strom单机速度差不多,但是pg-strom分布式速度更快,目前不支持子查询。
postgres与pg-strom性能接近的原因分析:上述postgres与pg-strom单机的性能差别不大,经过分析发现是因为在查询过程中,只有扫描(Custom Scan(GPUPreAgg))部分用到了GPU,而在join上并没有用到,还是Hash Join(不是GPU Hash Join)。
造成上述的原因是因为数据量太多,该型号的GPU处理性能受到限制。当把数据(1000w与1亿行)的join改为(1000w与100w行)的join时,又用到了GPU Hash Join。建议更新更好的GPU型号设备。
7.5 报错
语句较复杂时报错(1000w与1亿join)