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

[E. Schmidbauer]

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