[Spce-user] Fraud calculation takes hours
Walter Klomp
walter at myrepublic.net
Fri Jan 26 05:54:18 EST 2018
This is what is in our optimised my.cnf file on 4.5.4 installation …
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /run/mysqld <— RAMDISK
log-error = /var/log/mysql/mysqld.err
pid-file = /var/run/mysqld/mysqld.pid
log_slow_queries = /var/log/mysql/slow-queries.log
log_output = FILE
long_query_time = 5
log-warnings = 2
table_cache = 2048
join_buffer_size = 16M
tmp_table_size = 512M
sort_buffer_size = 512M
thread_cache_size = 64
thread_concurrency = 8
thread_stack = 192K
max_heap_table_size = 512M
query_cache_size = 128M
query_cache_type = 1
query_cache_limit = 32M
transaction_isolation = REPEATABLE-READ
# InnoDB options
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_per_table
innodb_buffer_pool_size = 6G
innodb_additional_mem_pool_size = 32M
innodb_log_group_home_dir = /var/lib/mysql
innodb_log_files_in_group = 4
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_max_dirty_pages_pct = 80
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 32
innodb_autoinc_lock_mode = 1
innodb_locks_unsafe_for_binlog
innodb_fast_shutdown = 1
innodb_max_purge_lag = 0
innodb_stats_on_metadata = 0
innodb_buffer_pool_instances = 8
This server has 16GB RAM - note your perl-fcgi also takes quite a bit of memory…
The above settings will allow for proper operations even with slow drives…
note the red lines which made a huge impact… this went through various iterations to optimise memory usage…
(you may want to change this in the template my.cnf.customtt.tt2 to survive upgrades)
In /etc/sysctl.conf I also changed this:
vm.swappiness=1
vm.dirty_ratio=40
vm.dirty_background_ratio=5
then sysctl -p
This may help you if your hardware can’t keep up, but adds some risk if you have a hard crash …
For the rest, I found the fraud scripts are by far the most resource intensive queries the SQL server may have to contend with, and putting the SQL server on another machine will definitely help in also keeping the quality of service up.
Warmest Regards
Walter Klomp
> On 26 Jan 2018, at 5:18 PM, Rene Krenn <rkrenn at sipwise.com> wrote:
>
> Makes sense, i was running it on a trunk VM, that also has increased buffers.
> Think it still will require minutes for the very first invocation though.
>
> Thx fort he update®ards
>
> -----Ursprüngliche Nachricht-----
> Von: Spce-user [mailto:spce-user-bounces at lists.sipwise.com] Im Auftrag von Alejandro Grijalba
> Gesendet: Freitag, 26. Jänner 2018 10:15
> An: spce-user at lists.sipwise.com
> Betreff: Re: [Spce-user] Fraud calculation takes hours
>
> 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
>>
>>
>
> _______________________________________________
> Spce-user mailing list
> Spce-user at lists.sipwise.com
> https://lists.sipwise.com/listinfo/spce-user
>
> _______________________________________________
> Spce-user mailing list
> Spce-user at lists.sipwise.com
> https://lists.sipwise.com/listinfo/spce-user
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20180126/5d180dbe/attachment-0001.html>
More information about the Spce-user
mailing list