[Spce-user] Fraud calculation takes hours

[Alejandro Grijalba]

Hi.
I think I got it.

The problem is disk usage. Even though this server has 20 GB of free 
RAM, it keeps reading from disk every time i run the query (at 3MB/s in 
this old test server).
I increased the cache in config.yml
     bufferpoolsize: 512M

By doing that fraud script runs in less than 2 seconds, as opposed to 2 
hours!!
(Keep in mind that it took 2 hours while hitting 0 CDR).

Running a modified query (which involves 130k CDR) takes about 30 seconds.

My conclusion: increase MySQL cache (if your server has enough RAM).




El 26/01/2018 a las 4:08, Rene Krenn escribió:
>
> Hi,
>
> i have now generated a db of similar size:
>
> provision subscribers completed:
>
> total contracts: 100269 rows
>
> total subscribers: 100216 rows
>
> total aliases: 100167 rows
>
> primary aliases: 100167 rows
>
> generate cdrs completed:
>
> total CDRs: 500100 rows
>
> time elapsed: 04:33:39
>
> the differnece to Alejandro’s DB is that his one will show a larger 
> billing.billing_mappings
>
> table. (can you report how many records?)
>
> on my laptop runnung the currently implemented query against that 
> generated db takes
>
> -19 secs when it does not hit cdrs. „hit“ means the cdr start time is 
> covered by the queries BETWEEN clause.
>
> -3mins40secs when it hits 500k cdrs and reports 45k contracts 
> exceeding a profile’s daily limit
>
> -the query time reduces to 1-2 secs when reapeating it (ie. temptables 
> got cached)
>
> -running the query when the database was under load (while populating 
> the data) went up to 10 minutes, app. 2minutes of which required to 
> gather the actual billing mappings subquery (also for the last query 
> variant i posted)
>
> This is what’s expected with the current structure. Ist ok for the 
> fraud jobs running once a day.
>
> i agree it’s too slow for accessing /api/customerfraudevents frequently.
>
> In november the query was patched to address profile level fraud 
> limits to work properly (at the cost of performance).
>
> so one option for Alejandro ist o use the acc-cleanup.pl tool to move 
> cdrs from the cdr table to monthly tables.
>
> Another option ist o collapse billing mappings records (for which a 
> tool could be made).
>
> On the Other hand, a change oft he billing mappings table structure is 
> planned on our side.
>
> Before this bigger refactoring, another goal is to avoid the fraud 
> jobs in favour of getting the fraud alarms generated by the rating 
> engine directly/instantly - a recent fix for „free cash“ also makes 
> the „debit“/“Spent this interval“ summary fields to show concise sums 
> meanwhile. This is also an indirect answer to walters point: customers 
> prefer the fraud alarms more aggressively rather than relaxed/delayed 
> – some prefer to run the fraud jobs hourly or even in 15 min 
> intervals. Changing the BETWEEN clause to eg. the last day will not 
> change the speed oft he query.
>
> regards, rene
>
>