Join the community today
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 Active Member

    415
    50
    28
    Apr 11, 2015
    Ratings:
    +98
    Local Time:
    5:33 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):
    [root@server ~]# 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
     
  2. eva2000

    eva2000 Administrator Staff Member

    54,647
    12,230
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,799
    Local Time:
    1:33 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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
     
  3. eva2000

    eva2000 Administrator Staff Member

    54,647
    12,230
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,799
    Local Time:
    1:33 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
  4. negative

    negative Active Member

    415
    50
    28
    Apr 11, 2015
    Ratings:
    +98
    Local Time:
    5:33 AM
    1.9.10
    10.1.11
  5. eva2000

    eva2000 Administrator Staff Member

    54,647
    12,230
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,799
    Local Time:
    1:33 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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

     
  6. negative

    negative Active Member

    415
    50
    28
    Apr 11, 2015
    Ratings:
    +98
    Local Time:
    5:33 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

    54,647
    12,230
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,799
    Local Time:
    1:33 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    yeah i accounted for both
     
  8. pamamolf

    pamamolf Premium Member Premium Member

    4,084
    428
    83
    May 31, 2014
    Ratings:
    +834
    Local Time:
    5:33 AM
    Nginx-1.25.x
    MariaDB 10.3.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

    54,647
    12,230
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,799
    Local Time:
    1:33 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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:33 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 Premium Member Premium Member

    4,084
    428
    83
    May 31, 2014
    Ratings:
    +834
    Local Time:
    5:33 AM
    Nginx-1.25.x
    MariaDB 10.3.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

    54,647
    12,230
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,799
    Local Time:
    1:33 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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 :)