MariaDB / MySQL Tweaks As Default & Option
The default MySQL config file (/etc/my.cnf) is bare-bones. There are really no options in it. This lead to my innodb-powered sites to suffer near 100% CPU usage on a high load site. I came up with a fix for it that served as good tweaks for both myisam and innodb. Here’s what I came up with:
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
[mysqld]
user= mysql
pid-file= /var/lib/mysql/mysql.pid
socket= /var/lib/mysql/mysql.sock
port= 3306
datadir= /var/lib/mysql
open_files_limit=50000
max_allowed_packet=268435456
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_old_blocks_time=1000
innodb_open_files=5000
key_buffer_size=16M
read_buffer_size=256K
read_rnd_buffer_size=256K
query_cache_size=256M
query_cache_limit=5M
join_buffer_size=4M
sort_buffer_size=4M
max_heap_table_size=64M
tmp_table_size=64M
table_open_cache=4500
table_definition_cache=4000
thread_cache_size=50
[mysqld1]
user= mysql
pid-file= /var/lib/mysql1/mysql.pid
socket= /var/lib/mysql1/mysql.sock
port= 3307
datadir= /var/lib/mysql1
open_files_limit=50000
max_allowed_packet=268435456
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_old_blocks_time=1000
innodb_open_files=5000
key_buffer_size=16M
read_buffer_size=256K
read_rnd_buffer_size=256K
query_cache_size=256M
query_cache_limit=5M
join_buffer_size=4M
sort_buffer_size=4M
max_heap_table_size=64M
tmp_table_size=64M
table_open_cache=4500
table_definition_cache=4000
thread_cache_size=50
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.
1.2MySQL 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;
Comments
Post a Comment