[OpenSIPS-Devel] Location table structure - account_contact_idx

Donat Zenichev donat.zenichev at gmail.com
Mon Sep 30 09:26:40 EDT 2019


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?

Thanks for your attention in advance!

On Mon, Sep 30, 2019 at 12:38 PM Donat Zenichev <donat.zenichev at gmail.com>
wrote:

> Hi opensips community.
>
> I have a question about an sql structure of the location table.
> I came across a capacity obstacle when working without
> 'account_contact_idx' on the opensips version 2.4+
> I'm completely aware that 'account_contact_idx' was deprecated after the
> 2.1 branch.
>
> What did I do, was setting up of the database structure as for 2.4
> version, but with added 'account_contact_idx' unique key to a location
> table.
> Why did I do this? Because the capacity of the database increased by
> several times.
>
> As an example:
> When having a usual 2.4 version db structure, a sending of 50-60
> registrations per second gives me 2/3 of requests that are re-transmitted.
> When having a location table with 'account_contact_idx' unique key, I can
> send 700+ registrations per second without any re-transmissions occurred.
>
> Why does it happen?
>
> My mysql setup is as following:
> Version - 5.7.27
> engine for opensips database - innodb
>
> binlog_format = mixed  <--- needed to fix a replication errors 1032/1062
> when failing over
> max_connections = 2000
> max_allowed_packet = 16M
> table_open_cache = 2000
> max_binlog_size = 500M
>
>
> It almost works out well with 'account_contact_idx' unique key, but it
> generates lots of duplicate entries, that's I want to deprecate this.
> But I don't know of how to save the same capacity as we have with that
> unique key.
>
> Looking forward to any advice, thanks!
>
> --
>
> Best regards,
> Donat Zenichev
>
>

-- 

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


More information about the Devel mailing list