<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body dir="auto">
Thanks Alex for the detailed explanation, I will check the provided link.
<div><br>
</div>
<div>Meanwhile, i would like to add that the application loading data into the postges tables uses 5 parallel processes to do so.</div>
<div><br>
</div>
<div>I’m worrying that the delay in response could be due to insufficient workers available for kamailio to process more calls.</div>
<div><br>
</div>
<div>For that I increased max_parallel_workers to 32, and kamailio children to 22.</div>
<div><br>
</div>
<div>I noticed a positive enhancement after this change , but still in some cases I have delayed response up to 5 seconds.</div>
<div><br>
</div>
<div>Here is a statistics of calls response during the table loading:</div>
<div><br>
</div>
<div>Response time Number of calls</div>
<div>————————————————</div>
<div> 5 secs 2</div>
<div> ~1 sec 157</div>
<div>
<div> >500ms 231</div>
<div> >100ms 427</div>
<div> <100ms 110k</div>
<div><br>
</div>
<div>I also noticed that the delayed responses occurs mostly at a specific second,while during the whole process of table loading , response is resonnable. So I’m wondering what could be happening during this specific second to make this delay in response,
knowing that the data loading rythm is same during the whole process.</div>
<div><br>
</div>
<div>Regards,</div>
<div>Ali<br>
<div id="AppleMailSignature" dir="ltr">Sent from my iPhone</div>
<div dir="ltr"><br>
On Dec 10, 2019, at 5:53 PM, Alex Balashov <<a href="mailto:abalashov@evaristesys.com">abalashov@evaristesys.com</a>> wrote:<br>
<br>
</div>
<blockquote type="cite">
<div dir="ltr"><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 <<a href="mailto:david.villasmil.work@gmail.com">david.villasmil.work@gmail.com</a>> 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><a href="mailto:sr-users@lists.kamailio.org">sr-users@lists.kamailio.org</a></span><br>
<span><a href="https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users">https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users</a></span><br>
</div>
</blockquote>
</div>
</div>
</blockquote>
<blockquote type="cite">
<div dir="ltr"><span>_______________________________________________</span><br>
<span>Kamailio (SER) - Users Mailing List</span><br>
<span><a href="mailto:sr-users@lists.kamailio.org">sr-users@lists.kamailio.org</a></span><br>
<span><a href="https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users">https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users</a></span><br>
</div>
</blockquote>
</div>
</div>
</body>
</html>