<div dir="ltr">Because it's running for more than 3700 seconds already and I have cascading processes... so multiple running... I had to kill all of them because at some point mysql can't keep up<div><br></div><div>Also I noticed that ngcp-fraud-daily-lock is a perl script that calls this MySQL - but after a few minutes the perl script is already gone, I am guessing "tired of waiting". So keeping this running in MySQL is also questionable...</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Sep 4, 2020 at 3:07 PM Skyler <<a href="mailto:skchopperguy@gmail.com">skchopperguy@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="auto">Hi Walter,</div><div dir="auto"><br></div><div dir="auto"> What makes you think that the script is not keeping up? I mean, it’s running in memory.</div><div dir="auto"><br></div><div dir="auto">— Skyler</div><div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Sep 4, 2020 at 12:55 AM Walter Klomp via Spce-user <<a href="mailto:spce-user@lists.sipwise.com" target="_blank">spce-user@lists.sipwise.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><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" target="_blank">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" target="_blank">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" target="_blank">c.id</a><br> JOIN billing.billing_profiles bp ON <a href="http://bp.id" target="_blank">bp.id</a> = bp_actual.billing_profile_id<br> JOIN billing.contacts n ON <a href="http://n.id" target="_blank">n.id</a> = c.contact_id<br> JOIN billing.resellers r ON <a href="http://r.id" target="_blank">r.id</a> = n.reseller_id<br> LEFT JOIN billing.contract_fraud_preferences cfp ON cfp.contract_id = <a href="http://c.id" target="_blank">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" target="_blank">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" target="_blank">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"><div dir="ltr"><table border="0" cellspacing="0" cellpadding="0" width="346" style="font-family:Helvetica;width:259.8pt"><tbody style="font-family:Helvetica"><tr style="font-family:Helvetica"><td valign="top" style="padding:0cm;font-family:Helvetica"><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="font-family:"Times New Roman";color:purple"><a href="https://myrepublic.com.sg/" style="font-family:"Times New Roman";color:purple" target="_blank"><br><img border="0" width="140" height="92" src="https://myrepublic.com.sg/resources/email-signature.png" style="font-family: "Times New Roman";"></a></span><span style="font-family:"Times New Roman""></span></div></td><td style="padding:0cm;font-family:Helvetica"><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman""><b style="font-family:"Times New Roman""><span style="font-size:10pt;font-family:Arial;color:rgb(127,43,153)">Walter Klomp</span></b><span style="font-family:"Times New Roman""></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 style="font-family:"Times New Roman""></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 style="font-family:"Times New Roman""></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 style="font-family:"Times New Roman""></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 style="font-family:Helvetica"><td width="333" colspan="2" style="width:249.75pt;padding:0cm;font-family:Helvetica"><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:"Times New Roman""> <span style="font-family:"Times New Roman""></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 style="font-family:"Times New Roman""></span></div><div style="font-family:arial,sans-serif;font-size:16px;margin:0in 0in 0.0001pt"><font face="Arial, sans-serif" style="font-family:Arial,sans-serif;color:rgb(77,77,77)"><span style="font-size:11.3333px;font-family:Arial,sans-serif"><a href="https://www.google.com/maps/search/11+Lorong+3+Toa+Payoh+Block+B?entry=gmail&source=g" style="font-family:Arial,sans-serif" target="_blank">11 Lorong 3 Toa Payoh Block B</a> Jackson Square</span></font></div><div style="font-family:arial,sans-serif;font-size:16px;margin:0in 0in 0.0001pt"><font face="Arial, sans-serif" style="font-family:Arial,sans-serif;color:rgb(77,77,77)"><span style="font-size:11.3333px;font-family:Arial,sans-serif">#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="font-family:"Times New Roman";color:rgb(77,77,77)"><a href="https://myrepublic.com.sg/" style="font-family:"Times New Roman";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 style="font-family:"Times New Roman""></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="font-family:Arial;color:purple" target="_blank"><span style="font-family:Arial;color:rgb(77,77,77)">Twitter</span></a> | <a href="https://facebook.com/myrepublicsg" style="font-family:Arial;color:purple" target="_blank"><span style="font-family:Arial;color:rgb(77,77,77)">Facebook</span></a> | <a href="https://www.linkedin.com/company/myrepublic" style="font-family:Arial;color:purple" target="_blank"><span style="font-family:Arial;color:rgb(5,99,193)">LinkedIn</span></a></span></div></td></tr></tbody></table><div style="font-family:Helvetica;font-size:14px;color:rgb(0,0,0)"><br></div><table border="0" cellspacing="0" cellpadding="0" width="400" style="font-family:Helvetica;width:259.8pt"><tbody style="font-family:Helvetica"><tr style="font-family:Helvetica"><td valign="top" style="padding:0in;font-family:Helvetica"><br></td></tr></tbody></table></div></div></div></div><br><br><br><br><br><br><br><span style="font-family:sans-serif;background-color:rgb(255,255,255);color:rgb(34,34,34)"><font size="1" style="font-family:sans-serif;color:rgb(34,34,34)"><span style="font-family:Arial;font-style:italic;vertical-align:baseline;white-space:pre-wrap;color:rgb(102,102,102)">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="font-family:Arial;font-style:italic;vertical-align:baseline;white-space:pre-wrap;color:rgb(102,102,102)"><a href="https://myrepublic.net/sg/legal/terms-of-use-policies/privacy-policy/" style="font-family:Arial" target="_blank">here</a></span><span style="font-family:Arial;font-style:italic;vertical-align:baseline;white-space:pre-wrap;color:rgb(102,102,102)">.</span></font></span>-- <br><br>Spce-user mailing list<br><br><a href="mailto:Spce-user@lists.sipwise.com" target="_blank">Spce-user@lists.sipwise.com</a><br><br><a href="http://lists.sipwise.com/mailman/listinfo/spce-user_lists.sipwise.com" rel="noreferrer" target="_blank">http://lists.sipwise.com/mailman/listinfo/spce-user_lists.sipwise.com</a><br><br></blockquote></div></div>
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" class="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>
<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>