forked from aliyun/ros-templates
-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathone-stop-htap-service.yml
383 lines (383 loc) · 11.9 KB
/
one-stop-htap-service.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
ROSTemplateFormatVersion: '2015-09-01'
Description:
zh-cn: 构建RDS MySQL+ClickHouse HTAP环境，含VPC、安全组配置，自动化部署与数据同步。
en: Construct an RDS MySQL+ClickHouse Hybrid Transactional and Analytical Processing
(HTAP) environment, encompassing Virtual Private Cloud (VPC) setup, Security Group
configurations, automated deployment processes, and data synchronization mechanisms.
Parameters:
CommonName:
Type: String
Default: rdsmysql2ck
ZoneId:
Type: String
Label:
en: Availability Zone
zh-cn: 选择可用区
AssociationProperty: ALIYUN::VPC::Zone::ZoneId
DBInstanceClass:
Type: String
Label:
en: Database Instance Class
zh-cn: 数据库实例规格
Description:
en: For optimal query performance, it is recommended that the instance size of the ClickHouse instance be larger than that of the RDS instance.
zh-cn: 为保证查询性能，建议ClickHouse实例的实例规格大于RDS实例实例规格。
Default: 8C32G
AllowedValues:
- 8C32G
- 16C64G
- 32C128G
- 64C256G
RDSDBUser:
Type: String
Label:
en: RDS DB Username
zh-cn: RDS数据库账号
Description:
en: Username of RDS DB database.
zh-cn: RDS数据库账号。
ConstraintDescription:
en: Consist of lowercase letters, numbers, underscore (_), start with a letter, end with a letter or number, up to 32 characters.
zh-cn: 由小写字母、数字、下划线（_）组成，以字母开头，以字母或数字结尾，最多32个字符。
Default: rds_user
MinLength: 2
MaxLength: 16
AllowedPattern: ^[a-z][a-z0-9_]{0,30}[a-z0-9]$
DataBasePassword:
Type: String
Label:
en: DataBase Password
zh-cn: 数据库密码
Description:
en: RDS database user and ClickHouse database user password. Length 8-32, must contain three(Capital letters, lowercase letters, numbers, !@#$%^&*()_+-= Special symbol in).
zh-cn: RDS数据库用户和ClickHouse数据库用户密码。长度8-32，必须包含三项（大写字母、小写字母、数字、 !@#$%^&*()_+-= 中的特殊符号）。
ConstraintDescription:
en: Length 8-32, must contain three(Capital letters, lowercase letters, numbers, !@#$%^&*()_+-= Special symbol in)
zh-cn: 长度8-32，必须包含三项（大写字母、小写字母、数字、 !@#$%^&*()_+-= 中的特殊符号）。
MinLength: 8
MaxLength: 32
NoEcho: true
AssociationProperty: ALIYUN::RDS::Instance::AccountPassword
ClickHouseUser:
Type: String
Label:
en: ClickHouse DB Username
zh-cn: ClickHouse数据库账号
Description:
en: Username of ClickHouse DB database.
zh-cn: ClickHouse数据库账号。
ConstraintDescription:
en: Consist of lowercase letters, numbers, underscore (_), start with a letter, end with a letter or number, up to 32 characters.
zh-cn: 由 2 到 16 个小写字母组成，下划线。必须以字母开头，以字母数字字符结尾。
Default: ck_user
MinLength: 2
MaxLength: 16
AllowedPattern: ^[a-z][a-z0-9_]{0,30}[a-z0-9]$
InstanceType:
Type: String
Label:
en: Instance Type
zh-cn: 实例类型
AssociationProperty: ALIYUN::ECS::Instance::InstanceType
AssociationPropertyMetadata:
ZoneId: ${ZoneId}
InstanceChargeType: PostPaid
SystemDiskCategory: cloud_essd
InstancePassword:
Type: String
Label:
en: Instance Password
zh-cn: 实例密码
Description:
en: Server login password, Length 8-30, must contain three(Capital letters, lowercase letters, numbers, ()`~!@#$%^&*_-+=|{}[]:;'<>,.?/ Special symbol in).
zh-cn: 服务器登录密码，长度8-30，必须包含三项（大写字母、小写字母、数字、 ()`~!@#$%^&*_-+=|{}[]:;'<>,.?/ 中的特殊符号）。
ConstraintDescription:
en: Length 8-30, must contain three(Capital letters, lowercase letters, numbers, ()`~!@#$%^&*_-+=|{}[]:;'<>,.?/ Special symbol in).
zh-cn: 长度8-30，必须包含三项（大写字母、小写字母、数字、 ()`~!@#$%^&*_-+=|{}[]:;'<>,.?/ 中的特殊符号）。
AllowedPattern: '[0-9A-Za-z\_\-\&:;''<>,=%`~!@#\(\)\$\^\*\+\|\{\}\[\]\.\?\/]+$'
MinLength: 8
MaxLength: 30
NoEcho: true
AssociationProperty: ALIYUN::ECS::Instance::Password
Metadata:
ALIYUN::ROS::Interface:
ParameterGroups:
- Parameters:
- ZoneId
Label:
default:
en: Available Zone
zh-cn: 可用区
- Parameters:
- DBInstanceClass
- RDSDBUser
- ClickHouseUser
- DataBasePassword
Label:
default:
en: RDS&CK DataBase Configuration
zh-cn: RDS&CK数据库配置
- Parameters:
- InstanceType
- InstancePassword
Label:
en: ECS Configuration
zh-cn: ECS服务器配置
TemplateTags:
- acs:technical-solution:database:RDS+ClickHouse构建一站式HTAP-tech_solu_19
Hidden:
- CommonName
Mappings:
DatabaseClass:
RDS:
8C32G: mysql.x4.xlarge.2c
16C64G: mysql.x4.2xlarge.2c
32C128G: mysql.x4.4xlarge.2c
64C256G: mysql.x4.8xlarge.2c
ClickHouse:
8C32G: S8
16C64G: S16
32C128G: S32
64C256G: S64
Resources:
VPC:
Type: ALIYUN::ECS::VPC
Properties:
VpcName:
Fn::Sub: vpc_${CommonName}
CidrBlock: 192.168.0.0/16
VSwitch:
Type: ALIYUN::ECS::VSwitch
Properties:
VpcId:
Ref: VPC
ZoneId:
Ref: ZoneId
CidrBlock: 192.168.0.0/24
VSwitchName:
Fn::Sub: vswitch_${CommonName}
SecurityGroup:
Type: ALIYUN::ECS::SecurityGroup
Properties:
VpcId:
Ref: VPC
SecurityGroupIngress_80:
Type: ALIYUN::ECS::SecurityGroupIngress
Properties:
SecurityGroupId:
Ref: SecurityGroup
SourceCidrIp: 0.0.0.0/0
IpProtocol: tcp
NicType: intranet
PortRange: 80/80
SecurityGroupIngress_3306:
Type: ALIYUN::ECS::SecurityGroupIngress
Properties:
SecurityGroupId:
Ref: SecurityGroup
SourceCidrIp: 0.0.0.0/0
IpProtocol: tcp
NicType: intranet
PortRange: 3306/3306
SecurityGroupIngress_443:
Type: ALIYUN::ECS::SecurityGroupIngress
Properties:
SecurityGroupId:
Ref: SecurityGroup
SourceCidrIp: 0.0.0.0/0
IpProtocol: tcp
NicType: intranet
PortRange: 443/443
RdsInstance:
Type: ALIYUN::RDS::DBInstance
Properties:
DBInstanceDescription:
Fn::Sub: rdsmysql_${CommonName}
Engine: MySQL
DBInstanceStorage: 100
EngineVersion: '8.0'
Category: HighAvailability
DBInstanceStorageType: cloud_essd
DBInstanceClass:
Fn::FindInMap:
- DatabaseClass
- RDS
- Ref: DBInstanceClass
SecurityIPList: 0.0.0.0/0
VpcId:
Ref: VPC
ZoneId:
Ref: ZoneId
VSwitchId:
Ref: VSwitch
PayType: Postpaid
MasterUserType: Super
MasterUsername:
Ref: RDSDBUser
MasterUserPassword:
Ref: DataBasePassword
Database:
Type: ALIYUN::RDS::Database
Properties:
CharacterSetName: utf8mb4
DBInstanceId:
Ref: RdsInstance
DBDescription:
Fn::Sub: tpcc_${CommonName}
DBName: tpcc
ClickHouse:
Type: ALIYUN::ClickHouse::DBCluster
Properties:
DbNodeStorageType: cloud_essd
DBNodeStorage: 100
Category: Basic
ZoneId:
Ref: ZoneId
VSwitchId:
Ref: VSwitch
DBClusterDescription:
Fn::Sub: ck_${CommonName}
DBClusterType:
Fn::FindInMap:
- DatabaseClass
- ClickHouse
- Ref: DBInstanceClass
VpcId:
Ref: VPC
DBClusterVersion: 22.8.5.29
DBNodeCount: 1
PaymentType: Postpaid
DBClusterNetworkType: VPC
EcsInstance:
Type: ALIYUN::ECS::Instance
Properties:
VpcId:
Ref: VPC
VSwitchId:
Ref: VSwitch
SecurityGroupId:
Ref: SecurityGroup
ImageId: centos_7_8
InstanceType:
Ref: InstanceType
SystemDiskCategory: cloud_essd
Password:
Ref: InstancePassword
IoOptimized: optimized
AllocatePublicIP: true
ZoneId:
Ref: ZoneId
InstallEnv:
Type: ALIYUN::ECS::RunCommand
Properties:
InstanceIds:
- Fn::GetAtt:
- EcsInstance
- InstanceId
Type: RunShellScript
Sync: true
Timeout: 3600
CommandContent:
Fn::Sub: |
#!/bin/bash
wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm
yum -y install * --nogpgcheck mysql-community-server
yum -y install gcc gcc-c++ autoconf automake make libtool bzr mysql-devel git mysql unzip --nogpgcheck
bzr branch lp:~percona-dev/perconatools/tpcc-mysql
cd tpcc-mysql/src
make
cd ..
mysql -h${RdsInstance.InnerConnectionString} -u${RDSDBUser} -p${DataBasePassword} -D tpcc < create_table.sql
mysql -h${RdsInstance.InnerConnectionString} -u${RDSDBUser} -p${DataBasePassword} -D tpcc < add_fkey_idx.sql
IpAddr=`ping ${RdsInstance.InnerConnectionString} -c 1 | sed '1{s/[^(]*(//;s/).*//;q}'`
echo $IpAddr
./tpcc_load $IpAddr tpcc ${RDSDBUser} ${DataBasePassword} 10
RunTpcc:
Type: ALIYUN::ECS::RunCommand
DependsOn:
- InstallEnv
Properties:
InstanceIds:
- Fn::GetAtt:
- EcsInstance
- InstanceId
Type: RunShellScript
Timeout: 3600
CommandContent:
Fn::Sub: |
#!/bin/bash
tpcc-mysql/tpcc_start -h ${RdsInstance.InnerConnectionString} -d tpcc -u ${RDSDBUser} -p ${DataBasePassword} -w 10 -c 100 -r 180 -l 600 -i 10 > tpcc.log 2>&1 &
ModuleAcsOosExecuteTemplate:
Version: default
Type: MODULE::ACS::OOS::ExecuteTemplate
Properties:
CleanUp: true
TemplateContent:
Fn::Sub: |
FormatVersion: OOS-2019-06-01
Tasks:
- Name: DescribeAccounts
Action: ACS::ExecuteAPI
Properties:
Service: ClickHouse
API: DescribeAccounts
Parameters:
RegionId: '{{ ACS::RegionId }}'
DBClusterId: ${ClickHouse.DBClusterId}
Outputs:
RequestId:
ValueSelector: .RequestId
Type: String
- Name: sleepTask1
Action: ACS::Sleep
Properties:
Duration: 5s
- Name: CreateSQLAccount
Action: ACS::ExecuteAPI
Properties:
Service: ClickHouse
API: CreateSQLAccount
Parameters:
RegionId: '{{ ACS::RegionId }}'
DBClusterId: ${ClickHouse.DBClusterId}
AccountName: ${ClickHouseUser}
AccountPassword: ${DataBasePassword}
AccountType: Super
- Name: sleepTask2
Action: ACS::Sleep
Properties:
Duration: 20s
SynDb:
Type: ALIYUN::ClickHouse::SynDb
DependsOn:
- ModuleAcsOosExecuteTemplate
- RdsInstance
- Database
- InstallEnv
Properties:
DbClusterId:
Ref: ClickHouse
CkUserName:
Ref: ClickHouseUser
CkPassword:
Ref: DataBasePassword
RdsId:
Ref: RdsInstance
RdsUserName:
Ref: RDSDBUser
RdsPassword:
Ref: DataBasePassword
SkipUnsupported: true
SynDbTables:
- Schema: tpcc
Tables:
- customer
- district
- history
- item
- new_orders
- order_line
- orders
- stock
- warehouse