MySQL Server Tuning

MySQL 8.0

The default MySQL values fit normally for minor/middle-size installations.

MySQL 8.0 has a feature to adaptively configure InnoDB parameters, it is recommended using this method to figure out a configuration that fits existing system performance. To let MySQL automatically configure InnoDB variables, set the innodb_dedicated_server = ON parameter. The following variables will be configured:

• innodb_buffer_pool_size
• innodb_log_file_size
• innodb_log_files_in_group
• innodb_flush_method

For more information about the automatic configuration and the variables, see: https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html

For larger installations it is recommended checking the MySQL values below that have the largest impact on codebeamer and MySQL performance (the values here are a general examples, it is advised to use the values automatically configured as a starting point):

• innodb_log_files_in_group = 4 - Determines the combined size of all logs. If the available RAM is <2GB, set the value to 2, if it is >2GB, set the value to 4.
• innodb_buffer_pool_instances = 8 (or 1 if innodb_buffer_pool_size < 1GB)
• innodb_buffer_pool_size = 96G - Use the following formula to calculate the value: 0.75*RAM. If the available RAM is 128GB, then 128*0.75 = 96.
• innodb_log_buffer_size = 256M
• innodb_thread_concurrency = X - Using 2x the quantity of cores is recommended.
• innodb_file_per_table = ON
• innodb_stats_on_metadata = OFF
• #innodb_flush_method = O_DIRECT - Commented out by default. Determines the method to flush data to InnoDB data files and logs. Using it can affect I/O throughput. (Recommended values for codebeamer, if needed: O_DIRECT: local/DAS, O_DSYNC: SAN/iSCSI)

The values below should be adjusted depending on the available physical memory (RAM) of the MySQL server (the values here are a general examples, it is advised to use the values automatically configured as a starting point):

• RAM: 2-8GB
  innodb_log_file_size = 128M
• RAM: 8-24GB
  innodb_log_file_size = 256M
• RAM: 24-64GB
  innodb_log_file_size = 512M
• RAM: 64-128GB
  innodb_log_file_size = 768M
• RAM: 128GM+
  innodb_log_file_size = 1024M

If codebeamer and MySQL run on the same server, it is recommended that codebeamer gets 50-60% and MySQL 20-25% of the available RAM.

Parameter values can be changed in MySQL server configuration file. File location can be different depending on the specific installation.

MySQL server configuration file location examples:

• Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
• CentOS: /etc/my.cnf
• Ubuntu Linux: /etc/mysql/mysql.conf.d/mysqld.cnf

An example, for 128GB RAM (for detailed calculations refer to the explanations above, or the MySQL documentation):

[mysqld]
# some other variables here
innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 96G
innodb_file_per_table = ON
innodb_log_file_size = 768M
innodb_log_files_in_group = 4
innodb_log_buffer_size = 256M
innodb_thread_concurrency = 8 #in case of a 4-core system
innodb_stats_on_metadata = OFF
#innodb_flush_method = O_DIRECT

Please refer to https://dev.mysql.com/doc/refman/8.0/en/optimization.html for further configuration options.

Occasionally it is advised to check and reorganize the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. MySQL's OPTIMIZE TABLE statement can be used on tables to achieve this. It is advised to do it offline, without running codebeamer instance.

An example in MySQL console:

mysql> OPTIMIZE TABLE object_reference;
mysql> OPTIMIZE TABLE object;
mysql> OPTIMIZE TABLE object_revision;
mysql> OPTIMIZE TABLE task;
mysql> OPTIMIZE TABLE task_type;

Please refer to https://dev.mysql.com/doc/refman/8.0/en/optimization.html for further options.

MySQL 5.7

The default MySQL values fit normally for minor/middle-size installations.

For larger installations it is recommended checking the MySQL values below that have the largest impact on codebeamer and MySQL performance:

• query_cache_size = 0
• query_cache_type = 0
• innodb_buffer_pool_instances = 8 (or 1 if innodb_buffer_pool_size < 1GB)
• innodb_file_per_table = ON
• innodb_stats_on_metadata = OFF

The values below should be adjusted depending on the available physical memory (RAM) of the MySQL server:

• RAM: 4GB
  innodb_buffer_pool_size = 2G
  innodb_log_file_size = 256M
• RAM: 8GB
  innodb_buffer_pool_size = 5G
  innodb_log_file_size = 512M
• RAM: 16GB
  innodb_buffer_pool_size = 10G
  innodb_log_file_size = 1GB

The values above are examples for dedicated MySQL servers (no other services are running on the server).

If codebeamer and MySQL run on the same server, it is recommended that codebeamer gets 50-60% and MySQL 20-25% of the available RAM.

Parameter values can be changed in MySQL server configuration file. File location can be different depending on the specific installation.

MySQL server configuration file location examples:

• Windows: C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
• CentOS: /etc/my.cnf
• Ubuntu Linux: /etc/mysql/mysql.conf.d/mysqld.cnf

An example:

[mysqld]
# some other variables here
query_cache_size = 0
query_cache_type = 0
innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 5G
innodb_file_per_table = ON
innodb_log_file_size = 512M
innodb_stats_on_metadata = OFF

Please refer to https://dev.mysql.com/doc/refman/8.0/en/optimization.html for further configuration options.

Occasionally it is advised to check and reorganize the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. MySQL's OPTIMIZE TABLE statement can be used on tables to achieve this. It is advised to do it offline, without running codebeamer instance.

An example in MySQL console:

mysql> OPTIMIZE TABLE object_reference;
mysql> OPTIMIZE TABLE object;
mysql> OPTIMIZE TABLE object_revision;
mysql> OPTIMIZE TABLE task;
mysql> OPTIMIZE TABLE task_type;

Please refer to https://dev.mysql.com/doc/refman/8.0/en/optimization.html for further options.