Discover Centmin Mod today
Register Now

Beta Branch Help test innodbio.sh for MySQL Tuning

Discussion in 'Beta release code' started by eva2000, Feb 5, 2016.

  1. SeaTea

    SeaTea Member

    49
    13
    8
    Feb 20, 2015
    the Netherlands
    Ratings:
    +28
    Local Time:
    1:28 PM
    Nginx:1.11
    MariaDB-10
    Another run:
    Code:
    curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
    
    --------------------------------------------------------------------
    System Info (0.1)
    --------------------------------------------------------------------
    Architecture:          x86_64
    CPU op-mode(s):        32-bit, 64-bit
    Byte Order:            Little Endian
    CPU(s):                2
    On-line CPU(s) list:   0,1
    Thread(s) per core:    1
    Core(s) per socket:    1
    Socket(s):             2
    NUMA node(s):          1
    Vendor ID:             GenuineIntel
    CPU family:            6
    Model:                 44
    Model name:            Westmere E56xx/L56xx/X56xx (Nehalem-C)
    Stepping:              1
    CPU MHz:               2199.998
    BogoMIPS:              4399.99
    Hypervisor vendor:     KVM
    Virtualization type:   full
    L1d cache:             32K
    L1i cache:             32K
    L2 cache:              4096K
    NUMA node0 CPU(s):     0,1
    3.10.0-327.4.4.el7.x86_64
    CentOS Linux release 7.2.1511 (Core)
    --------------------------------------------------------------------
    Filesystem                  Type      Size  Used Avail Use% Mounted on
    /dev/mapper/centos_sim-root xfs        50G   12G   39G  23% /
    devtmpfs                    devtmpfs  1.9G     0  1.9G   0% /dev
    tmpfs                       tmpfs     1.9G     0  1.9G   0% /dev/shm
    tmpfs                       tmpfs     1.9G   73M  1.8G   4% /run
    tmpfs                       tmpfs     1.9G     0  1.9G   0% /sys/fs/cgroup
    /dev/mapper/centos_sim-home xfs        96G  3.7G   92G   4% /home
    /dev/loop0                  ext4      3.7G   20M  3.5G   1% /tmp
    /dev/vda1                   xfs       497M  265M  233M  54% /boot
    tmpfs                       tmpfs     380M     0  380M   0% /run/user/0
    tmpfs                       tmpfs     380M     0  380M   0% /run/user/497
    --------------------------------------------------------------------
    
    
    --------------------------------------------------------------------
    ioping 0.9 calculated (IOPs)
    --------------------------------------------------------------------
    innodb_io_capacity = 317
    innodb_io_capacity = 297
    --------------------------------------------------------------------
    426 change to 200
    innodb_io_capacity = 200
    458 change to 200
    innodb_io_capacity = 200
    --------------------------------------------------------------------
    
    --------------------------------------------------------------------
    fio-2.2.8 calculated (IOPs)
    --------------------------------------------------------------------
    
    Full Reads: 3652
    Full Writes: 2514
    innodb_io_capacity = 83
    innodb_io_capacity = 62
    innodb_io_capacity = 50
    innodb_io_capacity = 35
    
    innodb_io_capacity = 502
    
    Full Reads: 3652
    Full Writes: 2578
    innodb_io_capacity = 85
    innodb_io_capacity = 64
    innodb_io_capacity = 51
    innodb_io_capacity = 36
    
    innodb_io_capacity = 515
    
    --------------------------------------------------------------------
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    --------------------------------------------------------------------
    
    My results seem to be very slow compared to others with same RAM and # of CPU if I read the results correctly. Is there a topic where this is discussed ?

     
  2. eva2000

    eva2000 Administrator Staff Member

    54,548
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    10:28 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Thanks.

    My calculation is based on the actual disk performance of your server, so if it's low, means your disk isn't as performant as others reported here. That's why i need to perfect the formula so can dynamically tailor innodb_io_capacity based on the server's measured disk performance.

    working on the actual routine for centmin mod itself now. Going to use fio benchmark derived numbers to formulate the iops value for innodb_io_capacity
    Code:
    ./setio.sh set
    Full Reads: 3778
    Full Writes: 114652
    innodb_io_capacity = 4500
    
    /etc/my.cnf adjustment
    existing value: innodb_io_capacity = 200
    +--------------------+-------+
    | Variable_name      | Value |
    +--------------------+-------+
    | innodb_io_capacity | 200  |
    +--------------------+-------+
    new value: innodb_io_capacity = 4500
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_io_capacity     | 4500  |
    | innodb_io_capacity_max | 9000  |
    +------------------------+-------+
    
     
    Last edited: Feb 5, 2016
  3. SeaTea

    SeaTea Member

    49
    13
    8
    Feb 20, 2015
    the Netherlands
    Ratings:
    +28
    Local Time:
    1:28 PM
    Nginx:1.11
    MariaDB-10
    hmm... My server has SSD disks, so should be fast. But maybe others are faster ;)
     
  4. eva2000

    eva2000 Administrator Staff Member

    54,548
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    10:28 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    yes not all SSDs are created equal :D

    Remember though SATA/SAS 10k IOPs is 75-100 and 100-150 respectively and SAS 15K around 175-250 IOPs which can put SSDs in perspective for the FIO reported FULL Read/Write values
     
  5. pamamolf

    pamamolf Premium Member Premium Member

    4,084
    428
    83
    May 31, 2014
    Ratings:
    +834
    Local Time:
    2:28 PM
    Nginx-1.25.x
    MariaDB 10.3.x
    Code:
    --------------------------------------------------------------------
    System Info (0.1)
    --------------------------------------------------------------------
    Architecture:          x86_64
    CPU op-mode(s):        32-bit, 64-bit
    Byte Order:            Little Endian
    CPU(s):                12
    On-line CPU(s) list:   0-11
    Thread(s) per core:    2
    Core(s) per socket:    6
    Socket(s):             1
    NUMA node(s):          1
    Vendor ID:             GenuineIntel
    CPU family:            6
    Model:                 62
    Stepping:              4
    CPU MHz:               3500.159
    BogoMIPS:              7000.31
    Virtualization:        VT-x
    L1d cache:             32K
    L1i cache:             32K
    L2 cache:              256K
    L3 cache:              12288K
    NUMA node0 CPU(s):     0-11
    2.6.32-573.12.1.el6.x86_64
    CentOS release 6.7 (Final)
    --------------------------------------------------------------------
    Filesystem     Type   Size  Used Avail Use% Mounted on
    /dev/md2       ext4   289G  239G   35G  88% /
    tmpfs          tmpfs   64G     0   64G   0% /dev/shm
    /dev/md0       ext4   488M  151M  313M  33% /boot
    /dev/md1       ext4    64G  157M   63G   1% /tmp
    tmpfs          tmpfs   64G  157M   63G   1% /tmp
    --------------------------------------------------------------------
    
    
    --------------------------------------------------------------------
    ioping 0.8 calculated (IOPs)
    --------------------------------------------------------------------
    innodb_io_capacity = 744
    innodb_io_capacity = 766
    --------------------------------------------------------------------
    426 change to 200
    innodb_io_capacity = 200
    innodb_io_capacity = 670
    --------------------------------------------------------------------
    
    --------------------------------------------------------------------
    fio-2.0.13 calculated (IOPs)
    --------------------------------------------------------------------
    
    Full Reads: 42772
    Full Writes: 8093
    innodb_io_capacity = 269
    innodb_io_capacity = 202
    innodb_io_capacity = 161
    innodb_io_capacity = 115
    
    innodb_io_capacity = 1011
    
    Full Reads: 35429
    Full Writes: 8363
    innodb_io_capacity = 278
    innodb_io_capacity = 209
    innodb_io_capacity = 167
    innodb_io_capacity = 119
    
    innodb_io_capacity = 1045
    
    --------------------------------------------------------------------
    innodb_read_io_threads = 6
    innodb_write_io_threads = 6
    --------------------------------------------------------------------
    Code:
    --------------------------------------------------------------------
    System Info (0.1)
    --------------------------------------------------------------------
    Architecture:          x86_64
    CPU op-mode(s):        32-bit, 64-bit
    Byte Order:            Little Endian
    CPU(s):                32
    On-line CPU(s) list:   0-31
    Thread(s) per core:    2
    Core(s) per socket:    8
    Socket(s):             2
    NUMA node(s):          2
    Vendor ID:             GenuineIntel
    CPU family:            6
    Model:                 63
    Model name:            Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz
    Stepping:              2
    CPU MHz:               1388.156
    BogoMIPS:              5204.16
    Virtualization:        VT-x
    L1d cache:             32K
    L1i cache:             32K
    L2 cache:              256K
    L3 cache:              20480K
    NUMA node0 CPU(s):     0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30
    NUMA node1 CPU(s):     1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31
    3.10.0-327.4.5.el7.x86_64
    CentOS Linux release 7.2.1511 (Core)
    --------------------------------------------------------------------
    Filesystem     Type      Size  Used Avail Use% Mounted on
    /dev/sda2      ext4      922G  3.6G  872G   1% /
    devtmpfs       devtmpfs   95G     0   95G   0% /dev
    tmpfs          tmpfs      95G     0   95G   0% /dev/shm
    tmpfs          tmpfs      95G   34M   95G   1% /run
    tmpfs          tmpfs      95G     0   95G   0% /sys/fs/cgroup
    /dev/sda1      ext4      477M  115M  333M  26% /boot
    tmpfs          tmpfs      19G     0   19G   0% /run/user/0
    --------------------------------------------------------------------
    
    
    --------------------------------------------------------------------
    ioping 0.9 calculated (IOPs)
    --------------------------------------------------------------------
    innodb_io_capacity = 1593
    innodb_io_capacity = 1541
    --------------------------------------------------------------------
    innodb_io_capacity = 3756
    innodb_io_capacity = 3668
    --------------------------------------------------------------------
    
    --------------------------------------------------------------------
    fio-2.2.8 calculated (IOPs)
    --------------------------------------------------------------------
    
    Full Reads: 120470
    Full Writes: 101135
    innodb_io_capacity = 3371
    innodb_io_capacity = 2528
    innodb_io_capacity = 2022
    innodb_io_capacity = 1444
    
    innodb_io_capacity = 4045
    
    Full Reads: 113777
    Full Writes: 102400
    innodb_io_capacity = 3413
    innodb_io_capacity = 2560
    innodb_io_capacity = 2048
    innodb_io_capacity = 1462
    
    innodb_io_capacity = 4096
    
    --------------------------------------------------------------------
    innodb_read_io_threads = 16
    innodb_write_io_threads = 16
    --------------------------------------------------------------------
    Code:
    --------------------------------------------------------------------
    System Info (0.1)
    --------------------------------------------------------------------
    Architecture:          x86_64
    CPU op-mode(s):        32-bit, 64-bit
    Byte Order:            Little Endian
    CPU(s):                4
    On-line CPU(s) list:   0-3
    Thread(s) per core:    2
    Core(s) per socket:    2
    Socket(s):             1
    NUMA node(s):          1
    Vendor ID:             GenuineIntel
    CPU family:            6
    Model:                 42
    Stepping:              7
    CPU MHz:               1688.976
    BogoMIPS:              6784.59
    Virtualization:        VT-x
    L1d cache:             32K
    L1i cache:             32K
    L2 cache:              256K
    L3 cache:              3072K
    NUMA node0 CPU(s):     0-3
    3.14.32-xxxx-grs-ipv6-64
    CentOS release 6.7 (Final)
    --------------------------------------------------------------------
    Filesystem     Type      Size  Used Avail Use% Mounted on
    rootfs         rootfs    481G   49G  408G  11% /
    /dev/root      ext3      481G   49G  408G  11% /
    devtmpfs       devtmpfs  3.9G  344K  3.9G   1% /dev
    /dev/sda3      ext4      1.4T   35G  1.3T   3% /home
    tmpfs          tmpfs     3.9G     0  3.9G   0% /dev/shm
    /dev/loop0     ext4      3.7G  7.7M  3.5G   1% /tmp
    /dev/root      ext3      481G   49G  408G  11% /var/named/chroot/etc/named
    /dev/root      ext3      481G   49G  408G  11% /var/named/chroot/var/named
    /dev/root      ext3      481G   49G  408G  11% /var/named/chroot/etc/named.conf
    /dev/root      ext3      481G   49G  408G  11% /var/named/chroot/etc/named.rfc19                                                                                                                               12.zones
    /dev/root      ext3      481G   49G  408G  11% /var/named/chroot/etc/rndc.key
    /dev/root      ext3      481G   49G  408G  11% /var/named/chroot/usr/lib64/bind
    /dev/root      ext3      481G   49G  408G  11% /var/named/chroot/etc/named.iscdl                                                                                                                               v.key
    /dev/root      ext3      481G   49G  408G  11% /var/named/chroot/etc/named.root.                                                                                                                               key
    --------------------------------------------------------------------
    
    
    --------------------------------------------------------------------
    ioping 0.8 calculated (IOPs)
    --------------------------------------------------------------------
    innodb_io_capacity = 925
    innodb_io_capacity = 1040
    --------------------------------------------------------------------
    innodb_io_capacity = 503
    innodb_io_capacity = 1057
    --------------------------------------------------------------------
    
    --------------------------------------------------------------------
    fio-2.0.13 calculated (IOPs)
    --------------------------------------------------------------------
    
    Full Reads: 453
    Full Writes: 477
    innodb_io_capacity = 15
    innodb_io_capacity = 11
    innodb_io_capacity = 9
    innodb_io_capacity = 6
    
    innodb_io_capacity = 200
    
    Full Reads: 451
    Full Writes: 438
    innodb_io_capacity = 14
    innodb_io_capacity = 10
    innodb_io_capacity = 8
    innodb_io_capacity = 6
    
    innodb_io_capacity = 200
    
    --------------------------------------------------------------------
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    --------------------------------------------------------------------
    Code:
    --------------------------------------------------------------------
    System Info (0.1)
    --------------------------------------------------------------------
    Architecture:          x86_64
    CPU op-mode(s):        32-bit, 64-bit
    Byte Order:            Little Endian
    CPU(s):                8
    On-line CPU(s) list:   0-7
    Thread(s) per core:    2
    Core(s) per socket:    4
    Socket(s):             1
    NUMA node(s):          1
    Vendor ID:             GenuineIntel
    CPU family:            6
    Model:                 58
    Stepping:              9
    CPU MHz:               1600.000
    BogoMIPS:              6785.19
    Virtualization:        VT-x
    L1d cache:             32K
    L1i cache:             32K
    L2 cache:              256K
    L3 cache:              8192K
    NUMA node0 CPU(s):     0-7
    2.6.32-573.12.1.el6.x86_64
    CentOS release 6.7 (Final)
    --------------------------------------------------------------------
    Filesystem     Type   Size  Used Avail Use% Mounted on
    /dev/sda2      ext4   908G   34G  829G   4% /
    tmpfs          tmpfs  7.8G     0  7.8G   0% /dev/shm
    /dev/sda1      ext4   772M  172M  560M  24% /boot
    tmpfs          tmpfs  7.8G  124K  7.8G   1% /tmp
    --------------------------------------------------------------------
    
    
    --------------------------------------------------------------------
    ioping 0.8 calculated (IOPs)
    --------------------------------------------------------------------
    innodb_io_capacity = 672
    innodb_io_capacity = 766
    --------------------------------------------------------------------
    innodb_io_capacity = 1046
    innodb_io_capacity = 1041
    --------------------------------------------------------------------
    
    --------------------------------------------------------------------
    fio-2.0.13 calculated (IOPs)
    --------------------------------------------------------------------
    
    Full Reads: 383
    Full Writes: 290
    innodb_io_capacity = 9
    innodb_io_capacity = 7
    innodb_io_capacity = 5
    innodb_io_capacity = 4
    
    innodb_io_capacity = 150
    
    Full Reads: 453
    Full Writes: 383
    innodb_io_capacity = 12
    innodb_io_capacity = 9
    innodb_io_capacity = 7
    innodb_io_capacity = 5
    
    innodb_io_capacity = 200
    
    --------------------------------------------------------------------
    innodb_read_io_threads = 4
    innodb_write_io_threads = 4
    --------------------------------------------------------------------
     
  6. eva2000

    eva2000 Administrator Staff Member

    54,548
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    10:28 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    Add tools/setio.sh Script



    Update: Feb 6th, 2016. Just added to Centmin Mod 123.09beta01 branch the new tools/setio.sh standalone tool which can set your MySQL server's /etc/my.cnf innodb_io_capacity variable optimally based on your server's measured disk write I/O performance numbers. This is based on the tests and feedback provided by other Centmin Mod users at Beta Branch - Help test innodbio.sh for MySQL Tuning | Centmin Mod Community

    To run setio.sh it's as easy as running this command and it will update /etc/my.cnf innodb_io_capacity variable with the new measured value and on the fly - dynamically set globally the value on the running MariaDB MySQL server. No need to restart MySQL for the setting to register :)

    SSH command after you update to latest Centmin Mod 123.09beta01 code
    Code:
    /usr/local/src/centminmod/tools/setio.sh set
    If on Centmin Mod 123.08stable or lower, you can just grab the tools/setio.sh file itself and download to tools directory to run it as well
    Code:
    wget -O /usr/local/src/centminmod/tools/setio.sh https://github.com/centminmod/centminmod/raw/123.09beta01/tools/setio.sh
    chmod +x /usr/local/src/centminmod/tools/setio.sh
    /usr/local/src/centminmod/tools/setio.sh set
    Commit add tools/setio.sh · centminmod/centminmod@0bbc9be · GitHub
     
  7. SeaTea

    SeaTea Member

    49
    13
    8
    Feb 20, 2015
    the Netherlands
    Ratings:
    +28
    Local Time:
    1:28 PM
    Nginx:1.11
    MariaDB-10
    Works fine !

    How about the innodb_io_capacity_max variable ? Is this set automatically from innodb_io_capacity? I cannot find the max in my.cnf while the screen shows:
    Code:
    [root@dev etc]# /usr/local/src/centminmod/tools/setio.sh set
    Full Reads: 3655
    Full Writes: 2579
    innodb_io_capacity = 500
    
    /etc/my.cnf adjustment
    existing value: innodb_io_capacity = 100
    +--------------------+-------+
    | Variable_name      | Value |
    +--------------------+-------+
    | innodb_io_capacity | 100   |
    +--------------------+-------+
    new value: innodb_io_capacity = 500
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_io_capacity     | 500   |
    | innodb_io_capacity_max | 2000  |
    +------------------------+-------+
    
     
  8. eva2000

    eva2000 Administrator Staff Member

    54,548
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    10:28 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    innodb_io_capacity_max is dynamically set by MySQL based on value of innodb_io_capacity

    looking good :D
     
  9. eva2000

    eva2000 Administrator Staff Member

    54,548
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    10:28 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Just updated tools/setio.sh to specifically set innodb_io_capacity_max variable as well to 2x innodb_io_capacity derived value
    Code:
    Full Reads: 3144
    Full Writes: 117928
    innodb_io_capacity = 4700
    
    /etc/my.cnf adjustment
    existing value: innodb_io_capacity = 4600
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_io_capacity     | 4600  |
    | innodb_io_capacity_max | 9000  |
    +------------------------+-------+
    new value: innodb_io_capacity = 4700
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_io_capacity     | 4700  |
    | innodb_io_capacity_max | 9400  |
    +------------------------+-------+
     
  10. pamamolf

    pamamolf Premium Member Premium Member

    4,084
    428
    83
    May 31, 2014
    Ratings:
    +834
    Local Time:
    2:28 PM
    Nginx-1.25.x
    MariaDB 10.3.x
    What about :

    Code:
    innodb_read_io_threads = ?
    innodb_write_io_threads = ?
     
  11. eva2000

    eva2000 Administrator Staff Member

    54,548
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    10:28 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    that's next up on my to do list :)
     
  12. eva2000

    eva2000 Administrator Staff Member

    54,548
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    10:28 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Updated tools/setio.sh for io threads adjustment and prompt to restart mysql server for changes
    Code:
    Full Reads: 3533
    Full Writes: 116267
    innodb_io_capacity = 4600
    
    +------------------------+-------+
    /etc/my.cnf adjustment
    +------------------------+-------+
    existing value:
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_io_capacity     | 4600  |
    | innodb_io_capacity_max | 9200  |
    +------------------------+-------+
    new value:
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_io_capacity     | 4600  |
    | innodb_io_capacity_max | 9200  |
    +------------------------+-------+
    
    +------------------------+-------+
    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
     
  13. dorobo

    dorobo Active Member

    420
    104
    43
    Jun 6, 2014
    Ratings:
    +162
    Local Time:
    8:28 PM
    latest
    latest
    so which is which :D
     
  14. eva2000

    eva2000 Administrator Staff Member

    54,548
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    10:28 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    no need to restart for innodb_io_capacity changes but need to restart for innodb io thread changes heh. I leave it up to end users when they want to restart mysql for io thread changes.
     
  15. eva2000

    eva2000 Administrator Staff Member

    54,548
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    10:28 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    new tools/setio.sh run on new 2GB Linode KVM VPS Fremont for my Sendy.co newsletter hosted server. It's using older Xeon E5-2680v2 processor
    Code:
    tools/setio.sh set
    Full Reads: 28151
    Full Writes: 23339
    innodb_io_capacity = 2300
    
    +------------------------+-------+
    /etc/my.cnf adjustment
    +------------------------+-------+
    existing value:
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_io_capacity     | 2000  |
    | innodb_io_capacity_max | 4000  |
    +------------------------+-------+
    new value:
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_io_capacity     | 2300  |
    | innodb_io_capacity_max | 4600  |
    +------------------------+-------+
    
    +------------------------+-------+
    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
     
  16. eva2000

    eva2000 Administrator Staff Member

    54,548
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    10:28 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    huge variance in disk i/o over time, same 2GB Linode from previous post

    might need to revise and reduce my formula as difference could be between a fresh light or busy server

    Code:
    tools/setio.sh set
    Full Reads: 29467
    Full Writes: 68266
    innodb_io_capacity = 3400
    
    +------------------------+-------+
    /etc/my.cnf adjustment
    +------------------------+-------+
    existing value:
    Variable_name   Value
    innodb_io_capacity      2300
    innodb_io_capacity_max  4600
    new value:
    Variable_name   Value
    innodb_io_capacity      3400
    innodb_io_capacity_max  6800
    
    +------------------------+-------+
    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
    revised down the formula for io capacity

    Code:
    tools/setio.sh set                                             
    Full Reads: 68266
    Full Writes: 65015
    innodb_io_capacity = 2700
    
    +------------------------+-------+
    /etc/my.cnf adjustment
    +------------------------+-------+
    existing value:
    Variable_name   Value
    innodb_io_capacity      3400
    innodb_io_capacity_max  6800
    new value:
    Variable_name   Value
    innodb_io_capacity      2700
    innodb_io_capacity_max  5400
    
    +------------------------+-------+
    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
     
    Last edited: Feb 8, 2016
  17. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    1:28 PM
    Nginx 1.17.9
    MariaDB 10.3.22
    Digital Ocean, 4GB

    This is the last one. In past few days I run this tool several times, and mostly every time it gives new recommandations (I think it is because @eva2000 tuneup script code)

    Code:
    [root@tvor-ocean ~]# /usr/local/src/centminmod/tools/setio.sh set
    Full Reads: 53194
    Full Writes: 7772
    innodb_io_capacity = 900
    
    +------------------------+-------+
    /etc/my.cnf adjustment
    +------------------------+-------+
    existing value:
    Variable_name   Value
    innodb_io_capacity      800
    innodb_io_capacity_max  1600
    new value:
    Variable_name   Value
    innodb_io_capacity      900
    innodb_io_capacity_max  1800
    
    +------------------------+-------+
    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
     
  18. eva2000

    eva2000 Administrator Staff Member

    54,548
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    10:28 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    disk i/o will change over time or periods due to measured disk performance at the moment in time you run it so it's normal
     
  19. eva2000

    eva2000 Administrator Staff Member

    54,548
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    10:28 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    updated 123.09beta01's tools/setio.sh tool with a fix thanks to @Matt feedback.

    setio.sh measured disk i/o performance from /root/tools/fio directory, which meant you measured /root directory disk I/O. However, mysql by default runs from /var/lib/mysql and maybe mounted on separate disks or disk arrays from root partition. So setio.sh could possibly be measuring the wrong disk partition to gauge disk i/o performance.

    The update fixes this by finding out mysql's defined datadir path and setting up fio tool to test in a directory at /var/lib/mysql/cmsetiofiotest or where ever your mysql datadir path is i.e. if /mnt/mysql is datadir, then /mnt/mysql/cmsetiofiotest will be where fio tests. It would only change the disk iops numbers if your mysql datadir is on a separate disk or partition from root.

    Code:
    ls -lahrt /var/lib/mysql | tail -1
    drwxr-xr-x   2 root  root    80 Feb 11 05:56 cmsetiofiotest
    Code:
    ls -lah /var/lib/mysql/cmsetiofiotest/
    total 20K
    drwxr-xr-x 2 root  root    80 Feb 11 05:56 .
    drwxr-xr-x 5 mysql mysql 4.0K Feb 11 05:56 ..
    -rw-r--r-- 1 root  root   184 Feb 11 05:56 reads-16k.ini
    -rw-r--r-- 1 root  root   182 Feb 11 05:56 reads.ini
    -rw-r--r-- 1 root  root   186 Feb 11 05:56 writes-16k.ini
    -rw-r--r-- 1 root  root   184 Feb 11 05:56 writes.ini
    the directory will be ignored by mysql and NOT mistaken for database name when you restart mysql server due to an added entry in your /etc/my.cnf under [mysqld] group
    Code:
    [mysqld]
    ignore-db-dir=cmsetiofiotest
    Code:
    bash tools/setio.sh set
    Full Reads: 12700
    Full Writes: 9764
    innodb_io_capacity = 1200
    
    +------------------------+-------+
    /etc/my.cnf adjustment
    +------------------------+-------+
    existing value:
    Variable_name   Value
    innodb_io_capacity      100
    innodb_io_capacity_max  2000
    new value:
    Variable_name   Value
    innodb_io_capacity      1200
    innodb_io_capacity_max  2400
    
    +------------------------+-------+
    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
     
  20. eva2000

    eva2000 Administrator Staff Member

    54,548
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    10:28 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    working nicely :D

    Code:
    tools/setio.sh set
    Full Reads: 3990
    Full Writes: 118724
    innodb_io_capacity = 3200
    
    +------------------------+-------+
    /etc/my.cnf adjustment
    +------------------------+-------+
    existing value:
    Variable_name   Value
    innodb_io_capacity      3200
    innodb_io_capacity_max  6400
    new value:
    Variable_name   Value
    innodb_io_capacity      3200
    innodb_io_capacity_max  6400
    
    +------------------------+-------+
    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