OushuDB入门（二）——性能篇

本文章向大家介绍OushuDB入门（二）——性能篇，主要内容包括其使用实例、应用技巧、基本知识点总结和需要注意事项，具有一定的参考价值，需要的朋友可以参考一下。

版权声明：本文为博主原创文章，未经博主允许不得转载。

一、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​​。

二、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所示。

表1

（3）典型查询

1. 查询给定周中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;
2. 查询各月从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;
3. 给定年份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;
4. 查询给定周中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的资源管理，参考​​HAWQ技术解析（十三） —— 资源管理_wzy0623的博客-CSDN博客​​。

（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的查询优化，参考​​HAWQ技术解析（十二） —— 查询优化_wzy0623的博客-CSDN博客_hawq 调优​​。

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所示。

表2

表3

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

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

图9

另外，OushuDB 会比Hive快至少一个数量级，相关测试可参考：​​HAWQ与Hive查询性能对比测试​​。