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

[Rene Krenn]

Hi,

can you paste the full slow query, and what is the number of records in your
accounting.cdr table?

regards

-----Ursprüngliche Nachricht-----
Von: Spce-user [mailto:spce-user-bounces at lists.sipwise.com] Im Auftrag von
Jon Bonilla (Manwe)
Gesendet: Freitag, 30. November 2018 08:09
An: "spce-user at lists.sipwise.com"
<spce-user at lists.sipwise.com>"@lists.sipwise.com
Betreff: [Spce-user] mysql 100% cpu after upgrade to mr6.5.2

Hi all

I've cloned a production system and tested the upgrade from 3.8 to 6.5.
After the upgrade, the lab system uses 100% CPU because some queries take
forever to process. The slow-query logs shows for example:


# Time: 181130  7:54:01
# User at Host: soap[soap] @ localhost []
# Thread_id: 4425  Schema: provisioning  QC_hit: No # Query_time:
8638.222048  Lock_time: 0.002123  Rows_sent: 1  Rows_examined:
4587190760 # Rows_affected: 0
SET timestamp=1543560841;
SELECT COUNT( * ) FROM (SELECT bpinfo.id, bpinfo.reseller_id,
bpinfo.interval, bpinfo.type,...


Looks like the fraud check takes too long. Is it normal the number of rows
examined? Looks too big to me.


EXPLAIN looks ok except for the number of rows affected in the first part
IMHO:

 *************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 32415222840
        Extra: 
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: <derived3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3990
        Extra: Using temporary; Using filesort
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: cdr
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8124116
        Extra: Using where
*************************** 4. row ***************************
           id: 3
  select_type: DERIVED
        table: <derived4>
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: 
*************************** 5. row ***************************
           id: 3
  select_type: DERIVED
        table: bp
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 21
        Extra: 
*************************** 6. row ***************************
           id: 3
  select_type: DERIVED
        table: cbpn
         type: ref
possible_keys: PRIMARY,cbpn_natural_idx,cbpn_pid_ref
          key: cbpn_pid_ref
      key_len: 4
          ref: billing.bp.id
         rows: 19
        Extra: Using where
*************************** 7. row ***************************
           id: 3
  select_type: DERIVED
        table: c
         type: eq_ref
possible_keys: PRIMARY,contactid_idx
          key: PRIMARY
      key_len: 4
          ref: billing.cbpn.contract_id
         rows: 1
        Extra: Using where
*************************** 8. row ***************************
           id: 3
  select_type: DERIVED
        table: cfp
         type: eq_ref
possible_keys: contract_id
          key: contract_id
      key_len: 4
          ref: billing.cbpn.contract_id
         rows: 1
        Extra: 
*************************** 9. row ***************************
           id: 3
  select_type: DERIVED
        table: cbpns
         type: ref
possible_keys: cbpns_pnid_est_idx
          key: cbpns_pnid_est_idx
      key_len: 4
          ref: billing.cbpn.id
         rows: 1
        Extra: Using index
*************************** 10. row ***************************
           id: 3
  select_type: DERIVED
        table: n
         type: eq_ref
possible_keys: PRIMARY,ct_resellerid_ref
          key: PRIMARY
      key_len: 4
          ref: billing.c.contact_id
         rows: 1
        Extra: Using where
*************************** 11. row ***************************
           id: 3
  select_type: DERIVED
        table: r
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: billing.n.reseller_id
         rows: 1
        Extra: Using index
*************************** 12. row ***************************
           id: 3
  select_type: DERIVED
        table: <derived6>
         type: ref
possible_keys: key1
          key: key1
      key_len: 8
          ref: billing.cbpns.effective_start_time
         rows: 10
        Extra: Using where
*************************** 13. row ***************************
           id: 6
  select_type: DERIVED
        table: cbpns
         type: index
possible_keys: cbpns_pnid_est_idx
          key: cbpns_pnid_est_idx
      key_len: 11
          ref: NULL
         rows: 759
        Extra: Using where; Using index; Using temporary; Using filesort
*************************** 14. row ***************************
           id: 6
  select_type: DERIVED
        table: cbpn
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: billing.cbpns.profile_network_id
         rows: 1
        Extra: Using where
*************************** 15. row ***************************
           id: 4
  select_type: DERIVED
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used



Any hints where the problem could be?



cheers,

Jon



--
https://pekepbx.com
https://www.issabel.com/multitenant