<div dir="ltr">Hi,<div><br></div><div>I have about 70000+ subscribers, the daily fraud script can't seem to keep up. I checked the Disk IO and I don't see it getting "loaded", because it's running on SSD with 32G RAM.</div><div><br></div><div>Running mr6.5.9</div><div><br></div><div>Is there no way to update the script to run faster ? I'm already at 1383 seconds... </div><div><br></div><div>This is what's running in my processlist.</div><div><br></div><div>SELECT `me`.`id`, `me`.`reseller_id`, `me`.`interval`, `me`.`type`, `me`.`interval_cost`, `me`.`interval_limit`, `me`.`interval_lock`, `me`.`interval_notify`, `me`.`use_reseller_rates` FROM (SELECT <a href="http://bpinfo.id">bpinfo.id</a>, bpinfo.reseller_id, bpinfo.interval, bpinfo.type,<br> IF (bpinfo.fraud_use_reseller_rates > 0, SUM(cdr.source_reseller_cost),<br> SUM(cdr.source_customer_cost)) as interval_cost,<br> bpinfo.interval_limit,<br> bpinfo.interval_lock,<br> bpinfo.interval_notify,<br> bpinfo.fraud_use_reseller_rates as use_reseller_rates<br>FROM (<br> SELECT <a href="http://c.id">c.id</a>, n.reseller_id, bp.fraud_use_reseller_rates, i.interval,<br> IF (i.interval = 'month',<br> IF (cfp.fraud_interval_limit > 0,<br> 'account_limit', 'profile_limit'),<br> IF (cfp.fraud_daily_limit > 0,<br> 'account_limit', 'profile_limit')<br> ) AS type,<br> IF (i.interval = 'month',<br> IF (cfp.fraud_interval_limit > 0,<br> cfp.fraud_interval_limit, bp.fraud_interval_limit),<br> IF (cfp.fraud_daily_limit > 0,<br> cfp.fraud_daily_limit, bp.fraud_daily_limit)<br> ) AS interval_limit,<br> IF (i.interval = 'month',<br> IF (cfp.fraud_interval_limit > 0,<br> cfp.fraud_interval_lock, bp.fraud_interval_lock),<br> IF (cfp.fraud_daily_limit > 0,<br> cfp.fraud_daily_lock, bp.fraud_daily_lock)<br> ) AS interval_lock,<br> IF (i.interval = 'month',<br> IF (cfp.fraud_interval_limit > 0,<br> cfp.fraud_interval_notify, bp.fraud_interval_notify),<br> IF (cfp.fraud_daily_limit > 0,<br> cfp.fraud_daily_notify, bp.fraud_daily_notify)<br> ) AS interval_notify<br> FROM (SELECT IF('day' = 'month','month','day') AS 'interval') i,<br> billing.contracts c<br> JOIN billing.v_actual_billing_profiles bp_actual ON bp_actual.contract_id = <a href="http://c.id">c.id</a><br> JOIN billing.billing_profiles bp ON <a href="http://bp.id">bp.id</a> = bp_actual.billing_profile_id<br> JOIN billing.contacts n ON <a href="http://n.id">n.id</a> = c.contact_id<br> JOIN billing.resellers r ON <a href="http://r.id">r.id</a> = n.reseller_id<br> LEFT JOIN billing.contract_fraud_preferences cfp ON cfp.contract_id = <a href="http://c.id">c.id</a><br> WHERE c.status = 'active'<br> HAVING interval_limit > 0<br>) AS bpinfo<br>JOIN accounting.cdr ON cdr.source_account_id = <a href="http://bpinfo.id">bpinfo.id</a><br>WHERE CASE WHEN bpinfo.interval = 'month'<br> THEN cdr.start_time<br> BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00'))<br> AND UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01 00:00:00'))-1<br> ELSE cdr.start_time<br> BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'))<br> AND UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 1 DAY, '%Y-%m-%d 00:00:00'))-1<br> END<br>GROUP BY <a href="http://bpinfo.id">bpinfo.id</a><br>HAVING interval_cost >= interval_limit<br>) `me` LIMIT 101</div><div><br clear="all"><div><br></div>-- <br><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><table border="0" cellspacing="0" cellpadding="0" width="346" style="font-family:Helvetica;width:259.8pt"><tbody><tr><td valign="top" style="padding:0cm"><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman""><br></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman"">Warmest Regards,</div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman""><span style="color:purple"><a href="https://myrepublic.com.sg/" style="color:purple" target="_blank"><br><img border="0" width="140" height="92" src="https://myrepublic.com.sg/resources/email-signature.png"></a></span><span></span></div></td><td style="padding:0cm"><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman""><b><span style="font-size:10pt;font-family:Arial;color:rgb(127,43,153)">Walter Klomp</span></b><span></span></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman""><span style="font-size:8.5pt;font-family:Arial;color:rgb(77,77,77)">Head of Voice & Systems</span><span></span></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman""><span style="font-size:8.5pt;font-family:Arial;color:rgb(77,77,77)">MyRepublic Limited</span><span></span></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman""><span style="font-size:8.5pt;font-family:Arial;color:rgb(77,77,77)">T: +65 6816 1120</span><span></span></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman""><span style="font-size:8.5pt;font-family:Arial;color:rgb(77,77,77)">F: +65 6717 2031</span></div></td></tr><tr><td width="333" colspan="2" style="width:249.75pt;padding:0cm"><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman""> <span></span></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman""><span style="font-size:8.5pt;font-family:Arial;color:rgb(77,77,77)">MyRepublic Limited</span><span></span></div><div style="font-family:arial,sans-serif;font-size:16px;margin:0in 0in 0.0001pt"><font color="#4d4d4d" face="Arial, sans-serif"><span style="font-size:11.3333px">11 Lorong 3 Toa Payoh Block B Jackson Square</span></font></div><div style="font-family:arial,sans-serif;font-size:16px;margin:0in 0in 0.0001pt"><font color="#4d4d4d" face="Arial, sans-serif"><span style="font-size:11.3333px">#04-11/15 Singapore 319579</span></font></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman""><span style="font-size:8.5pt;font-family:Arial;color:rgb(77,77,77)"><br></span><span style="color:rgb(77,77,77)"><a href="https://myrepublic.com.sg/" style="color:purple" target="_blank"><span style="font-size:8.5pt;font-family:Arial;color:rgb(77,77,77)">myrepublic.com.sg</span></a></span><span></span></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman""><span style="font-size:8.5pt;font-family:Arial;color:rgb(77,77,77)">Follow us on: <a href="https://twitter.com/myrepublic" style="color:purple" target="_blank"><span style="color:rgb(77,77,77)">Twitter</span></a> | <a href="https://facebook.com/myrepublicsg" style="color:purple" target="_blank"><span style="color:rgb(77,77,77)">Facebook</span></a> | <a href="https://www.linkedin.com/company/myrepublic" style="color:purple" target="_blank"><span style="color:rgb(5,99,193)">LinkedIn</span></a></span></div></td></tr></tbody></table><div style="color:rgb(0,0,0);font-family:Helvetica;font-size:14px"><br></div><table border="0" cellspacing="0" cellpadding="0" width="400" style="font-family:Helvetica;width:259.8pt"><tbody><tr><td valign="top" style="padding:0in"><br></td></tr></tbody></table></div></div></div></div>
<br>
<span style="color:rgb(34,34,34);font-family:sans-serif;background-color:rgb(255,255,255)"><font size="1"><span style="font-family:Arial;color:rgb(102,102,102);font-style:italic;vertical-align:baseline;white-space:pre-wrap">The contents of this email and any attachments are confidential and may also be privileged. You must not disseminate the contents of this email and any attachments without permission of the sender. If you have received this email by mistake, please delete all copies and inform the sender immediately. You may refer to our company's Privacy Policy </span><span style="color:rgb(102,102,102);font-family:Arial;font-style:italic;vertical-align:baseline;white-space:pre-wrap"><a href="https://myrepublic.net/sg/legal/terms-of-use-policies/privacy-policy/" target="_blank">here</a></span><span style="font-family:Arial;color:rgb(102,102,102);font-style:italic;vertical-align:baseline;white-space:pre-wrap">.</span></font></span>