Want to subscribe to topics you're interested in?
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

    217
    22
    18
    Apr 11, 2015
    Ratings:
    +50
    Local Time:
    10:41 PM
    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
     
    • Like Like x 1
  2. eva2000

    eva2000 Administrator Staff Member

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:41 AM
    Nginx 1.13.x
    MariaDB 5.5
    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

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:41 AM
    Nginx 1.13.x
    MariaDB 5.5
    • Useful Useful x 1
  4. negative

    negative Member

    217
    22
    18
    Apr 11, 2015
    Ratings:
    +50
    Local Time:
    10:41 PM
    1.9.10
    10.1.11
  5. eva2000

    eva2000 Administrator Staff Member

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:41 AM
    Nginx 1.13.x
    MariaDB 5.5
    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

    217
    22
    18
    Apr 11, 2015
    Ratings:
    +50
    Local Time:
    10:41 PM
    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

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:41 AM
    Nginx 1.13.x
    MariaDB 5.5
    yeah i accounted for both
     
  8. pamamolf

    pamamolf Well-Known Member

    2,825
    253
    83
    May 31, 2014
    Ratings:
    +449
    Local Time:
    10:41 PM
    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

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:41 AM
    Nginx 1.13.x
    MariaDB 5.5
    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 Premium Member Premium Member

    49
    13
    8
    Feb 20, 2015
    the Netherlands
    Ratings:
    +28
    Local Time:
    9:41 PM
    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

    2,825
    253
    83
    May 31, 2014
    Ratings:
    +449
    Local Time:
    10:41 PM
    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

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:41 AM
    Nginx 1.13.x
    MariaDB 5.5
    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 :)