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@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@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users