Ali Taher <ataher(a)vanrise.com> writes: You say these are being loaded into other tables. That implies that the loading transaction shouldn't interfere with the read, but that's a good thing to really check. I would try two things, both to mitigate the problem as well as perhaps understand. 1) Understand how your loading process uses transactions, and consider if you can make transactions smaller. This might not be a good idea, and it might not have any effect. But if you have one transactions with 6M inserts, and you don't need the atomicity, it might be better to have 600 transactions with 10k inserts. 2) Throttle the loading. You didn't say that you had a performance requirement for loading, and you might do smoeting like insert 1000 records, sleep a second, and repeat. That could take 2h to load, and I'm making up numbers anyway. My point is that if you can live with slower loading, then that may avoid whatever the contention piont is, even if you don't figure out the contention point.

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 <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 <abalashov@evaristesys.com<mailto:abalashov@evaristesys.com>> 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-per… 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@gmail.com<mailto:david.villasmil.work@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@gmail.com<mailto:david.villasmil.work@gmail.com> phone: +34669448337 On Tue, Dec 10, 2019 at 12:41 PM Ali Taher <ataher@vanrise.com<mailto:ataher@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@lists.kamailio.org<mailto:sr-users@lists.kamailio.org> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users _______________________________________________ Kamailio (SER) - Users Mailing List sr-users@lists.kamailio.org<mailto:sr-users@lists.kamailio.org> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users _______________________________________________ Kamailio (SER) - Users Mailing List sr-users@lists.kamailio.org<mailto:sr-users@lists.kamailio.org> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users