[OpenSIPS-Devel] Location table structure - account_contact_idx

[Donat Zenichev]

No MySQL gurus here? : (
Excuse me for firing a barrage of questions over there, but I still demand
an answer.
Thanks ever so much.

On Mon, Sep 30, 2019 at 4:26 PM Donat Zenichev <donat.zenichev at gmail.com>
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?
>
> 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
>
>

-- 

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