OushuDB入门（二）——性能篇

用户1148526

发布于 2019-05-25 19:36:35

7190

发布于 2019-05-25 19:36:35

一、OushuDB新版本的性能提升

    Oushu Database 2.2版本的执行器基于Apache HAWQ的执行器，而Apache HAWQ的执行器起源于Greenplum Database和PostgreSQL，是一个很成熟的执行器，经过多年的优化和沉淀。而Oushu Database 3.0是一个Major Release，最大的一个亮点功能是完全重新设计的新执行器，性能比2.2版本的执行器要快10倍左右。新执行器充分利用了新的CPU硬件指令，比如SIMD等，可以做到不浪费CPU的每一个时钟周期。

    具体来说，Oushu Database 3.0版本比Oushu Database 2.2.0版本有如下性能增强：

全新执行器，支持scan，projection，filter，aggregation等基本操作，性能提升约10倍。不支持的操作会fallback到老的执行器。
支持ORC外部存储格式，结合新的执行器，外部存储的性能提升10-50倍。
新执行器只支持ORC格式。

    Oushu Database 3.0.1版本比Oushu Database 3.0版本有如下新特性增强：

内部表支持ORC, TEXT和CSV格式。新执行器现在只支持ORC格式，所以该版本添加了ORC内部表格式，使得用户可以在更多场景使用新执行器。
外部表创建时默认由可读变为读写。创建的外部表在3.0.1版本之前默认为只读，因为可读写的外部表现在用户用的比较多，所以3.0.1版本更改了默认行为，创建的外部表默认为可读可写。
new_executor GUC值的默认值由”auto”改成了”on”，这样用户在ORC表上使用新执行器暂时还未支持的功能时会提示错误信息，用户如果需要把不支持的功能自动切换到老执行器，用户需要修改该值为”auto”.

    Oushu Database 3.1.0版本比Oushu Database 3.0.1版本有如下新特性增强：

新执行器新增对limit操作符的支持。
新执行器新增对sort操作符的支持。
新执行器新增对max/min聚集函数的支持。
新执行器新增内置的欧氏距离函数。
新执行器针对scan操作符加入了性能优化。
新执行器针对聚集函数加入了性能优化。
ORC格式的外部表text列支持dictionary encoding。
修复了一些ORC文件社区兼容性的问题。
ORC表支持更多的数据类型，包括bool, char, varchar, bytea, date, time, timestamp和array。
ORC表支持分区表。
ORC格式的外部表支持并发读写。

    目前最新版本的Oushu Database 3.1.1版本比Oushu Database 3.1.0版本在性能上是相同的，所做的增强为主节点（master）故障时，备用主节点（standby）可自动切换为主节点，维持集群正常运行。OushuDB各版本新增特性信息，参考[http://www.oushu.io/docs/ch/release-notes.html](http://www.oushu.io/docs/ch/release-notes.html)。

二、OushuDB性能测试环境

1. 实验目的

    本实验通过模拟一个典型的应用场景和实际数据量，测试对比OushuDB内部表、外部表的读写性能。

2. 硬件环境

    四台VMware虚机组成的HDP集群，每台机器配置如下：

15K RPM SAS 100GB
Intel(R) Xeon(R) E5-2620 v2 @ 2.10GHz，双核双CPU
8G内存，8GSwap
10000Mb/s虚拟网卡

3. 软件环境

Linux：CentOS Linux release 7.2.1511 (Core)
Ambari：2.4.2
HDP：2.5.3
OushuDB：3.1.1

4. 数据模型

（1）表结构

    实验模拟一个记录页面点击数据的应用场景。数据模型中包含日期、页面、浏览器、引用、状态5个维度表，1个页面点击事实表。表结构和关系如图1所示。

图1

（2）记录数

    各表的记录数如表1所示。

表名	行数
page_click_fact	1亿
page_dim	20万
referrer_dim	100万
browser_dim	2万
status_code	70
date_dim	366

表1

（3）典型查询

查询给定周中support.sas.com站点上访问最多的目录。select top_directory, count(*) as unique_visits from (select distinct visitor_id, substr(requested_file_txt,1,10) top_directory from page_click_fact, page_dim, browser_dim where domain_nm = 'support.sas.com' and flash_enabled_flg=1 and extract(week from detail_tm) = 19 and extract(year from detail_tm) = 2017 and page_click_fact.page_sk = page_dim.page_sk and page_click_fact.browser_sk = browser_dim.browser_sk ) directory_summary group by top_directory order by unique_visits;
查询各月从www.google.com访问的页面。select domain_nm, requested_file_txt, count(*) as unique_visitors, month from (select distinct domain_nm, requested_file_txt, visitor_id, extract(month from detail_tm) as month from page_click_fact, page_dim, referrer_dim where domain_nm = 'support.sas.com' and referrer_domain_nm = 'www.google.com' and page_click_fact.page_sk = page_dim.page_sk and page_click_fact.referrer_sk = referrer_dim.referrer_sk ) visits_pp_ph_summary group by domain_nm, requested_file_txt, month order by domain_nm, requested_file_txt, unique_visitors desc, month asc;
给定年份support.sas.com站点上的搜索字符串计数。select query_string_txt, count(*) as count from page_click_fact, page_dim where query_string_txt <> '' and domain_nm='support.sas.com' and extract(year from detail_tm) = '2017' and page_click_fact.page_sk = page_dim.page_sk group by query_string_txt order by count desc;
查询给定周中support.sas.com站点上浏览超过10秒的页面。select domain_nm, requested_file_txt, count(*) as unique_visits from (select distinct domain_nm, requested_file_txt, visitor_id from page_click_fact, page_dim where domain_nm='support.sas.com' and extract(week from detail_tm) = 19 and extract(year from detail_tm) = 2017 and seconds_spent_on_page_cnt > 10 and page_click_fact.page_sk = page_dim.page_sk ) visits_summary group by domain_nm, requested_file_txt order by unique_visits desc;

三、实验步骤

1. 查看并修配置

（1）资源相关配置

    查看资源管理类型，该值缺省值为none，表示使用独立模式管理资源。保持缺省值none。

hawq config -s hawq_global_rm_type

    查看每个segment的最大虚拟内存。使用独立模式（hawq\_global\_rm\_type=none）时，可以限制每个HAWQ segment所能使用的资源。保持初始安装值6GB。

hawq config -s hawq_rm_memory_limit_perseg

    查看每个segment的最大cpu核数。hawq\_rm\_nvcore\_limit\_perseg参数设置每个HAWQ segment使用的最大CPU虚拟核数。保持初始安装值4。

hawq config -s hawq_rm_nvcore_limit_perseg

    查看一条语句可以使用的最大虚拟段数量。hawq\_rm\_nvseg\_perquery\_limit参数在服务器级别上限制执行一条语句可以使用的最大虚拟段数量，缺省值为512。保持缺省值none

hawq config -s hawq_rm_nvseg_perquery_limit

    关于HAWQ的资源管理，参考[https://blog.csdn.net/wzy0623/article/details/70314393](https://blog.csdn.net/wzy0623/article/details/70314393)。

（2）执行器相关配置

    查看优化器设置。保持缺省值off。

hawq config -s optimizer

    为了避免查询因为新的执行器不支持而报错，将执行器从缺省的ON修改为AUTO。

# 设置new_executor参数
hawq config -c new_executor -v AUTO
# 重载配置
hawq stop cluster -u 
# 确认new_executor设置为AUTO
hawq config -s new_executor

    关于HAWQ的查询优化，参考[https://blog.csdn.net/wzy0623/article/details/70167990](https://blog.csdn.net/wzy0623/article/details/70167990)。

2. 建立用户和测试库

psql

create role wxy login password '123456' ;
alter role wxy with createdb;
alter role wxy with createexttable (type='readable',protocol='gpfdist') ;
alter role wxy with createexttable (type='writable',protocol='gpfdist') ;
alter role wxy with createexttable (type='readable',protocol='hdfs') ;
alter role wxy with createexttable (type='writable',protocol='hdfs') ;
\q

psql -U wxy -h hdp3 -d postgres

create database test;
\c test;
create schema wxy authorization wxy;
\q

    说明：

创建外部表需要给用户授予with createexttable权限，否则会报以下错误：permission denied: no privilege to create a writable hdfs external table
只有表的属主才能执行drop table或者truncate table操作，否则会报以下错误：ERROR: must be owner of relation tablename

3. 建立基础表

    基础数据表用于向其它测试表中装载数据。

psql -U wxy -h hdp3 -d test

drop external table if exists date_dim;
create external table date_dim(  
    cal_dt              date,        
    day_in_cal_yr_no    int4,  
    day_of_week_no      int4,  
    start_of_month_dt   date,   
    start_of_quarter_dt date,       
    start_of_week_dt    date,           
    start_of_year_dt    date) 
location ('hdfs://hdp1:8020/hawq_data/test_data/date_dim')
format 'text' (delimiter E'\t'); 

drop external table if exists browser_dim;
create external table browser_dim(  
    browser_sk              int8,                                    
    browser_nm              varchar(100),                              
    browser_version_no      varchar(100),                              
    flash_version_no        varchar(100),                              
    flash_enabled_flg       int,                                       
    java_version_no         varchar(100),                              
    platform_desc           text,                                    
    java_enabled_flg        int,      
    java_script_enabled_flg int,      
    cookies_enabled_flg     int,                                       
    user_language_cd        varchar(100),           
    screen_color_depth_no   varchar(100),              
    screen_size_txt         text     
) 
location ('hdfs://hdp1:8020/hawq_data/test_data/browser_dim')
format 'text' (delimiter E'\t'); 

drop external table if exists page_dim;  
create external table page_dim(  
    page_sk             int8,                                    
    domain_nm           varchar(200),                              
    reachability_cd     text,                             
    page_desc           text,                                    
    protocol_nm         varchar(20)   
) 
location ('hdfs://hdp1:8020/hawq_data/test_data/page_dim')
format 'text' (delimiter E'\t');  

drop external table if exists referrer_dim;   
create external table referrer_dim(  
    referrer_sk         int8,                   
    referrer_txt        text,                  
    referrer_domain_nm  varchar(200)   
) 
location ('hdfs://hdp1:8020/hawq_data/test_data/referrer_dim')
format 'text' (delimiter E'\t');  

drop external table if exists status_code_dim;   
create external table status_code_dim(  
    status_cd           varchar(100),                              
    client_error_flg    int4,                                       
    status_cd_desc      text,                                    
    server_error_flg    int4  
) 
location ('hdfs://hdp1:8020/hawq_data/test_data/status_code_dim')
format 'text' (delimiter E'\t');  

drop external table if exists page_click_fact; 
create external table page_click_fact(  
    visitor_id                varchar(100),                              
    detail_tm                 timestamp,                                 
    page_click_dt             date,                                      
    page_sk                   int8,                                    
    client_session_dt         date,   
    previous_page_sk          int8,                                    
    referrer_sk               int8,                                    
    next_page_sk              int8,                                    
    status_cd                 varchar(100),                              
    browser_sk                int8,                                    
    bytes_received_cnt        int8,                                    
    bytes_sent_cnt            int8,                                    
    client_detail_tm          timestamp,                                 
    entry_point_flg           int4,                                       
    exit_point_flg            int4,                                       
    ip_address                varchar(20),                               
    query_string_txt          text,                                    
    seconds_spent_on_page_cnt int4,                                       
    sequence_no               int4,                                       
    requested_file_txt        text      
) 
location ('hdfs://hdp1:8020/hawq_data/test_data/page_click_fact')
format 'text' (delimiter E'\t');

4. 装载测试数据

（1）把程序生成的文本文件传到hdfs上，作为测试的基础表数据。

chown hdfs:hdfs /home/hdfs/*
su - hdfs

hdfs dfs -put -f date_dim /hawq_data/test_data/date_dim/
hdfs dfs -chown -R gpadmin:gpadmin /hawq_data/test_data/date_dim/
hdfs dfs -put -f browser_dim /hawq_data/test_data/browser_dim/
hdfs dfs -chown -R gpadmin:gpadmin /hawq_data/test_data/browser_dim/
hdfs dfs -put -f page_dim /hawq_data/test_data/page_dim/
hdfs dfs -chown -R gpadmin:gpadmin /hawq_data/test_data/page_dim/
hdfs dfs -put -f referrer_dim /hawq_data/test_data/referrer_dim/
hdfs dfs -chown -R gpadmin:gpadmin /hawq_data/test_data/referrer_dim/
hdfs dfs -put -f status_code_dim /hawq_data/test_data/status_code_dim/
hdfs dfs -chown -R gpadmin:gpadmin /hawq_data/test_data/status_code_dim/
hdfs dfs -put -f page_click_fact /hawq_data/test_data/page_click_fact/
hdfs dfs -chown -R gpadmin:gpadmin /hawq_data/test_data/page_click_fact/

（2）确认基础表数据

select count(*) from date_dim;
select count(*) from browser_dim;
select count(*) from page_dim;
select count(*) from referrer_dim;
select count(*) from status_code_dim;
select count(*) from page_click_fact;

（3）分析表

analyze date_dim;  
analyze browser_dim;  
analyze page_dim;  
analyze referrer_dim;  
analyze status_code_dim;  
analyze page_click_fact;

5. 测试ORC内部表读写性能

（1）建表

date_dim_orc;
browser_dim_orc;
page_dim_orc;
referrer_dim_orc;
status_code_dim_orc;
page_click_fact_orc;

（2）批量装载数据，记录时间

insert into date_dim_orc select * from date_dim;
insert into browser_dim_orc select * from browser_dim;
insert into page_dim_orc select * from page_dim;
insert into referrer_dim_orc select * from referrer_dim;
insert into status_code_dim_orc select * from status_code_dim;
insert into page_click_fact_orc select * from page_click_fact;

    各表的装载时间如图2所示

图2

    最大的事实表1亿行数据用时176秒。

（3）分析表

analyze date_dim_orc;  
analyze browser_dim_orc;  
analyze page_dim_orc;  
analyze referrer_dim_orc;  
analyze status_code_dim_orc;  
analyze page_click_fact_orc;

（4）执行查询（每种查询执行三次取平均值）

    查询1执行时间（秒）：6.798

    查询2执行时间（秒）：107.117

    查询3执行时间（秒）：26.496

    查询4执行时间（秒）：9.215

6. 测试CSV内部表读写性能

（1）建表

    建立CSV格式的表：

date_dim_csv
browser_dim_csv
page_dim_csv
referrer_dim_csv
status_code_dim_csv
page_click_fact_csv

（2）批量装载数据，记录时间

insert into date_dim_csv select * from date_dim;
insert into browser_dim_csv select * from browser_dim;
insert into page_dim_csv select * from page_dim;
insert into referrer_dim_csv select * from referrer_dim;
insert into status_code_dim_csv select * from status_code_dim;
insert into page_click_fact_csv select * from page_click_fact;

    各表的装载时间如图3所示

图3

    最大的事实表1亿行数据用时160秒。

（3）分析表

analyze date_dim_csv;  
analyze browser_dim_csv;  
analyze page_dim_csv;  
analyze referrer_dim_csv;  
analyze status_code_dim_csv;  
analyze page_click_fact_csv;

（4）执行查询（每种查询执行三次取平均值）

    查询1执行时间（秒）：56.559

    查询2执行时间（秒）：151.704

    查询3执行时间（秒）：85.791

    查询4执行时间（秒）：5.698

7. 测试TEXT内部表读写性能

（1）建表

    建立TEXT格式的表：

date_dim_text
browser_dim_text
page_dim_text
referrer_dim_text
status_code_dim_text
page_click_fact_text

（2）批量装载数据，记录时间

insert into date_dim_text select * from date_dim;
insert into browser_dim_text select * from browser_dim;
insert into page_dim_text select * from page_dim;
insert into referrer_dim_text select * from referrer_dim;
insert into status_code_dim_text select * from status_code_dim;
insert into page_click_fact_text select * from page_click_fact;

    各表的装载时间如图4所示

图4

    最大的事实表1亿行数据用时182秒。

（3）分析表

analyze date_dim_text;  
analyze browser_dim_text;  
analyze page_dim_text;  
analyze referrer_dim_text;  
analyze status_code_dim_text;  
analyze page_click_fact_text;

（4）执行查询（每种查询执行三次取平均值）

    查询1执行时间（秒）：8.633

    查询2执行时间（秒）：100.266

    查询3执行时间（秒）：26.809

    查询4执行时间（秒）：7.366

8. 测试ORC外部表读写性能

（1）建表

    建立ORC格式的外部表：

date_dim_orc_ext
browser_dim_orc_ext
page_dim_orc_ext
referrer_dim_orc_ext
status_code_dim_orc_ext
page_click_fact_orc_ext

（2）批量装载数据，记录时间

insert into date_dim_orc_ext select * from date_dim;
insert into browser_dim_orc_ext select * from browser_dim;
insert into page_dim_orc_ext select * from page_dim;
insert into referrer_dim_orc_ext select * from referrer_dim;
insert into status_code_dim_orc_ext select * from status_code_dim;
insert into page_click_fact_orc_ext select * from page_click_fact;

    各表的装载时间如图5所示

图5

    最大的事实表1亿行数据用时171秒。

（3）分析表

analyze date_dim_orc_ext;  
analyze browser_dim_orc_ext;  
analyze page_dim_orc_ext;  
analyze referrer_dim_orc_ext;  
analyze status_code_dim_orc_ext;  
analyze page_click_fact_orc_ext;

（4）执行查询（每种查询执行三次取平均值）

    查询1执行时间（秒）：6.513

    查询2执行时间（秒）：96.986

    查询3执行时间（秒）：22.373

    查询4执行时间（秒）：7.131

9. 测试CSV外部表读写性能

（1）建表

    建立CSV格式的外部表：

date_dim_csv_ext
browser_dim_csv_ext
page_dim_csv_ext
referrer_dim_csv_ext
status_code_dim_csv_ext
page_click_fact_csv_ext

（2）批量装载数据，记录时间

insert into date_dim_csv_ext select * from date_dim;
insert into browser_dim_csv_ext select * from browser_dim;
insert into page_dim_csv_ext select * from page_dim;
insert into referrer_dim_csv_ext select * from referrer_dim;
insert into status_code_dim_csv_ext select * from status_code_dim;
insert into page_click_fact_csv_ext select * from page_click_fact;

    各表的装载时间如图6所示

图6

    最大的事实表1亿行数据用时167秒。

（3）分析表

analyze date_dim_csv_ext;  
analyze browser_dim_csv_ext;  
analyze page_dim_csv_ext;  
analyze referrer_dim_csv_ext;  
analyze status_code_dim_csv_ext;  
analyze page_click_fact_csv_ext;

（4）执行查询（每种查询执行三次取平均值）

    查询1执行时间（秒）：59.686

    查询2执行时间（秒）：160.208

    查询3执行时间（秒）：78.640

    查询4执行时间（秒）：55.496

10. 测试TEXT外部表读写性能

（1）建表

    建立TEXT格式的外部表：

date_dim_text_ext
browser_dim_text_ext
page_dim_text_ext
referrer_dim_text_ext
status_code_dim_text_ext
page_click_fact_text_ext

（2）批量装载数据，记录时间

insert into date_dim_text_ext select * from date_dim;
insert into browser_dim_text_ext select * from browser_dim;
insert into page_dim_csv_text select * from page_dim;
insert into referrer_dim_text_ext select * from referrer_dim;
insert into status_code_dim_text_ext select * from status_code_dim;
insert into page_click_fact_text_ext select * from page_click_fact;

    各表的装载时间如图7所示

图7

    最大的事实表1亿行数据用时170秒。

（3）分析表

analyze date_dim_text_ext;  
analyze browser_dim_text_ext;  
analyze page_dim_text_ext;  
analyze referrer_dim_text_ext;  
analyze status_code_dim_text_ext;  
analyze page_click_fact_text_ext;

（4）执行查询（每种查询执行三次取平均值）

    查询1执行时间（秒）：54.668

    查询2执行时间（秒）：301.859

    查询3执行时间（秒）：78.356

    查询4执行时间（秒）：57.425

11. 测试HAWQ缺省存储格式表（append only）的读写性能

（1）建表

    建立缺省存储格式的表：

date_dim_default
browser_dim_default
page_dim_default
referrer_dim_default
status_code_dim_default
page_click_fact_default

（2）批量装载数据，记录时间

insert into date_dim_default select * from date_dim;
insert into browser_dim_default select * from browser_dim;
insert into page_dim_default select * from page_dim;
insert into referrer_dim_default select * from referrer_dim;
insert into status_code_dim_default select * from status_code_dim;
insert into page_click_fact_default select * from page_click_fact;

    各表的装载时间如图8所示

图8

    最大的事实表1亿行数据用时166秒。

（3）分析表

analyze date_dim_default;  
analyze browser_dim_default;  
analyze page_dim_default;  
analyze referrer_dim_default;  
analyze status_code_dim_default;  
analyze page_click_fact_default;

（4）执行查询（每种查询执行三次取平均值）

    查询1执行时间（秒）：15.461

    查询2执行时间（秒）：85.345

    查询3执行时间（秒）：22.741

    查询4执行时间（秒）：17.678

四、测试结果

    以上测试了OushuDB ORC、CSV、TEXT内外部表，以及HAWQ缺省存储格式表，共7中格式表的读写性能。批量插入的性能对比如表2所示。查询时间对比如表3所示。

	ORC内表	CSV内表	TEXT内表	ORC外表	CSV外表	TEXT外表	append only
每秒插入行数	568182	625000	549451	584795	598802	588235	602409

表2

查询	ORC内表	CSV内表	TEXT内表	ORC外表	CSV外表	TEXT外表	append only
1	6.798	56.559	8.633	6.513	59.686	54.668	15.461
2	107.117	151.704	100.266	96.986	160.208	301.859	85.345
3	26.496	85.791	26.809	22.373	78.640	78.356	22.741
4	9.215	5.698	7.366	7.131	55.496	57.425	17.678

表3

    从表2可以看到，各种格式表的批量插入性能表现相差不是很大，差不多都在每秒6万条左右。从图9中的对比可以看到：

ORC格式的内外部表查询性能基本相同。
ORC表的查询性能与HAWQ的append only内表基本相当。
TEXT格式的内表比外表快很多。
CSV的内外表查询性能差不多，和TEXT外表相当。
ORC内外表最快，TEXT和HAWQ的append only内表其次，但这三者之间的差别不是很大。

图9

    另外，OushuDB 会比Hive快至少一个数量级，相关测试可参考：[HAWQ与Hive查询性能对比测试](https://blog.csdn.net/wzy0623/article/details/71479539)。

五、关于新执行器

    从以上实验的4个查询语句的执行时间看，OushuDB 3.1.1并没有比Apache HAWQ 2.1.1有明显提高，原因是这些查询使用的还是老的执行器。目前新执行器还不支持多表join查询。关于新执行器的性能与限制，参考[http://www.oushu.io/docs/ch/release-notes.html#oushu-database-3-0-2017920](http://www.oushu.io/docs/ch/release-notes.html#oushu-database-3-0-2017920)。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2018年04月20日，如有侵权请联系 cloudcommunity@tencent.com 删除

jquery