vicidial.org

I have a Vicidial cluster with 6 dialers, 1 web and 1 database server. Since 2-3 days, I am seeing a problem where almost every time the hopper script runs, the dialing ceases until the hopper script completes. Upon investigation, I found that there are a couple of queries that the script executes out of which one is taking almost 4-5 minutes to complete. I have copied the query into Pastebin for your review:
http://pastebin.com/LK11qg75

The database size was about 25GB due to piled up logs. I cleared it out yesterday and brought it down to 9GB hoping that would resolve this issue but it hasn't.

All servers have the following Vicidial installation:
Vicibox Preload 4.0.3 x64
SVN Version: 2176
DB Schema Version: 1386
VERSION: 2.10-451a
BUILD: 140902-0816

DB Hardware Specs:
2x X5650 @ 2.67 GHz
24GB RAM
4x Samsung 850 Pro SSD in RAID 10 configuration

I have other servers with similar database sizes and in fact slower servers that don't have this problem, the hopper script completes within 1-5 seconds on these other servers. Please help me figure this one out.

How many records are in your vicidial_list table?

Do you have MySQL slow query logging enabled?

About 19 million records in vicidial_list. I know its a lot but this wasn't happening before. Yes, slow query logging is enabled. That's where I pulled that query from.

19 million is too many on almost any system. For your system, you just tipped the scale to using temp tables or swap memory, and that's why your performance fell off a cliff.

We've been through this many times, the only solution is to remove leads from vicidial_list. That's why we created the vicidial_list_archive table, so you can still search old leads, but they won't be in the active leads table.

Thanks. What max lead count do you recommend?

Also, is there a reason the query I posted is so large? Its over 70k characters. Any way to reduce it to make it run faster? I already disabled DNC check and lead/list randomization.

The queries can be really long if you set the call time on a per-list basis I think. If you set them all to use the campaign call time then the hopper queries are smaller.

As for the number of leads, that depends on the hardware, for most high-end systems I recommend keeping them below 10 million.

I just ran the queries without the GMT offset clauses and it finished in 0.48 seconds. The campaign is set to 24-hour dialing. And I can't find anything relevant in the modify list page. Can you tell me exactly what I need to change to remove these clauses?

I really don't know actually, because the code you are running is two and a half years old, and we've done a lot of optimization and bug fixes for high-capacity systems in the last couple years that you wouldn't have in the version you are running.