Bug #67997 Make changing backlog dynamic in mysqld (even if it's a little disruptive
Submitted: 31 Dec 2012 12:58 Modified: 31 Dec 2012 18:43
Reporter: Simon Mudd Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Options Severity:S4 (Feature request)
Version:5.6.11 OS:Any
Assigned to: CPU Architecture:Any

[31 Dec 2012 12:58] Simon Mudd
See: http://www.mysqlperformanceblog.com/2012/01/06/mysql-high-number-connections-per-secon/

and the comment:

--- snip ---
Olivier Doucet says: January 6, 2012 at 12:14 pm

There is a difference between recreating socket (a few milliseconds of downtime), and having to restart MySQL (can take several minutes to stop depending on configuration, and some time after restart to get it working smoothly).
--- snip ---

and also 5.6's settings: http://dev.mysql.com/doc/refman/5.6/en//server-system-variables.html#sysvar_back_log

The value is not dynamic. I'd agree here that having a dynamic setting, EVEN if it is _slightly_ disruptive is better than having to bounce mysqld which is much more disruptive.

How to repeat:
Try to change the backlog dynamically. you can't.

Suggested fix:
Make the setting dynamic. Make it clear in the documentation that this may be slightly disruptive but is better than bouncing mysqld.
[31 Dec 2012 13:08] MySQL Verification Team
Thank you for the feature request.
[31 Dec 2012 18:25] Simon Mudd
Note: this issue is really not just about the backlog adjustment. There are several other non-dynamic settings in MySQL which if adjusted are "disruptive" but much less so than bouncing mysqld to achieve the same result.

2 that come to mind right now are:

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_read_io_thread... and

which I've already put in a feature request for to Oracle but haven't made it to 5.6.

So I think for those of us who really prefer to not have to shutdown mysqld, addressing those variables which are now static but could be dynamic even if this might trigger a "stall" in mysqld (as would be the case I'd guess for the innodb io threads) or even "some disruption" as would be the case of the backlog change, that's way better than the alternative of restarting the server.

I'll see if I can find other examples that I've bumped into in the past which might be appropriate and perhaps add them here, though I'm not sure if this is the appropriate place for that. Please advise.
[1 Jan 2013 16:58] Davi Arnaut
FWIW, adjusting the backlog is not actually disruptive at all. To adjust the backlog simply call listen() on the socket again.
[2 Jun 2013 8:39] Simon Mudd
Related: http://blog.mozilla.org/it/2013/05/31/ulimits-and-upgrading-from-oracle-mysql-5-0-to-perco...

Basically if there are some settings that can be configured dynamically do so.
Several ulimit settings fit this category.