[Spce-user] mysql 100% cpu after upgrade to mr6.5.2
Walter Klomp
walter at myrepublic.net
Fri Nov 30 20:21:51 EST 2018
Hi All,
While we’re working on this, as a matter of accuracy, I would prefer it be calculating the credit limit over the past month, i.e. NOW - 1 MONTH, not from the first day of the month to now… Days are long past that customers are billed on the 1st of the month… (at least in this region) - and by calculating the last 30/31 days there are no surprises should all of a sudden the traffic shoot up and the monthly limit kicks in (daily limit*30 is usually higher than monthly limit).
WHERE CASE WHEN bpinfo.interval = 'month'
THEN cdr.start_time
BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-%d 00:00:00')) AND UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00’))-1
Warmest Regards,
Walter Klomp
> On 1 Dec 2018, at 2:50 AM, Jon Bonilla (Manwe) <manwe at sipdoc.net> wrote:
>
> El Fri, 30 Nov 2018 10:30:07 +0100
> "Rene Krenn" <rkrenn at sipwise.com> escribió:
>
>> Hi,
>>
>> can you paste the full slow query, and what is the number of records in your
>> accounting.cdr table?
>>
>>
>
>
> Hi Rene
>
>
> I think the query is the daily fraud calc. I hacve several of them stuck in the
> server.
>
> The lab server is an exact copy of the production server but upgraded to version
> mr6.5.2: 8 cores, 20GB RAM and SSD disks. The CDR has about 8M rows. I have 2.8
> and 3.8 servers with much more rows and never had an issue before.
>
> The complete query is:
>
>
> # Time: 181130 19:20:52
> # User at Host: soap[soap] @ localhost []
> # Thread_id: 8248 Schema: provisioning QC_hit: No
> # Query_time: 9228.795092 Lock_time: 0.001511 Rows_sent: 1 Rows_examined:
> 4587190760 # Rows_affected: 0
> SET timestamp=1543602052;
> SELECT COUNT( * ) 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`;
>
>
>
> The maridb processlist:
>
> MariaDB [(none)]> show processlist\G
> *************************** 1. row ***************************
> Id: 2039
> User: nagios
> Host: localhost
> db: NULL
> Command: Sleep
> Time: 9
> State:
> Info: NULL
> Progress: 0.000
> *************************** 2. row ***************************
> Id: 6548
> User: soap
> Host: localhost
> db: provisioning
> Command: Query
> Time: 8097
> State: Sending data
> Info: SELECT COUNT( * ) FROM (SELECT bpinfo.id, bpinfo.reseller_id,
> bpinfo.interval, bpinfo.type, I
> Progress: 0.000
> *************************** 3. row ***************************
> Id: 7611
> User: soap
> Host: localhost
> db: provisioning
> Command: Query
> Time: 8127
> State: Sending data
> Info: SELECT `me`.`id`, `me`.`reseller_id`, `me`.`interval`, `me`.`type`,
> `me`.`interval_cost`, `me`.`inte Progress: 0.000
> *************************** 4. row ***************************
> Id: 7823
> User: soap
> Host: localhost
> db: provisioning
> Command: Query
> Time: 5770
> State: Sending data
> Info: SELECT `me`.`id`, `me`.`reseller_id`, `me`.`interval`, `me`.`type`,
> `me`.`interval_cost`, `me`.`inte Progress: 0.000
> *************************** 5. row ***************************
> Id: 8035
> User: soap
> Host: localhost
> db: provisioning
> Command: Query
> Time: 3468
> State: Sending data
> Info: SELECT `me`.`id`, `me`.`reseller_id`, `me`.`interval`, `me`.`type`,
> `me`.`interval_cost`, `me`.`inte Progress: 0.000
> *************************** 6. row ***************************
> Id: 8248
> User: soap
> Host: localhost
> db: provisioning
> Command: Query
> Time: 1487
> State: Sending data
> Info: SELECT `me`.`id`, `me`.`reseller_id`, `me`.`interval`, `me`.`type`,
> `me`.`interval_cost`, `me`.`inte Progress: 0.000
> *************************** 7. row ***************************
> Id: 8461
> User: soap
> Host: localhost
> db: provisioning
> Command: Query
> Time: 8358
> State: Sending data
> Info: SELECT COUNT( * ) FROM (SELECT bpinfo.id, bpinfo.reseller_id,
> bpinfo.interval, bpinfo.type, I
> Progress: 0.000
> *************************** 8. row ***************************
> Id: 8674
> User: soap
> Host: localhost
> db: provisioning
> Command: Query
> Time: 6026
> State: Sending data
> Info: SELECT COUNT( * ) FROM (SELECT bpinfo.id, bpinfo.reseller_id,
> bpinfo.interval, bpinfo.type, I
> Progress: 0.000
> *************************** 9. row ***************************
> Id: 8887
> User: soap
> Host: localhost
> db: provisioning
> Command: Query
> Time: 4086
> State: Sending data
> Info: SELECT COUNT( * ) FROM (SELECT bpinfo.id, bpinfo.reseller_id,
> bpinfo.interval, bpinfo.type, I
> Progress: 0.000
> *************************** 10. row ***************************
> Id: 9946
> User: soap
> Host: localhost
> db: provisioning
> Command: Query
> Time: 4536
> State: Sending data
> Info: SELECT COUNT( * ) FROM (SELECT bpinfo.id, bpinfo.reseller_id,
> bpinfo.interval, bpinfo.type, I
> Progress: 0.000
> *************************** 11. row ***************************
> Id: 10159
> User: soap
> Host: localhost
> db: provisioning
> Command: Query
> Time: 2736
> State: Sending data
> Info: SELECT COUNT( * ) FROM (SELECT bpinfo.id, bpinfo.reseller_id,
> bpinfo.interval, bpinfo.type, I
> Progress: 0.000
> *************************** 12. row ***************************
> Id: 10478
> User: root
> Host: localhost
> db: NULL
> Command: Query
> Time: 0
> State: init
> Info: show processlist
> Progress: 0.000
> 12 rows in set (0.00 sec)
>
>
> Top shows 766% CPU usage by mysqld, 0%wa
>
>
> cheers,
>
> Jon
>
>
>
> --
> https://pekepbx.com
> https://www.issabel.com/multitenant
> _______________________________________________
> Spce-user mailing list
> Spce-user at lists.sipwise.com
> https://lists.sipwise.com/listinfo/spce-user
--
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/mailman/private/spce-user_lists.sipwise.com/attachments/20181201/6224010e/attachment.html>
More information about the Spce-user
mailing list