[asterisk-dev] 1.4 and CDRs -- The Breaking Point

[Venefax]

Response from James K. Lowden, from Freetds
> 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.  

James K. Lowden

-----Original Message-----
From: asterisk-dev-bounces at lists.digium.com
[mailto:asterisk-dev-bounces at lists.digium.com] On Behalf Of Sergey Okhapkin
Sent: Sunday, February 08, 2009 11:28 AM
To: asterisk-dev at lists.digium.com
Cc: Tilghman Lesher
Subject: Re: [asterisk-dev] 1.4 and CDRs -- The Breaking Point

On Sunday 08 February 2009, Tilghman Lesher wrote:
> > I have the same problem with mysql. I use func_odbc (backport form 1.6)
> > in dialplan and asterisk chokes when call rate reaches 20-25 calls per
> > second. CPU usage on quad core mysql box never exceeds 100%, seems like
> > queries on pooled connections are executed sequentially but not in
> > parallel.
>
> How are you backporting?  Are you using the backport on svncommunity, or
> did you roll your own? 

I use svncommunity backport.

> Are you using the pooling feature to request 
> multiple connections, or are you using a single shared connection? 

Connection pool is turned on in res_odbc.conf:

[a2billing]
enabled => yes
dsn => a2billing
username => xxxxxx
password => xxxxxx
pre-connect => yes
pooling => yes
limit => 50
idlecheck => 3600

> Also, what is it about the backport that made you choose to use it, rather
> than the func_odbc that is in 1.4?

I need separate read and write handles to run selects on local mysql slave
and 
send updates to mysql master. Also I use multiple read and write handles for

failover, these features are missed in 1.4.

_______________________________________________
--Bandwidth and Colocation Provided by http://www.api-digital.com--

asterisk-dev mailing list
To UNSUBSCRIBE or update options visit:
   http://lists.digium.com/mailman/listinfo/asterisk-dev