[SR-Users] Huge DB load while using location and registrar module (save()).

Voip support voipexpert0 at gmail.com
Thu Jan 9 22:32:33 CET 2020 

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 at 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 at gmail.com
> phone: +34669448337
>
>
> On Thu, Jan 9, 2020 at 4:23 PM Sergiu Pojoga <pojogas at 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-Constantin.Mierla-Kamailio-Optimizations.pdf
>>
>>
>> Cheers.
>>
>> On Thu, Jan 9, 2020 at 8:45 AM Voip support <voipexpert0 at 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 at 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 at 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 at xxxx
>>>>> ',`instance`=NULL,`reg_id`=0,`server_id`=0,`connection_id`=-1,`keepalive`=1,`contact`='sip:1019 at 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 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
>>>>
>>> _______________________________________________
>>> 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
>>
> _______________________________________________
> 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/20200109/9a75139a/attachment.html>

More information about the sr-users mailing list