Welcome to Centmin Mod Community
Become a Member

MariaDB Ideal innodb_io_capacity for Raid SSD Disks

Discussion in 'MariaDB & General MySQL news & discussions' started by negative, Sep 21, 2016.

  1. negative

    negative Member

    236
    24
    18
    Apr 11, 2015
    Ratings:
    +54
    Local Time:
    5:21 AM
    1.9.10
    10.1.11
    Hello

    When i see the my.cnf that coming as standart while installing centminmod

    # 200 * # DISKS
    innodb_io_capacity = 1500
    innodb_io_capacity_max = 3000
    innodb_read_io_threads = 16
    innodb_write_io_threads = 16

    However, i have Dual INTEL 480GB SSD @ Raid 1 so i think that disks IO's is very high. Do i need any modify these values on my mariadb 10.1 settings? Of firstly need the benchmark that disks?

    Code (Text):
    [[email protected] ~]# iostat -x
    Linux 3.10.0-327.28.3.el7.x86_64 (server.server.net)     20-09-2016     _x86_64_    (32 CPU)
    
    avg-cpu:  %user   %nice %system %iowait  %steal   %idle
               0,50    0,00    0,08    0,00    0,00   99,43
    
    Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
    sda               0,00     5,59    0,39    5,72     5,41   214,98    72,14     0,17   28,13    0,33   30,01   0,11   0,07


    Also, innodb_flush_neighbor_pages value may be better if set to "none" (instead of 1 - default centminmod configuration)

    How To Tune MariaDB Write Performance - MariaDB.org
     
    • Like Like x 1
  2. eva2000

    eva2000 Administrator Staff Member

    36,477
    8,001
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,326
    Local Time:
    12:21 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    Centmin Mod auto detects and measures disk I/O speeds and adjusts mysql's IO capacity dynamically so should be about right from measured disk I/O

    MariaDB doesn't have that specific variable but innodb_flush_neighbors XtraDB/InnoDB Server System Variables - MariaDB Knowledge Base and yes I should make Centmin Mod detect this based on disk I/O measured
     
    • Like Like x 1
  3. eva2000

    eva2000 Administrator Staff Member

    36,477
    8,001
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,326
    Local Time:
    12:21 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    • Useful Useful x 1
  4. negative

    negative Member

    236
    24
    18
    Apr 11, 2015
    Ratings:
    +54
    Local Time:
    5:21 AM
    1.9.10
    10.1.11
  5. eva2000

    eva2000 Administrator Staff Member

    36,477
    8,001
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,326
    Local Time:
    12:21 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    Already apart of this update commited see Beta Branch - update my.cnf templates & tools/setio.sh in 123.09beta01 | Centmin Mod Community. As mentioned before, that variable doesn't exist in MariaDB 10 only 5.5

     
    • Winner Winner x 1
  6. negative

    negative Member

    236
    24
    18
    Apr 11, 2015
    Ratings:
    +54
    Local Time:
    5:21 AM
    1.9.10
    10.1.11
    Ah okay thank you i missed it for mariadb 5.5 only. Thanks updated the mysql configuration
     
  7. eva2000

    eva2000 Administrator Staff Member

    36,477
    8,001
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,326
    Local Time:
    12:21 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    yeah i accounted for both
     
  8. pamamolf

    pamamolf Well-Known Member

    3,117
    295
    83
    May 31, 2014
    Ratings:
    +530
    Local Time:
    5:21 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    I have it under the [mariadb-5.5] section and i am using MariaDB 10.1:

    Code:
    innodb_flush_neighbor_pages = 1
    Does that mean that is not active?
     
  9. eva2000

    eva2000 Administrator Staff Member

    36,477
    8,001
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,326
    Local Time:
    12:21 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    it's only active in your case if on mariadb 5.5 but not active if on mariadb version other than 5.5 i.e. 10.1
     
  10. SeaTea

    SeaTea Member

    49
    13
    8
    Feb 20, 2015
    the Netherlands
    Ratings:
    +28
    Local Time:
    4:21 AM
    Nginx:1.11
    MariaDB-10
    What is 'dynamically' here ? Only during install or every time you start the centmin menu ? So are hardware changes automatically recognized or do we need to run setio.sh or other scripts manually on a regular basis ?
     
  11. pamamolf

    pamamolf Well-Known Member

    3,117
    295
    83
    May 31, 2014
    Ratings:
    +530
    Local Time:
    5:21 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Dynamically is set the:

    Code:
    innodb_flush_neighbor_pages   <---MariaDB 5.x
    innodb_flush_neighbors            <---MariaDB 10.x
    

    setio.sh runs on setup of MariaDB and then we must run it manually :)
     
  12. eva2000

    eva2000 Administrator Staff Member

    36,477
    8,001
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,326
    Local Time:
    12:21 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    what @pamamolf said, dynamic in that it measures disk I/O at time of tools/setio.sh run which is done at initial MariaDB install or when you manually run tools/setio.sh :)
     
..