Poor performance on Sonata Stats

My vital pbx have almos 2 years now and arround 2000 extensions with lots and lots queues. So even when i try get a single day report from a especific tenant take a lot of time.

I do some mysql check and find this:

select COUNT(*) AS offered_calls, MAX(data3) AS max_callers, event, queuename AS queue from `queues_log` where `event` = 'ENTERQUEUE' and UNIX_TIMESTAMP(time) BETWEEN '1630713600' AND '1630799999' and `queuename` in ('T56_Q630') group by `queuename`

its a tipical stats query… big problem here is this : UNIX_TIMESTAMP(time) BETWEEN … time field on queues_log table is varchar…so Stats code use a function on mysql to convert field to timestamp and compare it with timestamp value. Here the catch: this way mysql cant use time field index and do a table scan.

A much better solution will be:

select COUNT(*) AS offered_calls, MAX(data3) AS max_callers, event, queuename AS queue from `queues_log` where `event` = 'ENTERQUEUE' and time BETWEEN ''2021-09-11 00:00' AND '2021-09-11 23:59' and `queuename` in ('T56_Q630') group by `queuename`

This way field time not on function and index can be used by mysql engine (off course need a time index… a time + queuename index is even better) . Results can be see on screen i attached

2 Likes

Hey @Vitor_Hugo_Costa Welcome to the forums!

Thanks for taking the time to report this issue as well as the potential solution!

In the future please try to use preformatted text when posting logs, blocs of code or MySQL queries. Otherwise, the formatting can get a bit funky… :wink: I went ahead and edited your post to fix that for now.

1 Like

seems like @Vitor_Hugo_Costa put quite some effort into this and the “fix” looks promising.

@miguel you have that topic in mind?

Thanks

Hi @miguel

Have fix request @Vitor_Hugo_Costa

Thanks

This page is updated with all the changes:

https://www.vitalpbx.org/sonata-stats-phone-changes-log/

No, @Vitor_Hugo_Costa asked already what was changed in 1.0.4-6 on telegram.
Changelog only talks about 1.0.4-5.
So changelog might not be up2date.
@miguel

Hi Vital Friends,

I notice Stats a lot faster now, when i select a small date range…sees new stats versions fixed the issue i reported.
I checked some sqls and looks like time conversion functions removed from sql.

Vital team can confirm that? (are no mention about this on Stats change log page…in fact actual Stats is 1.0.7-7 while Change log no mention any new version after 1.0.7-0

1 Like