MariaDB Innodb setup - need suggestion

Comments

• tetech Member

  September 2020 edited September 2020

  Insufficient information for meaningful answers. Run mysqltuner.
• pkr Member

  September 2020

  @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.
• tetech Member

  September 2020

  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
• Levi Veteran

  September 2020

  PerconaDB for innodb.
• coolgoole Barred

  October 2020 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/