Welcome to Centmin Mod Community
Register Now

MariaDB Why does 123.08centos7beta02 my.cnf not include `innodb_buffer_pool_instances` variable?

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by jeffwidman, Apr 18, 2015.

  1. jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    9:25 PM
    Hey George,
    Setting up a new server, and was just poking through the default my.cnf for centos7 beta2 and noticed there's no `innodb_buffer_pool_instances` variable: XtraDB/InnoDB Server System Variables - MariaDB Knowledge Base

    Curious why you didn't include that?

    Seems like a reasonable thing to include--it's the second thing I tweak in my.cnf normally after increasing the innodb_buffer_pool_size. You've got a number of other variables in there that are commented out for optional tweaking that I never touch.

    FYI--the new default in MariaDB 10 is 8 instead of the old 1. It's a bit high for the VPSs that most folks on here run, but Maria seems smart enough that `SHOW ENGINE INNODB STATUS` on my 2GB VPS looks like there's only 1 instance. I don't know how it handles when it's a 4GB RAM (where I'd normally set instances at 2-3 depending on how much RAM I give MariaDB).
     
  2. eva2000

    eva2000 Administrator Staff Member

    30,178
    6,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,139
    Local Time:
    2:25 PM
    Nginx 1.13.x
    MariaDB 5.5
    As per official doc MariaDB 10 already defaults innodb_buffer_pool_instances to 8 and that happens regardless of whether variable is set in /etc/my.cnf and has some smart auto detecting for >1GB buffer pools. Centmin Mod default innodb buffer pool = 48MB in size, so would hardly trigger innodb_buffer_pool_instances division automatically. So setting innodb_buffer_pool_instances would have no effect on default out of box Centmin Mod .08 beta install with MariaDB 10. Some settings are left to end users to tune for their specific needs. If are messing around with InnoDB specific /etc/my.cnf settings, then you should at least be this much clued into tuning for InnoDB performance in MySQL. Afterall, Centmin Mod is provided as is :)

    also see XtraDB/InnoDB Buffer Pool - MariaDB Knowledge Base

     
  3. jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    9:25 PM
    Yeah, it's more I was hoping you could just add it as a commented out line.

    I have an Ansible script that wraps Centminmod so I don't forget my various customizations that are scattered throughout various parts of centminmod (mysql character set utf8, turnoff ftp, increase memcache cache size, etc). And easy to wrap in ntpd, disabling passwords for sshd, etc. I basically just converted my "Notes_on_setting_up_a_server.txt" into code, so it's both faster and correctly documented.

    For the my.cnf, I used a regex to find and replace a couple of lines. I considered using my own template, but you're always adding little tweaks here and there, and I don't want to miss anything. :D So I figured a regex using Ansible's lineinfile module was better than using my own template. But if innodb_buffer_pool_instances isn't in there, there's nothing for the regex to match against. :(

    Definitely not worth worrying about if you don't want to, the two production VPSs that I run centminmod are both <8gb, so MariaDB defaults are fine. Mostly it just bothers my OCD that I can't parameterize the value. :whistle:
     
  4. eva2000

    eva2000 Administrator Staff Member

    30,178
    6,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,139
    Local Time:
    2:25 PM
    Nginx 1.13.x
    MariaDB 5.5
  5. jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    9:25 PM
    Zero worries mate. Get well and no rush on this. I'd be happy if it even got added in the next month. :)
     
  6. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    12:25 PM
    1.11
    10.2
    I have this innodb_buffer_pool_instances on my my.cnf since switching to MariaDb 10, but only set to 4 as I only allow 4GB buffer poll.
     
  7. jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    9:25 PM
    Did some testing.

    As long as innodb_buffer_pool_size is less than 1GB, there's only 1 buffer pool.
    As soon as it increases beyond 1 GB, MariaDB immediately defaults to 8 buffer pools, which is too high for most VPS's.

    For anyone in the 2gb-8gb RAM VPS range, since you'll likely be setting innodb_buffer_pool size > 1 GB, but less than 8gb, so be sure to also manually set innodb_buffer_pool_instances... ideally around 1GB each. Otherwise you'll think you're increasing performance, but you're probably actually decreasing it.

    @eva2000 maybe make a note in the wiki page on mysql about this?
     
  8. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    12:25 PM
    1.11
    10.2
    Yes, I have it like this:
    innodb_buffer_pool_size = 4G
    innodb_buffer_pool_instances = 4
     
  9. eva2000

    eva2000 Administrator Staff Member

    30,178
    6,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,139
    Local Time:
    2:25 PM
    Nginx 1.13.x
    MariaDB 5.5
    yup my quoted info at MariaDB - Why does 123.08centos7beta02 my.cnf not include `innodb_buffer_pool_instances` variable? | Centmin Mod Community mentions this

    it goes back to my initial assumption with regards to not enabling innodb by default, it is assumed that folks enabling innodb are versed and know innodb specific tuning requirements and that they would know or read innodb docs as such. It's why just blindly switching to innodb from myisam isn't a magic bullet that would give you the performance you may expect. You still need to know how to tune innodb parameters specifically. Centmin Mod only provides the tools, the rest such as software optimisation like nginx, php-fpm and mariadb mysql is up to end user :)