Longest slow query I've ever seen

[Vince-0]

Hi Vici forum!

I have this crazy long SQL query popping up in my slow log from what looks like a hopper select from a smaller campaign. This obviously affects all the other campaigns at the time of table lock.
I can tell the campaign from the list_id IN parameters.

I can't figure out why this one is so special that it takes so long to complete but there are so many gmt_offset criteria.
I don't know if that's normal but I don't use any GMT requirements whatsoever that it'd be nice to turn it off completely.

It doesn't seem to happen with any of the other campaigns with way more users and bigger hopper sizes.
There is a filter or two applied, DNC which you can see in the end of the statement - this is also on other campaigns.

Here's the stats:
# Query_time: 34.982444 Lock_time: 0.008343 Rows_sent: 200 Rows_examined: 5868970
Pastebin link: http://pastebin.com/tfaz7g8w

I know the number of rows is ridiculous but I've seen doubly worse.
This query hangs on "Copying to temp table" for that long and all other queries at the time of these ones hang with "waiting for table level lock" to complete after 35 seconds then everything catches up.

Questions:
What's up with all the huge amount of gmt_offset criteria, is it normal? Is there a way to get rid of them?
What's causing this one campaign's hopper select statement to take so long but similar campaigns don't have this same issue?

Any ideas will be greatly appreciated.

Vin.

--
Version:VERSION: 2.12-552a BUILD: 160429-0835 from Vicibox 6.0.3 ISO and upgraded from SVN
HW Specs:
Master DB: Dual Xeon Hex E5630@2.53GHz 32GB RAM LSI MegaRAID SAS-926508i 6x 15k RPM SAS RAID10
Slave DB: Dual Xeon E5420@2.50GHz 16GB RAM LSI MegaRAID SAS 1078 RAID1
Dialer x5: Xeon Quad X5355@2.66GHz 8GB RAM SAS Perc5i RAID1
Web: Dual Xeon Quad E5420@2.50GHz 16GB RAM SAS Perc5i RAID1
Codecs: ulaw on softphones / g729 to carrier.
Termination: VoIP SIP
OS: DB, Web+Dialers OpenSuse 13 from Vicibox 6.0.3

[mflorell]

How many leads are there in the active lists for that campaign?

[Vince-0]

38201 across 10 lists.

[mflorell]

Well, that shouldn't be a problem. What does your my.cnf file look like?

[Vince-0]

skip-external-locking
key_buffer_size = 900M
max_allowed_packet = 16M
table_open_cache = 8192
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 128M
query_cache_size = 0M
thread_cache_size = 16
thread_concurrency = 16
max_connections = 1000
open_files_limit = 24567
max_heap_table_size = 2G
tmp_table_size = 2G
concurrent_insert = 2
performance_schema = off

And replication configs.
I didn't see the disk IO util% shoot up during the query.

[mflorell]

Looks like you have query cache disabled, that is a bad idea. I would recommend setting query_cache_size to "24M" or "64M"

[williamconley]

List all the differences between this campaign and the others. In detail (Hint: "filters applied" is not "in detail").

That query is far too long.

[Vince-0]

I usually leave query cache off because it results in dead calls showing up in the real time screen when there are none but I will enable it and see.

I will go through the campaigns' settings and see what the differences are.

Thanks guys,

[lasthuman]

did you check table integrity?

i recommend you make a repair for table and try again.

[Vince-0]

The database gets repaired regularly.

So basically someone added another 3 statuses, bringing total to 15.
This created a knock-on effect long enough to lock the DB for it to be noticeable on the floor.

I'm still working on the query.

[njujihara]

i have the same problema , you got any solution?

[Vince-0]

We reduced the number of dial statuses in the campaign from 18 to 13 and the query came within acceptable lock times.