i checking i little more on Vital Mysql guts… cdr table have a lot index…on my case are arround 6 millions rows. 2 indexs have only one field and a very very low cardinality…so index cant be usefull.
are some others indexs maybe never used too like “duration”.
Vital ppl can check it ?
How does this affect you?
What are your recommendation about it?
I dont have evidence if this index affect my operation. Just see that , and believe is possible change it can reduce write time and storage site. I Will enable slow query log on my server and share…so we can check it.
Please, check my other post about Sonata Stats and queue_log…this one for sure have a big impact to my customers
# Time: 210917 17:06:00
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 79 Schema: ombutel QC_hit: No
# Query_time: 65.749052 Lock_time: 0.000202 Rows_sent: 17 Rows_examined: 6706776
# Rows_affected: 0 Bytes_sent: 763
use ombutel;
SET timestamp=1631909160;
select
`uniqueid`,
SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
from asterisk.cdr
where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);
# Time: 210917 17:06:21
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 234 Schema: ombutel QC_hit: No
# Query_time: 26.114853 Lock_time: 0.000152 Rows_sent: 17 Rows_examined: 6706827
# Rows_affected: 0 Bytes_sent: 764
SET timestamp=1631909181;
select
`uniqueid`,
SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
from asterisk.cdr
where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);
# Time: 210917 17:07:32
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 250 Schema: ombutel QC_hit: No
# Query_time: 37.346858 Lock_time: 0.000191 Rows_sent: 17 Rows_examined: 6706868
# Rows_affected: 0 Bytes_sent: 764
SET timestamp=1631909252;
select
`uniqueid`,
SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
from asterisk.cdr
where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);
# Time: 210917 17:08:23
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 323 Schema: ombutel QC_hit: No
# Query_time: 28.700613 Lock_time: 0.000198 Rows_sent: 17 Rows_examined: 6706909
# Rows_affected: 0 Bytes_sent: 764
SET timestamp=1631909303;
select
`uniqueid`,
SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
from asterisk.cdr
where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);
# Time: 210917 17:09:23
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 448 Schema: ombutel QC_hit: No
# Query_time: 27.842713 Lock_time: 0.000125 Rows_sent: 17 Rows_examined: 6706956
# Rows_affected: 0 Bytes_sent: 764
SET timestamp=1631909363;
select
`uniqueid`,
SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
from asterisk.cdr
where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);
# Time: 210917 17:10:11
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 474 Schema: ombutel QC_hit: No
# Query_time: 16.071432 Lock_time: 0.000203 Rows_sent: 17 Rows_examined: 6707003
# Rows_affected: 0 Bytes_sent: 764
SET timestamp=1631909411;
select
`uniqueid`,
SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
from asterisk.cdr
where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);
# Time: 210917 17:11:04
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 588 Schema: ombutel QC_hit: No
# Query_time: 8.817127 Lock_time: 0.000224 Rows_sent: 17 Rows_examined: 6707051
# Rows_affected: 0 Bytes_sent: 764
SET timestamp=1631909464;
select
`uniqueid`,
SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
from asterisk.cdr
where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);
Slow query log show lots this querys taking 26 to 65 s each… all do table scan
Seens this slow querys come from vital dashboard. “explain” show this query dont use any index. Query do a where with uniqueid… so I create a uniqueid index and now are no more slow querys
Yes, in the latest version!
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.