Learn about Centmin Mod LEMP Stack today
Become a Member

Beta Branch Help test innodbio.sh for MySQL Tuning

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

  1. eva2000

    eva2000 Administrator Staff Member

    54,535
    12,219
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,788
    Local Time:
    8:51 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Folks, I am tweaking the /etc/my.cnf preset templates and just need some folks to help gather some stats and test out the routine that is going to work out dynamically the optimal settings for each user's server. If you can run this command and post the output you get that would help :)
    Code:
    curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
    example output
    Code:
    curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
    
    innodb_io_capacity = 1800
    
    real    0m9.011s
    user    0m0.000s
    sys     0m0.003s
    
    innodb_io_capacity = 1900
    
    real    0m9.018s
    user    0m0.001s
    sys     0m0.002s
    
    innodb_io_capacity = 1300
    
    real    0m9.019s
    user    0m0.000s
    sys     0m0.002s
    
    innodb_io_capacity = 1200
    
    real    0m9.019s
    user    0m0.001s
    sys     0m0.002s
    

    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

    Will be integrated into 123.09beta01's initial installation setup routine so innodb_io_capacity variable is set optimally for the user's server. You can also re-run tools/setio.sh manually to re-adjust the innodb_io_capacity variable value whenever you need to do it i.e. after upgrading disk configurations or expanding disk configuration which change the underlying disk write I/O performance.

    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

     
    Last edited: Feb 6, 2016
  2. Ahmad

    Ahmad Active Member

    209
    80
    28
    Apr 13, 2015
    Ratings:
    +150
    Local Time:
    11:51 AM
    1.9.9
    10.1.10
    2GB Premium ramnode
    Code:
    [root@server ~]# curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
    
    innodb_io_capacity = 900
    
    real    0m9.030s
    user    0m0.019s
    sys     0m0.009s
    
    innodb_io_capacity = 1000
    
    real    0m9.035s
    user    0m0.017s
    sys     0m0.010s
    
    innodb_io_capacity = 1000
    
    real    0m9.028s
    user    0m0.021s
    sys     0m0.008s
    [root@server ~]#
    
     
  3. eva2000

    eva2000 Administrator Staff Member

    54,535
    12,219
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,788
    Local Time:
    8:51 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Cheers @Ahmad could you do another run, just updated the code to calculate averages too
    Code:
    curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
     
  4. pamamolf

    pamamolf Premium Member Premium Member

    4,084
    428
    83
    May 31, 2014
    Ratings:
    +834
    Local Time:
    12:51 PM
    Nginx-1.25.x
    MariaDB 10.3.x
    From my dedicated servers :)

    Code:
    innodb_io_capacity = 2700
    
    real    0m9.029s
    user    0m0.023s
    sys     0m0.010s
    
    innodb_io_capacity = 2800
    
    real    0m9.029s
    user    0m0.024s
    sys     0m0.009s
    
    innodb_io_capacity = 2500
    
    real    0m9.031s
    user    0m0.026s
    sys     0m0.007s
    
    innodb_io_capacity = 2700
    
    real    0m9.031s
    user    0m0.025s
    sys     0m0.008s
    Code:
    innodb_io_capacity = 1300
    
    real    0m9.076s
    user    0m0.002s
    sys     0m0.004s
    
    innodb_io_capacity = 1200
    
    real    0m9.066s
    user    0m0.002s
    sys     0m0.004s
    
    innodb_io_capacity = 0
    
    real    0m9.420s
    user    0m0.002s
    sys     0m0.005s
    
    innodb_io_capacity = 700
    
    real    0m9.082s
    user    0m0.002s
    sys     0m0.005s
    Code:
    innodb_io_capacity = 100
    
    real    0m9.062s
    user    0m0.000s
    sys     0m0.000s
    
    innodb_io_capacity = 1900
    
    real    0m9.055s
    user    0m0.004s
    sys     0m0.004s
    
    innodb_io_capacity = 100
    
    real    0m9.080s
    user    0m0.000s
    sys     0m0.000s
    
    innodb_io_capacity = 0
    
    real    0m9.121s
    user    0m0.000s
    sys     0m0.000s
    Code:
    innodb_io_capacity = 800
    
    real    0m9.110s
    user    0m0.001s
    sys     0m0.003s
    
    innodb_io_capacity = 300
    
    real    0m9.126s
    user    0m0.000s
    sys     0m0.006s
    
    innodb_io_capacity = 500
    
    real    0m9.131s
    user    0m0.000s
    sys     0m0.005s
    
    innodb_io_capacity = 700
    
    real    0m9.083s
    user    0m0.001s
    sys     0m0.004s
    That's amazing to automate this setting :)

    Code:
    innodb_io_capacity
    You will automate also the:

    Code:
    innodb_read_io_threads 
    and
    Code:
    innodb_write_io_threads
    ?

    Thanks
     
  5. Ahmad

    Ahmad Active Member

    209
    80
    28
    Apr 13, 2015
    Ratings:
    +150
    Local Time:
    11:51 AM
    1.9.9
    10.1.10
    Code:
    root@server ~]# curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
    
    innodb_io_capacity = 900
    
    real    0m9.065s
    user    0m0.020s
    sys     0m0.028s
    
    innodb_io_capacity = 400
    
    real    0m9.041s
    user    0m0.015s
    sys     0m0.016s
    
    innodb_io_capacity = 100
    
    real    0m9.066s
    user    0m0.018s
    sys     0m0.014s
    
    innodb_io_capacity = 400
    
    real    0m9.046s
    user    0m0.023s
    sys     0m0.003s
    
    innodb_io_capacity = 800
    
    real    0m27.168s
    user    0m0.056s
    sys     0m0.037s
    
    innodb_io_capacity = 800
    
    real    0m27.179s
    user    0m0.079s
    sys     0m0.017s
    [root@server ~]#
     
  6. eva2000

    eva2000 Administrator Staff Member

    54,535
    12,219
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,788
    Local Time:
    8:51 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    thanks guys

    @pamamolf the read/write io_threads probably don't need it but shall see

    looks like i also need to account for values = 0 for disk performance is is more contented
     
  7. eva2000

    eva2000 Administrator Staff Member

    54,535
    12,219
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,788
    Local Time:
    8:51 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    updated the test script to read/write io threads too
    Code:
    curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
    Code:
    curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
    
    innodb_io_capacity = 1900
    
    real    0m4.018s
    user    0m0.000s
    sys     0m0.003s
    
    innodb_io_capacity = 2000
    
    real    0m4.018s
    user    0m0.000s
    sys     0m0.003s
    
    innodb_io_capacity = 1200
    
    real    0m4.018s
    user    0m0.000s
    sys     0m0.002s
    
    innodb_io_capacity = 1200
    
    real    0m4.018s
    user    0m0.000s
    sys     0m0.003s
    
    innodb_io_capacity = 1200
    
    real    0m12.053s
    user    0m0.001s
    sys     0m0.006s
    
    innodb_io_capacity = 1200
    
    real    0m12.054s
    user    0m0.000s
    sys     0m0.006s
    
    innodb_io_capacity = 2000
    
    real    0m12.052s
    user    0m0.000s
    sys     0m0.007s
    
    innodb_io_capacity = 2000
    
    real    0m12.053s
    user    0m0.001s
    sys     0m0.006s
    
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
     
    Last edited: Feb 5, 2016
  8. pamamolf

    pamamolf Premium Member Premium Member

    4,084
    428
    83
    May 31, 2014
    Ratings:
    +834
    Local Time:
    12:51 PM
    Nginx-1.25.x
    MariaDB 10.3.x
    On three of my servers i got:

    Code:
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    Code:
    innodb_read_io_threads = 4
    innodb_write_io_threads = 4
    Code:
    innodb_read_io_threads = 6
    innodb_write_io_threads = 6
     
  9. rdan

    rdan Well-Known Member

    5,446
    1,408
    113
    May 25, 2014
    Ratings:
    +2,201
    Local Time:
    6:51 PM
    Mainline
    10.2
    4GB / 4 CPU Linode:
    Code:
    innodb_io_capacity = 1100
    
    real    0m4.030s
    user    0m0.020s
    sys    0m0.003s
    
    innodb_io_capacity = 900
    
    real    0m4.025s
    user    0m0.017s
    sys    0m0.000s
    
    0 change to 100
    innodb_io_capacity = 100
    
    real    0m4.053s
    user    0m0.020s
    sys    0m0.000s
    
    0 change to 100
    innodb_io_capacity = 100
    
    real    0m4.050s
    user    0m0.017s
    sys    0m0.003s
    
    innodb_io_capacity = 100
    
    real    0m12.132s
    user    0m0.063s
    sys    0m0.000s
    
    innodb_io_capacity = 100
    
    real    0m12.127s
    user    0m0.050s
    sys    0m0.010s
    
    innodb_io_capacity = 200
    
    real    0m12.137s
    user    0m0.060s
    sys    0m0.003s
    
    innodb_io_capacity = 200
    
    real    0m12.133s
    user    0m0.047s
    sys    0m0.013s
    
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2

    1GB / 4 CPU Ramnode Premium KVM
    Code:
    innodb_io_capacity = 2200
    
    real    0m4.014s
    user    0m0.000s
    sys    0m0.003s
    
    innodb_io_capacity = 2700
    
    real    0m4.022s
    user    0m0.004s
    sys    0m0.023s
    
    innodb_io_capacity = 2400
    
    real    0m4.024s
    user    0m0.005s
    sys    0m0.006s
    
    innodb_io_capacity = 2300
    
    real    0m4.024s
    user    0m0.001s
    sys    0m0.005s
    
    innodb_io_capacity = 2500
    
    real    0m12.056s
    user    0m0.000s
    sys    0m0.009s
    
    innodb_io_capacity = 2400
    
    real    0m12.054s
    user    0m0.004s
    sys    0m0.003s
    
    innodb_io_capacity = 4000
    
    real    0m12.054s
    user    0m0.001s
    sys    0m0.006s
    
    innodb_io_capacity = 3600
    
    real    0m12.058s
    user    0m0.007s
    sys    0m0.011s
    
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    

    128GB / 12 CPU OVH Dedicated Server
    Code:
    innodb_io_capacity = 3100
    
    real    0m4.015s
    user    0m0.000s
    sys    0m0.003s
    
    innodb_io_capacity = 2600
    
    real    0m4.013s
    user    0m0.000s
    sys    0m0.003s
    
    innodb_io_capacity = 2700
    
    real    0m4.014s
    user    0m0.000s
    sys    0m0.001s
    
    innodb_io_capacity = 2700
    
    real    0m4.015s
    user    0m0.000s
    sys    0m0.002s
    
    innodb_io_capacity = 3200
    
    real    0m12.028s
    user    0m0.001s
    sys    0m0.005s
    
    innodb_io_capacity = 2600
    
    real    0m12.042s
    user    0m0.001s
    sys    0m0.004s
    
    innodb_io_capacity = 4500
    
    real    0m12.041s
    user    0m0.002s
    sys    0m0.004s
    
    innodb_io_capacity = 4500
    
    real    0m12.039s
    user    0m0.002s
    sys    0m0.002s
    
    innodb_read_io_threads = 6
    innodb_write_io_threads = 6
    
    6GB / 6 CPU UltraVPS
    Code:
    0 change to 100
    innodb_io_capacity = 100
    
    real    0m4.307s
    user    0m0.013s
    sys    0m0.020s
    
    innodb_io_capacity = 1100
    
    real    0m4.048s
    user    0m0.016s
    sys    0m0.038s
    
    innodb_io_capacity = 1300
    
    real    0m4.095s
    user    0m0.006s
    sys    0m0.042s
    
    innodb_io_capacity = 1200
    
    real    0m4.068s
    user    0m0.003s
    sys    0m0.014s
    
    innodb_io_capacity = 1500
    
    real    0m12.279s
    user    0m0.005s
    sys    0m0.036s
    
    innodb_io_capacity = 1500
    
    real    0m12.155s
    user    0m0.007s
    sys    0m0.017s
    
    innodb_io_capacity = 2400
    
    real    0m12.166s
    user    0m0.004s
    sys    0m0.028s
    
    innodb_io_capacity = 2300
    
    real    0m12.735s
    user    0m0.002s
    sys    0m0.024s
    
    innodb_read_io_threads = 3
    innodb_write_io_threads = 3
    
    

    1GB / 1 CPU Linode
    Code:
    innodb_io_capacity = 1000
    
    real    0m4.014s
    user    0m0.003s
    sys    0m0.000s
    
    innodb_io_capacity = 1000
    
    real    0m4.010s
    user    0m0.000s
    sys    0m0.000s
    
    innodb_io_capacity = 900
    
    real    0m4.017s
    user    0m0.000s
    sys    0m0.000s
    
    innodb_io_capacity = 1000
    
    real    0m4.016s
    user    0m0.000s
    sys    0m0.000s
    
    innodb_io_capacity = 1000
    
    real    0m12.042s
    user    0m0.000s
    sys    0m0.003s
    
    innodb_io_capacity = 900
    
    real    0m12.051s
    user    0m0.000s
    sys    0m0.003s
    
    innodb_io_capacity = 1600
    
    real    0m12.041s
    user    0m0.000s
    sys    0m0.000s
    
    innodb_io_capacity = 1700
    
    real    0m12.041s
    user    0m0.000s
    sys    0m0.003s
    
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    
    
    32GB / 12 CPU Linode
    Code:
    innodb_io_capacity = 1200
    
    real    0m4.022s
    user    0m0.010s
    sys    0m0.000s
    
    innodb_io_capacity = 800
    
    real    0m4.020s
    user    0m0.010s
    sys    0m0.003s
    
    innodb_io_capacity = 100
    
    real    0m4.043s
    user    0m0.010s
    sys    0m0.003s
    
    innodb_io_capacity = 100
    
    real    0m4.044s
    user    0m0.007s
    sys    0m0.013s
    
    innodb_io_capacity = 100
    
    real    0m12.122s
    user    0m0.037s
    sys    0m0.003s
    
    innodb_io_capacity = 100
    
    real    0m12.131s
    user    0m0.037s
    sys    0m0.010s
    
    innodb_io_capacity = 200
    
    real    0m12.126s
    user    0m0.043s
    sys    0m0.000s
    
    innodb_io_capacity = 200
    
    real    0m12.134s
    user    0m0.037s
    sys    0m0.020s
    
    innodb_read_io_threads = 6
    innodb_write_io_threads = 6
    
    
     
  10. eva2000

    eva2000 Administrator Staff Member

    54,535
    12,219
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,788
    Local Time:
    8:51 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    thanks looks like i need to work on the formula for those 100 iops values
     
  11. eva2000

    eva2000 Administrator Staff Member

    54,535
    12,219
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,788
    Local Time:
    8:51 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    @RoldanLT fixed the formula for those 100 iops values :)
    Code:
    curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
     
  12. rdan

    rdan Well-Known Member

    5,446
    1,408
    113
    May 25, 2014
    Ratings:
    +2,201
    Local Time:
    6:51 PM
    Mainline
    10.2
    Do you want me to run on every server? :D
     
  13. eva2000

    eva2000 Administrator Staff Member

    54,535
    12,219
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,788
    Local Time:
    8:51 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    maybe just ones which reported 100 iops
     
  14. rdan

    rdan Well-Known Member

    5,446
    1,408
    113
    May 25, 2014
    Ratings:
    +2,201
    Local Time:
    6:51 PM
    Mainline
    10.2
    4GB / 4 CPU Linode:
    Code:
    innodb_io_capacity = 1160
    
    real    0m3.025s
    user    0m0.017s
    sys    0m0.007s
    
    innodb_io_capacity = 976
    
    real    0m3.025s
    user    0m0.017s
    sys    0m0.000s
    
    innodb_io_capacity = 138
    
    real    0m3.043s
    user    0m0.020s
    sys    0m0.000s
    
    innodb_io_capacity = 135
    
    real    0m3.044s
    user    0m0.020s
    sys    0m0.000s
    
    innodb_io_capacity = 140
    
    real    0m9.143s
    user    0m0.060s
    sys    0m0.003s
    
    innodb_io_capacity = 122
    
    real    0m9.136s
    user    0m0.057s
    sys    0m0.003s
    
    innodb_io_capacity = 222
    
    real    0m9.138s
    user    0m0.057s
    sys    0m0.007s
    
    innodb_io_capacity = 198
    
    real    0m9.146s
    user    0m0.053s
    sys    0m0.007s
    
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    
    6GB / 6 CPU UltraVPS
    Code:
    innodb_io_capacity = 1222
    
    real    0m3.040s
    user    0m0.006s
    sys    0m0.028s
    
    innodb_io_capacity = 516
    
    real    0m3.029s
    user    0m0.002s
    sys    0m0.021s
    
    innodb_io_capacity = 1000
    
    real    0m3.066s
    user    0m0.007s
    sys    0m0.022s
    
    innodb_io_capacity = 992
    
    real    0m3.071s
    user    0m0.009s
    sys    0m0.008s
    
    innodb_io_capacity = 930
    
    real    0m9.414s
    user    0m0.007s
    sys    0m0.053s
    
    innodb_io_capacity = 1096
    
    real    0m9.117s
    user    0m0.005s
    sys    0m0.040s
    
    innodb_io_capacity = 1262
    
    real    0m9.186s
    user    0m0.012s
    sys    0m0.046s
    
    innodb_io_capacity = 1775
    
    real    0m9.192s
    user    0m0.001s
    sys    0m0.039s
    
    innodb_read_io_threads = 3
    innodb_write_io_threads = 3
    
    
     
  15. eva2000

    eva2000 Administrator Staff Member

    54,535
    12,219
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,788
    Local Time:
    8:51 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    looks more accurate though that linode seems low for iops - though could be your forum is much busier disk I/O wise :)

    here's this forum's 4GB Linode

    Code:
    curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
    
    innodb_io_capacity = 685
    
    real    0m3.011s
    user    0m0.000s
    sys     0m0.003s
    
    innodb_io_capacity = 932
    
    real    0m3.008s
    user    0m0.000s
    sys     0m0.000s
    
    innodb_io_capacity = 825
    
    real    0m3.016s
    user    0m0.000s
    sys     0m0.003s
    
    innodb_io_capacity = 842
    
    real    0m3.014s
    user    0m0.000s
    sys     0m0.000s
    
    innodb_io_capacity = 832
    
    real    0m9.048s
    user    0m0.000s
    sys     0m0.003s
    
    innodb_io_capacity = 910
    
    real    0m9.043s
    user    0m0.000s
    sys     0m0.007s
    
    innodb_io_capacity = 1458
    
    real    0m9.046s
    user    0m0.003s
    sys     0m0.000s
    
    innodb_io_capacity = 1496
    
    real    0m9.040s
    user    0m0.003s
    sys     0m0.000s
    
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
     
  16. rdan

    rdan Well-Known Member

    5,446
    1,408
    113
    May 25, 2014
    Ratings:
    +2,201
    Local Time:
    6:51 PM
    Mainline
    10.2
    Yes, all of those server I have tested has busy sites hosted running Live :D.
     
  17. eva2000

    eva2000 Administrator Staff Member

    54,535
    12,219
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,788
    Local Time:
    8:51 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    i'll adjust the formula down a bit as it will be applied on fresh installs so no load so need to account for load
     
  18. eva2000

    eva2000 Administrator Staff Member

    54,535
    12,219
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,788
    Local Time:
    8:51 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    updated the formula to not set it so high
    Code:
    curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
    on 4GB Linode
    Code:
    curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
    
    innodb_io_capacity = 298
    
    real    0m2.009s
    user    0m0.000s
    sys     0m0.003s
    
    innodb_io_capacity = 399
    
    real    0m2.009s
    user    0m0.000s
    sys     0m0.003s
    
    innodb_io_capacity = 428
    
    real    0m2.029s
    user    0m0.000s
    sys     0m0.013s
    
    innodb_io_capacity = 542
    
    real    0m2.013s
    user    0m0.000s
    sys     0m0.000s
    
    innodb_io_capacity = 393
    
    real    0m6.042s
    user    0m0.000s
    sys     0m0.000s
    
    innodb_io_capacity = 316
    
    real    0m6.047s
    user    0m0.003s
    sys     0m0.007s
    
    innodb_io_capacity = 504
    
    real    0m6.045s
    user    0m0.000s
    sys     0m0.003s
    
    innodb_io_capacity = 499
    
    real    0m6.036s
    user    0m0.000s
    sys     0m0.003s
    
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    on 1GB theskyspace.com OpenVZ
    Code:
    curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
    
    innodb_io_capacity = 777
    
    real    0m2.011s
    user    0m0.001s
    sys     0m0.001s
    
    innodb_io_capacity = 809
    
    real    0m2.018s
    user    0m0.000s
    sys     0m0.003s
    
    innodb_io_capacity = 467
    
    real    0m2.018s
    user    0m0.000s
    sys     0m0.002s
    
    innodb_io_capacity = 485
    
    real    0m2.018s
    user    0m0.000s
    sys     0m0.003s
    
    innodb_io_capacity = 451
    
    real    0m6.052s
    user    0m0.001s
    sys     0m0.006s
    
    innodb_io_capacity = 488
    
    real    0m6.052s
    user    0m0.001s
    sys     0m0.006s
    
    innodb_io_capacity = 690
    
    real    0m6.053s
    user    0m0.001s
    sys     0m0.004s
    
    innodb_io_capacity = 647
    
    real    0m6.052s
    user    0m0.002s
    sys     0m0.005s
    
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
     
  19. eva2000

    eva2000 Administrator Staff Member

    54,535
    12,219
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,788
    Local Time:
    8:51 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    new version with 2 methods of calculation via ioping and fio

    Code:
    curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
    Code:
    -----------------------------------
    ioping calculated
    -----------------------------------
    
    innodb_io_capacity = 874
    innodb_io_capacity = 641
    -----------------------------------
    innodb_io_capacity = 937
    innodb_io_capacity = 991
    -----------------------------------
    innodb_io_capacity = 742
    innodb_io_capacity = 696
    -----------------------------------
    
    -----------------------------------
    fio calculated
    -----------------------------------
    
    Full Reads: 3923
    Full Writes: 117928
    innodb_io_capacity = 2358
    innodb_io_capacity = 1684
    innodb_io_capacity = 1310
    innodb_io_capacity = 1072
    
    Full Reads: 3788
    Full Writes: 117928
    innodb_io_capacity = 2358
    innodb_io_capacity = 1684
    innodb_io_capacity = 1310
    innodb_io_capacity = 1072
    
    -----------------------------------
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    -----------------------------------
    
     
    Last edited: Feb 5, 2016
  20. ModeltogTossen

    ModeltogTossen I wish I could??

    313
    97
    28
    Dec 20, 2015
    Denmark
    Ratings:
    +143
    Local Time:
    11:51 AM
    1.9.12
    10.0.23
    And here one run from me - 8 GB vps on own vmware host - CMM is not optimized in any way manually - only by menu options.

    Code:
    [root@sarah ~]# curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash
    
    -----------------------------------
    ioping calculated
    -----------------------------------
    innodb_io_capacity = 670
    innodb_io_capacity = 753
    -----------------------------------
    innodb_io_capacity = 684
    innodb_io_capacity = 993
    -----------------------------------
    innodb_io_capacity = 841
    innodb_io_capacity = 848
    -----------------------------------
    
    -----------------------------------
    fio calculated
    -----------------------------------
    
    Full Reads: 1853
    Full Writes: 283
    innodb_io_capacity = 5
    innodb_io_capacity = 4
    innodb_io_capacity = 3
    innodb_io_capacity = 2
    innodb_io_capacity = 9
    
    Full Reads: 1913
    Full Writes: 252
    innodb_io_capacity = 5
    innodb_io_capacity = 3
    innodb_io_capacity = 2
    innodb_io_capacity = 2
    innodb_io_capacity = 8
    
    -----------------------------------
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    -----------------------------------