[Spce-user] mysql 100% cpu after upgrade to mr6.5.2

Jon Bonilla (Manwe) manwe at sipdoc.net
Fri Nov 30 13:50:07 EST 2018 

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 833 bytes
Desc: Firma digital OpenPGP
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20181130/d0ad783b/attachment-0001.sig>

More information about the Spce-user mailing list