PostgreSQL PostGIS so 预加载(preload) 性能提升 - 暨什么动态库建议预加载

1 minute read

背景

PostgreSQL是一个可扩展的数据库,如果你的扩展是C扩展,代码在动态库中。那么在使用时需要加载这个动态库来实现一些功能例如创建类型,创建函数,创建操作符等。

PostgreSQL允许多种加载动态库的方法,例如数据库启动时加载,会话连接(启动backend_process)时加载,或者在会话中调用到对应的动态库时再加载。

《PostgreSQL 加载动态库详解》

那么到底选择哪种加载方法呢?

1、数据库启动时加载,所有会话共享,对于常用的动态库(每个会话都会调用到的),建议使用这种方式加载。

2、会话启动或即用即加载的方法,不共享,并且每次加载需要一定的时间,每个会话需要本地存储一份库(更消耗内存一点)。对于不常用的动态库,可以使用这种方法。

PostGIS是PG的一个空间数据库扩展,同样也面临以上的选择,到底怎么选呢?根据上面的建议来。

下面给出一个例子

预加载带来的好处

如果我们设置了预加载,那么在数据库启动时,会自动加载以下动态库

postgres=# show shared_preload_libraries ;  
                         shared_preload_libraries                           
--------------------------------------------------------------------------  
 pg_stat_statements,postgis-2.4.so,postgis_topology-2.4.so  
(1 row)  

第一次执行的时间,与第二次执行的时间相仿。没有太大差异。

执行PostGIS的空间函数或类型等,不需要加载动态库。

postgres=# explain analyze select * from car where rest_sites=sites  order by pos<->gen_pos() for update limit 1;  
                                                               QUERY PLAN                                                                 
----------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=0.41..1.45 rows=1 width=94) (actual time=0.276..0.276 rows=1 loops=1)  
   ->  LockRows  (cost=0.41..50620.34 rows=48776 width=94) (actual time=0.274..0.274 rows=1 loops=1)  
         ->  Index Scan using idx_car_pos_2 on car  (cost=0.41..50132.58 rows=48776 width=94) (actual time=0.250..0.250 rows=1 loops=1)  
               Order By: (pos <-> '0101000020E610000032569BFF57EE5C4062670A9DD7203940'::geometry)  
               Filter: (rest_sites = sites)  
 Planning time: 1.365 ms  
 Execution time: 0.340 ms  
(7 rows)  
  
postgres=# explain analyze select * from car where rest_sites=sites  order by pos<->gen_pos() for update limit 1;  
                                                               QUERY PLAN                                                                 
----------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=0.41..1.45 rows=1 width=94) (actual time=0.162..0.162 rows=1 loops=1)  
   ->  LockRows  (cost=0.41..50620.34 rows=48776 width=94) (actual time=0.161..0.161 rows=1 loops=1)  
         ->  Index Scan using idx_car_pos_2 on car  (cost=0.41..50132.58 rows=48776 width=94) (actual time=0.152..0.152 rows=1 loops=1)  
               Order By: (pos <-> '0101000020E61000003B71395E81905C404A404CC285383B40'::geometry)  
               Filter: (rest_sites = sites)  
 Planning time: 0.314 ms  
 Execution time: 0.189 ms  
(7 rows)  

而如果我们不使用postgis预加载,那么性能又会怎么样呢?

第一次和第二次相差很大,需要加载动态库。

postgres=# show shared_preload_libraries ;  
                         shared_preload_libraries                           
--------------------------------------------------------------------------  
 pg_stat_statements  
(1 row)  
postgres=# explain analyze select * from car where rest_sites=sites  order by pos<->gen_pos() for update limit 1;  
                                                               QUERY PLAN                                                                 
----------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=0.41..1.45 rows=1 width=94) (actual time=0.271..0.271 rows=1 loops=1)  
   ->  LockRows  (cost=0.41..50620.34 rows=48776 width=94) (actual time=0.268..0.268 rows=1 loops=1)  
         ->  Index Scan using idx_car_pos_2 on car  (cost=0.41..50132.58 rows=48776 width=94) (actual time=0.241..0.241 rows=1 loops=1)  
               Order By: (pos <-> '0101000020E6100000E61E12BEF7775D404DF910548DEA3940'::geometry)  
               Filter: (rest_sites = sites)  
 Planning time: 52.728 ms  
 Execution time: 0.346 ms  
(7 rows)  
  
postgres=# explain analyze select * from car where rest_sites=sites  order by pos<->gen_pos() for update limit 1;  
                                                               QUERY PLAN                                                                 
----------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=0.41..1.45 rows=1 width=94) (actual time=0.174..0.174 rows=1 loops=1)  
   ->  LockRows  (cost=0.41..50620.34 rows=48776 width=94) (actual time=0.173..0.173 rows=1 loops=1)  
         ->  Index Scan using idx_car_pos_2 on car  (cost=0.41..50132.58 rows=48776 width=94) (actual time=0.161..0.161 rows=1 loops=1)  
               Order By: (pos <-> '0101000020E6100000F986C267EBBA5C4012FB04508CEC3940'::geometry)  
               Filter: (rest_sites = sites)  
 Planning time: 0.266 ms  
 Execution time: 0.199 ms  
(7 rows)  

参考

为了提高postgis插件的装载速度,应该将so文件配置为数据库启动时自动加载。

使用数据库启动时自动加载,还有一个好处,内存使用量也大大减少。

对于短连接业务,提升特别明显。因为短连接用户,每次发起新的连接后,第一次查询PLAN都会更慢,需要加载lib库。

小结

《如何加快PostgreSQL结巴分词pg_jieba加载速度》

《PostgreSQL 加载动态库详解》

Flag Counter

digoal’s 大量PostgreSQL文章入口