<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="">This is what is in our optimised my.cnf file on 4.5.4 installation …<div class=""><br class=""></div><div class=""><div class="">basedir = /usr</div><div class="">datadir = /var/lib/mysql</div><div class=""><font color="#ff2600" class="">tmpdir = /run/mysqld <— RAMDISK</font></div><div class="">log-error = /var/log/mysql/mysqld.err</div><div class="">pid-file = /var/run/mysqld/mysqld.pid</div><div class="">log_slow_queries = /var/log/mysql/slow-queries.log</div><div class="">log_output = FILE</div><div class="">long_query_time = 5</div><div class="">log-warnings = 2</div><div class=""><br class=""></div><div class=""><div class="">table_cache = 2048</div><div class="">join_buffer_size = 16M</div><div class="">tmp_table_size = 512M</div><div class="">sort_buffer_size = 512M</div><div class="">thread_cache_size = 64</div><div class="">thread_concurrency = 8</div><div class="">thread_stack = 192K</div><div class="">max_heap_table_size<span class="Apple-tab-span" style="white-space:pre"> </span> = 512M</div><div class=""><br class=""></div><div class=""><br class=""></div><div class="">query_cache_size = 128M</div><div class="">query_cache_type = 1</div><div class="">query_cache_limit = 32M</div><div class=""><br class=""></div><div class="">transaction_isolation = REPEATABLE-READ</div></div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><div class=""># InnoDB options</div><div class="">innodb_data_home_dir = /var/lib/mysql</div><div class="">innodb_data_file_path = ibdata1:10M:autoextend</div><div class="">innodb_file_per_table</div><div class=""><font color="#ff2600" class="">innodb_buffer_pool_size = 6G</font></div><div class="">innodb_additional_mem_pool_size = 32M</div><div class="">innodb_log_group_home_dir = /var/lib/mysql</div><div class="">innodb_log_files_in_group = 4</div><div class="">innodb_log_file_size = 128M</div><div class="">innodb_log_buffer_size = 8M</div><div class=""><font color="#ff2600" class="">innodb_max_dirty_pages_pct = 80</font></div><div class=""><font color="#ff2600" class="">innodb_flush_log_at_trx_commit = 2</font></div><div class="">innodb_lock_wait_timeout = 50</div><div class="">innodb_flush_method = O_DIRECT</div><div class="">innodb_thread_concurrency = 32</div><div class="">innodb_autoinc_lock_mode = 1</div><div class="">innodb_locks_unsafe_for_binlog</div><div class="">innodb_fast_shutdown = 1</div><div class="">innodb_max_purge_lag = 0</div><div class="">innodb_stats_on_metadata = 0</div><div class=""><font color="#ff2600" class="">innodb_buffer_pool_instances<span class="Apple-tab-span" style="white-space:pre"> </span> = 8</font></div></div><div class=""><br class=""></div><div class=""><br class=""></div><div class="">This server has 16GB RAM - note your perl-fcgi also takes quite a bit of memory…</div><div class="">The above settings will allow for proper operations even with slow drives… </div><div class="">note the red lines which made a huge impact… this went through various iterations to optimise memory usage…</div><div class="">(you may want to change this in the template my.cnf.customtt.tt2 to survive upgrades) </div><div class=""><br class=""></div><div class="">In /etc/sysctl.conf I also changed this:</div><div class=""><br class=""></div><div class=""><div class="">vm.swappiness=1</div><div class="">vm.dirty_ratio=40</div><div class="">vm.dirty_background_ratio=5</div></div><div class=""><br class=""></div><div class="">then sysctl -p</div><div class=""><br class=""></div><div class="">This may help you if your hardware can’t keep up, but adds some risk if you have a hard crash …</div><div class=""><br class=""></div><div class="">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.</div><div class=""><br class=""></div><div class="">Warmest Regards</div><div class="">Walter Klomp</div><div class=""><br class=""></div>
<div><br class=""><blockquote type="cite" class=""><div class="">On 26 Jan 2018, at 5:18 PM, Rene Krenn <<a href="mailto:rkrenn@sipwise.com" class="">rkrenn@sipwise.com</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div class="">Makes sense, i was running it on a trunk VM, that also has increased buffers.<br class="">Think it still will require minutes for the very first invocation though.<br class=""><br class="">Thx fort he update®ards<br class=""><br class="">-----Ursprüngliche Nachricht-----<br class="">Von: Spce-user [<a href="mailto:spce-user-bounces@lists.sipwise.com" class="">mailto:spce-user-bounces@lists.sipwise.com</a>] Im Auftrag von Alejandro Grijalba<br class="">Gesendet: Freitag, 26. Jänner 2018 10:15<br class="">An: <a href="mailto:spce-user@lists.sipwise.com" class="">spce-user@lists.sipwise.com</a><br class="">Betreff: Re: [Spce-user] Fraud calculation takes hours<br class=""><br class="">Hi.<br class="">I think I got it.<br class=""><br class="">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).<br class="">I increased the cache in config.yml<br class=""> bufferpoolsize: 512M<br class=""><br class="">By doing that fraud script runs in less than 2 seconds, as opposed to 2 hours!!<br class="">(Keep in mind that it took 2 hours while hitting 0 CDR).<br class=""><br class="">Running a modified query (which involves 130k CDR) takes about 30 seconds.<br class=""><br class="">My conclusion: increase MySQL cache (if your server has enough RAM).<br class=""><br class=""><br class=""><br class=""><br class="">El 26/01/2018 a las 4:08, Rene Krenn escribió:<br class=""><blockquote type="cite" class=""><br class="">Hi,<br class=""><br class="">i have now generated a db of similar size:<br class=""><br class="">provision subscribers completed:<br class=""><br class="">total contracts: 100269 rows<br class=""><br class="">total subscribers: 100216 rows<br class=""><br class="">total aliases: 100167 rows<br class=""><br class="">primary aliases: 100167 rows<br class=""><br class="">generate cdrs completed:<br class=""><br class="">total CDRs: 500100 rows<br class=""><br class="">time elapsed: 04:33:39<br class=""><br class="">the differnece to Alejandro’s DB is that his one will show a larger <br class="">billing.billing_mappings<br class=""><br class="">table. (can you report how many records?)<br class=""><br class="">on my laptop runnung the currently implemented query against that <br class="">generated db takes<br class=""><br class="">-19 secs when it does not hit cdrs. „hit“ means the cdr start time is <br class="">covered by the queries BETWEEN clause.<br class=""><br class="">-3mins40secs when it hits 500k cdrs and reports 45k contracts <br class="">exceeding a profile’s daily limit<br class=""><br class="">-the query time reduces to 1-2 secs when reapeating it (ie. temptables <br class="">got cached)<br class=""><br class="">-running the query when the database was under load (while populating <br class="">the data) went up to 10 minutes, app. 2minutes of which required to <br class="">gather the actual billing mappings subquery (also for the last query <br class="">variant i posted)<br class=""><br class="">This is what’s expected with the current structure. Ist ok for the <br class="">fraud jobs running once a day.<br class=""><br class="">i agree it’s too slow for accessing /api/customerfraudevents frequently.<br class=""><br class="">In november the query was patched to address profile level fraud <br class="">limits to work properly (at the cost of performance).<br class=""><br class="">so one option for Alejandro ist o use the acc-cleanup.pl tool to move <br class="">cdrs from the cdr table to monthly tables.<br class=""><br class="">Another option ist o collapse billing mappings records (for which a <br class="">tool could be made).<br class=""><br class="">On the Other hand, a change oft he billing mappings table structure is <br class="">planned on our side.<br class=""><br class="">Before this bigger refactoring, another goal is to avoid the fraud <br class="">jobs in favour of getting the fraud alarms generated by the rating <br class="">engine directly/instantly - a recent fix for „free cash“ also makes <br class="">the „debit“/“Spent this interval“ summary fields to show concise sums <br class="">meanwhile. This is also an indirect answer to walters point: customers <br class="">prefer the fraud alarms more aggressively rather than relaxed/delayed <br class="">– some prefer to run the fraud jobs hourly or even in 15 min <br class="">intervals. Changing the BETWEEN clause to eg. the last day will not <br class="">change the speed oft he query.<br class=""><br class="">regards, rene<br class=""><br class=""><br class=""></blockquote><br class="">_______________________________________________<br class="">Spce-user mailing list<br class=""><a href="mailto:Spce-user@lists.sipwise.com" class="">Spce-user@lists.sipwise.com</a><br class="">https://lists.sipwise.com/listinfo/spce-user<br class=""><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></body></html>