Running Hopper Script on the slave Database

[alo]

Hey Friends!

We have been having trouble with our hopper script taking 30 seconds to run and locking the vicidial_list table. I think this is mostly caused because of the way the owner wants to run the campaign.

48 active lists, 62 inactive lists, 21 dialable statuses, Call count limit of 20, and lead recycling. so I understand why its taking so long. but at least there is only 150k dialable leads and not like a million. or a list Mix setup. lol

My question Is... Since the hopper script locks the tables, would it be possible for me to use the slave reporting database to query? If not, any suggestions for speeding the query time?

Dell R620
24 cores 64gb Ram
vicibox 7.0.4
SVN 3192
Raid1 SSDs

Thanks.

[mflorell]

Nope, can't run it on the slave DB.

Try disabling all of the Lead Recycling entries, those tend to cause the most issues as far as query delays.

Also, how many leads are in the active lists of your campaigns?

[williamconley]

running the hopper script on the slave will result in stale data inevitably and then you'll have leads in the hopper that have no business being in there. Hopper table data becomes stale in nanoseconds in some cases, so ... replication WILL be behind.

Check the indexes on the slow queries. You may find a where clause with a missing index. Note that they can't put ALL indexes for all purposes on all systems or there'd be too many indexes for everyone and the system would grind to a halt. So if you have a special situation, you may merely need to add an index or two to bring it back up to speed.

And note that "dialable" has NOTHING to do with the problem. Total leads in the system have to be checked by mysql. If you have millions, consider archiving the leads you don't need (and remember that NO time or table space is saved until afteer Optimize has run after archiving, which is why the archive script invokes optimization after it moves the leads to the archive tables).

Drive space can play a factor as well. If your DB server's HD is over 75%, free up some space. Remember that linux defrags all the time, but this can require space to write the files. Large files like vicidial_list can be problematic in this way. You may also check your HD's smart response for lag in case the HD is getting old.

[alo]

I was hoping to revisit this again.

I would really like to try running the hopper script on the slave database. this campaign has a requirement for some crazy filters thats taking 60 seconds to run the hopper script. Which locks up the vicidial_list table while its running. Easy answer is to change the data management and not use this stupid filter, but I am still working on them to understand that.

I have tried telling the script to only run every other minute or every 3 minutes but its not helpful because the hopper just needs to be bigger in order to do it that way.

Is it possible for me to make some change to the script or invoke it on the slave DB some how?

[jamiemurray]

@williamconley's suggestion of checking for missing indexes and creating them will almost certainly help your case.
I have some very busy clusters with multiple campaigns running complicated filters, over 1 million leads active at any given time across the cluster and yet hopper only takes a few seconds and no user perceivable locks, this was achieved by adding indexes that were missing for us.

[alo]

How might I know what indexes I should add? Its mostly the select part that determines how many dialable leads we have. anyway we can disable that part?

I still would like to try this hopper script on the slave db so it doesn't kill calls for a full minute while its running. Is it possible for me to just run the cron on the slave server or something?

[jamiemurray]

That's the point, with the correct indexes in place, the hopper script should take no more than a few seconds, any longer than that and you're asking for trouble.
How about posting your filter sql here? Perhaps there's a better way to do it that doesn't cause the hopper script to lock up the system.

[alo]

Thank you for your interest in helping. it really means a lot!

Here is the query thats locked running:

Code: Select all

SELECT count(*) FROM vicidial_list FORCE INDEX(list_id) where called_since_last_reset='N' and status IN('DAINB','HUP','DA','PU','CBL','AA','ERI','NEW','PDROP','N','A','SCE','SCM','SCA','CB') and ((list_id IN('801','802','804','803','805','806','807','808','809','810','811'))) and ((gmt_offset_now IN('3.25','3.00','2.75','2.50','2.25','2.00','1.75','1.50','1.25','1.00','0.75','0.50','0.25','0.00','-0.25','-0.50','-0.75','-1.00','-1.25','-1.50','-1.75','-2.00','-2.25','-2.50','-2.75','-3.00','-3.25','-3.50','-3.75','-4.00','-4.25','-4.50','-4.75','-5.00','-5.25','-5.50','-5.75','-6.00','-6.25','-6.50','-6.75','-7.00','-7.25','-7.50','-7.75','-8.00','-8.25','-8.50','-8.75','-9.00','-9.25','-9.50','99') ) ) and (postal_code IN (SELECT zip FROM ziplist2 WHERE zip=postal_code AND  CAST(SUBSTRING(vicidial_list.vendor_lead_code,1,1) as UNSIGNED)>=f1 AND  CAST(SUBSTRING(vicidial_list.vendor_lead_code,3,1) as UNSIGNED)>=f2))  and (called_count < 10);

We have another table with a bunch of zip codes and the filter needs to look at vendor lead code as well.

this makes the hopper run for like 400 seconds which is completely unusable. I wonder if indexes would even help it...