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: [email protected] phone: +34669448337 On Thu, Jan 9, 2020 at 4:23 PM Sergiu Pojoga <[email protected]> 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 <[email protected]> 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 <[email protected]> >> 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 <[email protected]> >>> 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 >>>> [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
