Call log query time from Sangoma Phone

Commercial Modules
1

Anyone else noticed how long it takes to pull the recent call logs into Sangoma Phone?
We’re now up to ~120 users on it and at nearly any time of the day there will be a queue of queries waiting for call logs.

Here’s the query I see pending:

SELECT 
    *, UNIX_TIMESTAMP(calldate) AS timestamp
FROM
    cdr
WHERE
    (dstchannel LIKE '%/98EXT-%'
        OR dstchannel LIKE '%-EXT@%'
        OR channel LIKE '%/98EXT-%'
        OR src = '98EXT'
        OR dst = '98EXT'
        OR src = 'vmu98EXT'
        OR dst = 'vmu98EXT'
        OR cnum = '98EXT'
        OR cnum = 'vmu98EXT')
ORDER BY timestamp DESC
LIMIT 0 , 250

The prefixed wildcard kills any chance of an index helping here, and without a date restriction, like say the past 6 months, it’s querying, in my case, 20+ million rows every single query to get at most 250 records.

Usually I’ll see times around 40-50 seconds per query.
With some simple changes that can be sped up quite a lot.
Using a 6-month restriction on calldate drops it down to ~9s
I get the reason for the wildcard, to support any tech and endpoint might have, but if you were to just expand that out and add a OR for every tech that the PBX supports (dstchannel LIKE 'PJSIP/98EXT-%' OR dstchannel LIKE 'SIP/98EXT-%' OR dstchannel LIKE 'IAX2/98EXT-%', maybe more), that’ll get me 1/10th of a second.

Obviously these times are specific to me, but with any decently used PBX I can’t see them any better, these are off a dedicated MySQL server separate from the PBX itself.

Any chance of a way to fix this query myself or is this going to have be do a module update?

2

I would get fully up to date on edge module versions if thats possible.
we have made many changes recently and more optimizations are coming soon!
the call log query should be hitting the new transient_cdr table when you are up to date

also with that number of clients I would also make this change which will soon be the default …
fwconsole sa scdswitchovertohaproxy

lastly - if you have licenses you have support , please open tickets when you can on any future issues

** make sure you have a backup **

3

Thanks for the info, good to know some updates coming down the pipe soon. I submitted some feature request tickets related to this and a few other items a few months back.

I’ll have to hold off on switching over to edge on this system, but I’ll keep an eye out for when they push to stable and update ASAP.

What would the inverse be to fwconsole sa scdswitchovertohaproxy ? I don’t see any CLI documentation on that action or anything that sounds similar.
I haven’t noticed any huge issues with performance or anything with the number of clients we’re sitting at but if I know I can quickly convert back with a similar command I’ll give it a go.

4

Did a trial update with all edge modules, not seeing this table created.

Not seeing any new issues, but also no better, and still seeing the same slow query hitting the normal CDR table.

5

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.