[SR-Users] Kamailio delayed reponse in case of database load

Alex Balashov abalashov at evaristesys.com
Tue Dec 10 16:45:49 CET 2019


Ali,

If Kamailio is performing a database-bound workload, there's no way to
make it respond faster if the database is being slow. Excluding things
like caching of route responses, how would that be logically possible?
:-)

Since we use PostgreSQL (and prefix_range) very extensively, I can say
that such extreme slow-downs when loading data are abnormal; relational
databases in general, and Postgres's MVCC in particular, are
specifically designed to deal with concurrently servicing intensive read
operations amidst bulk writes. Speculating purely a priori, these delays
are probably caused by very high I/O demand of a slow storage subsystem;
you can attempt to ascertain that by running 'iostat -x 1' while loading
the new data and looking at percentage utilisation on the relevant
storage interface, or with 'iotop' or similar tools.

But if the database responds slowly due to high background I/O load,
you can't make Kamailio render an answer any faster. About the only
thing you can do is to try mitigate the negative effects of this on the
SIP level:

(1) Send a '100 Trying' pre-emptively before initiating the query; this
will dampen the retransmissions that otherwise occur, and whose
proliferation could cause a positive feedback loop and deepen your
problems in a database slow-down scenario;

(2) Do asynchronous processing of the SQL query with t_suspend() /
t_continue() -- though, you should carefully weigh the downsides of this
given the (small) additional overhead of suspending/resuming every
transaction under normal operating conditions.

These approaches will prevent an overall request processing stall, but certainly not lower response time.

For more background on this topic, consider a look at my blog post on
the subject from some years ago:

http://www.evaristesys.com/blog/tuning-kamailio-for-high-throughput-and-performance/

Hope that helps!

Cheers,

-- Alex

PS. You may wish to escape the value of '$rU' and not lift it directly
into your SQL queries, e.g. 

https://www.kamailio.org/wiki/cookbooks/5.3.x/transformations#sescapecommon

https://www.kamailio.org/wiki/cookbooks/5.3.x/transformations#sqlval

Otherwise, you may be exposing yourself to a possible SQL injection
attack, i.e. if I get creative with what I put in the user part of the
request URI in a way that doesn't jam Kamailio's SIP parser.

—
Sent from mobile, with due apologies for brevity and errors.

> On Dec 10, 2019, at 7:04 AM, David Villasmil <david.villasmil.work at gmail.com> wrote:
> 
> 
> Hello,
> 
> Have you checked yourself how long PGSQL takes to answer a query from a different host?
> This really doesn't seem Kamalio-related, but PGSQL-related.
> 
> Regards,
> 
> David Villasmil
> email: david.villasmil.work at gmail.com
> phone: +34669448337
> 
> 
>> On Tue, Dec 10, 2019 at 12:41 PM Ali Taher <ataher at vanrise.com> wrote:
>> Hello,
>> 
>>  
>> 
>> I have Kamailio installed as SIP redirect for an SBC to make routing decisions.
>> 
>> I’m using SQLOps module with postgresql 11.5 where I have multiple tables containing each around 6M record of routing codes:
>> 
>> Code       options
>> 
>> -----------------------
>> 
>> 392342  sup1|sup2|sup3
>> 
>>  
>> 
>> Where code field is of prefix_range data type and has a gist index.
>> 
>> 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.
>> 
>> In normal cases, everything is going smoothly where Kamailio repsonds up to 50 ms.
>> 
>> 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.
>> 
>> Noting that during this process the data are loaded to staging tables and not to live tables used by Kamailio.
>> 
>> Kamailio is installed on a VM with 32GB RAM and 16 vcores.
>> 
>> Following configuration is used in Kamailio:
>> 
>> fork=yes
>> 
>> children=10
>> 
>>  
>> 
>> following are the customized parameters in postgresql.conf
>> 
>> max_connections = 100
>> 
>> shared_buffers = 8GB
>> 
>> effective_cache_size = 24GB
>> 
>> maintenance_work_mem = 2GB
>> 
>> checkpoint_completion_target = 0.7
>> 
>> wal_buffers = 16MB
>> 
>> default_statistics_target = 100
>> 
>> random_page_cost = 1.1
>> 
>> effective_io_concurrency = 200
>> 
>> work_mem = 5242kB
>> 
>> min_wal_size = 1GB
>> 
>> max_wal_size = 2GB
>> 
>> max_worker_processes = 16
>> 
>> max_parallel_workers_per_gather = 8
>> 
>> max_parallel_workers = 16
>> 
>>  
>> 
>> _______________________________________________
>> Kamailio (SER) - Users Mailing List
>> sr-users at lists.kamailio.org
>> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
> _______________________________________________
> Kamailio (SER) - Users Mailing List
> sr-users at lists.kamailio.org
> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20191210/8bb104d3/attachment.html>


More information about the sr-users mailing list