[Spce-user] Server lock up due to anti fraud script

[Walter Klomp]

Hi,

We have a CE installation on VMWare (but on a dedicated DELL R320 with 16Gb RAM), with now about 30.000 customers. All works fine (albeit the interface being pretty slow), however every half an hour or so the server is so loaded that it creates packet loss… MySQL has 100% CPU usage, writing to tmp tables etc… (which is now already on local storage).

This is the query that lasts about 5 minutes.  Can this be updated or speeded up ?  Alternatively I think we need to move this MySQL to another server.

SELECT bpinfo.type, bpinfo.id,
      SUM(cdr.source_customer_cost) as daily_cost,
      bpinfo.fraud_daily_limit, bpinfo.fraud_daily_lock, bpinfo.fraud_daily_notify
FROM (
 SELECT contracts.id,
   CASE WHEN cfp.fraud_daily_limit > 0 THEN 'account_limit'
                                       ELSE 'profile_limit' END as type,
   IF (cfp.fraud_daily_limit > 0, cfp.fraud_daily_limit, bp.fraud_daily_limit) as fraud_daily_limit,
   IF (cfp.fraud_daily_limit > 0, cfp.fraud_daily_lock, bp.fraud_daily_lock) as fraud_daily_lock,
   IF (cfp.fraud_daily_limit > 0, cfp.fraud_daily_notify, bp.fraud_daily_notify) as fraud_daily_notify
 FROM billing.contracts
 JOIN billing.billing_profiles bp
   ON (bp.id =
     (SELECT m.billing_profile_id
       FROM billing.billing_mappings m
        USE INDEX (contractid_idx)
       WHERE ((m.start_date IS NULL) OR (m.start_date <= NOW()))
         AND ((m.end_date IS NULL) OR (m.end_date >= NOW()))
         AND (m.contract_id = contracts.id)
       ORDER BY m.start_date DESC LIMIT 1
     )
   )
 LEFT JOIN billing.contract_fraud_preferences cfp ON cfp.contract_id = contracts.id
 WHERE (contracts.status = 'active')
   AND (cfp.fraud_daily_limit > 0 OR bp.fraud_daily_limit > 0)
) as bpinfo
JOIN accounting.cdr ON cdr.source_account_id = bpinfo.id
WHERE cdr.start_time BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'))
                        AND UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59'))
GROUP BY bpinfo.id
HAVING daily_cost > fraud_daily_limit
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/mailman/private/spce-user_lists.sipwise.com/attachments/20160613/6f85a241/attachment.html>