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

[Voip support]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20200109/e9267c64/attachment.html>