Hopper Script Extremely Slow

[DefLeppard]

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.

[mflorell]

How many records are in your vicidial_list table?

Do you have MySQL slow query logging enabled?

[DefLeppard]

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.

[mflorell]

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.

[DefLeppard]

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.

[mflorell]

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.

[DefLeppard]

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?

[mflorell]

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.