lightdb pllua存储过程实测

　　根据对pl/lua的相关介绍和一些说明如http://www.pgsql.tech/project_305_10000096，其性能相比plpgsql和plsql快不少，那实际到底如何呢？下面拿demo和一些实际的来对比下。

1、lua安装。

　　从https://www.lua.org/download.html下载最新版。

　　因为pllua需要依赖lua.so动态库，所以不能直接make test all（默认只会生成lua.a静态库）。需要修改一下Makefile。参考https://www.jianshu.com/p/91fac87bd6d3即可。

　　系统有可能自带了其他版本的lua，所以PATH和LD_LIBRARY_PATH环境变量需要配置一下。

2、pl/lua插件安装。

　参考https://pllua.github.io/pllua/#S3。

从github下载最新的tag https://github.com/pllua/pllua/tags。

解压，cd到pllua_REL_2_0_12，执行编译。

make PG_CONFIG=/home/lightdb/stage/lightdb-x/bin/lt_config \
     LUA_INCDIR="/usr/local/include" \
     LUALIB="-llua" \
     LUAC="luac" LUA="lua" install

[lightdb@host11 pllua-REL_2_0_12]$ make PG_CONFIG=/home/lightdb/stage/lightdb-x/bin/lt_config      LUA_INCDIR="/usr/local/include"      LUALIB="-llua"      LUAC="luac" LUA="lua" install
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O0 -g3 -D TRACE_SYNCSCAN -D EXEC_NESTLOOPDEBUG -D EXEC_SORTDEBUG -D EXEC_MERGEJOINDEBUG -D DEBUG_DEADLOCK -D DEBUG_TO_FROM_CHAR -D HJDEBUG -D WAL_DEBUG -D LOCK_DEBUG -D HASH_DEBUG -D OPTIMIZER_DEBUG -gdwarf-4 -mcx16 -fPIC -shared -o pllua.so src/compile.o src/datum.o src/elog.o src/error.o src/exec.o src/globals.o src/init.o src/jsonb.o src/numeric.o src/objects.o src/paths.o src/pllua.o src/preload.o src/spi.o src/time.o src/trigger.o src/trusted.o -L/home/lightdb/stage/lightdb-x/lib    -L/home/lightdb/zjh-dev/make/../.local/pmdk/lib -Wl,--as-needed -Wl,-rpath,'/home/lightdb/stage/lightdb-x/lib',--enable-new-dtags  src/compat.o -llua 
/bin/mkdir -p '/home/lightdb/stage/lightdb-x/lib'
/bin/mkdir -p '/home/lightdb/stage/lightdb-x/share/extension'
/bin/mkdir -p '/home/lightdb/stage/lightdb-x/share/extension'
/bin/install -c -m 755  pllua.so '/home/lightdb/stage/lightdb-x/lib/pllua.so'
/bin/install -c -m 644 .//pllua.control .//plluau.control '/home/lightdb/stage/lightdb-x/share/extension/'
/bin/install -c -m 644 .//scripts/pllua--2.0.sql .//scripts/pllua--1.0--2.0.sql .//scripts/plluau--2.0.sql .//scripts/plluau--1.0--2.0.sql  '/home/lightdb/stage/lightdb-x/share/extension/'
/bin/mkdir -p '/home/lightdb/stage/lightdb-x/include/server/extension/pllua/'
/bin/install -c -m 644   .//src/pllua.h .//src/pllua_pgver.h .//src/pllua_luaver.h .//src/pllua_luajit.h '/home/lightdb/stage/lightdb-x/include/server/extension/pllua/'

重启lightdb。

lightdb@postgres=# create extension pllua;
CREATE EXTENSION
lightdb@postgres=# create function hello(person text) returns text language pllua as $$
lightdb@postgres$#   return "Hello, " .. person .. ", from Lua!"
lightdb@postgres$# $$;
CREATE FUNCTION
lightdb@postgres=# select hello('Fred');
         hello          
------------------------
 Hello, Fred, from Lua!
(1 row)
lightdb@postgres=# create or replace function hello_plpgsql(person text) returns text as $$
begin
  return 'Hello, ' || person || ', from Lua!';
end;
$$ language plpgsql;
CREATE FUNCTION
Time: 3.088 ms
lightdb@postgres=# 
lightdb@postgres=# 
lightdb@postgres=# select count(hello_plpgsql('ab' || i)) from generate_series(1,1000000) i;
  count  
---------
 1000000
(1 row)

Time: 3548.604 ms (00:03.549)
lightdb@postgres=# select count(hello('ab' || i)) from generate_series(1,1000000) i;
  count  
---------
 1000000
(1 row)

Time: 20705.291 ms (00:20.705)

可见，对于简单计算的存储过程逻辑，pl/lua没有优势，因为上下文切换开销太大。

再看看计算密集型的逻辑。

lightdb@postgres=# create or replace function f_pl01(cnt int) returns int language plpgsql as $$
lightdb@postgres$# declare
lightdb@postgres$# i int;
lightdb@postgres$# begin
lightdb@postgres$#  i:=0;
lightdb@postgres$#  LOOP
lightdb@postgres$#      i = i + 1;
lightdb@postgres$#      EXIT WHEN i >= cnt;
lightdb@postgres$#  END LOOP;
lightdb@postgres$#  return i;
lightdb@postgres$# end;
lightdb@postgres$# $$;
CREATE FUNCTION
Time: 3.291 ms
lightdb@postgres=# 
lightdb@postgres=# 
lightdb@postgres=# create function f_lua01(cnt int) returns int language pllua as $$
lightdb@postgres$# local i=0
lightdb@postgres$# while( i < cnt ) do
lightdb@postgres$#    i = i+1
lightdb@postgres$# end
lightdb@postgres$# return i
lightdb@postgres$# $$;
CREATE FUNCTION
Time: 2.695 ms

lightdb@postgres=# select f_pl01(10000000);
f_pl01
----------
10000000
(1 row)

Time: 4980.736 ms (00:04.981)
lightdb@postgres=# select f_lua01(10000000);
f_lua01
----------
10000000
(1 row)

Time: 690.045 ms

纯粹的计算密集型逻辑，lua确实比plpgsql快很多。7倍之差了。

假设函数无状态，改成immutable。

lightdb@postgres=# alter function hello(text) immutable;
ALTER FUNCTION
Time: 2.001 ms
lightdb@postgres=# alter function hello_plpgsql(text) immutable;
ALTER FUNCTION
Time: 1.805 ms
lightdb@postgres=# select count(hello('ab' || i)) from generate_series(1,1000000) i;
  count  
---------
 1000000
(1 row)

Time: 16320.034 ms (00:16.320)
lightdb@postgres=# select count(hello_plpgsql('ab' || i)) from generate_series(1,1000000) i;
  count  
---------
 1000000
(1 row)

Time: 3587.384 ms (00:03.587)

可见，lua对此不敏感，反而不如plpgsql。

最后看一下执行sql的效率。

lightdb@postgres=# create table objects (id integer primary key, value text);
CREATE TABLE
Time: 31.956 ms
lightdb@postgres=# 
lightdb@postgres=# create function get_value(id integer) returns text language pllua stable
lightdb@postgres-# as $$
lightdb@postgres$#   local r = q:execute(id)
lightdb@postgres$#   return r and r[1] and r[1].value or 'value not found'
lightdb@postgres$# end
lightdb@postgres$# do -- the part below will be executed once before the first call
lightdb@postgres$#   q = spi.prepare("select value from objects where id=$1")
lightdb@postgres$# $$;
CREATE FUNCTION
Time: 2.918 ms
lightdb@postgres=# select count(get_value(1)) from generate_series(1,100000) i;
 count  
--------
 100000
(1 row)

Time: 9849.911 ms (00:09.850)
-------------------
lightdb@postgres=# CREATE OR REPLACE FUNCTION get_userid(name int) RETURNS text
lightdb@postgres-# AS $$
lightdb@postgres$# DECLARE
lightdb@postgres$# username text;
lightdb@postgres$# BEGIN
lightdb@postgres$#     select value into username from objects where id = name;
lightdb@postgres$# if username is null then
lightdb@postgres$# 

lightdb@postgres$# username = 'value not found';
lightdb@postgres$# end if;
lightdb@postgres$# return username;
lightdb@postgres$# EXCEPTION
lightdb@postgres$#         WHEN NO_DATA_FOUND THEN
lightdb@postgres$# 

lightdb@postgres$# 

lightdb@postgres$# username = 'value not found';
lightdb@postgres$# return username;
lightdb@postgres$# END;
lightdb@postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
Time: 2.720 ms

lightdb@postgres=# select count(get_userid(1)) from generate_series(1,100000) i;
 count  
--------
 100000
(1 row)

Time: 3713.081 ms (00:03.713)

从上可知，访问sql引擎，虽然都是spi。pllua性能远远低于plpgsql。

计算plpgsql在表达式上慢，那加一些初始化看下。如下：

lightdb@postgres=# CREATE OR REPLACE FUNCTION get_userid(name int) RETURNS text
lightdb@postgres-# AS $$
lightdb@postgres$# DECLARE
lightdb@postgres$# username text;
lightdb@postgres$# username1 text:='';
lightdb@postgres$# username2 text:='';
lightdb@postgres$# username3 text:='';
lightdb@postgres$# username4 text:='';
lightdb@postgres$# userid int:=0;
lightdb@postgres$# usernmae text:='abc';
lightdb@postgres$# BEGIN
lightdb@postgres$#     select value into username from objects where id = name;
lightdb@postgres$# if username is null then
lightdb@postgres$# 

lightdb@postgres$# username = 'value not found';
lightdb@postgres$# end if;
lightdb@postgres$# return username;
lightdb@postgres$# EXCEPTION
lightdb@postgres$#         WHEN NO_DATA_FOUND THEN
lightdb@postgres$# 

lightdb@postgres$# 

lightdb@postgres$# username = 'value not found';
lightdb@postgres$# 

lightdb@postgres$# 

lightdb@postgres$# userid = -1;
lightdb@postgres$# 

lightdb@postgres$# 

lightdb@postgres$# usernmae = 'error';
lightdb@postgres$# return username;
lightdb@postgres$# END;
lightdb@postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
Time: 3.263 ms
lightdb@postgres=# select count(get_userid(1)) from generate_series(1,100000) i;
 count  
--------
 100000
(1 row)

Time: 3590.226 ms (00:03.590)

虽然逻辑多了一些，但是性能差距不明显，仍然远远快于pllua。

从上可知，除非是跟数据库无关的计算密集型逻辑，否则使用pllua并不合适。