基于个性化查询场景的 Amazon Redshift 压力测试方案

一、前言

Amazon Redshift 是 AWS 提供的一种完全由云端托管的快速、简单、经济高效的数据仓库服务，您可以基于 Redshift ，结合使用标准的 SQL 和商业智能（BI），来实现大规模并行的PB级数据查询。您只需要根据业务需要在几分钟内建立几个到几十个节点的数据仓库集群，执行数据分析和查询任务，并根据需求变化调整集群规模。由于 Redshift 是完全托管的平台，您只需要关注业务需求和数据，其他底层集群管理、维护、监控、备份、升级、安全、备份等运维工作全部交由平台承担。

在将大规模数据迁移到 Redshift 之前，很多客户也想结合自身的数据特点和个性化的查询场景，尤其是基于数仓对外提供数据分析的业务，有验证数仓并发性能的需求，需要对 Redshift 进行针对性的压力测试。针对此类场景，本文重点介绍大规模测试数据（fake data）的生成方法和基于 sysbench 工具对 Redshift 进行压力测试等两个技术方案，希望对各位在数仓上云前的可行性和性能评估有所帮助。

二、环境准备

本文所介绍的方案涉及如下3个测试环境：

数据生成客户端：

机型	vCPU	内存(GiB)	磁盘	网络	数量
c5.18xlarge	72	144	通用 SSD 200G	25Gbps	3

压测客户端：

机型	vCPU	内存 (GiB)	磁盘	网络	数量
r5.24xlarge	96	768	通用 SSD 20G	高	1

Redshift集群：

节点类型	vCPU /节点	内存 (GiB) /节点	存储/节点	节点数量	工作负载管理（WLM）	网络
dc2.8xlarge	32	244	2.56TB	4	自动	高

注：以上机型和节点类型为本次实验选取，客户需根据自身实际数据规模和压测并发场景选择合适类型。

三、数仓测试数据准备

在进行 Redshift 压力评估之前，我们需要针对实际生产环境中的表结构和数据规模准备测试数据，在本次测试环境中，我们使用 Python3 来生成与生产环境中相当的模拟数据，共计2亿行数据，数据字段类型如下表。

	primary_id	user_id	hour	domain_hash	platform	country	province	city	domain	uri_path	cloumn_1-10
范围	1-200000000	?=5000的随机泊松分布数	2018-01-01 00:00:00至2019-01-01 00:00:00中随机整小时数	domain的md5哈希值	{“pc”, “mobile”, “sdk”, “weixin”, “weibo”, “toutiao”}中随机选取一个	中国	34个省份中随机选取一个	对应省份中的城市随机选取一个	一个user有20个domain，随机选取一个	随机生成	1-1000000中的随机整数

由于2亿行数据量较大，为了加快数据的生成，我们将2亿行数据拆分成200个文件，每个文件100万行数据，每个文件由一个进程去写入。因此我们启用3台 c5.18xlarge ，分别用70、70、60个进程去生成模拟数据文件。

1. 脚本介绍

本脚本使用 python3.7.3 ，完整脚本见https://github.com/lab798/fakedata-generator

genFakeData.py为核心代码脚本，用于生成模拟数据文件。

province_city.py保存了中国34个省份和对应城市的数据。

genFakeData.py脚本中使用了以下 Python 包：

from faker import Faker
import numpy as np
from province_city import province_city
import random
import datetime
import hashlib
from multiprocessing import Process

其中 faker 和 numpy 需要额外安装。

Faker 是一个可以让你生成模拟数据的 Python 包。使用方法可以参见 Faker 的文档，https://faker.readthedocs.io，我们使用 Faker 来生成随机时间和 uri_path。

f = Faker('zh_CN')
uri_path = f.uri_path(deep=None)
date = f.date_time_between(start_date=datetime.datetime(2018, 1, 1, 0, 0, 0),
end_date=datetime.datetime(2019, 1, 1, 0, 0, 0), tzinfo=None)

numpy 支持大量的维度数组与矩阵运算，此外也针对数组运算提供大量的数学函数库。我们使用numpy来生成柏松分布随机数。

user_id = str(np.random.poisson(lam=user_number // 2))

另外我们使用 random 包来生成随机数和从列表中进行随机选择。

city = random.choice(city_list)
cloumn_1 = random.randint(1, 1000)

脚本核心函数 generate_fakedata_file ，生成一个 csv 文件，文件中的每一行是以 | 作为分隔符的数据，如下图中的两行数据。

1|5047|2018-10-11 10:00:00|831434149d5331728f375128f72037f3|mobile|中国|新疆维吾尔自治区|乌鲁木齐市|www.domain504710.com|blog|935|7978|2850|42|69006|379|78194|151075|93950|729|2018-02-10 14:55:32|2018-01-13 05:28:28

2|5022|2018-05-08 17:00:00|f139787cc74a93b6b415e7df4caabe93|sdk|中国|上海市|上海市|www.domain50224.com|list|437|7386|3512|24|48836|980|98419|933130|13144|237|2018-06-23 18:49:27|2018-11-28 06:16:49

__main__ 函数使用 multiprocessing，启动 N 个 Process 进程运行 generate_fakedata_file 函数，同时生成 N 个文件。

p = Process(target=generate_fakedata_file,
args=(start_number_per_file, end_number_per_file,
file_path, user_num))
p.start()

2.使用脚本快速生成20 billion条记录的数据

a. 准备工作:

启动3台c5.18xlarge 的 ec2 实例，将脚本拷贝到目录～/

安装 python3，和两个包 Faker numpy ，并创建文件夹 fakeData

sudo yum install python3
pip3 install numpy Faker
mkdir fakeData

b. 修改每台实例上脚本中的参数（可根据自身需求修改参数）

第一台实例：

生成的文件： fakedata1.csv – fakedata70.csv

数据行数：1 – 70000000

if __name__ == "__main__":

process_num = 70 # 进程数
rows_per_file = 1000000 # 每个文件有多少行数据
row_start_id = 1 # 这个实例上的数据的起始行数
file_seq_start = 1 # 生成的文件名字的起始序列

第二台实例：

生成的文件：fakedata71.csv – fakedata140.csv

数据行数： 70000000 – 140000000

if __name__ == "__main__":

process_num = 70
rows_per_file = 1000000
row_start_id =  70000000
file_seq_start = 71

第三台实例：

生成的文件：fakedata141.csv – fakedata200.csv

数据行数： 140000000 – 200000000

if __name__ == "__main__":

process_num = 60
rows_per_file = 1000000
row_start_id = 140000000
file_seq_start = 141

c. 运行脚本

nohup python3 genFakeData.py

当 nohup.out 中出现 N(N=进程数)行 Generation finished ，说明数据生成完毕。

也可以用 tail -1 fakedataX.csv 命令查看进度。

本实验中，用3台 c5.18xlarge 生成2亿数据大概耗时6/7小时左右。

四、测试数据导入

在数据生成客户端上通过运行 python 脚本生成需求规模的测试数据后，便可将数据导入待测试 Redshift 集群中，分为两步：

1.将生成数据同步至 s3 存储桶中：

aws s3 sync localdir s3://mybucket/dir

其中， localdir 为本地 python 脚本生成数据的存放路径， s3://mybucket/dir 为目标 s3 存储桶中数据存放路径。

2. 创建表结构：

选择常用的 JDBC 客户端，通过配置 JDBC URL、端口（默认5439）、数据库名称、用户名、密码连接到待测 Redshift 数据仓库，连接成功后，创建测试数据对应的表结构，如：

CREATE TABLE test_data_table
(
    id                 BIGINT       NOT NULL,
    user_id            BIGINT       NOT NULL,
    hour               TIMESTAMP    NOT NULL,
    domain_hash        VARCHAR(128) NOT NULL,
    platform           varchar(32),
    country            varchar(64),
    province           varchar(64),
    city               varchar(64),
    domain             varchar(128),
    uri_path           varchar(255),
    cloumn_1           BIGINT,
    cloumn_2           BIGINT,
    cloumn_3           BIGINT,
    cloumn_4           BIGINT,
    cloumn_5           BIGINT,
    cloumn_6           BIGINT,
    cloumn_7           BIGINT,
    cloumn_8           BIGINT,
    cloumn_9           BIGINT,
    cloumn_10          BIGINT,
    created_at         TIMESTAMP,
    updated_at         TIMESTAMP,
    PRIMARY KEY (id)
)
    DISTSTYLE KEY
    DISTKEY (user_id)
    COMPOUND SORTKEY (user_id, hour);

关于数据的分配方式，需要依据客户常用查询语句来选定，本文选用的 KEY 分配方式，根据 user_id 、 hour 列中的值分配行,主节点会将相同user_id的数据分配到同一个节点切片上。分配方式的选择具体请见官方文档：https://docs.thinkwithwp.com/zh_cn/redshift/latest/dg/t_Distributing_data.html

表创建成功后，在客户端运行 copy 命令将 s3 数据导入至集群中（同时指定使用的IAM角色）：

copy test_data_table
from 's3://mybucket/dir'
iam_role 'arn:aws:iam::907488872981:role/myRedShiftRole';

此外，为测试真实的查询性能，需要禁用“查询结果缓存”：

set enable_result_cache_for_session to off;

至此，生成的测试数据已导入 Redshift 集群中，下面我们来进行压测客户端的环境配置。

五、压测客户端配置：

由于 Redshift 底层基于 PostgreSQL 实现， sysbench 连接 Redshift 基于 PostgreSQL driver，因此配置压测客户端环境，需在启动的 EC2 中分别安装 sysbench 和 PostgreSQL ：

1.以 root 身份安装依赖：

yum -y install bzr
yum -y install automake
yum -y install libtool

2.安装 PostgreSQL：

sudo yum install postgresql postgresql-server postgresql-devel postgresql-contrib postgresql-docs
sudo service postgresql initdb

3.下载解压 sysbench：

wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"
unzip sysbench-1.0.zip
cd sysbench-1.0

4.将 configure 指向安装的 pgSQL lib：

./configure --prefix=/usr/sysbench-1.0/sysbench --with-pgsql --without-mysql --with-pgsql-includes=/usr/include/pgsql92 --with-pgsql-libs=/usr/lib64/pgsql92

5.make & make install

6.添加环境变量：

export PATH=$PATH:/home/ec2-user/sysbench-1.0/sysbench/bin

六、Redshift 压力测试：

在个性化并发查询场景下，使用 sysbench 对 Redshift 压力测试，需根据测试数据及业务需要构建自定义查询请求，可通过编写自定义 lua 脚本来实现个性化并发查询：

编写 test.lua 文件，放入 ../sysbench-1.0/tests/include/oltp_legacy/ 路径下，本文使用 test.lua 示例如下：

--test.lua
pathtest = string.match(test, "(.*/)") or ""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
   set_vars()
   if (db_driver == "mysql" and mysql_table_engine == "myisam") then
      begin_query = "LOCK TABLES sbtest WRITE"
      commit_query = "UNLOCK TABLES"
   else
      begin_query = "BEGIN"
      commit_query = "COMMIT"
   end
end
--具体查询请求实现
function event(thread_id)
   local rs
   user_id = math.random(1,200)
   math.randomseed(tostring(os.time()):reverse():sub(1, 6) + thread_id)
   limit = 100
   month = math.random(1,9)
   day = math.random(10,20)
   day2 = day + 7
   final_sql = "select hour，domain_hash, sum(cloumn_1) as cloumn_1, sum(cloumn_2) as cloumn_2, sum(cloumn_3) as cloumn_3, sum(cloumn_4) as cloumn_4, sum(cloumn_5) as cloumn_5, sum(cloumn_6) as cloumn_6, sum(cloumn_7) as cloumn_7, sum(cloumn_8) as cloumn_8, sum(cloumn_9) as cloumn_9, sum(cloumn_10) as cloumn_10"
   table_name = " from test_data_table "
   final_sql = final_sql .. table_name
   final_sql = final_sql .. " where hour>=TO_TIMESTAMP('2018-0" .. month .. "-" .. day .. "','YYYY-MM-DD') and hour<=TO_TIMESTAMP('2018-0" .. month .. "-" .. day2 .. "', 'YYYY-MM-DD') and "
--设置时间种子
   math.randomseed(tostring(os.time()):reverse():sub(1, 7) + thread_id) 
   final_sql = final_sql .. "user_id = " .. user_id
   final_sql = final_sql .. " group by hour, domain_hash"
   final_sql = final_sql .. "offset "
   final_sql = final_sql .. tostring(math.random(1,100))
   final_sql = final_sql .. " limit "
   final_sql = final_sql .. tostring(limit)
   final_sql = final_sql .. ";"
   if(thread_id == 1)then
      print (final_sql)
   end
----
   if not oltp_skip_trx then
      db_query(begin_query)
   end
   rs = db_query(final_sql_before)
   rs = db_query(final_sql)
   rs = db_query(final_sql_after)
   if not oltp_skip_trx then
      db_query(commit_query)
   end
end

如脚本所示，在每个请求中，自定义构造了某一 user_id 在随机时间区间内7天的聚合查询，您可以根据实际业务场景需要，构建定制化的压测查询请求。

执行压测命令及参数配置如下：

sysbench --test=/usr/sysbench-1.0/tests/include/oltp_legacy/test.lua
--pgsql_variant="redshift" --pgsql-host=<your redshift cluster url>
--pgsql-port=5439 --pgsql-user=<db user name> --pgsql-password=<user password>
--pgsql-db=<db name> --db-driver=pgsql  --oltp-test-mode=complex
--oltp-reconnect-mode=session --percentile=90 --oltp-table-size=1000000000
--report-interval=10 --max-time=60 —num-threads=100 run

参数解释如下：
• –test=/usr/sysbench-1.0/tests/include/oltp_legacy/test.lua
o 自定义 Lua 脚本，建议放在和 common 同目录
• –pgsql_variant=“redshift”
o 针对 Redshift 的测试配置
• –pgsql-host=<your redshift cluster url>
o Redshift 集群 URL
• –pgsql-port=5439
o 端口
• –pgsql-user=<db user name> –pgsql-password=<user password> –pgsql-db=<db name>
o 用户名密码数据库
• –db-driver=pgsql
o pgsql driver
• –oltp-test-mode=complex
o 测试模式（simple：单个select模式；complex：事务模式;）
• –oltp-reconnect-mode=session
o 重新连接模式 session 表示不使用重新连接。每个线程断开只在测试结束
• –percentile=90
o 统计90%的请求最大延迟
• –oltp-table-size=1000000000
o 测试表的大小
• –report-interval=10
o 每隔10秒输出一次压测结果
• –max-time=60
o 测试时长，1分钟
• –num-threads=100
o 并发数量100

七、测试结果示例：

SQL statistics:
    queries performed:
        read:                            4965       读请求数量
        write:                           0
        other:                           9930       其他操作（如 set query  和 reset 等）
        total:                           14895
    transactions:                        4965   (80.88 per sec.)     事务 qps
    queries:                             14895  (242.63 per sec.)     请求 qps
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          61.3888s
    total number of events:              4965

Latency (ms):
         min:                                  208.23     单次操作最小延迟
         avg:                                 1219.83    单次操作平均延迟
         max:                                 7755.21   单次操作最大延迟
         90th percentile:               1352.03   90%的操作最大延迟
         sum:                              6056447.26

八、测试结果分析

本文在针对10亿条测试数据规模的 Redshift 集群进行了压力测试，结果如下所示：

1.时延： 100并发平均延迟1.5秒/查询事务，90%请求最高延迟1.8秒/查询事务；

2. TPS（transactions per second），在不同并发条件下：

	A	B	C	D	E	F	G	H	I	J
1	并发量	1	10	20	50	80	100	200	500	1000
2	TPS	3.58	30.19	45.25	46.29	49.82	50.9	50.65	50.39	50.03

由此可见，随着并发量增大，TPS 达到 Redshift 支持的最高并发数50。

目前，AWS Redshift 已经在部分区域推出了并发扩展（Concurrency Scaling）功能，您可以通过设置工作负载管理（Workload Management）中的队列属性 “Concurrency Scaling” 为 Auto，来扩展队列将查询路由到并发扩展集群。详情请参见：https://docs.thinkwithwp.com/zh_cn/redshift/latest/dg/concurrency-scaling.html

注：本文选取的是单一表查询的场景，目的是为读者提供数据构造及测试流程方法，如果客户实际场景中涉及多个表的并行查询，测试方法与之类似。

亚马逊AWS官方博客