PolarDB-X「DRDS」全局二級索引 (Global Secondary Index, GSI) 結合業務壓測記錄「服務端PHP」

執行環境

CPU：2.6 GHz 六核Intel Core i7
記憶體：16G
作業系統：macOS Catalina(10.15.2)
同一個docker容器中

壓測工具

 ab -c 100 -n 10000 -k url

執行流程

api網關->userRpc使用者服務->db資料庫

測試流程

SELECT COUNT() FROM users; !!#ff0000 COUNT()：1000w+!!

1、讀取一行資料（select *）

1）drds(5.6.29-TDDL-5.3.7-15460044)主表執行

執行的sql:

!!#0000ff SELECT * FROM users WHERE mobile=';!!

joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests


Server Software:        swoole-http-server
Server Hostname:        127.0.0.1
Server Port:            80

Document Path:          /
Document Length:        353 bytes

Concurrency Level:      100
Time taken for tests:   12.148 seconds
Complete requests:      10000
Failed requests:        0
Total transferred:      7130000 bytes
HTML transferred:       3530000 bytes
Requests per second:    823.16 [#/sec] (mean)
Time per request:       121.483 [ms] (mean)
Time per request:       1.215 [ms] (mean, across all concurrent requests)
Transfer rate:          573.16 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.3      0       5
Processing:    42  119  42.8    111     766
Waiting:       41  119  42.8    111     766
Total:         42  120  42.8    112     766

Percentage of the requests served within a certain time (ms)
  50%    112
  66%    123
  75%    133
  80%    139
  90%    160
  95%    184
  98%    242
  99%    321
 100%    766 (longest request)

2）drds(5.6.29-TDDL-5.3.7-15460044)備援表索引+主表缺失列回查執行

執行的sql:

!!#0000ff SELECT * FROM redundance_mobile WHERE mobile='';!!

!!#0000ff SELECT * FROM users WHERE uid=;!!

joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests


Server Software:        swoole-http-server
Server Hostname:        127.0.0.1
Server Port:            80

Document Path:          /
Document Length:        353 bytes

Concurrency Level:      100
Time taken for tests:   19.521 seconds
Complete requests:      10000
Failed requests:        0
Total transferred:      7130000 bytes
HTML transferred:       3530000 bytes
Requests per second:    512.28 [#/sec] (mean)
Time per request:       195.207 [ms] (mean)
Time per request:       1.952 [ms] (mean, across all concurrent requests)
Transfer rate:          356.69 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.3      0       7
Processing:    57  192 101.8    155     824
Waiting:       56  192 101.7    155     824
Total:         60  192 101.8    155     824

Percentage of the requests served within a certain time (ms)
  50%    155
  66%    171
  75%    185
  80%    199
  90%    391
  95%    440
  98%    478
  99%    514
 100%    824 (longest request)

3）drds(5.6.29-TDDL-5.4.4-15864860)全局二級索引執行

!!#0000ff SELECT * FROM users WHERE mobile='';!!

joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests


Server Software:        swoole-http-server
Server Hostname:        127.0.0.1
Server Port:            80

Document Path:          /
Document Length:        353 bytes

Concurrency Level:      100
Time taken for tests:   14.018 seconds
Complete requests:      10000
Failed requests:        0
Total transferred:      7130000 bytes
HTML transferred:       3530000 bytes
Requests per second:    713.37 [#/sec] (mean)
Time per request:       140.181 [ms] (mean)
Time per request:       1.402 [ms] (mean, across all concurrent requests)
Transfer rate:          496.71 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   1.8      0     181
Processing:    27  139  49.4    127     451
Waiting:       22  139  49.3    127     451
Total:         28  139  49.4    127     451

Percentage of the requests served within a certain time (ms)
  50%    127
  66%    141
  75%    152
  80%    161
  90%    210
  95%    257
  98%    287
  99%    302
 100%    451 (longest request)

||100并發，10000請求||drds(5.6.29-TDDL-5.3.7-15460044)備援表索引+主表缺失列回查執行|| 19.77 seconds||514||

||100并發，10000請求||drds(5.6.29-TDDL-5.4.4-15864860)全局二級索引|| 17.18 seconds||592||

PolarDB-X「DRDS」全局二級索引 (Global Secondary Index, GSI) 結合業務壓測記錄「服務端PHP」

2、讀取一行資料（select uid mobile）查詢二級索引/索引表(不回表)

1）drds(5.6.29-TDDL-5.3.7-15460044)備援表

!!#0000ff SELECT uid,mobile FROM redundance_mobile WHERE mobile='';!!

joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests


Server Software:        swoole-http-server
Server Hostname:        127.0.0.1
Server Port:            80

Document Path:          /
Document Length:        353 bytes

Concurrency Level:      100
Time taken for tests:   15.822 seconds
Complete requests:      10000
Failed requests:        0
Total transferred:      7130000 bytes
HTML transferred:       3530000 bytes
Requests per second:    632.03 [#/sec] (mean)
Time per request:       158.221 [ms] (mean)
Time per request:       1.582 [ms] (mean, across all concurrent requests)
Transfer rate:          440.07 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   1.8      0     178
Processing:    67  155  46.4    147     879
Waiting:       67  155  46.4    147     879
Total:         67  156  46.5    147     880

Percentage of the requests served within a certain time (ms)
  50%    147
  66%    160
  75%    168
  80%    175
  90%    193
  95%    216
  98%    323
  99%    365
 100%    880 (longest request)

2）drds(5.6.29-TDDL-5.4.4-15864860)全局二級索引

!!#0000ff SELECT uid,mobile FROM users WHERE mobile='';!!

joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests


Server Software:        swoole-http-server
Server Hostname:        127.0.0.1
Server Port:            80

Document Path:          /
Document Length:        90 bytes

Concurrency Level:      100
Time taken for tests:   12.937 seconds
Complete requests:      10000
Failed requests:        0
Total transferred:      4490000 bytes
HTML transferred:       900000 bytes
Requests per second:    773.00 [#/sec] (mean)
Time per request:       129.366 [ms] (mean)
Time per request:       1.294 [ms] (mean, across all concurrent requests)
Transfer rate:          338.94 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0  17.5      0    1753
Processing:    26  128 180.5    100    1989
Waiting:       26  128 180.5     99    1989
Total:         31  129 181.4    100    1989

Percentage of the requests served within a certain time (ms)
  50%    100
  66%    116
  75%    128
  80%    136
  90%    169
  95%    243
  98%    313
  99%   1789
 100%   1989 (longest request)

||~ __ab并發和請求數__||__資料庫環境__||__執行時間(5次平均值)__||__QPS（每秒請求數）(5次平均值)__||

||100并發，10000請求||drds(5.6.29-TDDL-5.3.7-15460044)|| 17.44 seconds||587||

||100并發，10000請求||drds(5.6.29-TDDL-5.4.4-15864860)全局二級索引|| 12.18 seconds||830||

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-WTtqoiEW-1586768686639)(/tfl/captures/2020-04/tapd_38981207_base64_1586765666_50.png)]

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-ntlWYqKS-1586768686639)(/tfl/captures/2020-04/tapd_38981207_base64_1586766337_18.png)]

3、插入資料

1）drds(5.6.29-TDDL-5.3.7-15460044)主表+備援表

joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests


Server Software:        swoole-http-server
Server Hostname:        127.0.0.1
Server Port:            80

Document Path:          /
Document Length:        162 bytes

Concurrency Level:      100
Time taken for tests:   418.928 seconds
Complete requests:      10000
Failed requests:        8842
   (Connect: 0, Receive: 0, Length: 8842, Exceptions: 0)
Total transferred:      5230254 bytes
HTML transferred:       1630254 bytes
Requests per second:    23.87 [#/sec] (mean)
Time per request:       4189.277 [ms] (mean)
Time per request:       41.893 [ms] (mean, across all concurrent requests)
Transfer rate:          12.19 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.3      0       4
Processing:   845 4161 672.7   4231    7236
Waiting:      842 4161 672.7   4231    7236
Total:        846 4162 672.7   4232    7236

Percentage of the requests served within a certain time (ms)
  50%   4232
  66%   4491
  75%   4632
  80%   4725
  90%   4948
  95%   5122
  98%   5375
  99%   5656
 100%   7236 (longest request)

2）drds(5.6.29-TDDL-5.4.4-15864860)全局二級索引主表

joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests


Server Software:        swoole-http-server
Server Hostname:        127.0.0.1
Server Port:            80

Document Path:          /
Document Length:        57 bytes

Concurrency Level:      100
Time taken for tests:   10.948 seconds
Complete requests:      10000
Failed requests:        0
Total transferred:      4160000 bytes
HTML transferred:       570000 bytes
Requests per second:    913.39 [#/sec] (mean)
Time per request:       109.482 [ms] (mean)
Time per request:       1.095 [ms] (mean, across all concurrent requests)
Transfer rate:          371.06 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.4      0      11
Processing:    38  108  43.8     99     605
Waiting:       38  107  43.8     98     600
Total:         38  108  43.8     99     605

Percentage of the requests served within a certain time (ms)
  50%     99
  66%    111
  75%    120
  80%    126
  90%    148
  95%    178
  98%    264
  99%    313
 100%    605 (longest request)

||100并發，10000請求||drds(5.6.29-TDDL-5.3.7-15460044)|| 24 seconds||408.55||

||100并發，10000請求||drds(5.6.29-TDDL-5.4.4-15864860)全局二級索引|| 11.808 seconds||856||

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-AqhVqhsc-1586768686640)(/tfl/captures/2020-04/tapd_38981207_base64_1586765507_87.png)]

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-iQN9GcAt-1586768686640)(/tfl/captures/2020-04/tapd_38981207_base64_1586765286_61.png)]

結論

目前的結論是基于資料量在1000W+

1、在查詢（SELECT * ）查詢所有列時：直接查詢主表-未建全局二級索引的執行時間和QPS是最優的；查詢查詢主表-建立全局二級索引的執行時間和QPS次之，應該是因為查詢了索引表後進行了回表；而自建備援表索引+主表缺失列回查執行和全局二級索引原理類似，執行效率也是差不多。

2、在查詢（select uid mobile）全局二級索引/自寫備援索引表（不回表）時：全局二級索引比自寫備援索引表的優勢明顯。

3、插入資料：主表+寫備援索引表和全局二級索引的對比，全局二級索引優勢更加明顯。

4、GSI綜合比較下來比自寫備援索引表方式效率更優，但考慮目前GSI在DML、DDL下有一定的限制與約定，是以在替換自寫備援索引表的時候得評估下限制與約定是否會造成影響。

PolarDB-X「DRDS」全局二級索引 (Global Secondary Index, GSI) 結合業務壓測記錄「服務端PHP」

執行環境

壓測工具

執行流程

測試流程

結論

繼續閱讀

Apache2.4.x 配置檔案詳解Apache配置需要了解如下：開始講解：

配置apache支援PHP（win7）

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Cloud Studio初體驗

CentOS 7,docker安裝

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

php 去掉字元串的最後一個字元及截取原字元串1,2,3,4,5,6,

【Docker】端口映射問題操作步驟

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

php——水印

SequoiaDB巨杉資料庫C++驅動概述

PolarDB-X「DRDS」 全局二級索引 (Global Secondary Index, GSI) 結合業務壓測記錄「服務端PHP」

執行環境

壓測工具

執行流程

測試流程

結論

繼續閱讀

PolarDB-X「DRDS」全局二級索引 (Global Secondary Index, GSI) 結合業務壓測記錄「服務端PHP」