Query Locking the vicidial_log table

[alo]

We have a new query we see locking the vicidial_log table and causing issues.

We have a slave database and have all the reports using it, I am thinking its some automation that checks hourly stats or something but it seems to have just started happening.
I also have been using the archive log script with days=1 to keep the log table as small as possible but its still locking.

Does anyone know whats running this query and why?

SVN 3594


SELECT CONCAT(substr(call_date, 1, 13), ':00:00') as hour_int, count(*), CONCAT(substr(call_date+INTERVAL 1 HOUR, 1, 13), ':00:00') as next_hour from vicidial_log FORCE INDEX (call_date) where campaign_id='CAMPAIGN1' and call_date >= '2023-01-27 09:00:00' and status IN('DROP','XDROP','CALLBK','CBHOLD','DEC','DNC','SALE','NI','NP','XFER','RQXFER','TIMEOT','AFTHRS','NANQUE','PDROP','IVRXFR','SVYCLM','MLINAT','MAXCAL','LRERR','QCFAIL','WN','TEST','CBL','DNCR','DA','FR','OptOUT','UTCB') and user != 'VDAD' group by hour_int, next_hour order by hour_int

and also

SELECT count(*) from vicidial_log FORCE INDEX (call_date) where campaign_id='CAMPAIGN1' and call_date > '2023-01-27 9:9:37' and status IN('DROP','XDROP','CALLBK','CBHOLD','DEC','DNC','SALE','NI','NP','XFER','RQXFER','TIMEOT','AFTHRS','NANQUE','PDROP','IVRXFR','SVYCLM','MLINAT','MAXCAL','LRERR','QCFAIL','WN','TEST','CBL','DNCR','DA','FR')


Thank you!

[mflorell]

That's the "AST_VDadapt.pl" script running those queries. I've never had a problem with large high-volume clients and that script if they are archiving their logs regularly, unless they have inadequate hardware or settings for their DB server.

[alo]

I noticed this happening again recently.

What are those queries for? I am archiving daily, I wonder if their drives aren't fast enough..

[alo]

So it looks like this is for figuring on what to set the dial level to when set on Adapt dial method. if we don't use Adapt is it safe to just disable this from astguiclient.conf?

[mflorell]

The adapt back-end script handles more than just the adapt dial levels, including other stats gathering and handling of other features like Call Quota Lead Ranking, Abandon Check Queue, Inbound Callback Queue and others. You can certainly try disabling it if you like, but it may cause some features you are using not to work.

[basha04]

Try adding the following index and remove the FORCE INDEX (call_date).

FORCE INDEX is deprecated in MySQL and it will be removed in future versions. As far as I know in MariaDB there is no information regarding the force index.

The index it is faster when comparing to = . call_date index in your case it is know as a range index and MySQL/MariaDB uses only one index per table and that index doesn't cover all the needed columns

Code: Select all

alter table vicidial_log add index campai_dt_stat_usr(campaign_id,call_date,status,user);

Let me know if it makes any differences.