list loading-loads up the DB

[Iz3k34l]

ok so there is a particular query that slows down and ultimately locks up the database;

Code: Select all

SELECT lead_id,list_id,gmt_offset_now,phone_number,state,status,modify_date,user,vendor_lead_code FROM vicidial_list where ... and (last_local_call_time < "2014-11-04 12:16:33") ) or ( (gmt_offset_now='-3.00') and (last_local_call_time < "2014-11-04 12:01:33") ) or ( (gmt_offset_now='-3.25') and (last_local_call_time < "2014-11-04 11:46:33") ) or ( (gmt_offset_now='-3.50') and (last_local_call_time < "2014-11-04 11:31:33") ) or ( (gmt_offset_now='-3.75') and (last_local_call_time < "2014-11-04 11:16:33") ) or ( (gmt_offset_now='-4.00') and (last_local_call_time < "2014-11-04 11:01:33") ) or ( (gmt_offset_now='-4.25') and (last_local_call_time < "2014-11-04 10:46:33") ) or ( (gmt_offset_now='-4.50') and (last_local_call_time < "2014-11-04 10:31:33") ) or ( (gmt_offset_now='-4.75') and (last_local_call_time < "2014-11-04 10:16:33") ) or ( (gmt_offset_now='-5.00') and (last_local_call_time < "2014-11-04 10:01:33") ) or ( (gmt_offset_now='-5.25') and (last_local_call_time < "2014-11-04 09:46:33") ) or ( (gmt_offset_now='-5.50') and (last_local_call_time < "2014-11-04 09:31:33") ) or ( (gmt_offset_now='-5.75') and ...,'15244847','15244772','15244755','15242997','15242941','0')  and ((gmt_offset_now IN('13.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','-9.75','-10.00','-10.25','-10.50','-10.75','-11.00','99') ) ) and ( phone_number not in (select phone_number from asterisk.vicidial_dnc) ) and (called_count < 7)  order by lead_id asc limit 500;


Aside from the fact that this query is huge and devours resources when it runs, there are no active campaigns that have any sort of last call time sorting, up or down. I have changed the campaigns to use "Down" for the list order and the above query is running. however when i change the campaigns to actually use down last call count, the DB doesn't lockup.

I was changing this setting to help reduce server load. I cant dump the vicidial_list table. Thanks for any suggestion

[mcargile]

That query actually does not use much load on a properly sized database. It normally finishes in less than a second. How many lead do you have in your database and what sort of a Raid card is in the database server?

[Iz3k34l]

We have just over 14 million records on the vicidial_list table. we have a RAID 10 on SSD

[mcargile]

What kind of Raid card are you using in your database? You could have a RAID 10 setup on a built in motherboard RAID which will perform horrible when compared to an LSI MegaRAID card. Even cards made by the same manufacturer can have wildly different performance characteristics on different load types. For instance LSI MegaRAID vs LSI 3ware. The 3ware card is designed for an archive server where continuous throughput is important. The MegaRaid card is designed for database servers where random read and write performance is important.

Also 14 million leads is excessive. We do not recommend more than ten million leads on a well speced DB server. Is there a reason you have so many?

[Iz3k34l]

yes we are using an onboard RAID controller the HP smart array. There is no real method to the madness one way or the other for keeping the data.

[mcargile]

HP Smart Arrays are actually fairly good. All the same I would still recommend exporting and then deleting unused leads. You will get the best performance if you keep it under 5 million lead records.

[Iz3k34l]

So what would you suggest, what would be the best method to remove these leads and what about historical statistics data which might rely on this table?

[Acidshock]

Have you tried to analyze and optimize the table?

[Acidshock]

Actually I think you are experiencing a bug I experienced. See this post... it has to do with MySQL not using the index properly.

viewtopic.php?f=4&t=32638

[Iz3k34l]

Acidshock,

The problem is exactly as yours, if i used 'down' it tanks... But i have them use 'down last call time' and it seems to be ok. is your vicidial_list table still 26 million plus records, if not how did you approach trimming it down and to what level did you trim it to.

[Acidshock]

Lol it is even more now! hahahaha! My solution... buy more hardware! You probably should separate your DB to be stand alone. Then edit your MySQL settings to basically cache the entire tables into memory.

In this particular case though you are really experiencing a problem with MySQL not understanding how to pull the data effectively. Thus its using a very basic method of going through the entire DB file rather than jumping to the most likely byte it will be at inside of the DB file. In plain terms it is trying to go through the entire haystack to find the needle rather than knowing where the needle is in the haystack.

You could edit the perl file that loads the hopper and have it specify the index hint. This will DRASTICALLY improve your result times.