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

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
性能测试 PTS,5000VUM额度
简介: 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)

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)全局二级索引执行
执行的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)

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

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)

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

||~ __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开源数据库
本实验环境已内置PostgreSQL数据库以及PolarDB开源数据库:PolarDB PostgreSQL版和PolarDB分布式版,支持一键拉起使用,方便各位开发者学习使用。
相关文章
|
3月前
|
SQL 安全 关系型数据库
PHP作为一种流行的服务端脚本语言,在Web开发领域具有显著的优势
【10月更文挑战第11天】PHP作为一种流行的服务端脚本语言,在Web开发领域具有显著的优势
60 0
|
5月前
|
存储 缓存 负载均衡
【PolarDB-X 技术揭秘】Lizard B+tree:揭秘分布式数据库索引优化的终极奥秘!
【8月更文挑战第25天】PolarDB-X是阿里云的一款分布式数据库产品,其核心组件Lizard B+tree针对分布式环境优化,解决了传统B+tree面临的数据分片与跨节点查询等问题。Lizard B+tree通过一致性哈希实现数据分片,确保分布式一致性;智能分区实现了负载均衡;高效的搜索算法与缓存机制降低了查询延迟;副本机制确保了系统的高可用性。此外,PolarDB-X通过自适应分支因子、缓存优化、异步写入、数据压缩和智能分片等策略进一步提升了Lizard B+tree的性能,使其能够在分布式环境下提供高性能的索引服务。这些优化不仅提高了查询速度,还确保了系统的稳定性和可靠性。
107 5
|
6月前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之相同的SQL语句在不同时间执行EXPLAIN计划显示出不同的索引类型,是什么原因
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
PolarDB产品使用问题之相同的SQL语句在不同时间执行EXPLAIN计划显示出不同的索引类型,是什么原因
|
6月前
|
关系型数据库 分布式数据库 PolarDB
PolarDB产品使用问题之如何基于Docker进行PolarDB-X单机模拟部署
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
PolarDB产品使用问题之如何基于Docker进行PolarDB-X单机模拟部署
|
5月前
|
C# UED 定位技术
WPF控件大全:初学者必读,掌握控件使用技巧,让你的应用程序更上一层楼!
【8月更文挑战第31天】在WPF应用程序开发中,控件是实现用户界面交互的关键元素。WPF提供了丰富的控件库,包括基础控件(如`Button`、`TextBox`)、布局控件(如`StackPanel`、`Grid`)、数据绑定控件(如`ListBox`、`DataGrid`)等。本文将介绍这些控件的基本分类及使用技巧,并通过示例代码展示如何在项目中应用。合理选择控件并利用布局控件和数据绑定功能,可以提升用户体验和程序性能。
103 0
|
6月前
|
Oracle 关系型数据库 分布式数据库
PolarDB产品使用问题之使用pxd安装PolarDB-X出现报错,该怎么办
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
6月前
|
Kubernetes 关系型数据库 分布式数据库
PolarDB产品使用问题之PolarDB-X的架构形态有什么区别
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
6月前
|
关系型数据库 分布式数据库 PolarDB
PolarDB产品使用问题之原PolarDB-X集群无法连接且Docker容器已经被删除,如何恢复数据
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
6月前
|
运维 关系型数据库 MySQL
PolarDB产品使用问题之PolarDB MySQL版和PolarDB-X的区别是什么
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
6月前
|
关系型数据库 分布式数据库 数据库
PolarDB产品使用问题之将RDS切换到PolarDB-X 2.0时,代码层的SQL该如何改动
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。