Query Locking the vicidial_log table

All installation and configuration problems and questions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Query Locking the vicidial_log table

Postby alo » Fri Jan 27, 2023 1:14 pm

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!
alo
 
Posts: 191
Joined: Wed Jun 20, 2012 10:21 am

Re: Query Locking the vicidial_log table

Postby mflorell » Fri Jan 27, 2023 5:59 pm

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.
mflorell
Site Admin
 
Posts: 18341
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Query Locking the vicidial_log table

Postby alo » Thu Apr 11, 2024 12:47 am

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
 
Posts: 191
Joined: Wed Jun 20, 2012 10:21 am

Re: Query Locking the vicidial_log table

Postby alo » Thu Apr 11, 2024 10:42 am

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?
alo
 
Posts: 191
Joined: Wed Jun 20, 2012 10:21 am

Re: Query Locking the vicidial_log table

Postby mflorell » Fri Apr 12, 2024 6:34 am

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.
mflorell
Site Admin
 
Posts: 18341
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Query Locking the vicidial_log table

Postby basha04 » Fri Apr 12, 2024 10:25 am

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.
basha04
 
Posts: 14
Joined: Tue Oct 18, 2022 3:14 am
Location: Tirana , Albania


Return to Support

Who is online

Users browsing this forum: Majestic-12 [Bot] and 75 guests