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并不合适。
