[SR-Users] Huge DB load while using location and registrar module (save()).
David Villasmil
david.villasmil.work at gmail.com
Thu Jan 9 17:39:12 CET 2020
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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20200109/3dffb6c2/attachment.html>
More information about the sr-users
mailing list