As someone else suggested, consider not doing the bulk load so intensively. Perhaps don’t use five parallel processes, but just one.
My article discusses the trade-offs in using more child processes, and how to detect whether a condition of insufficient child processes is encountered (check RecvQ). More workers will handle the database congestion better, but may not make sense the other 99% of the time. — Alex — Sent from mobile, with due apologies for brevity and errors. > On Dec 10, 2019, at 9:56 AM, Ali Taher <[email protected]> wrote: > > Thanks Alex for the detailed explanation, I will check the provided link. > > Meanwhile, i would like to add that the application loading data into the > postges tables uses 5 parallel processes to do so. > > I’m worrying that the delay in response could be due to insufficient workers > available for kamailio to process more calls. > > For that I increased max_parallel_workers to 32, and kamailio children to 22. > > I noticed a positive enhancement after this change , but still in some cases > I have delayed response up to 5 seconds. > > Here is a statistics of calls response during the table loading: > > Response time Number of calls > ———————————————— > 5 secs 2 > ~1 sec 157 > >500ms 231 > >100ms 427 > <100ms 110k > > 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. > > Regards, > Ali > Sent from my iPhone > >> On Dec 10, 2019, at 5:53 PM, Alex Balashov <[email protected]> wrote: >> >> 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 >>> <[email protected]> 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: [email protected] >>> phone: +34669448337 >>> >>> >>>> On Tue, Dec 10, 2019 at 12:41 PM Ali Taher <[email protected]> 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 >>>> [email protected] >>>> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users >>> _______________________________________________ >>> Kamailio (SER) - Users Mailing List >>> [email protected] >>> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users >> _______________________________________________ >> Kamailio (SER) - Users Mailing List >> [email protected] >> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users > _______________________________________________ > Kamailio (SER) - Users Mailing List > [email protected] > https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
_______________________________________________ Kamailio (SER) - Users Mailing List [email protected] https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
