<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">Hi All,<div class=""><div class=""><br class=""></div><div class="">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).</div><div class=""><br class=""></div><div class=""><blockquote type="cite" class=""></blockquote>WHERE CASE WHEN bpinfo.interval = 'month'<br class=""><blockquote type="cite" class=""></blockquote> THEN cdr.start_time<br class=""> 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<br class=""> <div class="">
<div style="color: rgb(0, 0, 0); letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><div style="color: rgb(0, 0, 0); letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="346" style="font-family: Helvetica; letter-spacing: normal; text-indent: 0px; text-transform: none; word-spacing: 0px; -webkit-text-stroke-width: 0px; width: 259.8pt;"><tbody class=""><tr class=""><td valign="top" style="padding: 0cm;" class=""><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""><br class=""></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class="">Warmest Regards,</div><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""><br class=""></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class="">Walter Klomp</div></td><td style="padding: 0cm;" class=""></td></tr></tbody></table><div style="color: rgb(0, 0, 0); font-family: Helvetica; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><br class=""></div><table class="x_MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="400" style="font-family: Helvetica; letter-spacing: normal; text-indent: 0px; text-transform: none; word-spacing: 0px; -webkit-text-stroke-width: 0px; width: 259.8pt;"><tbody class=""><tr class=""><td valign="top" class="" style="padding: 0in;"><br class=""></td></tr></tbody></table></div></div>
</div>
<div><br class=""><blockquote type="cite" class=""><div class="">On 1 Dec 2018, at 2:50 AM, Jon Bonilla (Manwe) <<a href="mailto:manwe@sipdoc.net" class="">manwe@sipdoc.net</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div class="">El Fri, 30 Nov 2018 10:30:07 +0100<br class="">"Rene Krenn" <<a href="mailto:rkrenn@sipwise.com" class="">rkrenn@sipwise.com</a>> escribió:<br class=""><br class=""><blockquote type="cite" class="">Hi,<br class=""><br class="">can you paste the full slow query, and what is the number of records in your<br class="">accounting.cdr table?<br class=""><br class=""><br class=""></blockquote><br class=""><br class="">Hi Rene<br class=""><br class=""><br class="">I think the query is the daily fraud calc. I hacve several of them stuck in the<br class="">server. <br class=""><br class="">The lab server is an exact copy of the production server but upgraded to version<br class="">mr6.5.2: 8 cores, 20GB RAM and SSD disks. The CDR has about 8M rows. I have 2.8<br class="">and 3.8 servers with much more rows and never had an issue before. <br class=""><br class="">The complete query is:<br class=""><br class=""><br class=""># Time: 181130 19:20:52<br class=""># User@Host: soap[soap] @ localhost []<br class=""># Thread_id: 8248 Schema: provisioning QC_hit: No<br class=""># Query_time: 9228.795092 Lock_time: 0.001511 Rows_sent: 1 Rows_examined:<br class="">4587190760 # Rows_affected: 0<br class="">SET timestamp=1543602052;<br class="">SELECT COUNT( * ) FROM (SELECT <a href="http://bpinfo.id" class="">bpinfo.id</a>, bpinfo.reseller_id, bpinfo.interval,<br class="">bpinfo.type, IF (bpinfo.fraud_use_reseller_rates > 0,<br class="">SUM(cdr.source_reseller_cost), SUM(cdr.source_customer_cost)) as interval_cost,<br class=""> bpinfo.interval_limit,<br class=""> bpinfo.interval_lock,<br class=""> bpinfo.interval_notify,<br class=""> bpinfo.fraud_use_reseller_rates as use_reseller_rates<br class="">FROM (<br class=""> SELECT <a href="http://c.id" class="">c.id</a>, n.reseller_id, bp.fraud_use_reseller_rates, i.interval,<br class=""> IF (i.interval = 'month',<br class=""> IF (cfp.fraud_interval_limit > 0,<br class=""> 'account_limit', 'profile_limit'),<br class=""> IF (cfp.fraud_daily_limit > 0,<br class=""> 'account_limit', 'profile_limit')<br class=""> ) AS type,<br class=""> IF (i.interval = 'month',<br class=""> IF (cfp.fraud_interval_limit > 0,<br class=""> cfp.fraud_interval_limit, bp.fraud_interval_limit),<br class=""> IF (cfp.fraud_daily_limit > 0,<br class=""> cfp.fraud_daily_limit, bp.fraud_daily_limit)<br class=""> ) AS interval_limit,<br class=""> IF (i.interval = 'month',<br class=""> IF (cfp.fraud_interval_limit > 0,<br class=""> cfp.fraud_interval_lock, bp.fraud_interval_lock),<br class=""> IF (cfp.fraud_daily_limit > 0,<br class=""> cfp.fraud_daily_lock, bp.fraud_daily_lock)<br class=""> ) AS interval_lock,<br class=""> IF (i.interval = 'month',<br class=""> IF (cfp.fraud_interval_limit > 0,<br class=""> cfp.fraud_interval_notify, bp.fraud_interval_notify),<br class=""> IF (cfp.fraud_daily_limit > 0,<br class=""> cfp.fraud_daily_notify, bp.fraud_daily_notify)<br class=""> ) AS interval_notify<br class=""> FROM (SELECT IF('day' = 'month','month','day') AS 'interval') i,<br class=""> billing.contracts c<br class=""> JOIN billing.v_actual_billing_profiles bp_actual ON bp_actual.contract_id =<br class=""><a href="http://c.id" class="">c.id</a> JOIN billing.billing_profiles bp ON <a href="http://bp.id" class="">bp.id</a> = bp_actual.billing_profile_id<br class=""> JOIN billing.contacts n ON <a href="http://n.id" class="">n.id</a> = c.contact_id<br class=""> JOIN billing.resellers r ON <a href="http://r.id" class="">r.id</a> = n.reseller_id<br class=""> LEFT JOIN billing.contract_fraud_preferences cfp ON cfp.contract_id = <a href="http://c.id" class="">c.id</a><br class=""> WHERE c.status = 'active'<br class=""> HAVING interval_limit > 0<br class="">) AS bpinfo<br class="">JOIN accounting.cdr ON cdr.source_account_id = <a href="http://bpinfo.id" class="">bpinfo.id</a><br class="">WHERE CASE WHEN bpinfo.interval = 'month'<br class=""> THEN cdr.start_time<br class=""> BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00'))<br class=""> AND UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01<br class="">00:00:00'))-1 ELSE cdr.start_time<br class=""> BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'))<br class=""> AND UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 1 DAY, '%Y-%m-%d<br class="">00:00:00'))-1 END<br class="">GROUP BY <a href="http://bpinfo.id" class="">bpinfo.id</a><br class="">HAVING interval_cost >= interval_limit<br class="">) `me`;<br class=""><br class=""><br class=""><br class="">The maridb processlist:<br class=""><br class="">MariaDB [(none)]> show processlist\G<br class="">*************************** 1. row ***************************<br class=""> Id: 2039<br class=""> User: nagios<br class=""> Host: localhost<br class=""> db: NULL<br class=""> Command: Sleep<br class=""> Time: 9<br class=""> State: <br class=""> Info: NULL<br class="">Progress: 0.000<br class="">*************************** 2. row ***************************<br class=""> Id: 6548<br class=""> User: soap<br class=""> Host: localhost<br class=""> db: provisioning<br class=""> Command: Query<br class=""> Time: 8097<br class=""> State: Sending data<br class=""> Info: SELECT COUNT( * ) FROM (SELECT <a href="http://bpinfo.id" class="">bpinfo.id</a>, bpinfo.reseller_id,<br class="">bpinfo.interval, bpinfo.type, I<br class="">Progress: 0.000<br class="">*************************** 3. row ***************************<br class=""> Id: 7611<br class=""> User: soap<br class=""> Host: localhost<br class=""> db: provisioning<br class=""> Command: Query<br class=""> Time: 8127<br class=""> State: Sending data<br class=""> Info: SELECT `me`.`id`, `me`.`reseller_id`, `me`.`interval`, `me`.`type`,<br class="">`me`.`interval_cost`, `me`.`inte Progress: 0.000<br class="">*************************** 4. row ***************************<br class=""> Id: 7823<br class=""> User: soap<br class=""> Host: localhost<br class=""> db: provisioning<br class=""> Command: Query<br class=""> Time: 5770<br class=""> State: Sending data<br class=""> Info: SELECT `me`.`id`, `me`.`reseller_id`, `me`.`interval`, `me`.`type`,<br class="">`me`.`interval_cost`, `me`.`inte Progress: 0.000<br class="">*************************** 5. row ***************************<br class=""> Id: 8035<br class=""> User: soap<br class=""> Host: localhost<br class=""> db: provisioning<br class=""> Command: Query<br class=""> Time: 3468<br class=""> State: Sending data<br class=""> Info: SELECT `me`.`id`, `me`.`reseller_id`, `me`.`interval`, `me`.`type`,<br class="">`me`.`interval_cost`, `me`.`inte Progress: 0.000<br class="">*************************** 6. row ***************************<br class=""> Id: 8248<br class=""> User: soap<br class=""> Host: localhost<br class=""> db: provisioning<br class=""> Command: Query<br class=""> Time: 1487<br class=""> State: Sending data<br class=""> Info: SELECT `me`.`id`, `me`.`reseller_id`, `me`.`interval`, `me`.`type`,<br class="">`me`.`interval_cost`, `me`.`inte Progress: 0.000<br class="">*************************** 7. row ***************************<br class=""> Id: 8461<br class=""> User: soap<br class=""> Host: localhost<br class=""> db: provisioning<br class=""> Command: Query<br class=""> Time: 8358<br class=""> State: Sending data<br class=""> Info: SELECT COUNT( * ) FROM (SELECT <a href="http://bpinfo.id" class="">bpinfo.id</a>, bpinfo.reseller_id,<br class="">bpinfo.interval, bpinfo.type, I<br class="">Progress: 0.000<br class="">*************************** 8. row ***************************<br class=""> Id: 8674<br class=""> User: soap<br class=""> Host: localhost<br class=""> db: provisioning<br class=""> Command: Query<br class=""> Time: 6026<br class=""> State: Sending data<br class=""> Info: SELECT COUNT( * ) FROM (SELECT <a href="http://bpinfo.id" class="">bpinfo.id</a>, bpinfo.reseller_id,<br class="">bpinfo.interval, bpinfo.type, I<br class="">Progress: 0.000<br class="">*************************** 9. row ***************************<br class=""> Id: 8887<br class=""> User: soap<br class=""> Host: localhost<br class=""> db: provisioning<br class=""> Command: Query<br class=""> Time: 4086<br class=""> State: Sending data<br class=""> Info: SELECT COUNT( * ) FROM (SELECT <a href="http://bpinfo.id" class="">bpinfo.id</a>, bpinfo.reseller_id,<br class="">bpinfo.interval, bpinfo.type, I<br class="">Progress: 0.000<br class="">*************************** 10. row ***************************<br class=""> Id: 9946<br class=""> User: soap<br class=""> Host: localhost<br class=""> db: provisioning<br class=""> Command: Query<br class=""> Time: 4536<br class=""> State: Sending data<br class=""> Info: SELECT COUNT( * ) FROM (SELECT <a href="http://bpinfo.id" class="">bpinfo.id</a>, bpinfo.reseller_id,<br class="">bpinfo.interval, bpinfo.type, I<br class="">Progress: 0.000<br class="">*************************** 11. row ***************************<br class=""> Id: 10159<br class=""> User: soap<br class=""> Host: localhost<br class=""> db: provisioning<br class=""> Command: Query<br class=""> Time: 2736<br class=""> State: Sending data<br class=""> Info: SELECT COUNT( * ) FROM (SELECT <a href="http://bpinfo.id" class="">bpinfo.id</a>, bpinfo.reseller_id,<br class="">bpinfo.interval, bpinfo.type, I<br class="">Progress: 0.000<br class="">*************************** 12. row ***************************<br class=""> Id: 10478<br class=""> User: root<br class=""> Host: localhost<br class=""> db: NULL<br class=""> Command: Query<br class=""> Time: 0<br class=""> State: init<br class=""> Info: show processlist<br class="">Progress: 0.000<br class="">12 rows in set (0.00 sec)<br class=""><br class=""><br class="">Top shows 766% CPU usage by mysqld, 0%wa <br class=""><br class=""><br class="">cheers,<br class=""><br class="">Jon<br class=""><br class=""><br class=""><br class="">-- <br class=""><a href="https://pekepbx.com" class="">https://pekepbx.com</a><br class="">https://www.issabel.com/multitenant<br class="">_______________________________________________<br class="">Spce-user mailing list<br class="">Spce-user@lists.sipwise.com<br class="">https://lists.sipwise.com/listinfo/spce-user<br class=""></div></div></blockquote></div><br class=""></div></div></body></html>
<br>
<span style="color:rgb(34,34,34);font-family:sans-serif;background-color:rgb(255,255,255)"><font size="1"><span style="font-family:Arial;color:rgb(102,102,102);font-style:italic;vertical-align:baseline;white-space:pre-wrap">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 </span><span style="color:rgb(102,102,102);font-family:Arial;font-style:italic;vertical-align:baseline;white-space:pre-wrap"><a href="https://myrepublic.net/sg/legal/terms-of-use-policies/privacy-policy/" target="_blank">here</a></span><span style="font-family:Arial;color:rgb(102,102,102);font-style:italic;vertical-align:baseline;white-space:pre-wrap">.</span></font></span>