升级greenplum 5.x到6.0beta3

之前的老集群,使用的是ubuntu 16.04下的greenplum 5.8,后来随系统升级到5.19版本。最近发现运行速度变慢(慢得过分,可以一个sql喝几杯茶的程度),而且经过和在另个一个新集群上的相同数据对比测试,发现速度差别至少有2个量级。

首先尝试将数据库备份,删除再恢复,发现速度改善很小。有了上个集群的安装经验,于是决定将操作系统升级,并且将greenplum也升级6.0版本(其master版本是7.0,但稳定性还是有问题,发现有几个sql查询异常)。

在升级过程中,遇到一个小坑:机器名不要命名为host01-1的形式,会导致greenplum将其判别为一类特殊的host。这个问题还是通过浏览gpinitsystem的代码才找出来的。

另外也再记录一下postgis的安装过程。由于libxerces-c-dev和gp-xerces的冲突,postgis安装时需要将raster功能禁止,在greenplum安装完成后执行:

source /usr/local/gdbp/greenplum_path.sh
git clone https://github.com/greenplum-db/geosptial.git
cd geospatial/postgis/build/postgis-2.1.5
./configure --with-pgconfig=$GPHOME/bin/pg_config --without-raster --without-topology --prefix=$GPHOME
make USE_PGXS=1 clean all
sudo make USE_PGXS=1 install 

这样在所有节点安装完成后即可开始greenplum的初始化脚本。

在ubuntu 18.04上安装最新版greenplum(7.0.alpha)

近期组建了一个新的大数据测试集群,由4个物理机器构成一个测试集群,分别命名为hadoop1-4号机器,想要进行MPP并行数据库的安装与测试工作。服务器物理机安装的操作系统为ubuntu server 18.04版本。开始以为可以采用lanchpad的官方库进行安装,发现greenplum官方目前并未支持ubuntu 18.04,需要从源码编译安装。在尝试编译的过程中遇到了几个错误,最终通过一个变通的方法进行了处理,在此记录一下过程。

简而言之,是greenplum所采用的xerces-c库与ubuntu官方库的版本不一致,导致调用时遇到了DSO missing错误。可以采用禁止gporca的查询优化器方法来避免此错误,同时安装postgis插件也需要禁止raster功能,或者移除官方的xerces-c库,使用greenplum自带的xerces-c库。

下面记录一下第二种方案的编译过程。

一、安装gp-xercesc

git clone https://github.com/greenplum-db/gp-xerces.git
cd gp-xerces
mkdir build
cd build
../configure --prefix=/usr/local
make
sudo make install

二、安装gporca

git clone https://github.com/greenplum-db/gporca.git
cd gporca
cmake -GNinja -H. -Bbuild
sudo ninja install -C build

三、安装gpdb

git clone https://github.com/greenplum-db/gpdb.git
cd gpdb
sudo ./README.ubuntu.bash

此时系统会默认安装libxerces-c-dev库,需要将此库移除以使其正确编译。

sudo apt remove libxerces-c-dev   ###此库会有冲突
./configure --enable-orca --with-perl --with-python --with-libxml --prefix=/usr/local/gpdb
make -j32
sudo make -j32 install
sudo ldconfig

这样可以确保编译通过,且可安装,但在初始化greenplum之前还需要确保:

1、master机器到其他机器可以ssh密钥登陆

2、limits.conf和sysctl.conf已修改,且修改后重新登陆过。

3、在其他各机器执行相同的操作。

上述操作执行完之后,在master节点,指定某一目录作为greenplum的数据目录,比如/data,然后在master节点上建立/data/master目录,在所有节点上建立/data/primary目录。然后:

source /usr/local/gpdb/greenplum_path.sh
cd /data/master
cp /usr/local/gpdb/docs/cli_help/gpconfigs/hostfile_gpinitsystem .
cp /usr/local/gpdb/docs/cli_help/gpconfigs/gpinitsystem_config .

修改这两个配置文件,hostfile记录了greenplum所有机器的hostname,一行一个机器名;gpinitsystem_config记录了greenplum的目录设置和segment设置参数,需进行相应的修改,完成后即可启动数据库。

gpinitsystem -c gpinitsystem_config -h hostfile_gpinitsystem 

postgresql中字符串的数字形式查询

在数据库执行sql查询时,需要在where部分限制执行范围为某字段为数字字符串形式。在使用LIKE语句时遇到了困难。LIKE只支持%和_两种通配符(前者为不限长度的字符串,后者为单个字符)。

其解决方案是使用~函数符号,这是支持正则形式的like功能代替。

因此解决方案就是:

select * from my_table where my_field ~ '^[0-9]+?$'

这样其含义就是查找所有纯数字形式的字符串。

postgresql: ‘not in’ subquery

在使用sql语句进行查询时突然发现一个语句不能返回合适结果:

select count(*) from weibo.poi where poiid not in (select poiid from weibo.url) and poiid not in (select poiid from weibo.url_bad);

正常应该有结果,但此语句返回空集。
搜索后发现后面的not in语句中存在null值,导致返回空集。因此应该这样修改:

select count(*) from weibo.poi where poiid not in (select poiid from weibo.url) and poiid not in (select poiid from weibo.url_bad where poiid is not null);

greenplum遇到节点死锁问题

在针对一个不大的表格执行清空操作时,遇到了长时间执行且未返回结果的情况。网上搜索后,发现是遇到了死锁问题。

1. 可以在master节点,查询各进程并判断有可能是死锁的进程:

 

select gp_execution_dbid(), pid, relation::regclass, locktype, mode, granted
from gp_dist_random('pg_locks');

2. 查看具体什么语句持有的锁

select gp_execution_dbid() dbid,procpid,current_query
from gp_dist_random('pg_stat_activity')
where procpid in
(select pid from gp_dist_random('pg_locks') where locktype='relation' and mode='ExclusiveLock');

3.处理持有锁的pid

select pg_terminate_backend('procpid');

在greenplum中删除重复数据

在PostgreSQL中,可以借助ctid变量删除重复数据:postgresql: 使用SQL删除重复数据,但在GreenPlum中,由于数据是分散到不同的segment上,因此仅使用ctid无法删除重复数据了,需要借助pg_segment_id来解决。

参考了这篇文章:http://www.cnblogs.com/kuang17/p/5861700.html,最终解决方法就是:

delete from taxi_2010_dup where (gp_segment_id,ctid) not in (select gp_segment_id,min(ctid) from taxi_2010_dup group by tid,gpstime,gp_segment_id);

在实际测试过程中,发现上述sql有时删除重复数据不彻底,还是这种办法好:

DELETE FROM dupes T1
    USING   dupes T2
WHERE   T1.ctid < T2.ctid  -- delete the older versions
    AND T1.key  = T2.key;  -- add more columns if needed

使用window函数处理表中相邻行间类似数据

出租车轨迹数据中,有时候同一辆车会在同一个空间位置发回来很多数据,这时除了时间字段外其他字段内容都相同。为了更好的进行数据清洗,需要处理这样的数据,即将其进行缩减。

数据表定义为:

CREATE TABLE public.taxi_2010
(
tid integer NOT NULL,
isempty smallint,
gpstime timestamp without time zone,
lon real,
lat real
);

比如下面的第1条和第2条就是重复的数据。

 tid  | isempty |   lon   |   lat   |       gpstime       

-------+---------+---------+---------+---------------------

 10037 |       1 | 121.486 | 31.3117 | 2015-04-01 00:31:07

 10037 |       1 | 121.486 | 31.3117 | 2015-04-01 00:31:15

 10037 |       1 | 121.486 | 31.3118 | 2015-04-01 00:31:17

 10037 |       1 | 121.486 | 31.3121 | 2015-04-01 00:31:22

要剔除这样重复的点,可以使用PostgreSQL中的window函数进行处理:

select t1.tid,t1.isempty,t1.lon,t1.lat,t1.gpstime from
(select tid,isempty,lon,lat,gpstime,row_number() over (order by tid,gpstime) as seq1 from taxi_2010 t
order by tid,gpstime) t1
inner join
(select tid,lon,lat,gpstime,row_number() over (order by tid,gpstime) as seq2 from taxi_2010 t
order by tid,gpstime) t2
on t2.seq2=t1.seq1+1 and t1.tid=t2.tid and (not (t1.lat::numeric=t2.lat::numeric and t1.lon::numeric=t2.lon::numeric));

其中,row_number()函数提取当期行号作为唯一id,inner join提取两表中共有的要素,通过行号+1提取邻近行。

参考:

https://blog.csdn.net/u013986802/article/details/74315870

PostgreSQL:real类型的比较

PostgreSQL的数据类型,对于小数来说,有decimal、numeric、real、double precision四种类型,在文档中,其定义为:

Name Storage Size Description Range
decimal variable user-specified precision, exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
numeric variable user-specified precision, exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
real 4 bytes variable-precision, inexact 6 decimal digits precision
double precision 8 bytes variable-precision, inexact 15 decimal digits precision

注意,real、double的描述为inexact,此时要用此类型进行逻辑判断的话,需要专门处理。

如下述的sql语句,其中lon、lat就定义为real类型:

select tid from taxi where lon1=lon2 and lat1=lat2

这个语句就不能正确的返回结果,需要这样处理才可以返回结果:

select tid from taxi where lon1::numeric=lon2::numeric and lat1::numeric=lat2::numeric

即需要将其转换成numeric类型进行精确判断。

Install psycopg2 on mac sierra

需要在我的mbp上进行数据入库测试,因此就需要安装psycopg2,但安装的时候一直有提示:

ld: library not found for -lssl

clang: error: linker command failed with exit code 1 (use -v to see invocation)

开始搜索以为库的位置不对,比如之前类似的问题:http://stackoverflow.com/questions/27264574/import-psycopg2-library-not-loaded-libssl-1-0-0-dylib

后来感觉不太对,又加上sierra关键词找,果然找到一个类似问题:
http://stackoverflow.com/questions/39767810/cant-install-psycopg2-package-through-pip-install-is-this-because-of-sierra
发现解决方法非常简单,就是安装xcode-select工具。

xcode-select –install

然后再安装psycopg2就没有任何问题了:

sudo pip install psycopg2