You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
分2个场景测试:一个不带where条件、一个带x_xid in (xxxx36,xxxx38)的条件,该条件能匹配出100W+数据,方便覆盖10W-100W之间的任何数据量场景,这里事先用select x_xid, count(x_id) c from t_xxxx_xxxx group by x_xid order by c desc;这样的语句对表做了统计,x_xid=xxxx36有696376条记录,x_xid=xxxx38有418576条记录。
脚本:apitest.sh
#!/bin/bashprintf -- '--------------------------\n开始不带where条件的情况测试\n'time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":100000, "T_xxxx_xxxx":{"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 10w_no_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":200000, "T_xxxx_xxxx":{"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 20w_no_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":500000, "T_xxxx_xxxx":{"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 50w_no_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":800000, "T_xxxx_xxxx":{"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 80w_no_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":1000000, "T_xxxx_xxxx":{"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 100w_no_where.log
printf -- '--------------------------\n开始带where条件的情况测试\n'time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":100000, "T_xxxx_xxxx":{"x_xid{}":[xxxx36,xxxx38],"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 10w_with_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":200000, "T_xxxx_xxxx":{"x_xid{}":[xxxx36,xxxx38],"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 20w_with_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":500000, "T_xxxx_xxxx":{"x_xid{}":[xxxx36,xxxx38],"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 50w_with_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":800000, "T_xxxx_xxxx":{"x_xid{}":[xxxx36,xxxx38],"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 80w_with_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":1000000, "T_xxxx_xxxx":{"x_xid{}":[xxxx36,xxxx38],"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 100w_with_where.log
也就是 MySQL 5.7 共 1.9KW 记录的大表,统计 CRUL 10-20M/s 网速从发起请求到接收完回包的总时长
分2个场景测试:一个不带where条件、一个带x_xid in (xxxx36,xxxx38)的条件,该条件能匹配出100W+数据,方便覆盖10W-100W之间的任何数据量场景,这里事先用select x_xid, count(x_id) c from t_xxxx_xxxx group by x_xid order by c desc;这样的语句对表做了统计,x_xid=xxxx36有696376条记录,x_xid=xxxx38有418576条记录。
脚本:apitest.sh
#!/bin/bashprintf -- '--------------------------\n开始不带where条件的情况测试\n'time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":100000, "T_xxxx_xxxx":{"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 10w_no_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":200000, "T_xxxx_xxxx":{"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 20w_no_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":500000, "T_xxxx_xxxx":{"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 50w_no_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":800000, "T_xxxx_xxxx":{"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 80w_no_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":1000000, "T_xxxx_xxxx":{"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 100w_no_where.log
printf -- '--------------------------\n开始带where条件的情况测试\n'time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":100000, "T_xxxx_xxxx":{"x_xid{}":[xxxx36,xxxx38],"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 10w_with_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":200000, "T_xxxx_xxxx":{"x_xid{}":[xxxx36,xxxx38],"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 20w_with_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":500000, "T_xxxx_xxxx":{"x_xid{}":[xxxx36,xxxx38],"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 50w_with_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":800000, "T_xxxx_xxxx":{"x_xid{}":[xxxx36,xxxx38],"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 80w_with_where.log
time curl -X POST -H 'Content-Type:application/json''http://x.xxx.xx.xxx:xxxx/get' -d '{"T_xxxx_xxxx[]":{"count":1000000, "T_xxxx_xxxx":{"x_xid{}":[xxxx36,xxxx38],"@column":"x_uin,x_send_time,x_xxxx_id,x_xid,x_xx_id,x_xxxxx_id,x_xxxx_result,x_result,x_credit"}}}'> 100w_with_where.log
也就是 MySQL 5.7 共 1.9KW 记录的大表,统计 CRUL 10-20M/s 网速从发起请求到接收完回包的总时长
---------- | ---------- | ---------- | ---------- | -------------
Total | Received | Time Total | Time Spent | Current Speed
72.5M | 72.5M | 0:00:05 | 0:00:05 | 20.0M
Activity
TommyLemon commentedon Sep 27, 2021
主要因为省掉了 AbstractObjectParser.onSQLExecute 878 行 for 循环 put 100 条数据(有日志) 3ms-8ms (MacBook Pro(13 英寸,2015 年初期)2.9 GHz Intel Core i5 16 GB 1867 MHz DDR3 MacOS 10.11.6 )
https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractObjectParser.java
以及 AbstractParser.onArrayParse 中用 1 次 addAll 替代 100 次 add 减少不必要的 ArrayList 频繁校验和扩容
https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractParser.java
TommyLemon commentedon Nov 22, 2021
腾讯 CSIG 某项目性能测试结果
1. 测试环境
1.1 机器配置
腾讯云tke docker pod, 4 核 / 16G。
1.2 db机器配置
腾讯云8核32000MB内存,805GB存储空间
1.3 测试表建表DML、数据量(mysql 5.7)
数据量:18558903
mysql版本:5.7
数据分布:使用group by 统计,基于其中x_xid来group by,得到以下表格:
select x_xid, count(x_id) counter from t_xxxx_xxxx group by x_xid order by counter desc limit 10;1.4 日志打印设置
2. 测试脚本 (使用Table[]: {Table: {}}格式)
脚本统计方式:
基于linux time命令输出的realtime来统计。
分2个场景测试:一个不带where条件、一个带x_xid in (xxxx36,xxxx38)的条件,该条件能匹配出100W+数据,方便覆盖10W-100W之间的任何数据量场景,这里事先用
select x_xid, count(x_id) c from t_xxxx_xxxx group by x_xid order by c desc;这样的语句对表做了统计,x_xid=xxxx36有696376条记录,x_xid=xxxx38有418576条记录。脚本:apitest.sh
也就是 MySQL 5.7 共 1.9KW 记录的大表,统计 CRUL 10-20M/s 网速从发起请求到接收完回包的总时长
TommyLemon commentedon Nov 22, 2021
以之前的 APIJSON 4.6.0 连接 2.3KW 大表带条件查出 12W+ 数据来估计:
https://github.com/Tencent/APIJSON/releases/tag/4.6.0
---------- | ---------- | ---------- | ---------- | -------------
Total | Received | Time Total | Time Spent | Current Speed
72.5M | 72.5M | 0:00:05 | 0:00:05 | 20.0M
/get >> http请求结束:5624
4.6.0-4.7.2 都没有明显的性能优化,所以 4.7.0 只花了约 2s 应该是因为换了张表,平均每行数据量减少了约 65% 为原来的 35%。
APIJSON 4.6.0 查原来 2.3KW 大表中 100W 数据按新旧表数据量比例估计耗时 = 20.632s / 65% = 31.7s;
APIJSON 4.6.0 查原来 2.3KW 大表中 100W 数据按同表查出数据量比例估计耗时 = 100W*72.5M/(12-13W)/(20M/s) = 27.9s-30.2s。
两种方式估算结果基本一致,也可以按这个 35% 新旧表平均每行数据量比例估算排除网络耗时后的整个服务耗时:
APIJSON 4.6.0 查原来 2.3KW 大表中 12W+ 数据量服务耗时 = 总耗时 5.624s - 数据 72.5M/下载速度 20.0Mbps = 2.00s;
APIJSON 4.7.0 查现在 1.9KW 大表中 10W 数据量服务耗时 = 总耗时 1.928s - 数据 72.5M*35%(10/12)/(下载速度 10-20.0Mbps) = 0.00-0.87s;
APIJSON 4.8.0 查现在 1.9KW 大表中 10W 数据量服务耗时 = 总耗时 1.392s - 数据 72.5M*35%(10/12)/(下载速度 10-20.0Mbps) = 0.00-0.33s,降低 62%;
APIJSON 4.7.0 查现在 1.9KW 大表中 100W 数据量服务耗时 = 总耗时 20.632s - 数据 725M*35%(10/12)/(下载速度 10-20.0Mbps) = 0.00-10.06s;
APIJSON 4.8.0 查现在 1.9KW 大表中 100W 数据量服务耗时 = 总耗时 16.481s - 数据 725M*35%(10/12)/(下载速度 10-20.0Mbps) = 0.00-5.91s,降低 41%。
TommyLemon commentedon Jan 9, 2022
腾讯业务百万数据 6s 响应,APIJSON 性能优化背后的故事

https://my.oschina.net/tommylemon/blog/5375645
[-]单表列表 性能优化前后对比[/-][+]4.8.0 单表列表 性能优化前后对比[/+]