Hi all, I would like to share the knowledge and recommended mysql variables for tune your mysql server in the right way and easy to use as below:

login as root user of mysql in command line
#mysql -u root -p

view by select the variable as:

select @@GLOBAL.default_storage_engine;

select @@GLOBAL.innodb_buffer_pool_size;

select @@GLOBAL.innodb_log_file_size;

select @@GLOBAL.innodb_flush_log_at_trx_commit;

select @@GLOBAL.sync_binlog;

select @@GLOBAL.innodb_flush_method;

select @@GLOBAL.innodb_buffer_pool_instances;

select @@GLOBAL.innodb_thread_concurrency;

select @@GLOBAL.skip_name_resolve;

select @@GLOBAL.innodb_io_capacity;

select @@GLOBAL.innodb_io_capacity_max;

select @@GLOBAL.innodb_stats_on_metadata;

select @@GLOBAL.innodb_buffer_pool_dump_at_shutdown;

select @@GLOBAL.innodb_buffer_pool_load_at_startup;

select @@GLOBAL.innodb_buffer_pool_dump_pct;

select @@GLOBAL.innodb_adaptive_hash_index_parts;

select @@GLOBAL.query_cache_type;

select @@GLOBAL.query_cache_size;

select @@GLOBAL.innodb_log_checksums;

select @@GLOBAL.innodb_checksum_algorithm;

select @@GLOBAL.table_open_cache_instances;

select @@GLOBAL.innodb_read_io_threads;

select @@GLOBAL.innodb_write_io_threads;
3. set value of avariables as global setting

SET @@GLOBAL.default_storage_engine = InnoDB;

SET @@GLOBAL.innodb_buffer_pool_size = 8589934592;

SET @@GLOBAL.innodb_log_file_size = 268435456;

SET @@GLOBAL.innodb_flush_log_at_trx_commit = 1;

SET @@GLOBAL.sync_binlog = 1;

SET @@GLOBAL.innodb_flush_method = O_DIRECT;

SET @@GLOBAL.innodb_buffer_pool_instances = 8;

SET @@GLOBAL.innodb_thread_concurrency = 16;

SET @@GLOBAL.skip_name_resolve = 1;

SET @@GLOBAL.innodb_io_capacity = 4000;
10.1. SET @@GLOBAL.innodb_io_capacity_max = 10000;

SET @@GLOBAL.innodb_stats_on_metadata = 1;

SET @@GLOBAL.innodb_buffer_pool_dump_at_shutdown = 1;
12.1. SET @@GLOBAL.innodb_buffer_pool_load_at_startup = 1;
12.2. SET @@GLOBAL.innodb_buffer_pool_dump_pct = 25;

SET @@GLOBAL.innodb_adaptive_hash_index_parts = 8;

SET @@GLOBAL.query_cache_type = 1;
14.1. SET @@GLOBAL.query_cache_size = 67108864;

SET @@GLOBAL.innodb_log_checksums = 1;
15.1. SET @@GLOBAL.innodb_checksum_algorithm =crc32;

SET @@GLOBAL.table_open_cache_instances = 16;

SET @@GLOBAL.innodb_read_io_threads = 32;
17.1. SET @@GLOBAL.innodb_write_io_threads = 16;

I have some recommendation variable for tune as below:

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

• Previous Entry: Configure MySQL Remote access with SSL Certificate in CentOS7
• Next Entry: Configure any Software RAID in Ubuntu 20.4