Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


MariaDB Innodb setup - need suggestion
New on LowEndTalk? Please Register and read our Community Rules.

MariaDB Innodb setup - need suggestion

pkrpkr Member

I am using 10.4.14-MariaDB on my VPS with 4 cores and 4.5gb RAM.
I modified the following variables in the MariaDB configuration file after going through several posts on MariaDB website. Is this a good setting or should I set different values?

innodb_buffer_pool_chunk_size = 256M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_size  = 2G
innodb_write_io_threads = 8
innodb_read_io_threads = 8

Comments

  • tetechtetech Member
    edited September 2020

    Insufficient information for meaningful answers. Run mysqltuner.

  • @tetech said:
    Insufficient information for meaningful answers. Run mysqltuner.

    The details on MariaDB pages are very confusing. That's why I asked this question here.

    On this page https://mariadb.com/kb/en/setting-innodb-buffer-pool-size-dynamically/, it says "The new buffer pool size must be a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances."

    On this page https://mariadb.com/kb/en/innodb-buffer-pool/, "if innodb_buffer_pool_size is 4GB and innodb_buffer_pool_instances is set to 4, each instance will be 1GB. Each instance should ideally be at least 1GB in size."

    The default innodb_buffer_pool_chunk_size was 128M and I changed it to 256M as my DB size is bigger than 128M.

  • No problem to ask here, but you provided insufficient information to get meaningful answers. How big are the tables? How many read/write transactions? Is it I/O bound or CPU bound? What type of storage/iops on the VPS?

    Run mysqltuner and it will give you some recommendations. Ultimately you need to benchmark it yourself - following setup guides is like treating an illness based on what a Google search tells you, i.e. it maybe works OK on average but might turn out badly for someone's specific situation.

    Thanked by 1AlwaysSkint
  • PerconaDB for innodb.

  • coolgoolecoolgoole Member
    edited October 2020
    [client]
    port        = 
    socket      = /var/run/mysqld/mysqld.sock
    
    
    [mysqld_safe]
    socket  = /var/run/mysqld/mysqld.sock
    nice  = 0
    skip-syslog
    
    
    [mysqld]
    user        = mysql
    pid-file    = /var/run/mysqld/mysqld.pid
    socket      = /var/run/mysqld/mysqld.sock
    port        = 
    basedir     = /usr
    datadir     = /var/lib/mysql
    tmpdir      = /tmp
    lc_messages_dir = /usr/share/mysql
    lc_messages = en_US
    
    
    max_allowed_packet = 32M                ## 128M for Recommend or 32M every 1GB of RAM
    
    
    skip-external-locking
    skip-character-set-client-handshake
    skip-host-cache
    skip_name_resolve
    ignore-db-dirs=lost+found
    
    
    
    bind-address        = 127.0.0.1
    #skip-networking
    
    
    
    
    max_connections = 100                   ## Max for = 2000
    connect_timeout     = 5
    max_user_connections = 80               ## Max for = 1800
    #interactive_timeout = 180               ## Max for = 300
    wait_timeout = 180                      ## Max for = 300
    
    
    
    #max_connections        = 100
    #max_connect_errors = 1000000
    #connect_timeout        = 10
    #wait_timeout       = 60
    thread_cache_size       = 50
    back_log = 512
    thread_stack = 192K
    #Interactive_timeout = 1800
    
    
    
    # Table Settings
    table_definition_cache = 10000          ## 10000 for every 1GB of RAM or -1 (autosizing if supported)
    table_open_cache = 10000                ## 10000 for every 1GB of RAM
    open_files_limit = 20000                ## This can be 2x to 3x the table_open_cache value
    
    
    
    bulk_insert_buffer_size = 16M
    max_heap_table_size = 32M               ## 32MB for every 1GB of RAM
    tmp_table_size = 32M                    ## 32MB for every 1GB of RAM
    
    
    key_buffer_size = 32M                   ## 1024M for Recommend or 32M every 1GB of RAM
    
    
    
    ft_min_word_len = 3
    
    
    # Buffer Settings
    sort_buffer_size = 2M                   ## 1MB for every 1GB of RAM
    read_buffer_size = 1M                   ## 1MB for every 1GB of RAM
    read_rnd_buffer_size = 1M               ## 1MB for every 1GB of RAM
    join_buffer_size = 1M                   ## 1MB for every 1GB of RAM
    
    
    
    
    default_storage_engine  = InnoDB
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    
    # Innodb Settings
    innodb_buffer_pool_instances = 1        ## Use 1 instance per 1GB of InnoDB pool size
    innodb_buffer_pool_size = 128M          ## 1G 128M 2G 256 every 1GB of RAM Recommend or (Can use up to 60-70% of RAM)
    innodb_log_buffer_size = 4M             ## 4M every 1GB of RAM - Max value = 64M
    innodb_log_file_size = 64M              ## 64M every 1GB of RAM - Max value = 1G
    innodb_file_per_table = 1
    innodb_stats_on_metadata = 0
    innodb_read_io_threads = 64             ## Max Value = 64 (Can reduce for 16 every 1GB of RAM)
    innodb_write_io_threads = 64            ## Max Value = 64 (Can reduce for 16 every 1GB of RAM)
    innodb_thread_concurrency = 1           ## Max Value = 2 or Optimize use by -1, -2 CPUs on server
                                            ## Such as your server has 8 CPUs, try 6 or 7 and check load by MySQL or MariaDB
    
    innodb_open_files   = 400
    innodb_io_capacity  = 400
    
    
    
    innodb_temp_data_file_path = ibtmp1:16M:autoextend:max:256M
    
    
    log_error   = /var/log/mysql/mariadb_error.log
    
    
    #slow-log
    slow_query_log = 0
    long_query_time = 5
    slow_query_log_file = /var/log/mysql/mariadb_slow.log
    
    
    
    log_bin         = /var/log/mysql/mariadb-bin
    log_bin_index       = /var/log/mysql/mariadb-bin.index
    #sync_binlog        = 1
    expire_logs_days    = 10
    max_binlog_size         = 100M
    
    
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet = 32M                ## 128M for Recommend or 32M every 1GB of RAM
    
    
    [mariadb]
    #!include /etc/mysql/mariadb.cnf
    #!includedir /etc/mysql/conf.d/
    
    
Sign In or Register to comment.