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

[Ali Taher]

Thanks Alex for drawing my attention regarding escaping $rU before using it in sql queries. But I'm not sure where to use sql.val in this case ? Do you mean I can use either {s.escape.common} or {sql.val} ?

Regarding the 100 trying , should I put modparam("tm", "auto_inv_100_reason", "Trying") in the beginning of if (is_method("INVITE"))   block ?

Regards,
Ali Taher

-----Original Message-----
From: sr-users <sr-users-bounces at lists.kamailio.org> On Behalf Of Alex Balashov
Sent: Tuesday, December 10, 2019 11:21 AM
To: Kamailio (SER) - Users Mailing List <sr-users at lists.kamailio.org>
Subject: Re: [SR-Users] Kamailio delayed reponse in case of database load

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.

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.

--
Alex Balashov | Principal | Evariste Systems LLC

Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free)
Web: http://www.evaristesys.com/, http://www.csrpswitch.com/

_______________________________________________
Kamailio (SER) - Users Mailing List
sr-users at lists.kamailio.org
https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users