Huge DB load while using location and registrar module (save()). - sr-users

E. Schmidbauer

9 Jan 9 Jan

2:46 p.m.

is there a particular reason you need the db updated in real-time and "know the count of registered users and the source IP / user-agent / username data." ? if it's for something outside of kamailio, id recommend using a module like evapi to push the data in real-time to an application that can consume it. you can us routes like: route[SAVE_LOCATION] { if (!save("location", "0x04")) { sl_reply_error(); exit; } route(REGISTER_UPDATE_EVENT) } event_route[usrloc:contact-expired] { route(REGISTER_EXPIRE_EVENT); } route[REGISTER_UPDATE_EVENT] { if (reg_fetch_contacts("location", "$fu", "caller")) { $var(i) = 0; while ($var(i) < $(ulc(caller=>count))) { evapi_relay("{\"event\":\"register-update\",\n\"data\":{\"aor\":\"$(ulc(caller=>aor))\",\"addr\":\"$(ulc(caller=>addr)[$var(i)])\"\n}"); $var(i) = $var(i) + 1; } } } route[REGISTER_EXPIRE_EVENT] { xlog("L_INFO", "registration [$ulc(exp=>aor)] expired"); evapi_relay("{\"event\":\"register-expire\",\n\"data\":{\"aor\":\"$ulc(exp=>aor)}\n}"); } On Thu, Jan 9, 2020 at 3:14 AM Voip support <voipexpert0(a)gmail.com> wrote:

...

Dear Community, I am facing an issue with using the usrloc and registrar module to save registration informations in database. It works for me but when tested yesterday with around 1500-1600 users i got a lot of queries on database mostly SELECT. I figured out that changing db_mode to 0 resolves the issue because it works totally in memory (almost no select query on DB). So i think its location module. I tried db_mode=3 and db_mode=2 but both create a lot of load on database. The only function i need is to store some information of REGISTER in DB to know the count of registered users and the source IP / user-agent / username data. It needs to be saved in table on successfull REGISTRATION and removed when registration is expired or somebody UN-REGISTER. When used location module for that there was lots of queries like this (20000-50000 per second): select `contact`,`expires`,`q`,`callid`,`cseq`,`flags`,`cflags`,`user_agent`,`received`,`path`,`socket`,`methods`,`last_modified`,`ruid`,`instance`,`reg_id`,`server_id`,`connection_id`,`keepalive` from `location` where `username`='xxxx' order by q update `location` set `expires`='2020-01-08 18:51:39',`q`=-1.000000 ,`cseq`=2042,`flags`=0,`cflags`=64,`user_agent`='n/a',`received`='sip:xxxx:5060',`path`=NULL,`socket`='udp:xxxx:20051',`methods`=NULL,`last_modified`='2020-01-08 18:50:39',`callid`='1205705227@xxxx ',`instance`=NULL,`reg_id`=0,`server_id`=0,`connection_id`=-1,`keepalive`=1,`contact`='sip:1019@xxx:5060' where `ruid`='uloc-5e1642a3-2b90-0161' Looking up for ideas how to resolve this issue. I was thinking of implementing INSERT UPDATE and DELETE based on SQLops. But how to recognize that REGISTER was expiered? I would like location module to work mostly like in-memory. For each new registration INSERT entry to DB, each REGISTER re-new an UPDATE or DELETE should be triggered. So i would expect for 1000 users around 2000 operations per minute. Best regards, Tom _______________________________________________ Kamailio (SER) - Users Mailing List sr-users(a)lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users

Reply

Sergiu Pojoga

7:17 p.m.

The other option you could do is have usrloc DB in delayed mode. Then, call RPC *ul.flush* in your web app before displaying registration status. http://www.kamailio.org/docs/docbooks/devel/rpc_list/rpc_list.html#ul.flush Not to mention usrloc can be easily optimized by using htable for performance and less stress on DB. https://www.kamailio.org/events/2017-KamailioWorld/Day0/W05-Daniel-Constant… Cheers. On Thu, Jan 9, 2020 at 8:45 AM Voip support <voipexpert0(a)gmail.com> wrote:

...

Thank you for the idea, it looks great. However what application i could use to consume the data and put it in DB. Is this websocket connection? Which when connect to EVAPI address will receive the JSON data from kamailio ? I wanted to show the regsistered clients in an web interface because i use kamaiio as proxy with dispatcher to handle active - passive load balancing. If main server is down we use it to route register and all other requests to secondary server. The issue is when people register over kamailio to our sip server then we dont know the real source IP of client. We want to store this information somewhere for reference. So we have active registrations list and know the login and user-agent and source ip of REGISTER etc... Best regards, Tom czw., 9 sty 2020 o 12:47 E. Schmidbauer <eschmidbauer(a)gmail.com> napisał(a):

is there a particular reason you need the db updated in real-time and "know the count of registered users and the source IP / user-agent / username data." ? if it's for something outside of kamailio, id recommend using a module like evapi to push the data in real-time to an application that can consume it. you can us routes like: route[SAVE_LOCATION] { if (!save("location", "0x04")) { sl_reply_error(); exit; } route(REGISTER_UPDATE_EVENT) } event_route[usrloc:contact-expired] { route(REGISTER_EXPIRE_EVENT); } route[REGISTER_UPDATE_EVENT] { if (reg_fetch_contacts("location", "$fu", "caller")) { $var(i) = 0; while ($var(i) < $(ulc(caller=>count))) { evapi_relay("{\"event\":\"register-update\",\n\"data\":{\"aor\":\"$(ulc(caller=>aor))\",\"addr\":\"$(ulc(caller=>addr)[$var(i)])\"\n}"); $var(i) = $var(i) + 1; } } } route[REGISTER_EXPIRE_EVENT] { xlog("L_INFO", "registration [$ulc(exp=>aor)] expired"); evapi_relay("{\"event\":\"register-expire\",\n\"data\":{\"aor\":\"$ulc(exp=>aor)}\n}"); } On Thu, Jan 9, 2020 at 3:14 AM Voip support <voipexpert0(a)gmail.com> wrote:

Dear Community, I am facing an issue with using the usrloc and registrar module to save registration informations in database. It works for me but when tested yesterday with around 1500-1600 users i got a lot of queries on database mostly SELECT. I figured out that changing db_mode to 0 resolves the issue because it works totally in memory (almost no select query on DB). So i think its location module. I tried db_mode=3 and db_mode=2 but both create a lot of load on database. The only function i need is to store some information of REGISTER in DB to know the count of registered users and the source IP / user-agent / username data. It needs to be saved in table on successfull REGISTRATION and removed when registration is expired or somebody UN-REGISTER. When used location module for that there was lots of queries like this (20000-50000 per second): select `contact`,`expires`,`q`,`callid`,`cseq`,`flags`,`cflags`,`user_agent`,`received`,`path`,`socket`,`methods`,`last_modified`,`ruid`,`instance`,`reg_id`,`server_id`,`connection_id`,`keepalive` from `location` where `username`='xxxx' order by q update `location` set `expires`='2020-01-08 18:51:39',`q`=-1.000000 ,`cseq`=2042,`flags`=0,`cflags`=64,`user_agent`='n/a',`received`='sip:xxxx:5060',`path`=NULL,`socket`='udp:xxxx:20051',`methods`=NULL,`last_modified`='2020-01-08 18:50:39',`callid`='1205705227@xxxx ',`instance`=NULL,`reg_id`=0,`server_id`=0,`connection_id`=-1,`keepalive`=1,`contact`='sip:1019@xxx:5060' where `ruid`='uloc-5e1642a3-2b90-0161' Looking up for ideas how to resolve this issue. I was thinking of implementing INSERT UPDATE and DELETE based on SQLops. But how to recognize that REGISTER was expiered? I would like location module to work mostly like in-memory. For each new registration INSERT entry to DB, each REGISTER re-new an UPDATE or DELETE should be triggered. So i would expect for 1000 users around 2000 operations per minute. Best regards, Tom _______________________________________________ Kamailio (SER) - Users Mailing List sr-users(a)lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users

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

Reply

Voip support

10 Jan 10 Jan

12:32 a.m.

You have right i was looking in wrong place in my mysql server. I get around 200 query per second while 1600 user registered. But mysql shows usage of 10-50 % peak CPU in top command for 4 core CPU. I am trying to get information from memory and save myself on REGISTER (i dont need to update on re-new) and want to delete on Expiry or unregister using SQLOps module. Best Regards, Tom czw., 9 sty 2020 o 17:40 David Villasmil <david.villasmil.work(a)gmail.com> napisał(a):

...

This isn't normal at all. I have ~2500 registered users (tcp) with db_mode 1 and i see around ~300 sql queries per second. Regards, David Villasmil email: david.villasmil.work(a)gmail.com phone: +34669448337 On Thu, Jan 9, 2020 at 4:23 PM Sergiu Pojoga <pojogas(a)gmail.com> wrote:

The other option you could do is have usrloc DB in delayed mode. Then, call RPC *ul.flush* in your web app before displaying registration status. http://www.kamailio.org/docs/docbooks/devel/rpc_list/rpc_list.html#ul.flush Not to mention usrloc can be easily optimized by using htable for performance and less stress on DB. https://www.kamailio.org/events/2017-KamailioWorld/Day0/W05-Daniel-Constant… Cheers. On Thu, Jan 9, 2020 at 8:45 AM Voip support <voipexpert0(a)gmail.com> wrote:

Thank you for the idea, it looks great. However what application i could use to consume the data and put it in DB. Is this websocket connection? Which when connect to EVAPI address will receive the JSON data from kamailio ? I wanted to show the regsistered clients in an web interface because i use kamaiio as proxy with dispatcher to handle active - passive load balancing. If main server is down we use it to route register and all other requests to secondary server. The issue is when people register over kamailio to our sip server then we dont know the real source IP of client. We want to store this information somewhere for reference. So we have active registrations list and know the login and user-agent and source ip of REGISTER etc... Best regards, Tom czw., 9 sty 2020 o 12:47 E. Schmidbauer <eschmidbauer(a)gmail.com> napisał(a):

is there a particular reason you need the db updated in real-time and "know the count of registered users and the source IP / user-agent / username data." ? if it's for something outside of kamailio, id recommend using a module like evapi to push the data in real-time to an application that can consume it. you can us routes like: route[SAVE_LOCATION] { if (!save("location", "0x04")) { sl_reply_error(); exit; } route(REGISTER_UPDATE_EVENT) } event_route[usrloc:contact-expired] { route(REGISTER_EXPIRE_EVENT); } route[REGISTER_UPDATE_EVENT] { if (reg_fetch_contacts("location", "$fu", "caller")) { $var(i) = 0; while ($var(i) < $(ulc(caller=>count))) { evapi_relay("{\"event\":\"register-update\",\n\"data\":{\"aor\":\"$(ulc(caller=>aor))\",\"addr\":\"$(ulc(caller=>addr)[$var(i)])\"\n}"); $var(i) = $var(i) + 1; } } } route[REGISTER_EXPIRE_EVENT] { xlog("L_INFO", "registration [$ulc(exp=>aor)] expired"); evapi_relay("{\"event\":\"register-expire\",\n\"data\":{\"aor\":\"$ulc(exp=>aor)}\n}"); } On Thu, Jan 9, 2020 at 3:14 AM Voip support <voipexpert0(a)gmail.com> wrote: > Dear Community, > > I am facing an issue with using the usrloc and registrar module to > save registration informations in database. > It works for me but when tested yesterday with around 1500-1600 users > i got a lot of queries on database mostly SELECT. I figured out that > changing db_mode to 0 resolves the issue because it works totally in memory > (almost no select query on DB). > So i think its location module. > > I tried db_mode=3 and db_mode=2 but both create a lot of load on > database. > > The only function i need is to store some information of REGISTER in > DB to know the count of registered users and the source IP / user-agent / > username data. > It needs to be saved in table on successfull REGISTRATION and removed > when registration is expired or somebody UN-REGISTER. > > When used location module for that there was lots of queries like this > (20000-50000 per second): > > select > `contact`,`expires`,`q`,`callid`,`cseq`,`flags`,`cflags`,`user_agent`,`received`,`path`,`socket`,`methods`,`last_modified`,`ruid`,`instance`,`reg_id`,`server_id`,`connection_id`,`keepalive` > from `location` where `username`='xxxx' order by q > > update `location` set `expires`='2020-01-08 18:51:39',`q`=-1.000000 > ,`cseq`=2042,`flags`=0,`cflags`=64,`user_agent`='n/a',`received`='sip:xxxx:5060',`path`=NULL,`socket`='udp:xxxx:20051',`methods`=NULL,`last_modified`='2020-01-08 > 18:50:39',`callid`='1205705227@xxxx > ',`instance`=NULL,`reg_id`=0,`server_id`=0,`connection_id`=-1,`keepalive`=1,`contact`='sip:1019@xxx:5060' > where `ruid`='uloc-5e1642a3-2b90-0161' > > Looking up for ideas how to resolve this issue. > I was thinking of implementing INSERT UPDATE and DELETE based on > SQLops. > But how to recognize that REGISTER was expiered? > > I would like location module to work mostly like in-memory. > For each new registration INSERT entry to DB, each REGISTER re-new an > UPDATE or DELETE should be triggered. > So i would expect for 1000 users around 2000 operations per minute. > > Best regards, > Tom > > _______________________________________________ > Kamailio (SER) - Users Mailing List > sr-users(a)lists.kamailio.org > https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users > _______________________________________________ Kamailio (SER) - Users Mailing List sr-users(a)lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users

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

Reply