[Freeswitch-dev] luasql or freeswitch.dbh
Bernhard Suttner
bernhard.suttner at winet.ch
Tue Sep 28 11:49:14 PDT 2010
Hi,
Great! Thanks a lot for that test. Now I am pretty sure that I will use the freeswitch.dbh technology. Thx!!!
We should find a good page to store this performance test within the wiki.
Best regards,
Bernhard
----- Original Message -----
From: Leon de Rooij [mailto:leon at scarlet-internet.nl]
To: freeswitch-dev at lists.freeswitch.org
Sent: Tue, 28 Sep 2010 20:39:47 +0200
Subject: Re: [Freeswitch-dev] luasql or freeswitch.dbh
> Good evening,
>
> I got the LuaSQL postgres driver working and ran the same example:
>
> require "luasql.postgres";
>
> local env= assert(luasql.postgres())
> local con= assert(env:connect("db", "user","pass","172.31.0.5"))
> local cur= assert(con:execute ("select 1 as foo, 2 as bar"))
>
> local row= {}
> while (cur:fetch(row,"a")) do
> for key, valin pairs(row) do
> stream:write(string.format("%25s : %s\n", key, val))
> end
> stream:write("\n")
> end
>
> cur:close()
> con:close()
> env:close()
>
> And did the same test:
>
> freeswitch at fs-dev> hammer 1000 lua test_luasql_postgres.lua
> +OK Finished in 40625 ms (min 30 ms, max 122 ms)
>
> So it's slowest of the three ! :-) - I didn't investigate any further
> about the cause of this.
>
> I ran it a couple of times with the same result, and it was done on the
> same machine.
>
> (for completeness sake: all tests were done on an Intel Q6600 with two
> KVM guests, one containing an Ubuntu 10.04/64 with Postgres 8.4 and one
> guest with CentOS 5.5/64 with FS Trunk)
>
> -- To answer your other question about whether it blocks:
>
> The FreeSWITCH cache db behaves in a way that it makes a new connection
> if there are no free handles in the pool. It will only block if your db
> server doesn't allow a new connection. In c, you should normally release
> your db handle manually, but Lua has garbage collection, that
> automatically triggers release of any db handles to the pool when they
> go out of scope (for example when your script terminates). If a free
> handle is available in the pool it will be re-used by another thread.
>
> If I understand it right, a free handle in the pool will be disconnected
> from the db server if it is not used for 120 seconds.
>
> Kind regards,
>
> Leon
>
>
>
>
> Op 9/28/2010 4:10 PM, Leon de Rooij schreef:
> > 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, valin 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, valin 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
> >> <mailto: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
> >
> >
> > _______________________________________________
> > 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
>
>
More information about the FreeSWITCH-dev
mailing list