<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body dir="auto"><span style="-webkit-text-size-adjust: auto;">Ali,</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">If Kamailio is performing a database-bound workload, there's no way to</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">make it respond faster if the database is being slow. Excluding things</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">like caching of route responses, how would that be logically possible?</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">:-)</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">Since we use PostgreSQL (and prefix_range) very extensively, I can say</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">that such extreme slow-downs when loading data are abnormal; relational</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">databases in general, and Postgres's MVCC in particular, are</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">specifically designed to deal with concurrently servicing intensive read</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">operations amidst bulk writes. Speculating purely a priori, these delays</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">are probably caused by very high I/O demand of a slow storage subsystem;</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">you can attempt to ascertain that by running 'iostat -x 1' while loading</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">the new data and looking at percentage utilisation on the relevant</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">storage interface, or with 'iotop' or similar tools.</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">But if the database responds slowly due to high background I/O load,</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">you can't make Kamailio render an answer any faster. About the only</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">thing you can do is to try mitigate the negative effects of this on the</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">SIP level:</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">(1) Send a '100 Trying' pre-emptively before initiating the query; this</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">will dampen the retransmissions that otherwise occur, and whose</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">proliferation could cause a positive feedback loop and deepen your</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">problems in a database slow-down scenario;</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">(2) Do asynchronous processing of the SQL query with t_suspend() /</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">t_continue() -- though, you should carefully weigh the downsides of this</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">given the (small) additional overhead of suspending/resuming every</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">transaction under normal operating conditions.</span><div><br></div><div>These approaches will prevent an overall request processing stall, but certainly not lower response time.<br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">For more background on this topic, consider a look at my blog post on</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">the subject from some years ago:</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"><a href="http://www.evaristesys.com/blog/tuning-kamailio-for-high-throughput-and-performance/" dir="ltr" x-apple-data-detectors="true" x-apple-data-detectors-type="link" x-apple-data-detectors-result="0" style="color: currentcolor;">http://www.evaristesys.com/blog/tuning-kamailio-for-high-throughput-and-performance/</a></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">Hope that helps!</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">Cheers,</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">-- Alex</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">PS. You may wish to escape the value of '$rU' and not lift it directly</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">into your SQL queries, e.g. </span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"><a href="https://www.kamailio.org/wiki/cookbooks/5.3.x/transformations#sescapecommon" dir="ltr" x-apple-data-detectors="true" x-apple-data-detectors-type="link" x-apple-data-detectors-result="1" style="color: currentcolor;">https://www.kamailio.org/wiki/cookbooks/5.3.x/transformations#sescapecommon</a></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"><a href="https://www.kamailio.org/wiki/cookbooks/5.3.x/transformations#sqlval" dir="ltr" x-apple-data-detectors="true" x-apple-data-detectors-type="link" x-apple-data-detectors-result="2" style="color: currentcolor;">https://www.kamailio.org/wiki/cookbooks/5.3.x/transformations#sqlval</a></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;"></span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">Otherwise, you may be exposing yourself to a possible SQL injection</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">attack, i.e. if I get creative with what I put in the user part of the</span><br style="-webkit-text-size-adjust: auto;"><span style="-webkit-text-size-adjust: auto;">request URI in a way that doesn't jam Kamailio's SIP parser.</span><br><br><div dir="ltr">—<div>Sent from mobile, with due apologies for brevity and errors.</div></div><div dir="ltr"><br><blockquote type="cite">On Dec 10, 2019, at 7:04 AM, David Villasmil <david.villasmil.work@gmail.com> wrote:<br><br></blockquote></div><blockquote type="cite"><div dir="ltr"><div dir="ltr">Hello,<div><br></div><div>Have you checked yourself how long PGSQL takes to answer a query from a different host?</div><div>This really doesn't seem Kamalio-related, but PGSQL-related.</div><div><br clear="all"><div><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div>Regards,</div><div><br></div>David Villasmil<div>email: <a href="mailto:david.villasmil.work@gmail.com" target="_blank">david.villasmil.work@gmail.com</a></div><div>phone: +34669448337</div></div></div></div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Dec 10, 2019 at 12:41 PM Ali Taher <<a href="mailto:ataher@vanrise.com">ataher@vanrise.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div lang="EN-US">
<div class="gmail-m_7157677901393123143WordSection1">
<p class="MsoNormal">Hello,<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">I have Kamailio installed as SIP redirect for an SBC to make routing decisions.<u></u><u></u></p>
<p class="MsoNormal">I’m using SQLOps module with postgresql 11.5 where I have multiple tables containing each around 6M record of routing codes:<u></u><u></u></p>
<p class="MsoNormal">Code options<u></u><u></u></p>
<p class="MsoNormal">-----------------------<u></u><u></u></p>
<p class="MsoNormal">392342 sup1|sup2|sup3<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Where code field is of prefix_range data type and has a gist index.<u></u><u></u></p>
<p class="MsoNormal">I’m sending traffic of 300 CPS to SBC which is forwarding it to Kamailio, and Kamailio respond with 300 multiple choice with the routing decision.<u></u><u></u></p>
<p class="MsoNormal">In normal cases, everything is going smoothly where Kamailio repsonds up to 50 ms.<u></u><u></u></p>
<p class="MsoNormal">But during the process of loading data to the routing tables, Kamailio response to SBC is dramatically delayed , where for some calls it takes up to 7 seconds to respond.<u></u><u></u></p>
<p class="MsoNormal">Noting that during this process the data are loaded to staging tables and not to live tables used by Kamailio.
<u></u><u></u></p>
<p class="MsoNormal">Kamailio is installed on a VM with 32GB RAM and 16 vcores.<u></u><u></u></p>
<p class="MsoNormal">Following configuration is used in Kamailio:<u></u><u></u></p>
<p class="MsoNormal">fork=yes<u></u><u></u></p>
<p class="MsoNormal">children=10<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">following are the customized parameters in postgresql.conf<u></u><u></u></p>
<p class="MsoNormal">max_connections = 100<u></u><u></u></p>
<p class="MsoNormal">shared_buffers = 8GB<u></u><u></u></p>
<p class="MsoNormal">effective_cache_size = 24GB<u></u><u></u></p>
<p class="MsoNormal">maintenance_work_mem = 2GB<u></u><u></u></p>
<p class="MsoNormal">checkpoint_completion_target = 0.7<u></u><u></u></p>
<p class="MsoNormal">wal_buffers = 16MB<u></u><u></u></p>
<p class="MsoNormal">default_statistics_target = 100<u></u><u></u></p>
<p class="MsoNormal">random_page_cost = 1.1<u></u><u></u></p>
<p class="MsoNormal">effective_io_concurrency = 200<u></u><u></u></p>
<p class="MsoNormal">work_mem = 5242kB<u></u><u></u></p>
<p class="MsoNormal">min_wal_size = 1GB<u></u><u></u></p>
<p class="MsoNormal">max_wal_size = 2GB<u></u><u></u></p>
<p class="MsoNormal">max_worker_processes = 16<u></u><u></u></p>
<p class="MsoNormal">max_parallel_workers_per_gather = 8<u></u><u></u></p>
<p class="MsoNormal">max_parallel_workers = 16<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div>
_______________________________________________<br>
Kamailio (SER) - Users Mailing List<br>
<a href="mailto:sr-users@lists.kamailio.org" target="_blank">sr-users@lists.kamailio.org</a><br>
<a href="https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users" rel="noreferrer" target="_blank">https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users</a><br>
</blockquote></div>
<span>_______________________________________________</span><br><span>Kamailio (SER) - Users Mailing List</span><br><span>sr-users@lists.kamailio.org</span><br><span>https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users</span><br></div></blockquote></div></body></html>