在进行Sysbench测试时,单表、单进程 初始1000万数据。
用select_random_points进行测试,tps:4000多;
然后进行oltp_insert.lua,测试,插入数据了 100多万数据后(总量11194801) 此时select_random_points的TPS降低到289,下降数十倍!
而直接初始prepare生成2000万数据,tps也能接近4000.
所以insert单行数据,插入100万以后,性能下降如此明显的原因是什么?如何分析定位、解决这一现象?
初始1000万数据:select_random_points的执行计划:0.3ms
explain analyze SELECT id,k,c,pad FROM sbtest1 WHERE k IN (1,2,3,4,5,600,7,8,9,1001);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Index Scan using k_1 on sbtest1 (cost=0.01..118.83 rows=19 width=190) (actual time=0.128..0.189 rows=11 loops=1)
Index Cond: (k = ANY ('{1,2,3,4,5,600,7,8,9,1001}'::integer[]))
Total runtime: 0.326 ms
(3 行记录)
写入100万以后,执行2.5ms,耗时增长到近乎8倍!
sysbench=> explain analyze SELECT id,k,c,pad FROM sbtest1 WHERE k IN (1,2,3,4,5,600,7,8,9,1001);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
-
Index Scan using k_1 on sbtest1 (cost=0.01..524.54 rows=130 width=190) (actual time=0.025..2.307 rows=1159 loops=1)
Index Cond: (k = ANY ('{1,2,3,4,5,600,7,8,9,1001}'::integer[]))
Total runtime: 2.560 ms
(3 行记录)
######初始生成2000万数据,执行计划0.4ms
sysbench=> explain analyze SELECT id,k,c,pad FROM sbtest1 WHERE k IN (1,2,3,4,5,600,7,8,9,1001);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Index Scan using k_1 on sbtest1 (cost=0.01..110.83 rows=17 width=190) (actual time=0.173..0.226 rows=7 loops=1)
Index Cond: (k = ANY ('{1,2,3,4,5,600,7,8,9,1001}'::integer[]))
Total runtime: 0.402 ms
(3 行记录)
复制

1、你使用的是MogDB哪个版本?另外是通过ptk安装的吗?
2、你的测试环境配置是怎么样的?


查询性能会随着对象的膨胀,有一定程度的衰减,pg系数据库都面临这个问题。
可以试试ustore存储引擎。能够很好的解决膨胀问题。


[root@mogdb lua]# /root/sysbench-1.0.18/src/sysbench oltp_insert.lua --pgsql-host=172.20.22.173 --pgsql-port=26000 --pgsql-user=roger --pgsql-password=roger@888 --pgsql-db=test --table-size=10000000 --tables=1 --threads=72 --time=120 --report-interval=3 prepare
sysbench 1.0.18 (using bundled LuaJIT 2.1.0-beta2)
Initializing worker threads...
Creating table 'sbtest1'...
Inserting 10000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
[root@mogdb lua]#
[root@mogdb lua]#
test=# \dt+ sbtest1
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+---------+-------+-------+---------+----------------------------------+-------------
public | sbtest1 | table | roger | 2112 MB | {orientation=row,compression=no} |
(1 row)
test=#
test=# \timing on
Timing is on.
test=# explain analyze SELECT id,k,c,pad FROM sbtest1 WHERE k IN (1,2,3,4,5,600,7,8,9,1001);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on sbtest1 (cost=48.48..3007.40 rows=767 width=190) (actual time=0.432..0.432 rows=0 loops=1)
Recheck Cond: (k = ANY ('{1,2,3,4,5,600,7,8,9,1001}'::integer[]))
-> Bitmap Index Scan on k_1 (cost=0.00..48.28 rows=767 width=0) (actual time=0.424..0.424 rows=0 loops=1)
Index Cond: (k = ANY ('{1,2,3,4,5,600,7,8,9,1001}'::integer[]))
Total runtime: 0.808 ms
(5 rows)
Time: 4.469 ms
此时压测select_random_points:
[root@mogdb lua]# /root/sysbench-1.0.18/src/sysbench select_random_points.lua --pgsql-host=172.20.22.173 --pgsql-port=26000 --pgsql-user=roger --pgsql-password=roger@888 --pgsql-db=test --table-size=10000000 --tables=1 --threads=72 --time=120 --report-interval=5 run
sysbench 1.0.18 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 72
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 5s ] thds: 72 tps: 70169.56 qps: 70169.56 (r/w/o: 70169.56/0.00/0.00) lat (ms,95%): 1.93 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 72 tps: 78534.14 qps: 78534.14 (r/w/o: 78534.14/0.00/0.00) lat (ms,95%): 1.55 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 72 tps: 73343.42 qps: 73343.42 (r/w/o: 73343.42/0.00/0.00) lat (ms,95%): 1.82 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 72 tps: 79771.81 qps: 79771.81 (r/w/o: 79771.81/0.00/0.00) lat (ms,95%): 1.52 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 72 tps: 75754.47 qps: 75754.47 (r/w/o: 75754.47/0.00/0.00) lat (ms,95%): 1.76 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 72 tps: 74521.76 qps: 74521.76 (r/w/o: 74521.76/0.00/0.00) lat (ms,95%): 1.76 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 72 tps: 82506.26 qps: 82506.26 (r/w/o: 82506.26/0.00/0.00) lat (ms,95%): 1.44 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 72 tps: 74965.76 qps: 74965.76 (r/w/o: 74965.76/0.00/0.00) lat (ms,95%): 1.76 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 72 tps: 77408.96 qps: 77408.96 (r/w/o: 77408.96/0.00/0.00) lat (ms,95%): 1.64 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 72 tps: 81698.43 qps: 81698.43 (r/w/o: 81698.43/0.00/0.00) lat (ms,95%): 1.52 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 72 tps: 74592.34 qps: 74592.34 (r/w/o: 74592.34/0.00/0.00) lat (ms,95%): 1.79 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 72 tps: 80461.29 qps: 80461.29 (r/w/o: 80461.29/0.00/0.00) lat (ms,95%): 1.50 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 72 tps: 76294.01 qps: 76294.01 (r/w/o: 76294.01/0.00/0.00) lat (ms,95%): 1.73 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 72 tps: 75210.56 qps: 75210.56 (r/w/o: 75210.56/0.00/0.00) lat (ms,95%): 1.73 err/s: 0.00 reconn/s: 0.00
[ 75s ] thds: 72 tps: 79460.73 qps: 79460.73 (r/w/o: 79460.73/0.00/0.00) lat (ms,95%): 1.58 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 72 tps: 74693.01 qps: 74693.01 (r/w/o: 74693.01/0.00/0.00) lat (ms,95%): 1.76 err/s: 0.00 reconn/s: 0.00
[ 85s ] thds: 72 tps: 76418.55 qps: 76418.55 (r/w/o: 76418.55/0.00/0.00) lat (ms,95%): 1.70 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 72 tps: 80032.61 qps: 80032.61 (r/w/o: 80032.61/0.00/0.00) lat (ms,95%): 1.61 err/s: 0.00 reconn/s: 0.00
[ 95s ] thds: 72 tps: 77611.83 qps: 77611.83 (r/w/o: 77611.83/0.00/0.00) lat (ms,95%): 1.67 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 72 tps: 82216.38 qps: 82216.38 (r/w/o: 82216.38/0.00/0.00) lat (ms,95%): 1.44 err/s: 0.00 reconn/s: 0.00
[ 105s ] thds: 72 tps: 74542.70 qps: 74542.70 (r/w/o: 74542.70/0.00/0.00) lat (ms,95%): 1.79 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 72 tps: 76000.01 qps: 76000.01 (r/w/o: 76000.01/0.00/0.00) lat (ms,95%): 1.76 err/s: 0.00 reconn/s: 0.00
[ 115s ] thds: 72 tps: 76232.41 qps: 76232.41 (r/w/o: 76232.41/0.00/0.00) lat (ms,95%): 1.70 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 72 tps: 81033.49 qps: 81033.49 (r/w/o: 81033.49/0.00/0.00) lat (ms,95%): 1.50 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 9268036
write: 0
other: 0
total: 9268036
transactions: 9268036 (77212.61 per sec.)
queries: 9268036 (77212.61 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0301s
total number of events: 9268036
Latency (ms):
min: 0.20
avg: 0.93
max: 52.58
95th percentile: 1.67
sum: 8627563.35
Threads fairness:
events (avg/stddev): 128722.7222/14940.88
execution time (avg/stddev): 119.8273/0.02
插入数据:
[root@mogdb lua]# /root/sysbench-1.0.18/src/sysbench oltp_insert.lua --pgsql-host=172.20.22.173 --pgsql-port=26000 --pgsql-user=roger --pgsql-password=roger@888 --pgsql-db=test --table-size=10000000 --tables=1 --threads=72 --time=120 --report-interval=5 run
sysbench 1.0.18 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 72
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 5s ] thds: 72 tps: 33301.82 qps: 33301.82 (r/w/o: 0.00/33301.82/0.00) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 72 tps: 30751.00 qps: 30751.00 (r/w/o: 0.00/30751.00/0.00) lat (ms,95%): 4.10 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 72 tps: 31571.58 qps: 31571.58 (r/w/o: 0.00/31571.58/0.00) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 72 tps: 28874.96 qps: 28874.96 (r/w/o: 0.00/28874.96/0.00) lat (ms,95%): 4.41 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 72 tps: 29889.63 qps: 29889.63 (r/w/o: 0.00/29889.63/0.00) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 72 tps: 28504.78 qps: 28504.78 (r/w/o: 0.00/28504.78/0.00) lat (ms,95%): 4.49 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 72 tps: 29289.11 qps: 29289.11 (r/w/o: 0.00/29289.11/0.00) lat (ms,95%): 4.41 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 72 tps: 29412.60 qps: 29412.60 (r/w/o: 0.00/29412.60/0.00) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 72 tps: 29875.65 qps: 29875.65 (r/w/o: 0.00/29875.65/0.00) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 72 tps: 26995.34 qps: 26995.34 (r/w/o: 0.00/26995.34/0.00) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 72 tps: 30856.17 qps: 30856.17 (r/w/o: 0.00/30856.17/0.00) lat (ms,95%): 4.18 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 72 tps: 33421.69 qps: 33421.69 (r/w/o: 0.00/33421.69/0.00) lat (ms,95%): 3.75 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 72 tps: 33139.80 qps: 33139.80 (r/w/o: 0.00/33139.80/0.00) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 72 tps: 33497.43 qps: 33497.43 (r/w/o: 0.00/33497.43/0.00) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
[ 75s ] thds: 72 tps: 33897.05 qps: 33897.05 (r/w/o: 0.00/33897.05/0.00) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 72 tps: 32783.91 qps: 32783.91 (r/w/o: 0.00/32783.91/0.00) lat (ms,95%): 4.03 err/s: 0.00 reconn/s: 0.00
[ 85s ] thds: 72 tps: 33264.88 qps: 33264.88 (r/w/o: 0.00/33264.88/0.00) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 72 tps: 31824.04 qps: 31824.04 (r/w/o: 0.00/31824.04/0.00) lat (ms,95%): 4.18 err/s: 0.00 reconn/s: 0.00
[ 95s ] thds: 72 tps: 33763.56 qps: 33763.56 (r/w/o: 0.00/33763.56/0.00) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 72 tps: 33674.19 qps: 33674.19 (r/w/o: 0.00/33674.19/0.00) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 105s ] thds: 72 tps: 34178.70 qps: 34178.70 (r/w/o: 0.00/34178.70/0.00) lat (ms,95%): 3.75 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 72 tps: 33511.37 qps: 33511.37 (r/w/o: 0.00/33511.37/0.00) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 115s ] thds: 72 tps: 34430.00 qps: 34430.00 (r/w/o: 0.00/34430.00/0.00) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 72 tps: 32997.31 qps: 32997.31 (r/w/o: 0.00/32997.31/0.00) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 3818738
other: 0
total: 3818738
transactions: 3818738 (31817.11 per sec.)
queries: 3818738 (31817.11 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0188s
total number of events: 3818738
Latency (ms):
min: 0.50
avg: 2.26
max: 131.04
95th percentile: 4.03
sum: 8625823.68
Threads fairness:
events (avg/stddev): 53038.0278/346.24
execution time (avg/stddev): 119.8031/0.00
test=# \dt+ sbtest1
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+---------+-------+-------+---------+----------------------------------+-------------
public | sbtest1 | table | roger | 2919 MB | {orientation=row,compression=no} |
(1 row)
test=# explain analyze SELECT id,k,c,pad FROM sbtest1 WHERE k IN (1,2,3,4,5,600,7,8,9,1001);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on sbtest1 (cost=50.49..4027.65 rows=1030 width=190) (actual time=0.081..0.081 rows=0 loops=1)
Recheck Cond: (k = ANY ('{1,2,3,4,5,600,7,8,9,1001}'::integer[]))
-> Bitmap Index Scan on k_1 (cost=0.00..50.23 rows=1030 width=0) (actual time=0.075..0.075 rows=0 loops=1)
Index Cond: (k = ANY ('{1,2,3,4,5,600,7,8,9,1001}'::integer[]))
Total runtime: 0.326 ms
(5 rows)
Time: 3.113 ms
test=# select count(1) from sbtest1;
count
----------
13818738
(1 row)
Time: 3322.126 ms
test=#


