[Spce-user] Daily fraud script runs forever...

[Walter Klomp]

Hi,

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.

Running mr6.5.9

Is there no way to update the script to run faster ?  I'm already at 1383
seconds...

This is what's running in my processlist.

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 bpinfo.id,
bpinfo.reseller_id, bpinfo.interval, bpinfo.type,
       IF (bpinfo.fraud_use_reseller_rates > 0,
SUM(cdr.source_reseller_cost),

SUM(cdr.source_customer_cost)) as interval_cost,
       bpinfo.interval_limit,
       bpinfo.interval_lock,
       bpinfo.interval_notify,
       bpinfo.fraud_use_reseller_rates as use_reseller_rates
FROM (
  SELECT c.id, n.reseller_id, bp.fraud_use_reseller_rates, i.interval,
    IF (i.interval = 'month',
        IF (cfp.fraud_interval_limit > 0,
            'account_limit', 'profile_limit'),
        IF (cfp.fraud_daily_limit > 0,
            'account_limit', 'profile_limit')
       ) AS type,
    IF (i.interval = 'month',
        IF (cfp.fraud_interval_limit > 0,
            cfp.fraud_interval_limit, bp.fraud_interval_limit),
        IF (cfp.fraud_daily_limit > 0,
            cfp.fraud_daily_limit, bp.fraud_daily_limit)
       ) AS interval_limit,
    IF (i.interval = 'month',
        IF (cfp.fraud_interval_limit > 0,
            cfp.fraud_interval_lock, bp.fraud_interval_lock),
        IF (cfp.fraud_daily_limit > 0,
            cfp.fraud_daily_lock, bp.fraud_daily_lock)
       ) AS interval_lock,
    IF (i.interval = 'month',
        IF (cfp.fraud_interval_limit > 0,
            cfp.fraud_interval_notify, bp.fraud_interval_notify),
        IF (cfp.fraud_daily_limit > 0,
            cfp.fraud_daily_notify, bp.fraud_daily_notify)
       ) AS interval_notify
  FROM (SELECT IF('day' = 'month','month','day') AS 'interval') i,
       billing.contracts c
  JOIN billing.v_actual_billing_profiles bp_actual ON bp_actual.contract_id
= c.id
  JOIN billing.billing_profiles bp ON bp.id = bp_actual.billing_profile_id
  JOIN billing.contacts n ON n.id = c.contact_id
  JOIN billing.resellers r ON r.id = n.reseller_id
  LEFT JOIN billing.contract_fraud_preferences cfp ON cfp.contract_id = c.id
  WHERE c.status = 'active'
  HAVING interval_limit > 0
) AS bpinfo
JOIN accounting.cdr ON cdr.source_account_id = bpinfo.id
WHERE CASE WHEN bpinfo.interval = 'month'
  THEN cdr.start_time
    BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00'))
        AND UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01
00:00:00'))-1
  ELSE cdr.start_time
    BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'))
        AND UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 1 DAY, '%Y-%m-%d
00:00:00'))-1
  END
GROUP BY bpinfo.id
HAVING interval_cost >= interval_limit
) `me` LIMIT 101


-- 

Warmest Regards,

<https://myrepublic.com.sg/>
*Walter Klomp*
Head of Voice & Systems
MyRepublic Limited
T: +65 6816 1120
F: +65 6717 2031

MyRepublic Limited
11 Lorong 3 Toa Payoh Block B Jackson Square
#04-11/15 Singapore 319579

myrepublic.com.sg
Follow us on: Twitter <https://twitter.com/myrepublic> | Facebook
<https://facebook.com/myrepublicsg> | LinkedIn
<https://www.linkedin.com/company/myrepublic>

-- 
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 here 
<https://myrepublic.net/sg/legal/terms-of-use-policies/privacy-policy/>.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20200904/3ecaf937/attachment.html>