AI 助理
备案控制台登录注册
开发者社区 数据库 文章 正文

PolarDB-X「DRDS」 全局二级索引 (Global Secondary Index, GSI) 结合业务压测记录「服务端PHP」

2021-08-11 543
版权
举报
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB 分布式版，标准版 2核8GB
推荐场景：
使用PolarDB-X与Flink搭建实时数据大屏
简介： PolarDB-X「DRDS」 全局二级索引 (Global Secondary Index, GSI) 压测记录

执行环境

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

        

          
        

        
        

          

          AI 代码解读

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)

        

          
        

        
        

          

          AI 代码解读

3）drds(5.6.29-TDDL-5.4.4-15864860)全局二级索引执行
执行的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:   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)

        

          
        

        
        

          

          AI 代码解读

|__ab并发和请求数__|__数据库环境__|__执行时间(5次平均值)__|__QPS（每秒请求数）(5次平均值)__|
|100并发，10000请求|drds(5.6.29-TDDL-5.3.7-15460044)主表执行|14.62 seconds|713|
||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||

在这里插入图片描述

2、 读取一行数据（select uid mobile）查询二级索引/索引表(不回表)
1）drds(5.6.29-TDDL-5.3.7-15460044)冗余表
执行的sql:
!!#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)

        

          
        

        
        

          

          AI 代码解读

2）drds(5.6.29-TDDL-5.4.4-15864860)全局二级索引
执行的sql:
!!#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)

        

          
        

        
        

          

          AI 代码解读

||~ __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)

        

          
        

        
        

          

          AI 代码解读

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)

        

          
        

        
        

          

          AI 代码解读

||~ __ab并发和请求数__||__数据库环境__||__执行时间(5次平均值)__||__QPS（每秒请求数）(5次平均值)__||
||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
性能测试
PHP
容器
测试技术
SQL
关系型数据库
索引
Docker
Apache
数据库
分布式数据库
关键词：
PHP服务端
云原生数据库 PolarDB索引
服务端PHP
云原生分布式数据库 PolarDB-X polardb-x
云原生分布式数据库 PolarDB-X索引
相关实践学习
快速体验PolarDB开源数据库
本实验环境已内置PostgreSQL数据库以及PolarDB开源数据库：PolarDB PostgreSQL版和PolarDB分布式版，支持一键拉起使用，方便各位开发者学习使用。
1693863475468354
+关注
4文章
目录
打赏
0
0
0
0
1
分享
相关文章
嚯嚯嚯www
|
关系型数据库 测试技术 Serverless
【PolarDB Serverless】资源伸缩&压测 TPC-C 测评
【PolarDB Serverless】资源伸缩&压测 TPC-C 测评
嚯嚯嚯www
156215 31
【PolarDB Serverless】资源伸缩&压测 TPC-C 测评
卓伊凡
|
2月前
|
前端开发 Java 测试技术
PHP的现代复兴：从脚本语言到企业级服务端引擎的演进之路-优雅草卓伊凡
PHP的现代复兴：从脚本语言到企业级服务端引擎的演进之路-优雅草卓伊凡
卓伊凡
60 6
卓伊凡
|
5月前
|
监控 Linux PHP
【02】客户端服务端C语言-go语言-web端PHP语言整合内容发布-优雅草网络设备监控系统-2月12日优雅草简化Centos stream8安装zabbix7教程-本搭建教程非docker搭建教程-优雅草solution
【02】客户端服务端C语言-go语言-web端PHP语言整合内容发布-优雅草网络设备监控系统-2月12日优雅草简化Centos stream8安装zabbix7教程-本搭建教程非docker搭建教程-优雅草solution
卓伊凡
146 20
卓伊凡
|
5月前
|
监控 关系型数据库 MySQL
【01】客户端服务端C语言-go语言-web端PHP语言整合内容发布-优雅草网络设备监控系统-硬件设备实时监控系统运营版发布-本产品基于企业级开源项目Zabbix深度二开-分步骤实现预计10篇合集-自营版
【01】客户端服务端C语言-go语言-web端PHP语言整合内容发布-优雅草网络设备监控系统-硬件设备实时监控系统运营版发布-本产品基于企业级开源项目Zabbix深度二开-分步骤实现预计10篇合集-自营版
卓伊凡
90 0
sunrr
|
9月前
|
SQL 安全 关系型数据库
PHP作为一种流行的服务端脚本语言，在Web开发领域具有显著的优势
【10月更文挑战第11天】PHP作为一种流行的服务端脚本语言，在Web开发领域具有显著的优势
sunrr
114 0
小Lee
|
存储 SQL 关系型数据库
PolarDB-X 与 DRDS 有什么区别？2
PolarDB-X 与 DRDS 有什么区别？2
小Lee
173 1
郑小健
|
JSON 安全 API
实战指南：使用PHP构建高性能API接口服务端
构建RESTful API的简要指南：使用PHP和Laravel，先安装Laravel并配置数据库，接着在`api.php`中定义资源路由，创建`PostController`处理CRUD操作，定义`Post`模型与数据库交互。使用Postman测试API功能，如创建文章。别忘了关注安全性、错误处理和性能优化。
郑小健
316 2
Echo_Wish
|
关系型数据库 Serverless 测试技术
PolarDB的Serverless压测能力测评
PolarDB的Serverless压测能力测评
Echo_Wish
52574 2
魏红斌
|
关系型数据库 MySQL Serverless
PolarDB MySQL 版 Serverless全方位压测实验测评报告
个人测评
魏红斌
929 1
二进制怪兽
|
消息中间件 物联网 Java
[笔记]阿里云物联网之业务服务端（java、php）接入阿里云平台（一）
[笔记]阿里云物联网之业务服务端（java、php）接入阿里云平台
二进制怪兽
462 1

热门文章

最新文章

  • 1
    Super MySQL｜揭秘PolarDB全异步执行架构，高并发场景性能利器
    28
  • 2
    Jmeter工具使用：HTTP接口性能测试实战
    15
  • 3
    PolarDB Supabase最佳实践-Web应用
    109
  • 4
    再获殊荣，阿里云PolarDB数据库蝉联SIGMOD最佳论文奖
    22
  • 5
    性能测试怎么做？方法、流程与核心要点解析
    14
  • 6
    附下载｜SIGMOD最佳论文：CXL赋能PolarDB，打造新一代分布式内存池
    59
  • 7
    阿里云PolarDB数据库蝉联SIGMOD最佳论文奖
    9
  • 8
    突破IO瓶颈：PolarDB分布式并行查询(Parallel Query)深度调优手册
    4
  • 9
    喜报｜阿里云PolarDB数据库（分布式版）荣获国内首台（套）产品奖项
    26
  • 10
    PolarDB Supabase 助力快速构建现代应用
    38
  • 1
    实战派必看！Python性能测试中，JMeter与Locust如何助力性能调优
    253
  • 2
    性能测试不再愁！Python+JMeter+Locust，三步打造高性能应用
    158
  • 3
    Python 性能测试进阶之路：JMeter 与 Locust 的强强联合，解锁性能极限
    202
  • 4
    性能测试新纪元！Python携手JMeter与Locust，开启应用性能优化新篇章
    222
  • 5
    Python性能测试新风尚：JMeter遇上Locust，性能分析不再难🧐
    199
  • 6
    告别卡顿！Python性能测试实战教程，JMeter&Locust带你秒懂性能优化💡
    232
  • 7
    深度挖掘：Python性能测试中JMeter与Locust的隐藏技能🔍
    172
  • 8
    Python性能测试全攻略：JMeter与Locust，双剑合璧斩断性能瓶颈🗡️
    197
  • 9
    从慢如蜗牛到飞一般的感觉！Python性能测试实战，JMeter&Locust助你加速🏃‍♂️
    62
  • 10
    惊呆了！Python性能测试高手都用这些神器：JMeter+Locust，效率翻倍📈
    237

    • 相关课程

    更多
  • PolarDB for PostgreSQL 开源人才初级认证培训课程
  • PolarDB for PostgreSQL 内核解读系列课程
  • 7天突破PolarDB for PostgreSQL 2022版
  • 关系型数据库ACP认证课程
  • 关系型数据库线上预习视频
  • PolarDB for PostgreSQL 入门

    • 相关电子书

    更多
  • PolarDB开发者大会：PolarDB产品演进和未来展望
  • PolarDB+AnalyticDB助力交通物流行业系统升级
  • PolarDB NL2SQL: 帮助您写出准确、优化的SQL

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 使用CloudLens采集PolarDB日志并进行审计分析
  • PolarDB for PostgreSQL 闪回特性体验
  • 快速体验PolarDB开源数据库
  • 体验PolarDB MySQL多主集群
    • 下一篇
    就是要你懂负载均衡--lvs和转发模式
    目录
    AI助理

    你好，我是AI助理

    可以解答问题、推荐解决方案等

    登录插画

    登录以查看您的控制台资源

    管理云资源
    状态一览
    快捷访问
    快捷注册登录阿里云