[OpenSIPS-Devel] Location table structure - account_contact_idx

[Donat Zenichev]

Hi Liviu!
And first of all thanks for your attention.

"Could you paste your usrloc/registrar module settings?" - here they are:
loadmodule "usrloc.so"
modparam("usrloc", "nat_bflag", "NAT1")
modparam("usrloc", "working_mode_preset", "sql-only")
modparam("usrloc", "use_domain", 1)

loadmodule "registrar.so"
modparam("registrar", "default_expires", 3600)
modparam("registrar", "tcp_persistent_flag", "TCP_PERSISTENT")
modparam("registrar", "max_contacts", 10)

"The first thing I'm trying to establish is whether you considered the
sql_write_mode [1] setting  when implementing the requirements of your
platform." -
Unfortunately that time when this system was developed, the newest version
of OpenSIPS was 2.1 branch, that didn't have "sql_write_mode" for usrloc.
But still there was a "db_mode" that had "single-instance-sql-write-back"
option, that wasn't enabled due to no-need for this.

Currently we have 2.4 branch version, but still are working with db_mode
parameter. Is it a bad way of handling locations?

"Depending on your server restart policy and frequency, the optimal
solution could simply be to switch to "write-back" (i.e. async MySQL
operations),
which would help scale your registration throughput by at least an order of
magnitude." - Thanks for your advice, I will consider this and will try out
on a staging system.

I also have one last question, is it a bad practice to include indexes
(unique/constraint keys) into OpenSIPS table structures?
Such as I did with a location table (of OpenSIPS 2.4 version):
"UNIQUE KEY `account_contact_idx`
(`username`,`domain`,`contact`,`callid`,`contact_id`)"

Would this have any superfluous impact on the system?
Have a nice day!


On Wed, Oct 2, 2019 at 11:16 AM Liviu Chircu <liviu at opensips.org> wrote:

> Hey Donat,
>
> Could you paste your usrloc/registrar module settings?  The first thing
> I'm trying
> to establish is whether you considered the sql_write_mode [1] setting
> when implementing
> the requirements of your platform.
>
> Depending on your server restart policy and frequency, the optimal
> solution could simply
> be to switch to "write-back" (i.e. async MySQL operations), which would
> help scale
> your registration throughput by at least an order of magnitude.
>
> Best Regards,
>
> Liviu Chircu
> OpenSIPS Developer
> http://www.opensips-solutions.com
>
> On 30.09.2019 16:26, Donat Zenichev wrote:
> > Seems I found a way out:
> >
> > I added 'contact_id' primary key into a list of sources columns for an
> > unique key, so now it looks as following:
> >   PRIMARY KEY (`contact_id`),
> >   UNIQUE KEY `account_contact_idx`
> > (`username`,`domain`,`contact`,`callid`,`contact_id`)
> >
> > It didn't increase the loading on the CPU that much, but now I have:
> > - absence of duplicate entries (that were causing 1032 errors when
> > failing over to slave side)
> > - pretty nice capacity, as I had with usual 'account_contact_idx'
> > (that was without 'contact_id' PK)
> >
> > But currently I have some doubts that this will cause some
> > unpredictable behavior.
> > MySQL gurus, any insight on this?
>
> _______________________________________________
> Devel mailing list
> Devel at lists.opensips.org
> http://lists.opensips.org/cgi-bin/mailman/listinfo/devel
>


-- 

Best regards,
Donat Zenichev
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opensips.org/pipermail/devel/attachments/20191002/fb661acf/attachment-0001.html>