# 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)||