[asterisk-dev] another odbc voicemail performance issue

[Ryan Mitchell]

With thousands of SIP clients each with a mailbox, voicemail code is doing a
ridiculous quatity of "SELECT COUNT(*) FROM %s WHERE dir=?" to support MWI
on each box.

Not ridiculous in that voicemail needs to know how many messages each box
has, but just to point out that this can big a bottleneck with a large
number of subscribers.  I don't see an easy fix.  Maybe instead of a
thousand individual queries you can periodically -- say once every 20 sec --
scan the entire table, cache the results and do the accounting in-memory.
Or periodically do a "select count(*),dir,.. from %s GROPU BY dir" query.

Then have some caching optimizations so a user can get MWI notice as soon as
a new message is left, in case you didn't want to wait up to 20 sec or
whatever.  That can get messy though.  Ideally there's a model that would be
relevant for IMAP and file storage as well, already there are almost 3
implementations of voicemail in app_voicemail.c.

--Ryan


On 10/25/07, Tilghman Lesher <tilghman at mail.jeffandtilghman.com> wrote:
>
> On Thursday 25 October 2007 06:06:37 Tobias Engel wrote:
> > When there are about 100 parallel connections to asterisk (1.4.11, btw),
> > all leaving voicemails, the peformance becomes really abysmal:
> > Connections take over a minute before they are even accepted, etc.
> >
> > Since this does not happen when just using realtime for the voicemail
> > config, but storing the messages itself on the local harddisk, I assumed
> > some db (Oracle) or ODBC bottleneck (the CPU usage never goes over 20%).
> > But a few hundred added debug statements and some tcpdump sessions
> > later, to my surprise I found out that most of the time is spent waiting
> > for a lock on the "users" list in "find_user".
>
> We could probably fix this by switching over to using rwlocks.
>
> > So far I couldn't find out why this only happens with ODBC message
> > storage, but since this users list is - as far as I can see from the
> > source - unused when using realtime, do I even need this lock?
>
> You do, to avoid a possible race condition with using the data versus
> the data being destroyed on a reload.
>
> > When the user data is fetched from the db, the operation should be
> > atomic - either it's there or it's not there, right?
>
> It is, but the data transfer is not atomic -- it takes time to process
> each
> column.
>
> > So do you think it is safe to remove the AST_LIST_LOCK call for realtime
> > usage?
>
> Definitely not.  But as above, you could convert it to rwlocks.  You'd
> just
> have to make sure that there are no recursive locks, because rwlocks do
> not support recursive locks.
>
> > And does anybody have any insights on these performance issues? (I also
> > found out that it takes sometimes up to 6 seconds from the SQLExecute
> > call in res_odbc until the call is really forwarded to the database -
> > but I assume this is an ODBC issue)
>
> It's probably another lock that is not exposed to the interface.  You
> might
> post a note to the UnixODBC list, looking for an answer to that.
>
> --
> Tilghman
>
> _______________________________________________
> --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
>



-- 
Ryan Mitchell <rjm at tcl.net>
Telecom Logic, LLC
http://www.tcl.net
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digium.com/pipermail/asterisk-dev/attachments/20071025/21830376/attachment.htm