---
# System prepended metadata

title: AWS Aurora & RDS 測試
tags: [AWS, DB]

---

# AWS Aurora & RDS 測試

###### tags: `DB`,`AWS`

- JMeter
- Sysbench
- Isolation Level

## JMeter 測試

- JMeter 是一套Java-based、開源的測試工具

![](https://i.imgur.com/ss0qWrz.png)

![](https://i.imgur.com/rVtw2bh.png)



### Aurora MySQL

AWS Aurora 支援 MYSQL 5.6 及 5.7，DB instance class 又可分成 Memory Optimized 及 Burstable，MYSQL 5.6 又有 Parallel query 優化版本。資料庫中建有兩筆簡單的 block 資料，測試 SQL statement 為 `SELECT * FROM block`


將對五種 DB 進行測試，以下皆設定一秒內所有 threads 發出一次請求

* Aurora MySQL **5.7.12**, Memory Optimized class 的 **db-r5-large**(2vCPUs , 16GiB RAM, EBS: 3500 Mbps)
* Aurora MySQL **5.7.12**, Burstable class 的 **db-t3-small**(2vCPUs , 2GiB RAM, EBS: 1500 Mbps)
* Aurora MySQL **5.6.10a**, Memory Optimized class 的 **db-r5-large**(2vCPUs , 16GiB RAM, EBS: 3500 Mbps)
* Aurora MySQL **5.6.10a**, **Parallel query**, Memory Optimized class 的 **db-r5-large**(2vCPUs , 16GiB RAM, EBS: 3500 Mbps)
* Aurora MySQL **5.6.10a**, Burstable class 的 **db-t3-small**(2vCPUs , 2GiB RAM, EBS: 1500 Mbps)


#### 30 threads

![](https://i.imgur.com/6ge3TZ7.png)

#### 50 threads

![](https://i.imgur.com/JoYd9H3.png)


當 50 threads 併發時，db-t3-small 開始 reject connection

![](https://i.imgur.com/AjeM6fh.png)


#### 100 threads
![](https://i.imgur.com/16IOPCH.png)


#### 小結

- threads 越多會延長回應時間
- Aurora MySQL 5.6 回應時間及 CPU 使用率皆優於 Aurora MySQL 5.7 版本
- Aurora MySQL 5.6 parallel query 與 Aurora MySQL 5.6 差異不大


### Aurora MySQL VS Amazon RDS for MySQL

對六種 DB 進行測試，以下皆設定一秒內所有 threads 發出一次請求

* Aurora MySQL **5.6.10a**, **Parallel query**, Memory Optimized class 的 **db-r5-large**(2vCPUs , 16GiB RAM, EBS: 3500 Mbps)
* Aurora MySQL **5.6.10a**, Memory Optimized class 的 **db-r5-large**(2vCPUs , 16GiB RAM, EBS: 3500 Mbps)
* AWS RDS for MySQL **5.6.44**, Memory Optimized class 的 **db-r5-large**(2vCPUs , 16GiB RAM, EBS: 3500 Mbps)
* Aurora MySQL **5.7.12**, Memory Optimized class 的 **db-r5-large**(2vCPUs , 16GiB RAM, EBS: 3500 Mbps)
* AWS RDS for MySQL **5.7.22**, Memory Optimized class 的 **db-r5-large**(2vCPUs , 16GiB RAM, EBS: 3500 Mbps)
* AWS RDS for MySQL **8.0.16**, Memory Optimized class 的 **db-r5-large**(2vCPUs , 16GiB RAM, EBS: 3500 Mbps)

#### 100 threads

##### local
![](https://i.imgur.com/7KzX4BK.png)


##### EC2
![](https://i.imgur.com/F9ET1PO.png)


#### 300 threads

##### local
![](https://i.imgur.com/ffA6ONb.png)


##### EC2
![](https://i.imgur.com/zgspqZ0.png)


#### 500 threads

##### local
![](https://i.imgur.com/5wuiTmP.png)


##### EC2
![](https://i.imgur.com/JAn1l2y.png)

#### CPU 使用率

![](https://i.imgur.com/oIin1Z0.png)

p.s. 
* burgess-mysql56 : AWD RDS for MySQL 5.6
* burgess-mysql57 : AWD RDS for MySQL 5.7
* burgess-mysql80 : AWD RDS for MySQL 8.0
* burgess-mysql56-parallel-r5-large-instance-1 : AWD Aurora MySQL 5.6, parallel query 優化版本
* burgess-mysql56-r5-large-instance-1 : AWD Aurora MySQL 5.6
* burgess-test-ii : AWD Aurora MySQL 5.7

#### 小結

* 整體而言，AWD RDS 回應時間 MySQL 8.0 > MySQL 5.7 >MySQL 5.6
* 不管是在 local 或 EC2 測試，AWD RDS MySQL 回應時間皆少於 AWD Aurora MySQL
* [說好的五倍效能呢？](https://aws.amazon.com/tw/rds/aurora/mysql-features/)

## Sysbench 測試

- sysbench 提供 File I/O, CPU , Memory , Threads , Mutx , OLTP 的測試

- 安裝及測試方式可參考：[對Aurora進行壓力測試](https://aws.amazon.com/cn/blogs/china/aurora-test/)

- 產生 50 tables，每個 table 有 25000 筆 records，測試時間 60 秒，read only
![](https://i.imgur.com/xjV7ntp.png)

- sysbench 會隨機生成 query or transaction

```
Query BEGIN
Query SELECT c FROM sbtest1 WHERE id=50374
Query SELECT c FROM sbtest1 WHERE id BETWEEN 50199 AND 50199+99
Query SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN 49799 AND 49799+99
Query SELECT c FROM sbtest1 WHERE id BETWEEN 50407 AND 50407+99 ORDER BY c
Query COMMIT
```
- 跟 JMeter 一樣，100, 300, 500 threads 分別對六種 DB 進行測試


### 100 threads

![](https://i.imgur.com/wIGfHCE.png)


| 數據 | aurora-mysql56 | aurora-mysql56-parallel | rds for mysql56 |aurora-mysql57|rds for mysql57|rds for mysql80|
| -------- | -------- | -------- | ------- | ------- | ------- | ------- |
| read     | 1304618  | 1507254  |798238|1551732|942914|897834|
| write    | 0     | 0     |0|0|0|0|
| other    | 186374   | 215322   |114034|221676|134702|128262|
| total    | 1490992  | 1722576  |912272|1773408|1077616|1026096|
| transactions | 93187 | 107661  |57017|110838|110838|64131 |
| response time : min | 44.65ms | 35.31ms |9.34ms|37.92ms|10.02ms|39.24ms|
| response time : avg | 64.03ms | 55.66ms |105.09ms|53.48ms|87.49ms|92.90ms|
| response time : max | 299.60ms | 299.65ms |334.90ms|271.04ms|393.04ms|300.05ms|
| response time : 95% Line|  84.47ms | 89.49ms  |124.47ms|65.77ms| 99.35ms|104.75ms|

### 300 threads

| 數據 | aurora-mysql56 | aurora-mysql56-parallel | rds for mysql56 |aurora-mysql57|rds for mysql57|rds for mysql80|
| -------- | -------- | -------- | ------- | ------- | ------- | ------- |
| read     | 1348620  | 1460536  | 742826|1515500|884912|849590|
| write    | 0     | 0     |0|0|0|0|
| other    | 192660   | 208648   |106118|216500|126416|121370|
| total    | 1541280  | 1669184  | 848944|1732000|1011328|970960|
| transactions | 96330 | 104324  |53059 |108250 |63208|60685 |
| response time : min | 104.35ms | 39.00ms |24.57ms|97.66ms|8.70ms|35.38ms|
| response time : avg | 185.85ms |  171.32ms | 337.54ms| 166.14ms|281.09ms|293.39ms|
| response time : max | 438.23ms | 420.80ms |802.94ms|405.39ms|894.26ms| 795.28ms|
| response time : 95% Line| 224.33ms | 221.40ms |409.57ms|201.90ms| 321.77ms| 337.96ms|

### 500 threads

| 數據 | aurora-mysql56 | aurora-mysql56-parallel | rds for mysql56 |aurora-mysql57|rds for mysql57|rds for mysql80|
| -------- | -------- | -------- | ------- | ------- | ------- | ------- |
| read     | 1220996  | 1386658  |734720|1347696|851900|790132|
| write    | 0     | 0     |0|0|0|0|
| other    |  174428   |  198094   |104960|192528| 121700| 112876|
| total    | 1395424  | 1584752  | 839680|1540224|973600|903008|
| transactions | 87214  | 99047  |52480|96264 |60850|56438  |
| response time : min | 121.03ms | 59.24ms |26.44ms| 199.99ms|5.72ms|46.22ms|
| response time : avg | 338.93ms | 296.24ms | 568.73ms| 311.23ms| 486.84ms| 524.61ms|
| response time : max | 634.64ms | 762.33ms |1371.15ms|586.60ms|1074.76ms| 2370.09ms|
| response time : 95% Line| 406.76ms | 363.02ms  |707.89ms|358.06ms| 551.17ms|612.78ms|


### CPU 使用率
![](https://i.imgur.com/nR1X6BH.png)

### 小結

* AWS Aurora MySQL 比 AWS RDS MySQL 同時間內處理 query 數較多 
* AWS Aurora MySQL 5.6 parallel 處理量及回應時間確實比較優
* AWS RDS MySQL 最短回應時間比 AWS Aurora MySQL 少，也許單純的 query 在 AWS RDS MySQL 處理效能較高？ 

## Isolation Level 測試

Aurora MySQL 5.6

### Read Committed

| Connection 1 | Connection 2 |
| -------- | -------- |
|![](https://i.imgur.com/KqiKj2u.png)||
||![](https://i.imgur.com/hH7krxO.png)|
|![](https://i.imgur.com/UVcyqHK.png)||
||![](https://i.imgur.com/PXIPbso.png)|

### Repeatable Read

#### Update
| Connection 1 | Connection 2 |
| -------- | -------- |
|![](https://i.imgur.com/1cnqgBv.png)||
||![](https://i.imgur.com/yveLNEd.png)|
|![](https://i.imgur.com/fBwKj4r.png)||
||![](https://i.imgur.com/Q1Vu1tp.png)|
|![](https://i.imgur.com/liFPdtt.png)||
|![](https://i.imgur.com/fWpeAjO.png)||

#### Insert
| Connection 1 | Connection 2 |
| -------- | -------- |
|![](https://i.imgur.com/Oqx81ev.png)||
||![](https://i.imgur.com/Db2Wse8.png)|
|![](https://i.imgur.com/ZuqOmcv.png)||
|![](https://i.imgur.com/0itly8Y.png)||
|![](https://i.imgur.com/OXNiIhz.png)||


#### LOCK IN SHARE MODE

| Connection 1 | Connection 2 |
| -------- | -------- |
|![](https://i.imgur.com/UD22Pzh.png)||
||![](https://i.imgur.com/lARjMKY.png)|


| Connection 1 | Connection 2 |
| -------- | -------- |
|![](https://i.imgur.com/yBdVFtZ.png)||
||![](https://i.imgur.com/GXGAwr0.png)|
|![](https://i.imgur.com/6xHAPN3.png)||
||![](https://i.imgur.com/oKniuND.png)|
|![](https://i.imgur.com/6U5l0nO.png)|![](https://i.imgur.com/df5d1UH.png)|

#### FOR UPDATE

| Connection writer 1 | Connection writer 2 | Connection reader |
| -------- | -------- | -------- |
|![](https://i.imgur.com/lSX9wio.png)|![](https://i.imgur.com/6cOCxso.png)|![](https://i.imgur.com/JixkmEL.png)|

##### Phantom reads
一般 `SELECT` 是讀 snapshot，若加入 `SELECT ... LOCK IN SHARE MODE` or `SELECT ... FOR UPDATE` 會出現 Phantom reads
| Connection 1 | Connection 2 |
| -------- | -------- |
|![](https://i.imgur.com/7chom7i.png)||
||![](https://i.imgur.com/GihNJXF.png)|
|![](https://i.imgur.com/m6TvTOu.png)||














