[Spce-user] Daily fraud script runs forever...
Walter Klomp
walter at myrepublic.net
Fri Sep 4 03:36:45 EDT 2020
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> 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> 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
>>
>>
--
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/017733a6/attachment-0002.html>
More information about the Spce-user
mailing list