[Freeswitch-dev] luasql or freeswitch.dbh

[Leon de Rooij]

Hi Bernhard,

I tried out several ways for connecting to a database from inside  
FreeSWITCH myself before writing the freeswitch.Dbh (which is rather  
simple actually - it only exposes already existing FreeSWITCH  
functionality in mod_lua).

My findings were that caching the connection as is provided by  
FreeSWITCH gives a huge speed increase compared to re-connecting all  
the time as must be done with LuaSQL. Perhaps there are ways to have a  
connection pool in ODBC but I didn't investigate that.


Here's a test with LuaSQL with ODBC driver:
require "luasql.odbc";

local env = assert(luasql.odbc())
local con = assert(env:connect("dsn","user","pass"))
local cur = assert(con:execute ("select 1 as foo, 2 as bar"))

local row = {}
while (cur:fetch(row, "a")) do
   for key, val in pairs(row) do
     stream:write(string.format("%25s : %s\n", key, val))
   end
   stream:write("\n")
end

cur:close()
con:close()
env:close()

freeswitch at fs-dev> hammer 1000 lua test_luasql_odbc.lua
+OK Finished in 38952 ms (min 29 ms, max 107 ms)


The same test with freeswitch.Dbh:

local dbh = assert(freeswitch.Dbh("dsn","user","pass"))

assert(dbh:query("select 1 as foo, 2 as bar", function(row)
   for key, val in pairs(row) do
     stream:write(string.format("%25s : %s\n", key, val))
   end
   stream:write("\n")
end))

assert(dbh:release())

freeswitch at fs-dev> hammer 1000 lua test_lua_dbh.lua
+OK Finished in 2723 ms (min 1 ms, max 25 ms)

As you can see, with these tests the freeswitch.Dbh is about 14 times  
as fast as luasql.odbc. Of course this test is single threaded (the  
scripts are executed serially), so you'd get different results in real- 
world, and if you have 'long' running scripts with many queries on the  
same handle, the results would even out a bit.

(btw mod_hammer is a simple app in contrib/ledr/c that executes  
another app n times and shows how long it took)

I just tried compiling LuaSQL with Postgres driver to do the same  
tests, but that borked. Maybe you can give results of that ? I am  
curious how it behaves.

About having a native driver or odbc: odbc is just an abstraction  
layer which uses the native drivers itself, so I'd reckon it should be  
almost as fast - correct me if I'm wrong ?

I wrote some more about these tests on my blog at http://www.toyos.nl

Kind regards,

Leon


On Sep 16, 2010, at 8:52 PM, Bernhard Suttner wrote:

> Hi,
>
> from performance point of view, what should be used to connect to  
> many different databases, luasql or freeswitch.dbh which does use  
> ODBC?
>
> The problem is, that is not only one database but 3-5 databases  
> which are on 2-3 different hosts. Lua will be used to fetch the  
> directory and dialplan directly from the db. Also live-data will be  
> stored on the different databases. The advantage of freeswitch.dbh  
> would be, that the db connections will be re-used. LUAsql would use  
> the native e.g. postgres or mysql driver.
>
> Thanks for any hint.
>
> Best Regards,
> Bernhard
>
> _______________________________________________
> FreeSWITCH-dev mailing list
> FreeSWITCH-dev at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-dev
> http://www.freeswitch.org

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-dev/attachments/20100928/e27b36d8/attachment.html