[asterisk-users] Realtime mysql extensions mutiple queries for each priority?

[Ishfaq Malik]

JR Richardson wrote:
>> On Monday 28 December 2009 23:49:13 Tilghman Lesher wrote:
>>     
>>> On Monday 28 December 2009 18:09:15 JR Richardson wrote:
>>>       
>>>> I turned on console debug to see the actual mysql queries and to my
>>>> surprise and concern, I see every query for an extension priority
>>>> repeated 3 or more times prior to dialplan execution.  For instance my
>>>> first dialplan activity is all extracted from the database:
>>>>
>>>> context             exten   pri     app     appdata
>>>> dpdefault14 _991X   1       NoOp    INBOUND CALL FROM SIPP
>>>> dpdefault14 _991X   2       NoOp    TRUNK-${EXTEN:0:2} DID-${EXTEN:2}
>>>> dpdefault14 _991X   3       Set     CALLERID(number)=600
>>>> dpdefault14 _991X   4       Answer
>>>> dpdefault14 _991X   5       Goto    ${EXTEN:2}|1
>>>>
>>>> Each priority is queried several times before executing.  Here is a
>>>> sample of the first 2 priorities on a pastebin:
>>>>
>>>> http://pastebin.com/m54c9c41e
>>>>
>>>> I would not think this is normal activity as I can query the database
>>>> directly once and get a valid response.  I don't have any realtime
>>>> mysql connections issues that I can see, no errors in the logs and
>>>> console status is:
>>>>         
>>> No, that's normal.  The order of queries done is 1) check if the extension
>>> exists, 2) on spawn, retrieve the extension to populate information about
>>> the application into the channel structure, and 3) actually execute the
>>> application.  There are 3 queries done for each extension actually executed
>>> in order of priorities and a few more when the extension changes (or
>>> originates).  It's not optimal, but it's the way that it works.
>>>
>>> At some point, a slight optimization could certainly be done to narrow this
>>> down to a single query from the database, followed by a fairly short
>>> caching period (1 second would be plenty), but that optimization has never
>>> been done.
>>>       
>> https://issues.asterisk.org/view.php?id=16521
>>
>> Needs testing.
>>
>> --
>> Tilghman Lesher
>>     
>
> Tilghman,
>
> Saying I’m a bit excited right now is an understatement. First of all,
> the patch seems to work fine applied to 1.4.28 stable release. The
> performance of this patch is extraordinary. Before migrating my static
> dialplan to the database I could push 380 calls at 15 to 20 CPS. After
> migrating to the database, I could only push a little more than 100
> calls and no more than 6 to 9 CPS. With this patch applied, I am
> pushing reliably 300 calls at 15 CPS. 7500+ calls without a hiccup.
> Not quite as good as a static dialplan, but that is expected. MySQLd
> has also decreased utilization, as expected, from 6 to 12, now 1 to 6.
> This has got to be an overall performance increase by 50% or more.
>
> I will be patching on my new 1.4 systems going forward. The sooner
> this patch gets applied to Asterisk, the better.
>
> Thanks.
>
> JR
>   
Looks like I'll be giving it a go in week or so then!

I'll let you know how I get on too.

-- 
Ishfaq Malik
Software Developer
PackNet Ltd

Office:   0161 660 3062