Skip to Content.
Sympa Menu

freetds - Re: [freetds] Asterisk discussion

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT freetds.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Asterisk discussion
  • Date: Sun, 8 Feb 2009 23:08:13 -0500

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




Archive powered by MHonArc 2.6.24.

Top of Page