[OpenSIPS-Devel] Location table structure - account_contact_idx

[Donat Zenichev]

Now I understand why does it happen.
I just want to leave here my deduction for others, who will search for the
same information.

Firstly we need to understand a correlation of opensips subscribers
activity and mysql wait_timout parameter.

wait_timeout - this is a timeout responsible for disconnecting idling (not
used) connections after a given timer for that triggers.
so say we have it set to 300 seconds, in case a certain mysql connection
(worker) is not in use during 300 seconds, this gonna be dropped.
Default value is 28800 seconds, that is 8 hours.

While you have a constant flow of subscribers that work with your opensips
system, you have a bunch of mysql connections that send some data and are
not in idle state (for the mysql server), thus the wait timer for such
workers (on the mysql server side) is always updating and never reach it's
end.

When a certain mysql connection triggers it's wait_timeout it's gonna be
dropped by mysql server. (And I guess opensips will re-connect this worker
again?)

Summarizing, it's a completely normal thing when you have re-connections
appearing in you opensips log.
In case you don't want to see them, just play with mysql timers not to lose
any connection from opensips systems, but this has its drawback as well,
like overfilled process-list on  the mysql server side, thus you have to
increase a value of max_connections. And this is not such as good idea.

There are also other useful timers of mysql that can be useful for solving
certain problems:
max_allowed_packet - responsible for the biggest packet size MySQL server
can receive (consider this when you have pretty huge transactions).
net_read_timeout and net_write_timeout - for cases when you have troubles
with your network (delays, packets loss, jitter etc.)
wait_timeout and interactive_timeout - both are responsible for
disconnecting idling sessions (different between this couple is in the
connection type, read mysql manuals).
connect_timeout - the number of seconds that the mysqld server waits for a
connect packet before responding with a Bad handshake.

On Wed, Oct 9, 2019 at 1:43 PM Liviu Chircu <liviu at opensips.org> wrote:

> On 09.10.2019 12:07, Donat Zenichev wrote:
> > Yes 'wait_timout' has its default value of 8 hours. I would try to
> > decrease it, but I guess this can lead to more re-connections.
> > Am I right?
>
> No, it's quite the opposite.  The longer MySQL tolerates inactive
> connections, the less often it will
> have to destroy them.  Try setting "wait_timeout" to 10 seconds from the
> console and play with it,
> you will see what I mean.
>
> On 09.10.2019 12:07, Donat Zenichev wrote:
> > Could you please describe a bit,
> > why this "the more MySQL ops you will make them do" correlates to this
> > "the less reconnects they will have to make" ?
> > Does that mean, that the more job usrloc timer jobs do, the less mysql
> > re-connections I have?
>
> The MySQL "wait_timeout" only tracks inactive connections (no data sent
> by the client).
> As soon as the client sends data on the conn, MySQL will reset its timer
> to "wait_timeout"for that conn.
>
> If your calls are in the air for 4-5 seconds on a 100 Trying, I suggest
> you switch to full DBG logs
> and try to understand which MySQL op you're dealing with, and maybe
> change the settings for that module.
> It cannot be usrloc, since you just switched it to being fully async,
> thanks to "write-back" SQL.
>
> Regards,
>
> Liviu Chircu
> OpenSIPS Developer
> http://www.opensips-solutions.com
>
>
> _______________________________________________
> 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/20191010/de6b8495/attachment.html>