Hi Andrei,

From what I have seen in the code this is pretty useless (no offence).

The ping in this particular instance is only executed JUST before a real query is to be issued. i.e. if a query is not used in Kamailio for a couple of hours - the ping is actually never sent...........

what I have noticed is that when the connection is torn down by the firewall, the ping actually hangs (reconnect fails), etc

So, we NEED a proper ping (keepalive) functionality :D

Cheers Jason

On Tue, Sep 22, 2009 at 9:38 AM, Andrei Pelinescu-Onciul andrei@iptel.orgwrote:

On Sep 22, 2009 at 09:28, Jason Penton jason.penton@smilecoms.com wrote:

...

Hi *,

What would be the suggestions regarding having a process / thread that

can

...

be used to ping (keepalive) a connection to a particular db backend. I am specifically looking at coding something for mysql (our firewall is

closing

...

connections to mysql if they are idle for longer than 30mins). I would

like

...

to have a process that would go through all connections and "ping" them every so often. I would imagine each DB impl can either ignore the ping function or actually do something with it. I would imagine this will be useful for a few backend db implementations. Any input before I go ahead?

Doesn't modparam("db_mysql", "ping_interval", 15) work?

Andrei

Hello,

On 22.09.2009 12:46 Uhr, Henning Westerholt wrote:

On Dienstag, 22. September 2009, Jason Penton wrote:

...

Hi Andrei,

From what I have seen in the code this is pretty useless (no offence).

The ping in this particular instance is only executed JUST before a real query is to be issued. i.e. if a query is not used in Kamailio for a

couple

...

of hours - the ping is actually never sent...........

Hi Jason,

you're right that its not executed with a timer or similar, but the mysql_ping() should reconnect to the database if the auto reconnect is activated, according the documentation:

http://dev.mysql.com/doc/refman/5.0/en/mysql-ping.html

...

what I have noticed is that when the connection is torn down by the firewall, the ping actually hangs (reconnect fails), etc

So, we NEED a proper ping (keepalive) functionality :D

This could be implemented with help of the internal timers, just execute periodically the mysql_ping command, for example.

what are you referring as "internal timer" -- the connections are per process, not shared across them, therefore the ping has to be sent from same process.

Cheers, Daniel

On Dienstag, 22. September 2009, Jason Penton wrote:

...

you're right that its not executed with a timer or similar, but the mysql_ping() should reconnect to the database if the auto reconnect is activated, according the documentation:

http://dev.mysql.com/doc/refman/5.0/en/mysql-ping.html

Actually the reconnect doesnt work if your firewall has torn down the connection. You can try this with other mysql clients like - the usual client, toad, etc. Leave your connection idle for about 45 mins and you will see that there's nothing you can do to reconnect.

Hi Jason,

ok, i'd just re-configure the firewall to not be stateful anymore, this is easier as working around in the application IMHO. But perhaps this is not possible in your case.

I have tested this extensively and can only imageine that because the firewall has closed the connection, the reconnect in mysql doesnt work - because the socket has been torn down underneath mysql (at the OS level). So effectively you see a hangin process in Kamailio up to 5 minutes :) which is REALLLLLLLLY bad :D

This is strange, do you've tried to set the timeout_interval parameter? We do regularly tests where we block on network level all connections to the mysql, and it notices this and reports an error to the upper level. Perhaps the mysql version you use not supports this parameter? You find in the mod docs the versions.

Ok so I am going to go ahead with using a timer to ping the mysql cons in the db_pool. Everyone happy with that?

As Daniel already mentioned, you need to ping from the individual process and not the timer process, otherwise it probably will not work.

Regards,

Henning

On Tue, Sep 22, 2009 at 12:55 PM, Jason Penton jason.penton@smilecoms.com wrote:

Hey Henning,

On Tue, Sep 22, 2009 at 12:46 PM, Henning Westerholt henning.westerholt@1und1.de wrote:

...

On Dienstag, 22. September 2009, Jason Penton wrote:

...

Hi Andrei,

From what I have seen in the code this is pretty useless (no offence).

The ping in this particular instance is only executed JUST before a real query is to be issued. i.e. if a query is not used in Kamailio for a couple of hours - the ping is actually never sent...........

Hi Jason,

you're right that its not executed with a timer or similar, but the mysql_ping() should reconnect to the database if the auto reconnect is activated, according the documentation:

http://dev.mysql.com/doc/refman/5.0/en/mysql-ping.html

Actually the reconnect doesnt work if your firewall has torn down the connection. You can try this with other mysql clients like - the usual client, toad, etc. Leave your connection idle for about 45 mins and you will see that there's nothing you can do to reconnect.

I have tested this extensively and can only imageine that because the firewall has closed the connection, the reconnect in mysql doesnt work - because the socket has been torn down underneath mysql (at the OS level). So effectively you see a hangin process in Kamailio up to 5 minutes :) which is REALLLLLLLLY bad :D

I am not sure I understand. The reconnect option in mysql should be able to recover from this. It can reconnect even if you restart the database server. The client library does not really care about the reason why the connection was lost.

One reason why you might be seeing the long interval is because the client does not know that the firewall shut the connection down. It tries to send TCP segments on that connection but they are discarded by the firewall. In that case it takes time until the socket layer notifies the mysqlclient library that the connection was lost.

Ok so I am going to go ahead with using a timer to ping the mysql cons in the db_pool. Everyone happy with that?

It probably won't work this way, because you cannot ping all connections from one process. Every ser/kamailio process opens its own connection to the database and if you want to send a ping on a connection, you have to do it in the process which created the connection.

But that is difficult because most processes are normally suspended in recvfrom if they do not have a SIP message to process.

Jan.