<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Good evening,<br>
<br>
I got the LuaSQL postgres driver working and ran the same example:<br>
<pre class="lua" style="font-family: monospace;"><span style="color: rgb(177, 177, 0);">require</span> <span style="color: rgb(255, 0, 0);">"luasql.postgres"</span><span style="color: rgb(102, 204, 102);">;</span>
<span style="color: rgb(177, 177, 0);">local</span> env <span style="color: rgb(102, 204, 102);">=</span> <span style="color: rgb(177, 177, 0);">assert</span><span style="color: rgb(102, 204, 102);">(</span>luasql.postgres<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">local</span> con <span style="color: rgb(102, 204, 102);">=</span> <span style="color: rgb(177, 177, 0);">assert</span><span style="color: rgb(102, 204, 102);">(</span>env:connect<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">"db", "user","pass","172.31.0.5"</span><span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">local</span> cur <span style="color: rgb(102, 204, 102);">=</span> <span style="color: rgb(177, 177, 0);">assert</span><span style="color: rgb(102, 204, 102);">(</span>con:<span style="color: rgb(177, 177, 0);">execute</span> <span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">"select 1 as foo, 2 as bar"</span><span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">local</span> row <span style="color: rgb(102, 204, 102);">=</span> <span style="color: rgb(102, 204, 102);">{</span><span style="color: rgb(102, 204, 102);">}</span>
<span style="color: rgb(177, 177, 0);">while</span> <span style="color: rgb(102, 204, 102);">(</span>cur:fetch<span style="color: rgb(102, 204, 102);">(</span>row, <span style="color: rgb(255, 0, 0);">"a"</span><span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span> <span style="color: rgb(177, 177, 0);">do</span>
<span style="color: rgb(177, 177, 0);">for</span> key, val <span style="color: rgb(177, 177, 0);">in</span> <span style="color: rgb(177, 177, 0);">pairs</span><span style="color: rgb(102, 204, 102);">(</span>row<span style="color: rgb(102, 204, 102);">)</span> <span style="color: rgb(177, 177, 0);">do</span>
stream:<span style="color: rgb(177, 177, 0);">write</span><span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(177, 177, 0);">string.format</span><span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">"%25s : %s<span style="color: rgb(0, 0, 153); font-weight: bold;">\n</span>"</span>, key, val<span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">end</span>
stream:<span style="color: rgb(177, 177, 0);">write</span><span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">"<span style="color: rgb(0, 0, 153); font-weight: bold;">\n</span>"</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">end</span>
cur:close<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(102, 204, 102);">)</span>
con:close<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(102, 204, 102);">)</span>
env:close<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(102, 204, 102);">)</span></pre>
And did the same test:<br>
<pre>freeswitch@fs-dev> hammer 1000 lua test_luasql_postgres.lua
+OK Finished in 40625 ms (min 30 ms, max 122 ms)</pre>
So it's slowest of the three ! :-) - I didn't investigate any
further about the cause of this.<br>
<br>
I ran it a couple of times with the same result, and it was done on
the same machine.<br>
<br>
(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)<br>
<br>
-- To answer your other question about whether it blocks:<br>
<br>
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. <br>
<br>
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.<br>
<br>
Kind regards,<br>
<br>
Leon<br>
<br>
<br>
<br>
<br>
Op 9/28/2010 4:10 PM, Leon de Rooij schreef:
<blockquote
cite="mid:ABD8ED07-B3F2-48CB-8E39-7FDA797D915E@scarlet-internet.nl"
type="cite">Hi Bernhard,
<div><br>
</div>
<div>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).</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div><br>
</div>
<div>Here's a test with LuaSQL with ODBC driver:</div>
<div>
<div class="wp_syntax">
<div class="code">
<pre class="lua" style="font-family: monospace;"><span style="color: rgb(177, 177, 0);">require</span> <span style="color: rgb(255, 0, 0);">"luasql.odbc"</span><span style="color: rgb(102, 204, 102);">;</span>
<span style="color: rgb(177, 177, 0);">local</span> env <span style="color: rgb(102, 204, 102);">=</span> <span style="color: rgb(177, 177, 0);">assert</span><span style="color: rgb(102, 204, 102);">(</span>luasql.odbc<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">local</span> con <span style="color: rgb(102, 204, 102);">=</span> <span style="color: rgb(177, 177, 0);">assert</span><span style="color: rgb(102, 204, 102);">(</span>env:connect<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">"dsn","user","pass"</span><span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">local</span> cur <span style="color: rgb(102, 204, 102);">=</span> <span style="color: rgb(177, 177, 0);">assert</span><span style="color: rgb(102, 204, 102);">(</span>con:<span style="color: rgb(177, 177, 0);">execute</span> <span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">"select 1 as foo, 2 as bar"</span><span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">local</span> row <span style="color: rgb(102, 204, 102);">=</span> <span style="color: rgb(102, 204, 102);">{</span><span style="color: rgb(102, 204, 102);">}</span>
<span style="color: rgb(177, 177, 0);">while</span> <span style="color: rgb(102, 204, 102);">(</span>cur:fetch<span style="color: rgb(102, 204, 102);">(</span>row, <span style="color: rgb(255, 0, 0);">"a"</span><span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span> <span style="color: rgb(177, 177, 0);">do</span>
<span style="color: rgb(177, 177, 0);">for</span> key, val <span style="color: rgb(177, 177, 0);">in</span> <span style="color: rgb(177, 177, 0);">pairs</span><span style="color: rgb(102, 204, 102);">(</span>row<span style="color: rgb(102, 204, 102);">)</span> <span style="color: rgb(177, 177, 0);">do</span>
stream:<span style="color: rgb(177, 177, 0);">write</span><span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(177, 177, 0);">string.format</span><span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">"%25s : %s<span style="color: rgb(0, 0, 153); font-weight: bold;">\n</span>"</span>, key, val<span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">end</span>
stream:<span style="color: rgb(177, 177, 0);">write</span><span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">"<span style="color: rgb(0, 0, 153); font-weight: bold;">\n</span>"</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">end</span>
cur:close<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(102, 204, 102);">)</span>
con:close<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(102, 204, 102);">)</span>
env:close<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(102, 204, 102);">)</span></pre>
<pre class="lua" style="font-family: monospace;">
</pre>
<pre class="lua" style="font-family: monospace;">freeswitch@fs-dev> hammer 1000 lua test_luasql_odbc.lua</pre>
<pre class="lua" style="font-family: monospace;">+OK Finished in 38952 ms (min 29 ms, max 107 ms)</pre>
</div>
</div>
</div>
<div>
<div>
<div><br>
</div>
<div><br>
</div>
<div>The same test with freeswitch.Dbh:</div>
<div><br>
</div>
<div>
<pre class="lua" style="font-family: monospace;"><span style="color: rgb(177, 177, 0);">local</span> dbh <span style="color: rgb(102, 204, 102);">=</span> <span style="color: rgb(177, 177, 0);">assert</span><span style="color: rgb(102, 204, 102);">(</span>freeswitch.Dbh<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">"dsn"</span>,<span style="color: rgb(255, 0, 0);">"user"</span>,<span style="color: rgb(255, 0, 0);">"pass"</span><span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">assert</span><span style="color: rgb(102, 204, 102);">(</span>dbh:query<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">"select 1 as foo, 2 as bar"</span>, <span style="color: rgb(177, 177, 0);">function</span><span style="color: rgb(102, 204, 102);">(</span>row<span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">for</span> key, val <span style="color: rgb(177, 177, 0);">in</span> <span style="color: rgb(177, 177, 0);">pairs</span><span style="color: rgb(102, 204, 102);">(</span>row<span style="color: rgb(102, 204, 102);">)</span> <span style="color: rgb(177, 177, 0);">do</span>
stream:<span style="color: rgb(177, 177, 0);">write</span><span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(177, 177, 0);">string.format</span><span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">"%25s : %s<span style="color: rgb(0, 0, 153); font-weight: bold;">\n</span>"</span>, key, val<span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">end</span>
stream:<span style="color: rgb(177, 177, 0);">write</span><span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">"<span style="color: rgb(0, 0, 153); font-weight: bold;">\n</span>"</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">end</span><span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span>
<span style="color: rgb(177, 177, 0);">assert</span><span style="color: rgb(102, 204, 102);">(</span>dbh:release<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">)</span></pre>
</div>
<div><br>
</div>
<div>
<pre>freeswitch@fs-dev> hammer 1000 lua test_lua_dbh.lua
+OK Finished in 2723 ms (min 1 ms, max 25 ms)
</pre>
</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>(btw mod_hammer is a simple app in contrib/ledr/c that
executes another app n times and shows how long it took)</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>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 ?</div>
<div><br>
</div>
<div>I wrote some more about these tests on my blog at <a
moz-do-not-send="true" href="http://www.toyos.nl">http://www.toyos.nl</a></div>
<div><br>
</div>
<div>Kind regards,</div>
<div><br>
</div>
<div>Leon</div>
<div><br>
</div>
<div><br>
</div>
<div>On Sep 16, 2010, at 8:52 PM, Bernhard Suttner wrote:</div>
<br class="Apple-interchange-newline">
<blockquote type="cite">
<div>Hi,<br>
<br>
from performance point of view, what should be used to
connect to many different databases, luasql or
freeswitch.dbh which does use ODBC?<br>
<br>
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.<br>
<br>
Thanks for any hint. <br>
<br>
Best Regards,<br>
Bernhard<br>
<br>
_______________________________________________<br>
FreeSWITCH-dev mailing list<br>
<a moz-do-not-send="true"
href="mailto:FreeSWITCH-dev@lists.freeswitch.org">FreeSWITCH-dev@lists.freeswitch.org</a><br>
<a class="moz-txt-link-freetext" href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev">http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev</a><br>
UNSUBSCRIBE:<a class="moz-txt-link-freetext" href="http://lists.freeswitch.org/mailman/options/freeswitch-dev">http://lists.freeswitch.org/mailman/options/freeswitch-dev</a><br>
<a class="moz-txt-link-freetext" href="http://www.freeswitch.org">http://www.freeswitch.org</a><br>
</div>
</blockquote>
</div>
<br>
</div>
<pre wrap="">
<fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
FreeSWITCH-dev mailing list
<a class="moz-txt-link-abbreviated" href="mailto:FreeSWITCH-dev@lists.freeswitch.org">FreeSWITCH-dev@lists.freeswitch.org</a>
<a class="moz-txt-link-freetext" href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev">http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev</a>
UNSUBSCRIBE:<a class="moz-txt-link-freetext" href="http://lists.freeswitch.org/mailman/options/freeswitch-dev">http://lists.freeswitch.org/mailman/options/freeswitch-dev</a>
<a class="moz-txt-link-freetext" href="http://www.freeswitch.org">http://www.freeswitch.org</a>
</pre>
</blockquote>
<br>
</body>
</html>