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

[Jon Bonilla (Manwe)]

El Tue, 4 Dec 2018 01:15:00 +0100
"Rene Krenn" <rkrenn at sipwise.com> escribió:

> What is your exact setup, ie.
> - how many contracts use billing profiles with monthly fraud limits, how many
> with profiles with daily fraud limits?
> - how many contracts have contract fraudpreferences in place?

MariaDB [billing]> select count(*) from billing_profiles where
fraud_interval_limit is not null or fraud_daily_limit is not null;

+----------+
| count(*) |
+----------+
|       16 |
+----------+

MariaDB [billing]> select count(*) from contract_fraud_preferences;
+----------+
| count(*) |
+----------+
|       44 |
+----------+





> 
> As a general tip for now, try avoid using fraud limts with trunk subscriber
> contracts or system contracts, which tend to produce exceptional big call
> histories. 

That's a problem. Most of my customers use trunk subscribers.

>To avoid piling up, run auto-lock and fraud-daily with a proper
> job period. *update* since you reported the count() query of the fraud job
> appear in the slow query log in particular, we will prepare hotfixes in next
> days to strictly suppress them. This should mitigate the issues you see right
> away. Thanks for pointing/insisting here.

Month check is run once per day.
Daily check is run every 30 minutes. 


> People around (Walter iirc) reported improved query times by increasing ram
> and/or innodb buffer sizes (make sure perf settings are in effect in
> configs). This is because the query structure still relies on preparing (big)
> temptables, and indexes of involved tables must fit into ram. This not really
> scalable and will however only help to some limited extend -> ie. as long the
> stuff still fits into ram.


Will check increasing the buffer. It's 6GB ATM. Will try increasing to 12GB and
report back here. 


> PS. Btw., how is call list loading for you now with new recent version, can
> you confirm the improvement?
> 

Will check. I have different production systems and in some of them a
subscriber can hace millions of calls in call history. those are running
versions 2 and 3 though. Will check if there's any big subscriber in this lab
setup and report back too.


cheers and thanks,

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/20181204/78516ab7/attachment-0001.sig>