Beta Branch update tools/setio.sh use local ffio settings files

@eva, every time I run tools/setio.sh and after that restart msql, innodb_buffer_pool_instances variable back to 1.

I have added innodb_buffer_pool_instances = 2 and innodb_buffer_pool_size = 2048M

Why is that, and should I leave it on 1 pool instance then?
But with pool size 2GB is better to have 2 instance (1gb each) than 1 instance with 2gb

 

@eva2000
I am talking about variable in my.cnf

 

This is just now, before I run setio tools

Code:

[root@tvor-ocean ~]# INNODB_BPSIZE=$(mysqladmin var | grep 'innodb_buffer_pool_size' | tr -s ' ' | awk '{print $4}')
[root@tvor-ocean ~]# CPUS=$(grep -c "processor" /proc/cpuinfo)
[root@tvor-ocean ~]# echo $CPUS
2
[root@tvor-ocean ~]# echo "$INNODB_BPSIZE/$CPUS" | bc
1073741824

This is after I run setio and restart mysql

Code:

[root@tvor-ocean ~]# /usr/local/src/centminmod/tools/setio.sh set
Full Reads: 93090
Full Writes: 9041

set innodb_flush_neighbors = 0

innodb_io_capacity = 1100

+------------------------+-------+
/etc/my.cnf adjustment
+------------------------+-------+
existing value:
Variable_name   Value
innodb_io_capacity      900
innodb_io_capacity_max  1800
new value:
Variable_name   Value
innodb_io_capacity      1100
innodb_io_capacity_max  2200

+------------------------+-------+
innodb io threads adjustment
+------------------------+-------+
existing value:
+------------------------+-------+
innodb_read_io_threads = 2
innodb_write_io_threads = 2
+------------------------+-------+
new value:
+------------------------+-------+
innodb_read_io_threads = 2
innodb_write_io_threads = 2
+------------------------+-------+

Restart MySQL server for io thread changes

You have new mail in /var/spool/mail/root
[root@tvor-ocean ~]# mysqlrestart
Restarting mysql (via systemctl):                          [  OK  ]
[root@tvor-ocean ~]# INNODB_BPSIZE=$(mysqladmin var | grep 'innodb_buffer_pool_size' | tr -s ' ' | awk '{print $4}')
[root@tvor-ocean ~]# CPUS=$(grep -c "processor" /proc/cpuinfo)
[root@tvor-ocean ~]# echo $CPUS
2
[root@tvor-ocean ~]# echo "$INNODB_BPSIZE/$CPUS" | bc
1073741824

But my.cnf automatically back variable innodb_buffer_pool_instances to from 2 to 1

Code:

# cat /etc/my.cnf
[client]
socket=/var/lib/mysql/mysql.sock

[mysql]
max_allowed_packet = 64M

[mysqld]
local-infile=0
ignore-db-dir=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# vbtechsupport.com

tmpdir=/home/mysqltmp

innodb=ON
#skip-federated
#skip-pbxt
#skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 512
max_connections = 500
key_buffer_size = 128M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
join_buffer_size = 768K
read_buffer_size = 256K
sort_buffer_size = 256K
table_definition_cache = 8192
table_open_cache = 4096
thread_cache_size = 256
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 64M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
# query_cache boost for MariaDB >10.1.2+
# https://community.centminmod.com/posts/30811/
query_cache_limit = 1024K
query_cache_size = 256M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
log-error=/var/log/mysqld.log

# innodb settings
innodb_large_prefix=1
innodb_purge_threads = 1
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 2048M

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
innodb_buffer_pool_instances = 1

innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1
....

 

Now it stays on 2
Hope it will in future.

 

Same problem again @eva2000
After run tools/setio.sh and restart mysql, my.cnf automatically back variable innodb_buffer_pool_instances to from 2 to 1

 

Here is the output.
After run tools/setio.sh and restart mysql, I manually back variable innodb_buffer_pool_instances to from 1 to 2

Code:

[root@upcloud ~]# INNODB_BPSIZE=$(mysqladmin var | grep 'innodb_buffer_pool_size' | tr -s ' ' | awk '{print $4}')
[root@upcloud ~]# CPUS=$(grep -c "processor" /proc/cpuinfo)
[root@upcloud ~]# echo $CPUS
4
[root@upcloud ~]# echo "$INNODB_BPSIZE/$CPUS" | bc
536870912

 

But my database is > 800MB
I added 2GB ram to database buffer pool and regarding that make 2 pools instances.

Is it not better to have 2 pools each with 1GB, than 1 pool with 2GB

 

Centmin updated.
Same thing happened again. Revert innodb_buffer_pool_instances from 2 to 1

before

Code:

# innodb settings
innodb_large_prefix=1
innodb_purge_threads = 2
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 2048M

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
innodb_buffer_pool_instances = 2

After run setio.sh

Code:

# innodb settings
innodb_large_prefix=1
innodb_purge_threads = 2
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 2048M

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
innodb_buffer_pool_instances = 1

 

Working

 

XF1.5 to XF 2.1 Upgrade will love this new server.