title: MySQL memory configurations link: https://onemoretech.wordpress.com/2013/12/09/mysql-memory-configurations/ author: phil2nc description: post_id: 6755 created: 2013/12/09 10:59:47 created_gmt: 2013/12/09 15:59:47 comment_status: closed post_name: mysql-memory-configurations status: publish post_type: post

MySQL memory configurations

Found a great resource for sample MySQL configurations. Joyent is a cloud service provider that competes with the likes of Amazon and Microsoft. Among other things they're the primary sponsor of node.js and have developed their own fork of OpenSolaris. They've also got some pretty impressive documentation. For example, a veritable Chinese menu of sample MySQL server configurations by desired memory footprint. These examples assume a monolithic /etc/my.cnf file. Here's what they came up with for a 4G memory space:

##  JOYENT: MySQL 5.1 Configuration
##

[client]
port                                    = 3306
socket                                  = /tmp/mysql.sock
default-character-set                   = utf8

# The MySQL server
[mysqld]
user                                    = mysql
basedir                                 = /mysql
datadir                                 = /databases
bind-address                            = xxx.xxx.xxx.xxx
port                                    = 3306
socket                                  = /tmp/mysql.sock
tmpdir                                  = /tmp
slow_query_log                          = 1
slow_query_log_file                     = /databases/slowquery.log
long_query_time                         = 1
skip_external_locking
default-storage-engine                  = innodb
server-id                               = 1
character-set-server                    = utf8

# Capacity Limits
#thread_concurrency                     = 0
max_connections                         = 200
max_allowed_packet                      = 24M
table_open_cache                        = 256

# General
query_cache_size                        = 16M
thread_cache_size                       = 128

# MyISAM Engine Settings
key_buffer                              = 400M
myisam_sort_buffer_size                 = 32M

# InnoDB Engine settings
innodb_data_home_dir                    = /databases
innodb_data_file_path                   = ibdata1:100M:autoextend
innodb_buffer_pool_size                 = 2048M
innodb_additional_mem_pool_size         = 30M
#innodb_thread_concurrency              = 0
innodb_flush_log_at_trx_commit          = 2
#innodb_doublewrite                     = 0
innodb_log_buffer_size                  = 32M
innodb_log_file_size                    = 512M


[mysqldump]
quick
max_allowed_packet                      = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer                              = 128M
sort_buffer_size                        = 128M
read_buffer                             = 2M
write_buffer                            = 2M

[myisamchk]
key_buffer                              = 128M
sort_buffer_size                        = 128M
read_buffer                             = 2M
write_buffer                            = 2M

[mysqlhotcopy]
interactive-timeout

Here's my own "standard" config for the same memory footprint:

[mysqld]
datadir=/data/app/mysql/var
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

# For debugging
log-error=/data/logs/mysql/mysqld.log

# For standards
default-storage-engine=innodb
character-set-server=utf8
collation-server=utf8_general_ci

# For performance
back_log=50
open_files_limit=65535
table_open_cache=1024
max_allowed_packet=16M
max_heap_table_size=64M
read_buffer_size=2M
read_rnd_buffer_size=16M
sort_buffer_size=8M
join_buffer_size=8M
thread_cache_size=8
query_cache_size=16M
query_cache_limit=64M
thread_stack=192K
tmp_table_size=64M
innodb_buffer_pool_size=1G
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=8M
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
innodb_file_per_table
innodb_additional_mem_pool_size=20M
key_buffer_size=512M
skip_external_locking

[mysqldump]
quick

[myisamchk]
key_buffer_size=512M
sort_buffer_size=512M
read_buffer=8M
write_buffer=8M

[mysqld_safe]
open_files_limit=65535
log-error=/data/logs/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Copyright 2004-2019 Phil Lembo