云原生数据库He3DB-联邦内存池设计及测试

原创手机用户4553 2023-08-28

2216

什么是联邦内存

He3DB 为充分利用备机资源，提升读性能，进而引入内存池概念，设计了He3DB 特有的联邦内存池技术。
设计的核心理念是让备机内存缓存数据形成差异化，使各备机内存组成一个大的缓存，缓存更多的数据，并结合中间件的分发能力，将对应表数据的查询发送到缓存此表的备机，
提高缓存命中率，从而提升查询性能。

arch

测试环境

1.私有化部署模式，一主一备，主也承担读请求，容器方式部署，单个容器资源限制2c4G，share_buffer设置为1GB;

[root@21-master2 he3db]# docker ps |grep he3db-
13e8c9b238cc        registry.paas/cmss/he3db:wy230727   "bash"              32 minutes ago      Up 32 minutes       15430/tcp, 15432-15433/tcp, 0.0.0.0:25431->15431/tcp   he3db-sp
0a10a13b2152        registry.paas/cmss/he3db:wy230727   "bash"              32 minutes ago      Up 32 minutes       15430-15432/tcp, 0.0.0.0:25433->15433/tcp              he3db-s
69a0f222d271        registry.paas/cmss/he3db:wy230727   "bash"              32 minutes ago      Up 32 minutes       15431-15433/tcp, 0.0.0.0:25430->15430/tcp              he3db-mp
1429b555f6c4        registry.paas/cmss/he3db:wy230727   "bash"              32 minutes ago      Up 32 minutes       15430-15431/tcp, 15433/tcp, 0.0.0.0:25432->15432/tcp   he3db-m

2.导入数据，两张表，每张表400W行数据，共约2GB数据；

sysbench --db-driver=pgsql --pgsql-user=postgres --pgsql-password='***' --pgsql-db=postgres --events=0 --report-interval=5 --pgsql-host=*.*.*.* --pgsql-port=25432 --table-size=4000000 --tables=2  oltp_insert.lua --db-ps-mode=disable --threads=1 prepare

3.内存池缓存策略设置SQL；

create table pg_hot_data(datname name, relname name, crules char, ctype char, indexname name, keyname name, keyvalue name, keytype char, clientname name, clientaddr name, action name, crulessettime timestamp with time zone, cachetime timestamp with time zone);

# 设置主缓存表sbtest1
insert into pg_hot_data values('postgres','sbtest1','t','t','','','','','master','*.*.*.*:25432','precache','2022-10-10 15:55:39.496208+08','2000-10-10 15:55:39.496208+08');
# 设置备缓存表sbtest1
insert into pg_hot_data values('postgres','sbtest2','t','t','','','','','privstandby','*.*.*.*:25433','precache','2022-10-10 15:55:39.496208+08','2000-10-10 15:55:39.496208+08');

注：开启内存池功能postgresql.conf配置需要增加he3_meta_conninfo参数配置，用于存储缓存池配置；

he3_meta_conninfo='application_name=master user=postgres password=*** host=*.*.*.* port=25432'
he3_meta_conninfo='application_name=privstandby user=postgres password=*** host=*.*.*.* port=25432'

4.通过中间件He3Proxy 进行sysybench测试

（1）点查-point_select

sysbench --db-driver=pgsql --pgsql-user=postgres --pgsql-password='***' --pgsql-db=postgres --events=0 --report-interval=5 --pgsql-host=*.*.*.* --pgsql-port=9696 --table-size=4000000 --tables=2 oltp_point_select.lua run --time=120 --db-ps-mode=disable --threads=16  --rand-type='uniform'

参数–rand-type=‘uniform’，可使查询分布更加随机。

关闭内存池策略					开启内存池策略
主		备		QPS	主		备		QPS
缓存命中数	磁盘读取	缓存命中数	磁盘读取	QPS	缓存命中数	磁盘读取	缓存命中数	磁盘读取	QPS
12,554,924	1,790,730	12,574,793	1,775,255	59672.94	17,042,436	90,052	16,798,562	89,602	70553.19

（2）读写-read-write

sysbench --db-driver=pgsql --pgsql-user=postgres --pgsql-password='***' --pgsql-db=postgres --events=0 --report-interval=5 --pgsql-host=*.*.*.* --pgsql-port=9696 --table-size=4000000 --tables=2 oltp_read_write.lua run --time=120 --db-ps-mode=disable --threads=16  --rand-type='uniform'

读写比	关闭内存池策略						开启内存池策略
	主		备		TPS	QPS	主		备		TPS	QPS
	缓存命中数	磁盘读取	缓存命中数	磁盘读取	TPS	QPS	缓存命中数	磁盘读取	缓存命中数	磁盘读取	TPS	QPS
1:3	2,842,857	372,952	1,120,866	259,430	309.95	6199.20	3,117,868	183,479	1,426,280	141,784	317.85	6356.91
1:1	2,912,824	360,266	1,889,447	309,126	203.33	8133.33	3,868,684	181,627	2,553,201	128,191	264.11	10564.69
2:1	2,943,697	387,639	2,187,982	352,757	141.01	9870.53	4,502,375	163,333	3,455,374	115,108	203.21	14224.84

5.结果分析

测试结果详情参考附录信息，通过缓存命中数统计信息也可以直观看出，开启联邦内存池后缓存命中率提升明显。

总体而言开启联邦内存池点查性能提升约18%，效果明显；
读写测试场景中，当设置读写比1：3时，性能提升有限，但读写比2：1时，QPS提升约40%，同时主备节点的缓存命中数均大幅提升。

因本测试磁盘采用nvme-ssd读取性能较好，若使用普通ssd或SATA盘提升效果更加明显，同时当备机数量更多时，效果也更好。

6.未来演进

1.缓存策略更智能
目前仅支持按表粒度预设缓存，后期将结合数据访问指标动态设置缓存策略；

2.内存更精细化管理
目前内存加载表数据为整表加载，若data_buffer内存不足将无法进行数据页置换，后续需将data_buffer内存管理更精细化，划分专门区域处理内存池，
并对表缓存的数据精细化管理，细化到行粒度；

附-测试过程记录：

1.开启联邦缓存

sysbench --db-driver=pgsql --pgsql-user=postgres --pgsql-password='****' --pgsql-db=postgres --events=0 --report-interval=5 --pgsql-host=*.*.*.* --pgsql-port=9696 --table-size=4000000 --tables=2 oltp_point_select.lua run --time=120 --db-ps-mode=disable --threads=16  --rand-type='uniform'
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 16
Report intermediate results every 5 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 5s ] thds: 16 tps: 60188.94 qps: 60188.94 (r/w/o: 60188.94/0.00/0.00) lat (ms,95%): 0.28 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 16 tps: 69230.49 qps: 69230.49 (r/w/o: 69230.49/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 16 tps: 71599.36 qps: 71599.36 (r/w/o: 71599.36/0.00/0.00) lat (ms,95%): 0.26 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 71017.42 qps: 71017.42 (r/w/o: 71017.42/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 16 tps: 71200.13 qps: 71200.33 (r/w/o: 71200.33/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 70901.49 qps: 70901.29 (r/w/o: 70901.29/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 16 tps: 71200.22 qps: 71200.22 (r/w/o: 71200.22/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 71281.22 qps: 71281.22 (r/w/o: 71281.22/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 16 tps: 70667.65 qps: 70667.65 (r/w/o: 70667.65/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 71144.37 qps: 71144.37 (r/w/o: 71144.37/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 16 tps: 69962.83 qps: 69962.83 (r/w/o: 69962.83/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 71491.05 qps: 71491.05 (r/w/o: 71491.05/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 16 tps: 71421.91 qps: 71421.91 (r/w/o: 71421.91/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 16 tps: 70821.87 qps: 70821.87 (r/w/o: 70821.87/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 75s ] thds: 16 tps: 71222.63 qps: 71222.63 (r/w/o: 71222.63/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 16 tps: 71303.09 qps: 71303.09 (r/w/o: 71303.09/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 85s ] thds: 16 tps: 70586.13 qps: 70586.13 (r/w/o: 70586.13/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 16 tps: 71408.65 qps: 71408.65 (r/w/o: 71408.65/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 95s ] thds: 16 tps: 71304.84 qps: 71304.84 (r/w/o: 71304.84/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 16 tps: 71061.36 qps: 71061.36 (r/w/o: 71061.36/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 105s ] thds: 16 tps: 70774.05 qps: 70774.05 (r/w/o: 70774.05/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 16 tps: 70965.77 qps: 70965.77 (r/w/o: 70965.77/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 115s ] thds: 16 tps: 70721.79 qps: 70721.79 (r/w/o: 70721.79/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 16 tps: 71823.29 qps: 71823.29 (r/w/o: 71823.29/0.00/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            8466620
        write:                           0
        other:                           0
        total:                           8466620
    transactions:                        8466620 (70553.19 per sec.)
    queries:                             8466620 (70553.19 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0021s
    total number of events:              8466620

Latency (ms):
         min:                                    0.16
         avg:                                    0.23
         max:                                  214.05
         95th percentile:                        0.27
         sum:                              1916824.82

Threads fairness:
    events (avg/stddev):           529163.7500/476.75
    execution time (avg/stddev):   119.8016/0.00

主机缓存命中数

blks_read：在这个数据库中读取的磁盘块的数量;
blks_hit：高速缓存中已经发现的磁盘块的次数，这样读取是不必要的（这只包括PostgreSQL缓冲区高速缓存，没有操作系统的文件系统缓存;

# 测试前
postgres=# select datname,blks_read,blks_hit from pg_stat_database;
  datname  | blks_read | blks_hit 
-----------+-----------+----------
           |       204 |   477359
 postgres  |   5899359 | 98132708
 template1 |         0 |        0
 template0 |         0 |        0
(4 rows)

# 执行sysbench测试后
postgres=# select datname,blks_read,blks_hit from pg_stat_database;
  datname  | blks_read | blks_hit  
-----------+-----------+-----------
           |       204 |    542287
 postgres  |   5989411 | 115175144
 template1 |         0 |         0
 template0 |         0 |         0
(4 rows)

备机缓存命中数

# 测试前
postgres=# select datname,blks_read,blks_hit from pg_stat_database;
  datname  | blks_read | blks_hit 
-----------+-----------+----------
           |        55 |     1001
 postgres  |   3790512 | 27572963
 template1 |         0 |        0
 template0 |         0 |        0
(4 rows)

# 执行sysbench测试后
postgres=# select datname,blks_read,blks_hit from pg_stat_database;
  datname  | blks_read | blks_hit 
-----------+-----------+----------
           |        55 |     1402
 postgres  |   3880114 | 44371525
 template1 |         0 |        0
 template0 |         0 |        0
(4 rows)

2.关闭联邦缓存

sysbench --db-driver=pgsql --pgsql-user=postgres --pgsql-password='***' --pgsql-db=postgres --events=0 --report-interval=5 --pgsql-host=*.*.*.* --pgsql-port=9696 --table-size=4000000 --tables=2 oltp_point_select.lua run --time=120 --db-ps-mode=disable --threads=16  --rand-type='uniform'
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 16
Report intermediate results every 5 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 5s ] thds: 16 tps: 55707.25 qps: 55707.25 (r/w/o: 55707.25/0.00/0.00) lat (ms,95%): 0.31 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 16 tps: 58657.43 qps: 58657.43 (r/w/o: 58657.43/0.00/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 16 tps: 58882.62 qps: 58882.62 (r/w/o: 58882.62/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 58460.32 qps: 58460.32 (r/w/o: 58460.32/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 16 tps: 58654.24 qps: 58654.24 (r/w/o: 58654.24/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 59433.36 qps: 59433.36 (r/w/o: 59433.36/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 16 tps: 59364.22 qps: 59364.22 (r/w/o: 59364.22/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 60091.18 qps: 60091.18 (r/w/o: 60091.18/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 16 tps: 59170.57 qps: 59170.57 (r/w/o: 59170.57/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 59589.45 qps: 59589.45 (r/w/o: 59589.45/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 16 tps: 61210.38 qps: 61210.38 (r/w/o: 61210.38/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 60139.06 qps: 60139.26 (r/w/o: 60139.26/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 16 tps: 60663.67 qps: 60663.47 (r/w/o: 60663.47/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 16 tps: 59989.60 qps: 59989.60 (r/w/o: 59989.60/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 75s ] thds: 16 tps: 59860.30 qps: 59860.30 (r/w/o: 59860.30/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 16 tps: 60549.58 qps: 60549.58 (r/w/o: 60549.58/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 85s ] thds: 16 tps: 61103.69 qps: 61103.69 (r/w/o: 61103.69/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 16 tps: 61489.30 qps: 61489.30 (r/w/o: 61489.30/0.00/0.00) lat (ms,95%): 0.28 err/s: 0.00 reconn/s: 0.00
[ 95s ] thds: 16 tps: 61487.85 qps: 61487.85 (r/w/o: 61487.85/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 16 tps: 58071.50 qps: 58071.50 (r/w/o: 58071.50/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 105s ] thds: 16 tps: 58054.37 qps: 58054.37 (r/w/o: 58054.37/0.00/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 16 tps: 61092.20 qps: 61092.20 (r/w/o: 61092.20/0.00/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00
[ 115s ] thds: 16 tps: 59318.63 qps: 59318.63 (r/w/o: 59318.63/0.00/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 16 tps: 61132.40 qps: 61132.40 (r/w/o: 61132.40/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            7160953
        write:                           0
        other:                           0
        total:                           7160953
    transactions:                        7160953 (59672.94 per sec.)
    queries:                             7160953 (59672.94 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0021s
    total number of events:              7160953

Latency (ms):
         min:                                    0.16
         avg:                                    0.27
         max:                                   76.91
         95th percentile:                        0.29
         sum:                              1917303.45

Threads fairness:
    events (avg/stddev):           447559.5625/413.68
    execution time (avg/stddev):   119.8315/0.00