Learn about Centmin Mod LEMP Stack today
Become a Member

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

Discussion in 'Centmin Mod Github Commits' started by eva2000, Mar 12, 2016.

  1. eva2000

    eva2000 Administrator Staff Member

    29,732
    6,713
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,023
    Local Time:
    1:52 PM
    Nginx 1.13.x
    MariaDB 5.5
  2. Sunka

    Sunka Active Member

    910
    238
    43
    Oct 31, 2015
    Rijeka, Croatia
    Ratings:
    +385
    Local Time:
    5:52 AM
    Nginx 1.13.3
    MariaDB 10.1.24
    @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
     
  3. Sunka

    Sunka Active Member

    910
    238
    43
    Oct 31, 2015
    Rijeka, Croatia
    Ratings:
    +385
    Local Time:
    5:52 AM
    Nginx 1.13.3
    MariaDB 10.1.24
    @eva2000
    I am talking about variable in my.cnf
     
  4. eva2000

    eva2000 Administrator Staff Member

    29,732
    6,713
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,023
    Local Time:
    1:52 PM
    Nginx 1.13.x
    MariaDB 5.5
    type in SSH these commands what is the output
    Code (Text):
    INNODB_BPSIZE=$(mysqladmin var | grep 'innodb_buffer_pool_size' | tr -s ' ' | awk '{print $4}')
    CPUS=$(grep -c "processor" /proc/cpuinfo)
    echo $CPUS
    echo "$INNODB_BPSIZE/$CPUS" | bc
    
     
  5. Sunka

    Sunka Active Member

    910
    238
    43
    Oct 31, 2015
    Rijeka, Croatia
    Ratings:
    +385
    Local Time:
    5:52 AM
    Nginx 1.13.3
    MariaDB 10.1.24
    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
    ....
     
  6. eva2000

    eva2000 Administrator Staff Member

    29,732
    6,713
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,023
    Local Time:
    1:52 PM
    Nginx 1.13.x
    MariaDB 5.5
    just updated tools/setio.sh, try again :)
     
  7. Sunka

    Sunka Active Member

    910
    238
    43
    Oct 31, 2015
    Rijeka, Croatia
    Ratings:
    +385
    Local Time:
    5:52 AM
    Nginx 1.13.3
    MariaDB 10.1.24
    Now it stays on 2
    Hope it will in future.
     
    • Like Like x 1
  8. Sunka

    Sunka Active Member

    910
    238
    43
    Oct 31, 2015
    Rijeka, Croatia
    Ratings:
    +385
    Local Time:
    5:52 AM
    Nginx 1.13.3
    MariaDB 10.1.24
    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
     
  9. eva2000

    eva2000 Administrator Staff Member

    29,732
    6,713
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,023
    Local Time:
    1:52 PM
    Nginx 1.13.x
    MariaDB 5.5
    what's output for these commands?
    Code (Text):
    INNODB_BPSIZE=$(mysqladmin var | grep 'innodb_buffer_pool_size' | tr -s ' ' | awk '{print $4}')
    CPUS=$(grep -c "processor" /proc/cpuinfo)
    echo $CPUS
    echo "$INNODB_BPSIZE/$CPUS" | bc
    
     
  10. Sunka

    Sunka Active Member

    910
    238
    43
    Oct 31, 2015
    Rijeka, Croatia
    Ratings:
    +385
    Local Time:
    5:52 AM
    Nginx 1.13.3
    MariaDB 10.1.24
    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
     
  11. eva2000

    eva2000 Administrator Staff Member

    29,732
    6,713
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,023
    Local Time:
    1:52 PM
    Nginx 1.13.x
    MariaDB 5.5
    That is correct for the formula used as your "$INNODB_BPSIZE/$CPUS = 536870912 buffer per cpu is less than 1GB (specifically 1073741824 bytes) so innodb_buffer_pool_size is auto tuned to 1 as documentated in official mysql manual recommendations
     
  12. Sunka

    Sunka Active Member

    910
    238
    43
    Oct 31, 2015
    Rijeka, Croatia
    Ratings:
    +385
    Local Time:
    5:52 AM
    Nginx 1.13.3
    MariaDB 10.1.24
    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
     
  13. eva2000

    eva2000 Administrator Staff Member

    29,732
    6,713
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,023
    Local Time:
    1:52 PM
    Nginx 1.13.x
    MariaDB 5.5
  14. eva2000

    eva2000 Administrator Staff Member

    29,732
    6,713
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,023
    Local Time:
    1:52 PM
    Nginx 1.13.x
    MariaDB 5.5
    fyi just made a formula revision to tools/setio.sh so try it out :)
     
  15. Sunka

    Sunka Active Member

    910
    238
    43
    Oct 31, 2015
    Rijeka, Croatia
    Ratings:
    +385
    Local Time:
    5:52 AM
    Nginx 1.13.3
    MariaDB 10.1.24
    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
     
  16. eva2000

    eva2000 Administrator Staff Member

    29,732
    6,713
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,023
    Local Time:
    1:52 PM
    Nginx 1.13.x
    MariaDB 5.5
    looks like i need more work on the formula :)
     
    • Optimistic Optimistic x 1
  17. eva2000

    eva2000 Administrator Staff Member

    29,732
    6,713
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,023
    Local Time:
    1:52 PM
    Nginx 1.13.x
    MariaDB 5.5
    updated tools/setio.sh again :D
     
    • Like Like x 1
  18. Sunka

    Sunka Active Member

    910
    238
    43
    Oct 31, 2015
    Rijeka, Croatia
    Ratings:
    +385
    Local Time:
    5:52 AM
    Nginx 1.13.3
    MariaDB 10.1.24
    Working ;)
     
    • Like Like x 1
  19. eva2000

    eva2000 Administrator Staff Member

    29,732
    6,713
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,023
    Local Time:
    1:52 PM
    Nginx 1.13.x
    MariaDB 5.5
    excellent :D
     
    • Winner Winner x 2