FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

Federico Alves wrote:
> using Perl I can open a connection, execute a query, close it and thus I
> can have dozens, possibly hundreds of simultaneous queries to the SQL.
> Using Asterisk's ODBC sharing mechanism, it chokes to death and after a
> few queries per second, Asterisk starts to slow down and "constipate",
> many queries are queued and just a few are executed. 

If you turn on TDSDUMP with debug flags 0x7fff, the log will include the
pid and time on every line. Use awk to grab the first columns, load the
whole thing in to a table, and look for lags. That might shed some light. 


I'm willing to provide any technical information the Asterisk folks need. 
I don't know anything about their project, but that doesn't really matter;
to me, it's just another application.  

One possible stumbling block for would-be "any database will do" systems
is not taking into account TDS's (the protocol, not the library)
one-query-at-a-time restriction.  If you have hundreds of threads sharing
a tenth that number of database connections, it's easy to imagine a
bottleneck if the results of each connnection aren't immediately
processed.  

Tilghman says:
> you're
> running multiple Perl instances, which means that you aren't testing
> FreeTDS for concurrent queries, the way Asterisk runs.  

http://msdn.microsoft.com/en-us/library/ms715361(VS.85).aspx

I'm not sure what's meant by "concurrent queries".  From FreeTDS's point
of view, multiple threads accessing a handle is no different from one, is
no different from many processes each using its own handle.  It's all one.
 There's no lock, no resource contention/management, no mutex anywhere in
the ODBC driver.  Mutexes are used in db-lib to guard global lists, and in
logging, but nowhere in query processing:

C symbol: TDS_MUTEX_LOCK

  File        Function              Line
0 tdsthread.h <global>                32 #define TDS_MUTEX_LOCK(a)
                                         pthread_mutex_lock(a)
1 tdsthread.h <global>                47 #define TDS_MUTEX_LOCK(a)
2 dblib.c     dblib_get_tds_ctx      272 TDS_MUTEX_LOCK(&dblib_mutex);
3 dblib.c     dblib_release_tds_ctx  304 TDS_MUTEX_LOCK(&dblib_mutex);
4 dblib.c     dbinit                 650 TDS_MUTEX_LOCK(&dblib_mutex);
5 dblib.c     tdsdbopen             1136 TDS_MUTEX_LOCK(&dblib_mutex);
6 dblib.c     tdsdbopen             1160 TDS_MUTEX_LOCK(&dblib_mutex);
7 dblib.c     tdsdbopen             1167 TDS_MUTEX_LOCK(&dblib_mutex);
8 dblib.c     dbclose               1385 TDS_MUTEX_LOCK(&dblib_mutex);
9 dblib.c     dbexit                1448 TDS_MUTEX_LOCK(&dblib_mutex);
a dblib.c     dbsetmaxprocs         3812 TDS_MUTEX_LOCK(&dblib_mutex);
b dblib.c     dbgetmaxprocs         3881 TDS_MUTEX_LOCK(&dblib_mutex);
c dblib.c     dbsettime             3902 TDS_MUTEX_LOCK(&dblib_mutex);
d dblib.c     dbsetlogintime        3943 TDS_MUTEX_LOCK(&dblib_mutex);
e dblib.c     dbrecftos             6470 TDS_MUTEX_LOCK(&dblib_mutex);
f log.c       tdsdump_off             95 TDS_MUTEX_LOCK(&g_dump_mutex);
g log.c       tdsdump_on             107 TDS_MUTEX_LOCK(&g_dump_mutex);
h log.c       tdsdump_open           132 TDS_MUTEX_LOCK(&g_dump_mutex);
i log.c       tdsdump_close          223 TDS_MUTEX_LOCK(&g_dump_mutex);
j log.c       tdsdump_dump_buf       296 TDS_MUTEX_LOCK(&g_dump_mutex);
k log.c       tdsdump_log            389 TDS_MUTEX_LOCK(&g_dump_mutex);

But remember: no matter how many threads you have, you get *one* query per
connection at a time.  

If the contention is that multithreading per se is a problem for FreeTDS,
it's easily tested, just a SMP.  I myself don't do much multithreaded ODBC
work, but surely it's not hard to devise a test harness to compare the
throughput of threads versus proceses.  

HTH.  

--jkl