ReaderMX.
PostgreSQL码农集散地

数据库实验手册系列: 7 移动社交、多媒体、内容分发、游戏业务场景, 跨地域多机房的智能加速

Pdigoal约 11 分钟

文章开始前推荐2个学习环境: 

1、欢迎使用镜像快速体验PostgreSQL/DuckDB强大功能:《最好的PostgreSQL学习镜像》

2、欢迎使用云起实验室: 《免费体验PolarDB开源数据库》

标签

PostgreSQL , PolarDB , 数据库 , 教学

背景

欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.

  • 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.

本文的实验可以使用永久免费的阿里云云起实验室来完成.

如果你本地有docker环境也可以把镜像拉到本地来做实验:

x86_64机器使用以下docker image:

  • 《amd64 image》

ARM机器使用以下docker image:

  • 《arm64 image》

业务场景1 介绍: 移动社交、多媒体、内容分发、游戏业务场景, 跨地域多机房的智能加速

在移动社交、多媒体、内容分发业务场景中, 如果用户要交互的内容都在中心网络(假设深圳), 现在用户流动非常频繁, 当用户从深圳出差到北京, 因为网络延迟急剧增加, 他的访问体验就会变得非常差.

网络延迟对游戏业务的影响则更加严重.

为了解决这个问题, 企业会将业务部署在全国各地, 不管用户在哪里出差, 他都可以就近访问最近的中心.

由于标记用户的只有IP地址, 怎么根据用户的接入IP来判断他应该访问哪个中心呢? 通过这个实验, 大家可以了解到在数据库中如何存储IP地址范围和各中心IDC的映射关系, 以及如何根据用户的来源IP(接入IP)来判断他应该去哪个中心IDC访问.

实现和对照

1、设计一张IDC元数据表, 存储IDC的基本信息.

create table tbl_idc (  
  id int primary key,   -- 主键   
  info text    -- 随便输入一些信息, 这个字段和这个实验无关, 不影响这个实验的核心目标   
);     
  -- 写入100个IDC数据  
  insert into tbl_idc select generate_series(1,100), md5(random()::text);

2、下载(或购买)IP地址库.

参考这篇文章的方法, 得到一张IP地址库表. 《如何获得IP地址对应的地理信息库, 实现智能DNS解析? 就近路由提升全球化部署业务的访问性能》

su - postgres  
  curl http://ftp.apnic.net/apnic/stats/apnic/delegated-apnic-latest -o src.html   
grep "^apnic|CN|ipv4" src.html > ./cn.csv    
./duckdb -c "copy (select column3, (32-log2(column4::int))::int from '/var/lib/postgresql/cn.csv') to '/var/lib/postgresql/ip2region.csv' with (delimiter '/')"
vi c.sh    
    #!/bin/bash    
    for line in $(cat /var/lib/postgresql/ip2region.csv)    
do    
  # echo $line    
  echo "${line},`curl -s http://whois.pconline.com.cn/ip.jsp?ip=${line}|iconv -f GBK -t UTF8|grep -v "^$"|awk '{print $1","$2}'`"    
done
. ./c.sh  >./c.csv

得到类似这样的信息:

43.226.64.0/22,辽宁省大连市,电信  
43.226.68.0/22,辽宁省大连市,电信  
43.226.72.0/22,辽宁省大连市,电信  
43.226.76.0/22,辽宁省大连市,电信  
43.226.80.0/22,辽宁省大连市,电信  
43.226.84.0/22,辽宁省大连市,电信  
43.226.88.0/22,辽宁省大连市,电信  
43.226.92.0/22,辽宁省大连市,电信  
43.226.96.0/22,江苏省常州市,电信  
43.226.100.0/22,江苏省常州市,电信  
43.226.104.0/22,江苏省常州市,电信  
43.226.108.0/22,江苏省常州市,电信

导入postgresql|polardb

psql   
  create table tbl_iprange (  
  id serial primary key,  -- 主键   
  ipr inet,  -- ip地址段   
  loc text,  -- 物理地址   
  provider text  -- 运营商   
);   
    copy tbl_iprange(ipr,loc,provider) from '/var/lib/postgresql/c.csv';

3、根据IP地址库的地理位置信息, 整理维护一份属于自己的IP地址范围对应IDC的表.

create table tbl_idc_iprange (  -- IP地址范围对应IDC的表  
  id int primary key,  -- 主键   
  ipr inet,  -- ip地址段   
  loc text,  -- 物理地址   
  provider text,  -- 运营商   
  idcid int references tbl_idc(id)  -- idc id, 随便输入, 不影响测试   
);   
  insert into tbl_idc_iprange select *, ceil(random()*100) from tbl_iprange;

4、根据用户的来源IP(移动端接入地址通常是基站IP)

本实验会使用三种方法来实现.

传统方法 设计和实验

传统数据库没有IP类型, 也没有iprange类型, 所以需要将IP地址段拆成2个int8字段来存储IP地址段的开始和结束地址.

create table tbl_idc_iprange_int8 (  
  id int primary key,  -- 主键   
  ipstart int8,  -- ip地址段开始地址  
  ipend int8,  -- ip地址段结束地址  
  loc text,  -- 物理地址   
  provider text,  -- 运营商   
  idcid int references tbl_idc(id)  -- idc id, 随便输入, 不影响测试   
);

创建一个函数, 将inet类型转换为int8类型

create or replace function ip2int8 (inet) returns int8 as $$  
select ('b'||  
  split_part($1::text,'.',1)::int::bit(8)::text||  
  split_part($1::text,'.',2)::int::bit(8)::text||  
  split_part($1::text,'.',3)::int::bit(8)::text||  
  (split_part(split_part($1::text,'.',4),'/',1))::int::bit(8)::text)::bit(32)::int8;  
$$ language sql strict;

将ip开始和结束值转换为int8 , 写入测试表.

insert into tbl_idc_iprange_int8   
select id,  
ip2int8(network(ipr)),  
ip2int8(broadcast(ipr)),  
loc,  
provider,  
idcid  
from tbl_idc_iprange;

创建索引

create index on tbl_idc_iprange_int8 (ipstart, ipend);

根据用户的来源IP(移动端接入地址通常是基站IP). 假设来源来源ip是202.101.172.35 : 3395660835

select * from tbl_idc_iprange_int8 where ipstart < 3395660835 and ipend > 3395660835;   
    id  |  ipstart   |   ipend    | loc | provider | idcid   
------+------------+------------+-----+----------+-------  
 6924 | 3395649536 | 3395665919 |     |          |     4  
(1 row)  
    postgres=# explain analyze select * from tbl_idc_iprange_int8 where ipstart < 3395660835 and ipend > 3395660835;  
                                                                          QUERY PLAN                                                                             
---------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using tbl_idc_iprange_int8_ipstart_ipend_idx on tbl_idc_iprange_int8  (cost=0.28..85.71 rows=1 width=88) (actual time=0.385..0.388 rows=1 loops=1)  
   Index Cond: ((ipstart < '3395660835'::bigint) AND (ipend > '3395660835'::bigint))  
 Planning Time: 0.322 ms  
 Execution Time: 0.426 ms  
(4 rows)

编写压测脚本进行压力测试

vi t1.sql    
  select * from tbl_idc_iprange_int8 where ipstart < 3395660835 and ipend > 3395660835;   
  pgbench -M prepared -n -r -f ./t1.sql -P 1 -c 10 -j 10 -T 120

压测结果

transaction type: ./t1.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 10  
number of threads: 10  
duration: 120 s  
number of transactions actually processed: 7666686  
latency average = 0.156 ms  
latency stddev = 0.084 ms  
initial connection time = 19.873 ms  
tps = 63899.196660 (without initial connection time)  
statement latencies in milliseconds:  
         0.156  select * from tbl_idc_iprange_int8 where ipstart < 3395660835 and ipend > 3395660835;  
    TPS: 63899.196660

PolarDB|PG新方法1 设计和实验

PolarDB和PG都支持inet类型, 可以直接使用IP地址进行范围判断.

create table tbl_idc_iprange (  
  id int primary key,  -- 主键   
  ipr inet,  -- ip地址段   
  loc text,  -- 物理地址   
  provider text,  -- 运营商   
  idcid int references tbl_idc(id)  -- idc id, 随便输入, 不影响测试   
);   
  -- 创建inet的spgist索引.  
create index on tbl_idc_iprange using spgist (ipr);

根据用户的来源IP(移动端接入地址通常是基站IP). 假设来源来源ip是202.101.172.35 : 3395660835

select * from public.tbl_idc_iprange where ipr >>= '202.101.172.35';  
    id  |       ipr        | loc | provider | idcid   
------+------------------+-----+----------+-------  
 6924 | 202.101.128.0/18 |     |          |     4  
(1 row)  
  postgres=# explain analyze select * from public.tbl_idc_iprange where ipr >>= '202.101.172.35';  
                                                                QUERY PLAN                                                                  
------------------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using tbl_idc_iprange_ipr_idx on tbl_idc_iprange  (cost=0.15..2.37 rows=1 width=79) (actual time=0.070..0.072 rows=1 loops=1)  
   Index Cond: (ipr >>= '202.101.172.35'::inet)  
 Planning Time: 0.371 ms  
 Execution Time: 0.117 ms  
(4 rows)

编写压测脚本进行压力测试

vi t2.sql  
select * from public.tbl_idc_iprange where ipr >>= '202.101.172.35';  
    pgbench -M prepared -n -r -f ./t2.sql -P 1 -c 10 -j 10 -T 120

压测结果

transaction type: ./t2.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 10  
number of threads: 10  
duration: 120 s  
number of transactions actually processed: 32939025  
latency average = 0.036 ms  
latency stddev = 0.025 ms  
initial connection time = 21.646 ms  
tps = 274540.956059 (without initial connection time)  
statement latencies in milliseconds:  
         0.036  select * from public.tbl_idc_iprange where ipr >>= '202.101.172.35';  
  TPS: 274540.956059

PolarDB|PG新方法2 设计和实验

很显然使用了spgist后性能得到了飙升. 如果用户最初设计的表结构并未使用inet类型, 那么可以使用表达式索引来支持该能力.

使用传统方法的表结构, 将字段转换为int8range类型, 然后使用范围类型索引加速范围检索.

create index on tbl_idc_iprange_int8 using gist (int8range(ipstart,ipend));
select * from tbl_idc_iprange_int8 where int8range(ipstart,ipend) @> 3395660835;  
  id  |  ipstart   |   ipend    | loc | provider | idcid   
------+------------+------------+-----+----------+-------  
 6924 | 3395649536 | 3395665919 |     |          |     4  
(1 row)  
    postgres=# explain analyze select * from tbl_idc_iprange_int8 where int8range(ipstart,ipend) @> 3395660835;  
                                                                 QUERY PLAN                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl_idc_iprange_int8  (cost=1.58..36.16 rows=43 width=88) (actual time=0.155..0.158 rows=1 loops=1)  
   Recheck Cond: (int8range(ipstart, ipend) @> '3395660835'::bigint)  
   Heap Blocks: exact=1  
   ->  Bitmap Index Scan on tbl_idc_iprange_int8_int8range_idx  (cost=0.00..1.57 rows=43 width=0) (actual time=0.144..0.145 rows=1 loops=1)  
         Index Cond: (int8range(ipstart, ipend) @> '3395660835'::bigint)  
 Planning Time: 0.629 ms  
 Execution Time: 0.269 ms  
(7 rows)  
  postgres=# set enable_bitmapscan =off;  
SET  
  postgres=# explain analyze select * from tbl_idc_iprange_int8 where int8range(ipstart,ipend) @> 3395660835;  
                                                                         QUERY PLAN                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using tbl_idc_iprange_int8_int8range_idx on tbl_idc_iprange_int8  (cost=0.15..38.30 rows=43 width=88) (actual time=0.127..0.129 rows=1 loops=1)  
   Index Cond: (int8range(ipstart, ipend) @> '3395660835'::bigint)  
 Planning Time: 0.212 ms  
 Execution Time: 0.174 ms  
(4 rows)

编写压测脚本进行压力测试

vi t3.sql  
select * from tbl_idc_iprange_int8 where int8range(ipstart,ipend) @> 3395660835;  
  pgbench -M prepared -n -r -f ./t3.sql -P 1 -c 10 -j 10 -T 120

压测结果

transaction type: ./t3.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 10  
number of threads: 10  
duration: 120 s  
number of transactions actually processed: 27106165  
latency average = 0.044 ms  
latency stddev = 0.032 ms  
initial connection time = 20.660 ms  
tps = 225923.247074 (without initial connection time)  
statement latencies in milliseconds:  
         0.044  select * from tbl_idc_iprange_int8 where int8range(ipstart,ipend) @> 3395660835;  
    TPS: 225923.247074

对照

测试模型性能指标(TPS)
传统方法int8类型 between and63899.196660
PG|PolarDB inet类型 >>=274540.956059
PG|PolarDB int8range 类型表达式索引 @>225923.247074

传统方法没有IP类型也没有range类型, 使用传统的2字段范围检索, 性能较差.

知识点

inet类型: https://www.postgresql.org/docs/16/datatype-net-types.html

  • 得到ip地址段的开始IP network(inet) AS start_address

  • 得到ip地址段的结束IP broadcast(inet) AS end_address

  • 得到ip地址段的IP split_part(inet::text, '/', 1)

  • 将ip地址转换为int8 ip2int8(inet)

IP地址段匹配: https://www.postgresql.org/docs/16/functions-net.html

int8range类型: https://www.postgresql.org/docs/16/rangetypes.html

表达式索引: https://www.postgresql.org/docs/16/indexes-expressional.html

范围查找: https://www.postgresql.org/docs/16/functions-range.html

gist索引:

  • 《PostgreSQL 9种索引的原理和应用场景》

  • 《PostgreSQL GiST 索引原理 - 4》

  • 《PostgreSQL GiST 索引原理 - 3》

  • 《PostgreSQL GiST 索引原理 - 2》

  • 《PostgreSQL GiST 索引原理 - 1》

spgist索引:

  • 《PostgreSQL gist, spgist索引的原理、差别、应用场景》

  • 《PostgreSQL SP-GiST 索引原理》

  • 《PostgreSQL 黑科技 - 空间聚集存储, 使用gevel插件(增强pageinspect)内窥GIN, GiST, SP-GiST索引》

  • 《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》

  • 《从难缠的模糊查询聊开 - PostgreSQL独门绝招之一 GIN , GiST , SP-GiST , RUM 索引原理与技术背景》

  • 《PostgreSQL SP-GiST GiST 索引选择?》

思考

还有什么业务需要通过本实例提供的方法解决网络延迟的问题.

如果是星链网络, 有没有来源IP, 是不是涉及到3维实时计算离哪个基站最近?

参考

《如何获得IP地址对应的地理信息库, 实现智能DNS解析? 就近路由提升全球化部署业务的访问性能》

《PolarDB for PostgreSQL 采用iprange和SPGiST index 实现超光速 - 全球化业务根据来源IP智能DNS路由就近分配本地机房访问, 提升用户体验》

《聊聊between and的坑 和 神奇的解法 - range + gist - 智能DNS解析》

《PostgreSQL 黑科技 range 类型及 gist index 20x+ speedup than Mysql index combine query》

《PostgreSQL 黑科技 range 类型及 gist index 助力物联网(IoT)》

欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.  
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
Image

文章中的参考文档请点击阅读原文获得. 

KEEP THE THREAD GOING

更多来源文章