MySQL Configuration Files not optimal

[morgo]

Overview of the Issue

I reviewed the config files in ./config/mycnf, and I think some of the configuration changes it makes are not optimal.. possibly dangerous in some cases.

Reproduction Steps

Let me use default-fast.cnf as an example, since it has the most problems:

• sql_mode = STRICT_TRANS_TABLES -> This is on by default in MySQL 5.7+, but the problem is setting it here disables other modes in MySQL 5.7 such as NO_AUTO_CREATE_USER: which introduces a security risk. Unless the version is 5.6, the default sql_mode should be used.

• back_log = 50 -> MySQL has an autosizing formula by default. I think we should not set this, since the formula will autotune as other variables like max_connections change.

• binlog_format = STATEMENT -> We only support format = ROW, and this is later overwritten by another config file. Row is also the default in 5.7+ I don't recommend either setting variables to later be overwritten in the file (confusing) or setting variables to default (adds maintenance effort). So we should only need to set this in 5.6 or MariaDB.

• character_set_server and collation_server -> This is the 3 byte utf8, which is deprecated. It is recommended to use the 4 byte, which is the default in MySQL 8.0. Because of backwards compatibility, maybe it makes sense to allow 3 byte in 5.6 and 5.7, but it should not be used in 8.0.

• innodb_autoextend_increment = 1 this was a problem in earlier versions of MySQL where extending the file was blocking, it is now async. I think we should now use default to reduce fragmentation.

• innodb_data_file_path = ibdata1:10M:autoextend MySQL 8.0 requires 12M minimum. This should just be left default.

• innodb_file_per_table This is the default 5.6+ anyway (remove from config)

• innodb_flush_method = O_DIRECT This uses direct (unbuffered IO). The reason MySQL leaves the default to buffered is it can not rely on innodb_buffer_pool_size to configured correctly, so being able to use filesystem cache is really useful to improve performance for untuned systems.

• innodb_log_file_size = 4M this is too small. I recommend just leaving at default. It will increase first start times, but the problem is InnoDB behaves differently with small log files. The maximum transaction size is 10% of log capacity, so it could cause false positive failures very easily.

• innodb_max_dirty_pages_pct = 75 I recommend leaving this default. There are better protections now to protect against a full buffer pool (innodb_max_dirty_pages_pct_lwm).

• innodb_thread_concurrency = 2 this is not recommended to set for most workloads. It was useful in the past where InnoDB could not scale well on multiple cores, but it has overhead to do the accounting - and prevents vitess from using all the cores on my 16 thread machine.

• key_buffer_size = 2M I am fine with this, but it is not much of a memory saving from leaving it default (8M) and simplifying config files.

• max_allowed_packet = 16M this is the allocated up to size, not a pre-alloc size, so I recommend just leaving default. The minimum size is another setting called net_buffer_length.

• max_connections = 200: this is not that much different from the default (200). Unless there is a specific reason, why don't we just leave default for simplifying the files?

• net_write_timeout=60: this is the default.

• read_buffer_size = 1M this is the default.

• read_rnd_buffer_size=1M this is larger than the default (256K) and only really helps with MyISAM and MRR optimization. I don't know enough about Vitess yet to know if MRR is typical, but I would have assumed not.

• slave_net_timeout=60 this changed to 60 in MySQL 5.7. It is very important for 5.6, so we should put in a 5.6. specific config file.

• sort_buffer_size = 2M It is workload dependent, but I think it's better to leave this default.

• table_open_cache=2048 This defaults to 2000 in MySQL 5.6+ anyway, and 4000 in MySQL 8.0, so i suggest we leave it default.

• thread_cache_size=200 this autoscales by default in 5.6+ so i suggest we leave it default.

• tmp_table_size=32M this defaults to 16M. This change has no impact, since max_heap_table_size was left at 16M (both need to be changed).

• transaction_isolation = REPEATABLE-READ this is the default, so I suggest removing it from the config file.

[morgo]

Here is the error log from MySQL 8.0.16. You can see it complaining about a few configuration options that Vitess sets:

2019-07-15T00:58:47.508666Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2019-07-15T00:58:47.508795Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2019-07-15T00:58:47.508957Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2019-07-15T00:58:47.509060Z 0 [System] [MY-013169] [Server] /home/morgo/opt/mysql/8.0.16/bin/mysqld (mysqld 8.0.16) initializing of server in progress as process 10258
2019-07-15T00:58:47.510756Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2019-07-15T00:58:47.510766Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.

[morgo]

I have arrived at the conclusion that it is very difficult to have one my.cnf that applies to multiple MySQL versions. If you consider a setting such as sql_mode: in MySQL 5.7 and 8.0 it would be nice to leave it as default, but in 5.6 you want to enable STRICT_TRANS_TABLES.

But it is not safe to specify a long list of sql modes, since you do not want to have ONLY_FULL_GROUP_BY enabled in 5.6, but you do want it in 5.7+

Edit: Another example is character set. In MySQL 8.0 it should be left default (to use utf8mb4) since utf8mb3 is deprecated. In MySQL 5.6/5.7 it should be set to utf8.