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

[Skyler]

Hi Walter,

 What makes you think that the script is not keeping up? I mean, it’s
running in memory.

— Skyler

On Fri, Sep 4, 2020 at 12:55 AM Walter Klomp via Spce-user <
spce-user at lists.sipwise.com> wrote:

> 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
> <https://www.google.com/maps/search/11+Lorong+3+Toa+Payoh+Block+B?entry=gmail&source=g>
> 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/>.--
>
>
> Spce-user mailing list
>
> Spce-user at lists.sipwise.com
>
> http://lists.sipwise.com/mailman/listinfo/spce-user_lists.sipwise.com
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20200904/ece6eed0/attachment-0002.html>