How to Change innodb_buffer_pool_size using MariaDB?

Discussion in 'Installation/Configuration' started by jaypabs, Sep 4, 2018.

  1. jaypabs

    jaypabs Member

    I just finished installing my new server with this tutorial:
    https://www.howtoforge.com/tutorial/perfect-server-ubuntu-18-04-nginx-bind-dovecot-and-ispconfig-3/
    I have the following server:
    Processor - 20 Core Server - Dual Intel Xeon Silver 4114
    Memory - 64 GB DDR4 ECC RAM
    Operating System - Ubuntu 18.04
    I came from MySQL database setup and new to MariaDB. If I want to change the innodb configuration like innodb_buffer_pool_size, where should I put it?
    In /etc/mysql/mariadb.cnf it says:
    # The MariaDB/MySQL tools read configuration files in the following order:
    # 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
    # 2. "/etc/mysql/conf.d/*.cnf" to set global options.
    # 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
    # 4. "~/.my.cnf" to set user-specific options.
    So I assume I should change it under /etc/mysql/mariadb.conf.d/50-server.cnf. Am I correct?
    And can I just add the following config under 50-server.cnf:
    Code:
    innodb_strict_mode             = ON
    innodb_file_format_check       = 1
    # innodb_file_format             = Barracuda                           # For dynamic and compressed InnoDB tables
    innodb_buffer_pool_size        = 38G                                # Go up to 80% of your available RAM
    innodb_buffer_pool_instances   = 8                                   # Bigger if huge InnoDB Buffer Pool or high concurrency
    
    innodb_file_per_table          = 1                                   # Is the recommended way nowadays
    # innodb_flush_method            = O_DIRECT                            # O_DIRECT is sometimes better for direct attached storage
    # innodb_write_io_threads        = 8                                   # If you have a strong I/O system or SSD
    # innodb_read_io_threads         = 8                                   # If you have a strong I/O system or SSD
    # innodb_io_capacity             = 1000                                # If you have a strong I/O system or SSD
    
    innodb_flush_log_at_trx_commit = 2                                   # 1 for durability, 0 or 2 for performance
    innodb_log_buffer_size         = 8M                                  # Bigger if innodb_flush_log_at_trx_commit = 0
    innodb_log_file_size           = 256M                                # Bigger means more write throughput but longer recovery time
    Or do I need to stop mysql first before changing this configuration?
    I hope somebody can help me 'cause there's no sample configuration under 50-server.cnf. It only shows the following description:
     
  2. Jesse Norell

    Jesse Norell ISPConfig Developer Staff Member ISPConfig Developer

    That would work; any .cnf file in that directory will be read, so it doesn't really matter what you call it, or which existing file you might add settings to, except that is the same seeing is specified multiple times, the one from the latest filename will be used. I sometimes make a 99-local.cnf so my additions don't get overwritten with os package updates, and to ensure those are the settings which are applied if they also appear in earlier-read files.
    For most settings you can just change then, then reload mariadb; iirc changing the innodb log file size might require you to stop MySQL, remove the old log files, and start up again. But verify that first, and keep a short term backup of the files you remove, in case my memory is off. :)
     
  3. jaypabs

    jaypabs Member

    Hi Jesse,
    Thank you very much for the reply. That was very helpful.
     

Share This Page