MySQL Server Tuning

The default MySQL values fit normally for minor/middle 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

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:

[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.