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 <[email protected]> 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: [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 >
_______________________________________________ Kamailio (SER) - Users Mailing List [email protected] https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
