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.