MySQL Bugs: #78273: Make innodb_sort_buffer

Bug #78273	Make innodb_sort_buffer_size dynamic
Submitted:	30 Aug 2015 8:08	Modified:	16 Sep 2015 6:26
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	5.7.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
innodb_sort_buffer_size is a static configuration setting so changing it is intrusive.
Figuring out the best setting is going to be difficult if you have to restart mysqld each time you do this.

See:
* bug#78270
* bug#78272

How to repeat:
root@localhost [performance_schema]> set global  innodb_sort_buffer_size = 2 * @@innodb_sort_buffer_size;
ERROR 1238 (HY000): Variable 'innodb_sort_buffer_size' is a read only variable
root@localhost [performance_schema]> select @@version;
+--------------+
| @@version    |
+--------------+
| 5.7.7-rc-log |
+--------------+
1 row in set (0.00 sec)

Suggested fix:
(1) Make innodb_sort_buffer_size dynamic

(2) provide [better?] metrics on usage of this buffer.

I_S.innodb_metrics ddl_sort_file_alter_table seems to provide this data but by default it's disabled. Perhaps it's worthwhile enabling this setting by default?

If other P_S tables provide information about memory and usage related to this then perhaps it's worth adding documentation so we know where to look. This would help us then monitor and be able to tune the setting to get best performance out of the server.

Hello Simon,

Thank you for the feature request.

Thanks,
Umesh

Hi, 

Thanks for this FR

I believe this can be closed as duplicate of Bug #77626. I'd suggest that point 2 (the one about providing better metrics on innodb sort buffer usage) is included in a new FR. This way we can have a chance to separate the two issues 

Thanks again for the feedback,

Please close this then.
I've filed bug#78448 as a FR for the extra metrics to be provided on the sort buffer.

Marking this as duplicate of Bug #77626