Mysql not using indexes correctly

[Acidshock]

Ok got a weird one. Have a rather large system that has been working great, has 26 million records in vicidial_list. Everything has been fine and then all of a sudden, mid-day, the queries to fill the hopper have been taking 1min+. This naturally shuts down the entire call center since everything is so dependent on the vicidial_list table. Below is the query that VICIDIAL is sending to fill the hopper. Sorry but it is rather large. This query is taking over a minute to process. If I limit it to one list the problem appears to go away. If I hint to mysql to use the list_id index using "use index(list_id)"; the query processes in about 3/10ths of a second. I have tried reanalyzing the table, optimizing it, even dumping, dropping and recreating the table. All with no success. I have even tried switching to MariaDB and it still does the same thing.

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 called_since_last_reset='N' and status IN('AA','NH','DROP','DA','N','B','A','CALLBK','NEW') and list_id IN('1387','1392') and lead_id NOT IN('29430500','29685337','29685314','29685270','29431948','29684652','29684708','29431757','29431906','29431783','29431321','29431167','29429708','29431010','29430525','29430941','29430545','29430633','29430752','29430820','29430861','29430868','29430920','29431190','29430136','29684500','29683114','29685099','29685188','29685045','29432175','29432068','29685217','29684507','29684614','29684532','29684496','29684426','29431172','29684982','29431747','29432135','29430296','29341229','29682507','29430628','29430889','29430398','29682522','29341245','29682691','29341390','29682759','29341465','29429744','29429707','29682843','29431808','29431638','29341268','29684669','29684719','29684759','29684774','29684870','29684960','29431929','29685085','29432022','29685349','29431494','29682539','29682671','29431376','29432075','29685111','29685325','29432177','29685215','29684494','29684581','29684463','29684248','29431490','29685315','29432122','29432184','29684626','29684679','29684530','29684373','29684352','29684258','29431782','29431766','29341311','29431071','29430800','29430958','29430404','29341214','29341361','29341387','29682748','29341453','29429721','29682884','29431237','29431883','29683641','29684689','29684763','29684839','29684946','29685048','29685010','29685070','29432188','29432223','29431710','29682567','29430391','29431015','29431297','29431045','29430923','29430373','29341426','29341488','29430834','29430758','29430676','29682603','29341378','29341287','29682678','29341389','29682738','29682747','29341431','29682793','29341448','29682810','29682821','29682833','29682850','29682918','29682896','29682909','29682898','29682903','29682907','29429751','29429755','29429779','29429799','29429802','29429803','29429818','29429828','29429833','29682973','29682982','29682983','29429884','29429901','29683056','29682995','29683039','29429960','29429970','29683162','29430062','29430064','29430072','29430159','29429894','29683157','29430160','29430176','29430181','29430100','29430164','29430200','29683029','29683045','29683059','29683060','29683068','29683098','29683206','29683240','29430233','29430259','29430268','29430274','29683490','29430337','29683223','29683227','29683235','29683252','29683291','29430040','29683373','29430227','29430230','29430248','29430254','29430262','29430269','29683488','29683505','29432235','29431500','29431517','29684746','29430470','29431387','29683756','29431270','29683676','29683900','29685105','29683805','29683898','29430687','29430498','29431064','29684415','29683944','29684148','29684309','29684232','29684521','29431589','29431687','29431303','29431024','29685050','29684369','29684348','29684272','29684246','29684097','29683619','29431158','29683988','29683575','29431468','29341301','29431765','29431825','29431719','29431466','29683973','29685202','29432243','29683910','29430753','29430613','29682531','29684947','29683990','29684057','29684134','29683631','29684828','29683605','29430781','29685079','29684696','29684349','29684265','29682552','29430515','29430731','29431335','29683801','29683657','29430359','29683980','29683787','29685303','29431797','29432011','29341271','29431879','29431910','29431102','29684717','29683726','29430827','29682642','29341316','29431008','29431531','29431488','29683561','29430379','29341294','29430757','29431385','29431773','29341322','29430821','29431053','29683985','29682536','29431707','29685331','29685363','29432164','29685196','29683860','29682549','29431216','29685332','29432061','29432086','29431954','29431921','29684988','29431932','29684992','29431827','29682522','29341283','29431789','29684896','29684894','29684721','29684809','29684712','29684659','29341213','29682586','29431604','29341308','29431905','29431709','29682531','29431813','29341294','29431669','29431287','29431193','29431060','29431118','29430452','29430766','29430601','29430689','29430810','29430828','29430842','29430863','29430896','29430899','29430946','29341498','29682906','29682882','29682865','29341483','29429710','29341466','29682808','29682771','29341392','29682534','29430392','29430886','29682573','29341332','29430947','29431447','29684966','29684813','29431963','29685057','29431946','29684908','29341287','29683784','29431417','29431201','29685365','29684951','29684656','29431693','29431779','29341269','29431732','29682510','29432109','29432023','29432044','29685297','29432130','29685219','29432215','29684640','29684561','29684572','29684533','29684371','29684442','29684180','29684283','29684162','29683516','29685226','29684474','29431059','29431120','29684958','29685320','29430921','29682608','29682845','29429733','29431762','29682572','29684655','29684762','29684767','29431238','29682657','29431051','29341407','29430975','29683492','29684858','29341278','29683583','29683916','29685201','29684270','29684122','29684436','29684544','29683074','29683541','29431597','29431610','29431392','29431690','29685339','29431552','29431293','29431255','29341214','29683827','29683584','29683681','29683639','29431323','29431227','29431352','29431657','29431653','29683576','29683629','29683517','29683864','29684341','29429766','29682875','29431022','29430616','29430548','29430671','29341403','29341398','29341268','29341313','29430370','29685273','29431920','29683556','29684787','29341255','29431870','29682534','29683596','29431304','29682571','29431875','29684136','29684149','29429769','29429695','29431098','29430395','29430734','29430605','29430636','29430785','29430833','29430904','29430845','29432076','29685233','29432132','29685240','29685362','29684646','29684618','29684566','29684558','29684458','29431135','29684517','29431897','29431820','29430959','29430561','29430469','29682540','29430672','29430867','29431047','29430412','29341310','29341317','29341314','29341396','29682807','29429709','29429694','29682905','29682937','29684190','29431767','29341285','29682573','29683626','29684608','29684737','29684765','29684797','29684873','29684934','29684922','29431949','29432166','29432226','29685347','29682931','29341373','29341308','29430389','29682587','29685268','29430453','29683875','29685213','29432115','29432018','29684664','29684555','29684338','29432024','29684629','29684391','29684576','29684524','29684410','29684222','29683526','29431854','29430877','29341255','29430497','29431021','29430374','29682536','29682624','29682572','29341393','29682786','29341469','29682872','29682854','29341229','29341278','29684715','29684754','29684777','29684728','29684897','29431945','29431942','29432035','29685364','29431583','29682715','29341235','29430732','29341331','29430979','29341271','29341411','29682839','29430880','29430815','29430746','29682530','29341352','29682711','29682651','29341381','29682722','29682742','29682774','29682792','29682794','29341451','29341458','29682825','29682841','29682912','29682919','29429727','29429711','29429718','29429720','29429747','29682922','29682925','29682940','29682947','29682948','29429817','29429826','29429831','29429866','29429875','29429878','29429881','29429896','29429963','29683112','29683026','29683153','29683078','29683161','29683167','29430063','29430065','29430081','29429889','29683079','29430217','29430170','29430178','29430184','29430132','29430191','29430207','29683031','29429912','29429922','29429946','29683076','29683106','29683238','29683412','29683463','29683477','29683482','29430282','29683508','29683212','29683226','29683232','29683246','29683268','29683338','29430049','29683402','29683411','29683449','29683457','29683470','29683478','29683452','29683504','29431912','29684888','29430544','29683643','29431672','29683798','29431577','29683865','29685030','29683935','29431095','29431527','29430744','29430526','29682652','29430435','29684311','29684275','29684326','29684229','29684527','29683803','29431513','29431497','29431776','29683893','29685198','29685116','29684231','29684325','29684268','29684185','29684095','29683953','29431186','29431106','29683959','29683914','29682567','29682562','29431880','29431711','29431798','29431512','29683920','29685153','29683955','29431502','29431032','29430661','29430477','29430862','29431264','29431833','29684041','29684088','29682552','29431320','29431492','29684313','29684602','29432189','29683746','29431654','29684347','29684264','29684279','29682586','29684099','29430945','29430679','29683771','29683967','29431026','29429983','29431569','29683651','29341245','29685029','29431350','29684775','29431900','29683530','29684172','29430778','29341347','29682659','29682692','29431054','29431331','29431722','29431832','29430437','29341285','29341446','29683697','29430678','29431250','29431230','29431307','29685336','29430955','29682549','29682770','29430330','29430308','29430304','29683498','29430302','29683496','29430301','29430284','29430250','29430244','29683445','29430238','29683439','29430237','29683433','29430234','29683426','29430225','29683400','29430219','29683394','29683393','29683392','29683391','29683387','29683273','29430042','29430039','29430034','29683150','29430031','29430030','29683136','29430026','29683132','29430025','29683130','29683129','29430015','29683127','29430009','29683119','29430008','29683367','29683363','29683351','29683346','29683339','29683326','29683317','29683315','29683309','29683305','29683303','29683297','29683296','29683178','29683173','29430044','29683149','29430020','29430003','29429996','29429990','29683100','29429988','29429957','29683069','29429949','29430151','29429913','29429910','29683041','29429909','29429908','29429907','29430210','29430206','29430154','29430149','29430137','29430128','29430127','29430126','29430124','29430116','29430112','29430097','29430082','29430079','29683022','29683008','29683002','29682965','29429855','29682964','29682960','29682959','29682954','29682953','29682952','29682951','29341427','29341421','29341408','29682744','29682740','29682724','29682716','29341269','29682510','29430399','29430478','29430535','29430589','29430623','29431314','29431402','29682647','29682750','29341395','29682811','29341439','29429731','29341472','29429704','29429693','29341417','29682885','29685063','29432112','29685255','29685246','29432003','29431969','29685037','29685014','29431956','29431834','29683611','29682507','29682530','29682539','29341293','29341329','29682562','29682658','29430511','29430550','29430626','29430790','29430841','29431150','29341213','29684545','29684584','29684554','29432140','29685253','29685308','29432067','29432074','29432169','29685351','29431144','29431639','29683719','29684024','29684459','29684207','29430650','29685149','29685210','29432131','29685327','29685343','29431185','29431593','29431830','29431463','29683578','29683950','29685125','29685114','29685123','29432212','29685067','29685134','29432056','29683783','29431817','29683781','29431821','29431383','29683665','29341301','29430420','29684332','29430448','29682571','29341429','29683710','29684226','29684259','29684424','29684491','29684471','29684529','29684597','29684380','29684651','29683688','29683752','29683970','29431701','29431764','29431067','29430774','29430750','29430617','29430674','29430611','29430539','29430518','29430495','29684994','29432058','29431009','29685180','29683816','29683708','29683820','29683711','29430771','29430706','29430730','29430698','29430656','29430547','29430520','29430485','29430458','29431068','29430798','29341288','29431165','29430405','29682630','29341283','29341293','29682590','29341435','29432209','29431682','29683831','29683907','29683869','29683841','29341288','29683638','29683592','29683859','29683822','29683757','29683595','29341235','29431479','29431558','29431246','29431418','29683778','29683618','29683570','29431544','29431239','29683690','29431078','29684079','29684105','29684061','29684115','29684086','29684030','29684011','29431429','29684017','29684004','29431243','29431220','29431291','29431305','29682686','29432124','29431399','29682540','29683551','29683594','29683951','29683825','29684050','29431128','29431170','29431274','29683923','29684048','29684129','29684187','29684263','29684228','29684451','29684331','29684419','29684273','29684144','29684240','29684985','29685027','29685200','29432059','29685204','29683965','29683894','29684991','29431137','29431178','29431419','29684021','29684063','29684089','29684194','29684039','29684317','29684465','29684339','29684071','29431699','29684208','29432182','29685060','29685101','29685229','29685013','29685230','29685346','0')  and ((gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99') and state NOT IN('AL','IN','KY','LA','MA','MS','NV','RI','UT','WY')) or (state='AL' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='IN' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='KY' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='LA' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='MA' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='MS' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='NV' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='RI' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='UT' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='WY' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) ) and (state NOT IN('MO', 'CA', 'WA', 'NC', 'PA'))   order by lead_id asc limit 350;


Here is the EXPLAIN of the query:

Code: Select all

+----+-------------+---------------+-------+--------------------------------------------------------------------------------------+---------+---------+------+-------+-------------+
| id | select_type | table         | type  | possible_keys                                                                        | key     | key_len | ref  | rows  | Extra       |
+----+-------------+---------------+-------+--------------------------------------------------------------------------------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | vicidial_list | index | PRIMARY,list_id,called_since_last_reset,status,gmt_offset_now,list_phone,list_status | PRIMARY | 4       | NULL | 71063 | Using where |
+----+-------------+---------------+-------+--------------------------------------------------------------------------------------+---------+---------+------+-------+-------------+


Mysqld-Slow.log entry:

Code: Select all

# Time: 140308 15:02:22
# User@Host: root[root] @ localhost []
# Query_time: 51.336835  Lock_time: 0.001188 Rows_sent: 350  Rows_examined: 25605419
SET timestamp=1394319742;
SELECT lead_id,list_id,gmt_offset_now,phone_number,state,status,modify_date,user,vendor_lead_code FROM vicidial_list where called_since_last_reset='N' and status IN('AA','NH','DROP','DA','N','B','A','CALLBK','NEW') and list_id IN('1385','1391') and lead_id NOT IN('29430500','29685337','29685314','29685270','29431948','29684652','29684708','29431757','29431906','29431783','29431321','29431167','29429708','29431010','29430525','29430941','29430545','29430633','29430752','29430820','29430861','29430868','29430920','29431190','29430136','29684500','29683114','29685099','29685188','29685045','29432175','29432068','29685217','29684507','29684614','29684532','29684496','29684426','29431172','29684982','29431747','29432135','29430296','29341229','29682507','29430628','29430889','29430398','29682522','29341245','29682691','29341390','29682759','29341465','29429744','29429707','29682843','29431808','29431638','29341268','29684669','29684719','29684759','29684774','29684870','29684960','29431929','29685085','29432022','29685349','29431494','29682539','29682671','29431376','29432075','29685111','29685325','29432177','29685215','29684494','29684581','29684463','29684248','29431490','29685315','29432122','29432184','29684626','29684679','29684530','29684373','29684352','29684258','29431782','29431766','29341311','29431071','29430800','29430958','29430404','29341214','29341361','29341387','29682748','29341453','29429721','29682884','29431237','29431883','29683641','29684689','29684763','29684839','29684946','29685048','29685010','29685070','29432188','29432223','29431710','29682567','29430391','29431015','29431297','29431045','29430923','29430373','29341426','29341488','29430834','29430758','29430676','29682603','29341378','29341287','29682678','29341389','29682738','29682747','29341431','29682793','29341448','29682810','29682821','29682833','29682850','29682918','29682896','29682909','29682898','29682903','29682907','29429751','29429755','29429779','29429799','29429802','29429803','29429818','29429828','29429833','29682973','29682982','29682983','29429884','29429901','29683056','29682995','29683039','29429960','29429970','29683162','29430062','29430064','29430072','29430159','29429894','29683157','29430160','29430176','29430181','29430100','29430164','29430200','29683029','29683045','29683059','29683060','29683068','29683098','29683206','29683240','29430233','29430259','29430268','29430274','29683490','29430337','29683223','29683227','29683235','29683252','29683291','29430040','29683373','29430227','29430230','29430248','29430254','29430262','29430269','29683488','29683505','29432235','29431500','29431517','29684746','29430470','29431387','29683756','29431270','29683676','29683900','29685105','29683805','29683898','29430687','29430498','29431064','29684415','29683944','29684148','29684309','29684232','29684521','29431589','29431687','29431303','29431024','29685050','29684369','29684348','29684272','29684246','29684097','29683619','29431158','29683988','29683575','29431468','29341301','29431765','29431825','29431719','29431466','29683973','29685202','29432243','29683910','29430753','29430613','29682531','29684947','29683990','29684057','29684134','29683631','29684828','29683605','29430781','29685079','29684696','29684349','29684265','29682552','29430515','29430731','29431335','29683801','29683657','29430359','29683980','29683787','29685303','29431797','29432011','29341271','29431879','29431910','29431102','29684717','29683726','29430827','29682642','29341316','29431008','29431531','29431488','29683561','29430379','29341294','29430757','29431385','29431773','29341322','29430821','29431053','29683985','29682536','29431707','29685331','29685363','29432164','29685196','29683860','29682549','29431216','29685332','29432061','29432086','29431954','29431921','29684988','29431932','29684992','29431827','29682522','29341283','29431789','29684896','29684894','29684721','29684809','29684712','29684659','29341213','29682586','29431604','29341308','29431905','29431709','29682531','29431813','29341294','29431669','29431287','29431193','29431060','29431118','29430452','29430766','29430601','29430689','29430810','29430828','29430842','29430863','29430896','29430899','29430946','29341498','29682906','29682882','29682865','29341483','29429710','29341466','29682808','29682771','29341392','29682534','29430392','29430886','29682573','29341332','29430947','29431447','29684966','29684813','29431963','29685057','29431946','29684908','29341287','29683784','29431417','29431201','29685365','29684951','29684656','29431693','29431779','29341269','29431732','29682510','29432109','29432023','29432044','29685297','29432130','29685219','29432215','29684640','29684561','29684572','29684533','29684371','29684442','29684180','29684283','29684162','29683516','29685226','29684474','29431059','29431120','29684958','29685320','29430921','29682608','29682845','29429733','29431762','29682572','29684655','29684762','29684767','29431238','29682657','29431051','29341407','29430975','29683492','29684858','29341278','29683583','29683916','29685201','29684270','29684122','29684436','29684544','29683074','29683541','29431597','29431610','29431392','29431690','29685339','29431552','29431293','29431255','29341214','29683827','29683584','29683681','29683639','29431323','29431227','29431352','29431657','29431653','29683576','29683629','29683517','29683864','29684341','29429766','29682875','29431022','29430616','29430548','29430671','29341403','29341398','29341268','29341313','29430370','29685273','29431920','29683556','29684787','29341255','29431870','29682534','29683596','29431304','29682571','29431875','29684136','29684149','29429769','29429695','29431098','29430395','29430734','29430605','29430636','29430785','29430833','29430904','29430845','29432076','29685233','29432132','29685240','29685362','29684646','29684618','29684566','29684558','29684458','29431135','29684517','29431897','29431820','29430959','29430561','29430469','29682540','29430672','29430867','29431047','29430412','29341310','29341317','29341314','29341396','29682807','29429709','29429694','29682905','29682937','29684190','29431767','29341285','29682573','29683626','29684608','29684737','29684765','29684797','29684873','29684934','29684922','29431949','29432166','29432226','29685347','29682931','29341373','29341308','29430389','29682587','29685268','29430453','29683875','29685213','29432115','29432018','29684664','29684555','29684338','29432024','29684629','29684391','29684576','29684524','29684410','29684222','29683526','29431854','29430877','29341255','29430497','29431021','29430374','29682536','29682624','29682572','29341393','29682786','29341469','29682872','29682854','29341229','29341278','29684715','29684754','29684777','29684728','29684897','29431945','29431942','29432035','29685364','29431583','29682715','29341235','29430732','29341331','29430979','29341271','29341411','29682839','29430880','29430815','29430746','29682530','29341352','29682711','29682651','29341381','29682722','29682742','29682774','29682792','29682794','29341451','29341458','29682825','29682841','29682912','29682919','29429727','29429711','29429718','29429720','29429747','29682922','29682925','29682940','29682947','29682948','29429817','29429826','29429831','29429866','29429875','29429878','29429881','29429896','29429963','29683112','29683026','29683153','29683078','29683161','29683167','29430063','29430065','29430081','29429889','29683079','29430217','29430170','29430178','29430184','29430132','29430191','29430207','29683031','29429912','29429922','29429946','29683076','29683106','29683238','29683412','29683463','29683477','29683482','29430282','29683508','29683212','29683226','29683232','29683246','29683268','29683338','29430049','29683402','29683411','29683449','29683457','29683470','29683478','29683452','29683504','29431912','29684888','29430544','29683643','29431672','29683798','29431577','29683865','29685030','29683935','29431095','29431527','29430744','29430526','29682652','29430435','29684311','29684275','29684326','29684229','29684527','29683803','29431513','29431497','29431776','29683893','29685198','29685116','29684231','29684325','29684268','29684185','29684095','29683953','29431186','29431106','29683959','29683914','29682567','29682562','29431880','29431711','29431798','29431512','29683920','29685153','29683955','29431502','29431032','29430661','29430477','29430862','29431264','29431833','29684041','29684088','29682552','29431320','29431492','29684313','29684602','29432189','29683746','29431654','29684347','29684264','29684279','29682586','29684099','29430945','29430679','29683771','29683967','29431026','29429983','29431569','29683651','29341245','29685029','29431350','29684775','29431900','29683530','29684172','29430778','29341347','29682659','29682692','29431054','29431331','29431722','29431832','29430437','29341285','29341446','29683697','29430678','29431250','29431230','29431307','29685336','29430955','29682549','29682770','29430330','29430308','29430304','29683498','29430302','29683496','29430301','29430284','29430250','29430244','29683445','29430238','29683439','29430237','29683433','29430234','29683426','29430225','29683400','29430219','29683394','29683393','29683392','29683391','29683387','29683273','29430042','29430039','29430034','29683150','29430031','29430030','29683136','29430026','29683132','29430025','29683130','29683129','29430015','29683127','29430009','29683119','29430008','29683367','29683363','29683351','29683346','29683339','29683326','29683317','29683315','29683309','29683305','29683303','29683297','29683296','29683178','29683173','29430044','29683149','29430020','29430003','29429996','29429990','29683100','29429988','29429957','29683069','29429949','29430151','29429913','29429910','29683041','29429909','29429908','29429907','29430210','29430206','29430154','29430149','29430137','29430128','29430127','29430126','29430124','29430116','29430112','29430097','29430082','29430079','29683022','29683008','29683002','29682965','29429855','29682964','29682960','29682959','29682954','29682953','29682952','29682951','29341427','29341421','29341408','29682744','29682740','29682724','29682716','29341269','29682510','29430399','29430478','29430535','29430589','29430623','29431314','29431402','29682647','29682750','29341395','29682811','29341439','29429731','29341472','29429704','29429693','29341417','29682885','29685063','29432112','29685255','29685246','29432003','29431969','29685037','29685014','29431956','29431834','29683611','29682507','29682530','29682539','29341293','29341329','29682562','29682658','29430511','29430550','29430626','29430790','29430841','29431150','29341213','29684545','29684584','29684554','29432140','29685253','29685308','29432067','29432074','29432169','29685351','29431144','29431639','29683719','29684024','29684459','29684207','29430650','29685149','29685210','29432131','29685327','29685343','29431185','29431593','29431830','29431463','29683578','29683950','29685125','29685114','29685123','29432212','29685067','29685134','29432056','29683783','29431817','29683781','29431821','29431383','29683665','29341301','29430420','29684332','29430448','29682571','29341429','29683710','29684226','29684259','29684424','29684491','29684471','29684529','29684597','29684380','29684651','29683688','29683752','29683970','29431701','29431764','29431067','29430774','29430750','29430617','29430674','29430611','29430539','29430518','29430495','29684994','29432058','29431009','29685180','29683816','29683708','29683820','29683711','29430771','29430706','29430730','29430698','29430656','29430547','29430520','29430485','29430458','29431068','29430798','29341288','29431165','29430405','29682630','29341283','29341293','29682590','29341435','29432209','29431682','29683831','29683907','29683869','29683841','29341288','29683638','29683592','29683859','29683822','29683757','29683595','29341235','29431479','29431558','29431246','29431418','29683778','29683618','29683570','29431544','29431239','29683690','29431078','29684079','29684105','29684061','29684115','29684086','29684030','29684011','29431429','29684017','29684004','29431243','29431220','29431291','29431305','29682686','29432124','29431399','29682540','29683551','29683594','29683951','29683825','29684050','29431128','29431170','29431274','29683923','29684048','29684129','29684187','29684263','29684228','29684451','29684331','29684419','29684273','29684144','29684240','29684985','29685027','29685200','29432059','29685204','29683965','29683894','29684991','29431137','29431178','29431419','29684021','29684063','29684089','29684194','29684039','29684317','29684465','29684339','29684071','29431699','29684208','29432182','29685060','29685101','29685229','29685013','29685230','29685346','0')  and ((gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99') and state NOT IN('AL','IN','KY','LA','MA','MS','NV','RI','UT','WY')) or (state='AL' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='IN' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='KY' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='LA' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='MA' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='MS' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='NV' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='RI' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='UT' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) or (state='WY' && gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.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','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99')) ) and (state NOT IN('MO', 'CA', 'WA', 'NC', 'PA'))   order by lead_id asc limit 350;


[Vince-0]

26 million records in vicidial_list is way bigger than I've seen it. How does your disk iostat %util look?

Vin.

[williamconley]

Make sure your ****_log tables are being cleared out regularly.

Note that another query could be slowing this query down. Have you watched this happen with "show processlist"? Can be quite educational to see if another process is in any way interfering.

Is your campaign filtered? Beware "joins" (obviously).

How many servers are running the hopper script?

[Acidshock]

Hi Everyone,

The server has 12 SSDs in a raid 10 configuration. At first I thought it was a hardware failure. However the IO was fine. I tried migrated everything over to another server and ran the same query. Took the same amount of time. If I add the index hint it works fine and completes in .17 seconds. I have even tried dropping the indexes and re-adding them. Same problem. I do have a filter but it is simple. It is state NOT IN('MO', 'CA', 'WA', 'NC', 'PA'). It seems to be related to the order by lead_id asc. If I remove that from the query completes in .02 seconds. With it in it takes 1min 5 seconds. Problem is that this is being automatically generated by vicidial and I don't want to make a simple change that could break a bunch of other things. It use to work fine but now MySQL's query optimizer appears to be on vacation.

[williamconley]

I am not sure why you would need to tell it to use the index for the table, or why it would make any difference when you did. I would hunt that down, definitely.

If you modify the perl script in question to use that hint ONLY if the order by involves the lead_id field, you should be safe.

I wonder if you could test this on a 5.0.3 machine or even a NONVicidial MySQL server to see if this issue persists.

I also wonder if the index hint would benefit other systems with heavy databases.

[Acidshock]

Just want to say problem wasn't solved but it is a moot point currently. I have been chasing my tail for a weekend. I didn't notice it and I was staring at it the whole time. They had it on DOWN 2nd New and they were trying to accomplish DOWN TIMEZONE 2nd NEW. DOWN TIMEZONE 2nd NEW works fine. However if they ever decide to run DOWN its going to be an issue. I am pretty sure its just an issue with the query optimizer in MySQL.

Is there any chance somebody can test it on a large system and see if the hint improves performance? Key is using multiple lists. Problem does not seem to manifest itself with a single list.

[ccabrera]

Right now I´m having the same issue. It´s a 3 server cluster with Vicibox 4.0.3. DB is a standalone server, 64 GB in RAM and 32 cores.

Everything was fine until this week. If I run the AST_VDhopper.pl 15 times in a row, it runs just fine, but suddenly, one run of the hopper loading script takes about a minute to finish, which conflicts with the crontab´s next minute run.

My "solution" so far has been to increase the hopper size, and then run the script every 3 minutes instead of 1. At least this way, the problem happens less frequently, but it still happens and I haven´t found a proper way to solve it.

So far, I ´ve tried this:

My log tables are archived daily
MySQL tmpdir is on tmpfs
I´ve tweaked my query_cache_size: moved it from 128M to 64M to 0, no changes at all.

My vicidial_list is at about 6M records, so it´s not "that big". All my campaigns have the default lead order DOWN. Tried the DOWN TIMEZONE but didn´t seem to work.

Any other ideas of what could be wrong?

[mflorell]

We have found that one of the common problems when we see this is that there are too many leads in the active lists for the active campaigns. It is recommended that you have no more than 500,000 leads in the active lists for a single campaign. To clarify, this has nothing to do with the number of dialable leads in the campaign, but the total number of leads in the active lists. You could have 6 million leads in your system and that can be just fine, as long as no more than 500,000 of those are in active lists for a single campaign.

Of course, this is just a guideline, every system is different, but we have fixed several clients' issues by just moving leads around and deactivating lists that aren't used anyway.

[williamconley]

And don't forget to run a "show processlist" and verify that the hopper query is your actual problem. It's entirely possible another query is jumping in and slowing down the system and the hopper script is merely how YOU find out there's a problem because it's visible. Except for invisible queries such as replication, the oldest running query will likely be your culprit.

We've had so many of these that I hesitate to say "it's probably X" any more especially if we did not build the system. Clients very often put in their own custom code from a "non-vicidial professional" and *poof* mysql problems. LOL.

[ccabrera]

We fixed this about 20 minutes ago. The hopper query was the problem, as the 'show processlist' pointed out, so we were completely sure about that.

The problem was inside the recycling leads statuses.

We found out 2 different recycling statuses which where deleted from the campaign a long time ago, thus no leads have them so no recycling can be done. We also discovered that if we disable those statuses, the hopper query finishes in about 2 seconds. If we enabled the statuses, it would take 40.

These are the tests we did, all of them while the server was in production:

- Run AST_VDhopper with the 'wipe-clean' to begin with a clean state.
- We enabled both recycling statuses which didn´t exist at all in the active lists
- We ran AST_VDhopper again. Took 40 seconds to complete.

Now the opposite way:
- Run AST_VDhopper with the 'wipe-clean' to begin with a clean state.
- We disabled both recycling statuses
- We ran AST_VDhopper again. Took 2 seconds to complete.

The process was repeateable at will. We are 100% sure this fixed the issue.

It looks to me that if the query is ran and the recycling statuses are inexistent in the active lists, then MySQL/MariaDB cannot use indexes properly and instead do a full table scan. When removing those statuses, it uses indexes properly.

I know it sounds weird, but this solved the issue. We are using MariaDB 5.5. Maybe it is a know issue, but it was unknown for us.

Thanks.

[williamconley]

This may be why there are so many comments saying clearly "do not use lead recycling". That functionality hasn't been useful in Vicidial for quite some time.

We always tell clients to ignore the function completely.

[ccabrera]

William,

I find it quite useful, and so our clients. Perhaps I´m outdated and haven´t been following the forums as I should. Could you point me out why it isn´t considered useful anymore and/or what function should I use to achieve the same results as if I´m using recycling? List reset perhaps?

[williamconley]

1) Dial ALL the leads.

2) Reset the Lists.

3) Dial ALL the leads again.

This process does not improve by redialing specific statuses outside that order. Lead recycling was initially designed to allow redialing "busy" numbers in a couple minutes (since that often was successful). But seriously: if you dial all the leads through and then dial them all again next time, you treat all statuses the same, all leads get called. Micromanaging individual statuses is actually counterproductive at this stage.