[Spce-user] Daily fraud script runs forever...
Alex Lutay
alutay at sipwise.com
Fri Sep 4 09:00:54 EDT 2020
Hi all,
Modern LTS releases have significant improvements for fraud
detection subsystem and performance optimisations overall:
> https://www.sipwise.org/news/announcements/sipwise-c5-mr7-5-1-released/
> Rework and optimise the customer calls fraud control and notification logic [TT#51409]
With the latest tests mr8.5.1 LTS consumes 20% less CPU
comparing to mr6.5 LTS (for the same calls scenarios),
see fresh test results https://ibb.co/9vhvJwQ
It has been tested on commercial PRO/Carrier versions but CE
should also see benefits.
Consider to upgrade to receive all the recent optimisations,
bugfixes and new features.
Tnx!
On 9/4/20 9:36 AM, Walter Klomp via Spce-user wrote:
> 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
>
> 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...
>
> On Fri, Sep 4, 2020 at 3:07 PM Skyler <skchopperguy at gmail.com
> <mailto:skchopperguy at gmail.com>> wrote:
>
> 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 <mailto: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
> <http://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 <http://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 <http://c.id>
> JOIN billing.billing_profiles bp ON bp.id <http://bp.id> =
> bp_actual.billing_profile_id
> JOIN billing.contacts n ON n.id <http://n.id> = c.contact_id
> JOIN billing.resellers r ON r.id <http://r.id> = n.reseller_id
> LEFT JOIN billing.contract_fraud_preferences cfp ON
> cfp.contract_id = c.id <http://c.id>
> WHERE c.status = 'active'
> HAVING interval_limit > 0
> ) AS bpinfo
> JOIN accounting.cdr ON cdr.source_account_id = bpinfo.id
> <http://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 <http://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 <https://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 <mailto:Spce-user at lists.sipwise.com>
>
> http://lists.sipwise.com/mailman/listinfo/spce-user_lists.sipwise.com
>
>
>
> --
>
> 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 <https://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/>.
>
--
Alex Lutay
More information about the Spce-user
mailing list