MySQL Bugs: #67997: Make changing backlog dynamic in mysqld (even if it's a little disruptive

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:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	5.6.11	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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

Peter,
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.

Thank you for the feature request.

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
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_write_io_threa...

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.

FWIW, adjusting the backlog is not actually disruptive at all. To adjust the backlog simply call listen() on the socket again.

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.