1

I'm find my.cnf optimized for 2G RAM. It's good configuration for 2G RAM and magento 1.9 and 5.5.56-MariaDB? Server VPS CentOS 7.5 on SSD. In this case i have about 30 domains in 1 multidomain configuration and about 300 items and about 270 concurrent connections.

########################################################################
#DATE: 2011-02-02
#SITE: http://datastrangler.com
#DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x 
#RAM: 2GB RAM dedicated server
#Connections: 500 connections
########################################################################
[mysqld_safe]
nice                = -15

[client]
socket              = /var/lib/mysql/mysql.sock
default-character-set       = utf8

[mysqld]
## Charset and Collation
character-set-server        = utf8
collation-server        = utf8_general_ci

## Files
back_log            = 300
open-files-limit        = 8192
open-files          = 1024  
port                = 3306
socket              = /var/lib/mysql/mysql.sock
pid-file            = /var/lib/mysql/mysql.pid
skip-external-locking
skip-name-resolve

## Logging
datadir             = /var/lib/mysql
relay_log           = mysql-relay-bin
relay_log_index         = mysql-relay-index
#log                = mysql-gen.log 
log_error           = mysql-error.err
log_warnings
log_bin             = mysql-bin
log_slow_queries        = mysql-slow.log
#log_queries_not_using_indexes
long_query_time         = 10    #default: 10
max_binlog_size         = 256M  #max size for binlog before rolling
expire_logs_days        = 4 #binlog files older than this will be purged

## Per-Thread Buffers * (max_connections) = total per-thread mem usage
thread_stack            = 256K    #default: 32bit: 192K, 64bit: 256K
sort_buffer_size        = 256K    #default: 2M, larger may cause perf issues
read_buffer_size        = 256K    #default: 128K, change in increments of 4K
read_rnd_buffer_size        = 256K    #default: 256K                
join_buffer_size        = 256K    #default: 128K
binlog_cache_size       = 64K     #default: 32K, size of buffer to hold TX queries
## total per-thread buffer memory usage: 672000K = 656.25MB

## Query Cache
query_cache_size        = 32M   #global buffer
query_cache_limit       = 512K  #max query result size to put in cache

## Connections
max_connections         = 500   #multiplier for memory usage via per-thread buffers
max_connect_errors      = 100   #default: 10
concurrent_insert       = 2 #default: 1, 2: enable insert for all instances
connect_timeout         = 30    #default -5.1.22: 5, +5.1.22: 10
max_allowed_packet      = 32M   #max size of incoming data to allow

## Default Table Settings
sql_mode            = NO_AUTO_CREATE_USER

## Table and TMP settings
max_heap_table_size         = 128M  #recommend same size as tmp_table_size
bulk_insert_buffer_size     = 128M  #recommend same size as tmp_table_size
tmp_table_size                  = 128M 
#tmpdir                         = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir

## Table cache settings
#table_cache            = 512   #5.0.x <default: 64>
#table_open_cache       = 512   #5.1.x, 5.5.x <default: 64>

## Thread settings
thread_concurrency      = 16  #recommend 2x CPU cores
thread_cache_size       = 100 #recommend 5% of max_connections

## Replication
#read_only
#skip-slave-start       
#slave-skip-errors      = <default: none, recommend:1062>
#slave-net-timeout      = <default: 3600>
#slave-load-tmpdir      = <location of slave tmpdir>
#slave_transaction_retries  = <default: 10>
#server-id                      = <unique value>
#replicate-same-server-id       = <default: 0, recommend: 0, !if log_slave_updates=1> 
#auto-increment-increment       = <default: none>
#auto-increment-offset          = <default: none>
#master-connect-retry           = <default: 60>
#log-slave-updates      = <default: 0 disable>
#report-host            = <master_server_ip>
#report-user            = <replication_user>
#report-password                = <replication_user_pass>
#report-port            = <default: 3306>
#replicate-do-db                =
#replicate-ignore-db        =
#replicate-do-table     = 
#relicate-ignore-table      =
#replicate-rewrite-db       =
#replicate-wild-do-table    =
#replicate-wild-ignore-table    =

## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability 
#rpl_semi_sync_master_enabled   = 1 #enable = 1, disable = 0
#rpl_semi_sync_master_timeout   = 1000 #in milliseconds <default: 10000>, master only setting

## 5.1.x and 5.5.x replication related setting. 
#binlog_format          = MIXED

## MyISAM Engine
key_buffer          = 512K  #global buffer
myisam_sort_buffer_size     = 128M  #index buffer size for creating/altering indexes
myisam_max_sort_file_size   = 256M  #max file size for tmp table when creating/alering indexes
myisam_repair_threads       = 4 #thread quantity when running repairs
myisam_recover          = BACKUP    #repair mode, recommend BACKUP 

## InnoDB Plugin Dependent Settings
#ignore-builtin-innodb
#plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so

## InnoDB IO Capacity - 5.1.x plugin, 5.5.x
#innodb_io_capacity     = 200

## InnoDB IO settings -  5.1.x only
#innodb_file_io_threads     = 16

## InnoDB IO settings -  5.5.x and greater
#innodb_write_io_threads    = 16
#innodb_read_io_threads     = 16

## InnoDB Plugin Independent Settings
innodb_data_home_dir            = /var/lib/mysql
innodb_data_file_path       = ibdata1:128M;ibdata2:10M:autoextend
innodb_log_file_size        = 64M   #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 
innodb_log_files_in_group   = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4
innodb_buffer_pool_size     = 768M  #global buffer
innodb_additional_mem_pool_size = 4M    #global buffer
innodb_status_file          #extra reporting
innodb_file_per_table           #enable always
innodb_flush_log_at_trx_commit  = 2 #2/0 = perf, 1 = ACID
innodb_table_locks      = 0 #preserve table locks
innodb_log_buffer_size      = 32M   #global buffer
innodb_lock_wait_timeout    = 60    
innodb_thread_concurrency   = 16    #recommend 2x core quantity
innodb_commit_concurrency   = 16    #recommend 4x num disks
#innodb_flush_method        = O_DIRECT     #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI
innodb_support_xa       = 0        #recommend 0, disable xa to negate extra disk flush
skip-innodb-doublewrite

## Binlog sync settings
## XA transactions = 1, otherwise set to 0 for best performance
sync_binlog         = 0

## TX Isolation
transaction-isolation       = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA

## Per-Thread Buffer memory utilization equation:
#(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections

## Global Buffer memory utilization equation:
# innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size

[mysqldump]
quick
quote-names
max_allowed_packet      = 128M
  • Depends on max concurrent users – Dominic Xigen Nov 13 at 13:00
  • for 2gb ram you need minimal linux install. you need to know your db size and traffic, but your config is over 2gb – MagenX Nov 13 at 23:25
  • Additional information request. Post on pastebin.com and share the links. # cores of your MySQL Host server From SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Nov 14 at 20:59
  • How many items are in your Magento catalog? Please post the information requested Nov 14, 19 so we can provide suggestions after analysis. – Wilson Hauck Nov 19 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.