MySQL 8.0: MTR Configurations to Be Set to Server Defaults Where Possible

MySQL Test Run or MTR for short, is a MySQL test program. It was developed to ensure that the MySQL server’s operation is as expected whether it be in terms of testing the functionality of new features or integrity of the old. There are suites of existing tests which are to be run whenever a change is introduced in any of the components of the server to see if there are any side effects as a direct result of the change.

MTR test cases mainly contain SQL statements which will be executed against the server, and produce results that can be verified. Since the intention of these tests is to emulate the client interaction with the MySQL server, the configurations of the server started by MTR should ideally be the same as that of a default MySQL server. However, the my.cnf configuration file which MTR uses, used to set some of the server system variables to non-default values rather than the server default values. This configuration was prevalent for many years, the reason for which could have been the lack of powerful virtual machines like the ones which are available today. Over the years, a lot of investment has gone into building the testing team for MySQL, both in man power and machine power. What started out as a handful of engineers has now grown into a fully fledged team and the machines available have commendable specifications. With each machine consisting of large RAM disks and processors of up to 128 cores, resources are available to run thousands of tests on various platforms, a fact that proved to be the driving force behind this worklog.

WL#8795 set out to peruse the my.cnf config file used by MTR and change the settings of server system variables to their defaults where ever it was possible. In addition to this, the worklog attempted to find the reason behind some of these non-default settings.

This blog will talk about the variables and their settings which were present in the config file.
Some variables were already set to the server default values:

1. open-files-limit = 1024
2. local-infile
3. character-set-server = latin1
4. sort_buffer_size = 256K
5. loose-innodb_log_files_in_group = 2

The following variables were set to non-default values previously but have been modified such that they are now set to the server default values:

1. key_buffer_size = 1M
Since increasing this value provides better index handling for all reads and multiple writes, the value was changed to the default value of 8M in the config file. The maximum value of this variable depends on the availability of physical RAM and per-process RAM limits of the machine. That is probably why it was set to a lower value before.

2. max_heap_table_size = 1M
The value was changed to the default value 16M. This value sets the maximum size to which MEMORY tables are allowed to grow and since machines have larger RAM disks now, the default setting would not cause any adverse affects.

3. loose-innodb_data_file_path = ibdata1:10M:autoextend
This variable was set to the server default value of ibdata1:12M:autoextend, where 12M is the size of the ibdata file. Machines now can handle the increase in the size of the files.

4. loose-innodb_lru_scan_depth = 100
This variable value was changed to the server default value of 1024.

5. loose-innodb_write_io_threads = 2 and loose-innodb_read_io_threads = 2
These variables were set to the default value of 4. However for replication tests, they were set to 2 so that the aio-max-nr limit of the machine is not exceeded due to the increased number of concurrent mysqld instances when MTR runs replication tests with a parallel value of 4 or more.

6. loose-innodb_log_buffer_size = 1M
With a larger innodb_log_buffer_size, large transactions can run without having to write the log to disk before committing. This reduces the disk I/O, and hence provided another motive for this variable to be set to its default value of 16M.

7. binlog-direct-non-transactional-updates
The default value is OFF. Turning this variable ON causes updates to non-transactional tables to be written directly to the binary log instead of the transaction cache. This resolves any concurrency issue when transactions update both transactional and non-transactional tables.
It was turned OFF in the config file, but it was set to ON in some tests which contained transactions that update both MyISAM and InnoDB tables so that statements would be written directly to the binary log, to avoid any inconsistency in the slave caused by concurrency issues.

8. slave-net-timeout = 120
After setting this variable to the default value of 60, instead of 120, no failures were seen.

There were some variables which could not be set to the default value and this was discovered while the worklog was in progress.

1. connect-timeout = 60
The default value is 10 but it needs to be set to 60 to avoid intermittent disconnects when test servers are put under load. This issue can be seen in Bug #28359.

2. log-bin-trust-function-creators = 1
When binary logging is enabled, this variable controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log. Hence this variable is set to 1 so that no restrictions are enforced on stored function creation.

3. log-bin = mysqld-bin
Binlogging is OFF by default, but it is necessary to set it to ON for replication tests which have multiple mysqld servers. The config file for the replication suite sets it to ON only for two servers, the master and slave. However there might be more than two servers started in a particular test and they all need binlogging to be enabled. Other tests which have replication scenarios also require this variable to be ON.

4. loose-innodb_buffer_pool_size = 24M
This variable was changed to the default value of 128M, and tested. This change along with the increase in innodb-log-file-size caused the disk usage (/dev/shm) to increase while running the standard MTR suite with the –mem option. The –mem option is used to run the test suite in memory so that the run takes lesser time. This resulted in valgrind runs crashing. Therefore, these two variables were set to non-default values because the RAM disk could not handle it.

5. loose-innodb_log_file_size = 5M
This variable specifies the size of each redo log file in a log group. It was tested with the default value of 48M, but set back to 5M due to the reason mentioned above.

Apart from a few variables which are required to be set to non-default values in the config file due to environmental reasons, the rest were set to the server defaults. Now, MTR runs all the test suites by starting a server whose settings are as close as possible to the default MySQL server. MTR is known to be a salient tool for testing the MySQL code, and ensuring that the testing happens in an environment similar to the community version, provides better odds of finding bugs before the user encounters them.

About Deepa Dixit

Deepa Dixit has been part of the MySQL team since 2015. She is part of the ServerQA team, and works on assuring the quality of the new features added to the optimizer.

6 thoughts on “MySQL 8.0: MTR Configurations to Be Set to Server Defaults Where Possible

  1. loose-innodb* configuration settings feels wrong to me.

    Oracle has indicated that InnoDB is the primary engine and is no longer optional so I do not understand why any innodb option should be configured as loose if that is true.

    The need to support settings like this seems to be when configuration settings change, are added or removed during a minor version release, something I think that should not happen frequently, and in the case of InnoDB would seem less helpful.

    Maybe this is to support upgrades from one major version to another not causing breakage but again this feels like a workaround when something better is needed.

    If there is a need to handle plugins, their configuration settings in such a way as to avoid such issues I wonder if we could come up with something which is somewhat cleaner than simply allowing any incorrect loose-whatever = something type setting to go into the configuration unnoticed without causing issue, or achieving the intention that the DBA had actually wanted. Potentially this type of mis-configuration going unnoticed could be quite dangerous so I would prefer to avoid it.

    Especially in testing where you have complete control over the software and environment being tested this really should be unnecessary. In a production environment while not ideal I can see that this may help in some cases.

    I certainly try to ensure that I configure the servers I manage in a way that the configuration is correct for the specific version of MySQL I am using. This can get quite tricky and more so when you take into account the various flavours of MySQL out there where configuration differences exist and need to be taken into account.

    The six settings mentioned here thus feel wrong. It would be interesting to understand the reasoning for these settings and why they are necessary.

    1. Hi Simon,

      Thank you for your feedback. You are right, the ‘loose’ prefix should be removed. It was added before because InnoDB was not the default storage engine which MySQL used, and the setting should have been changed after InnoDB became the primary storage engine.

      Thank you for pointing this out and as you said, using the ‘loose’ prefix in a controlled testing environment should be avoided because any incorrect setting could go unnoticed. The change in the settings will be made.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.