Mysql Config parameters

In particular, note the high values set for the InnoDB related parameters, below, and change them to match your system and application’s requirements. You have been warned.

[client]
#password = [your_password]
port = 3306

# The following location is not the default location for many
# pre-packaged utilities. For example, mytop or mysqlreport
# (or any Perl or Python based client, for that matter), will
# NOT look in the /tmp path for the mysql.sock file.
# The value is set to common location by your RPM or DEB
# package on GNU/Linux, yet defaults to the following in the
# .tgz mysql-server downlaod files.
socket = /tmp/mysql.sock

default-character-set=utf8

[mysqld]

# For a new installation, best to use a strict sql_mode:

sql_mode=TRADITIONAL
datadir= [path to mysql data]
# I prefer working case-sensitive. However, some 3rd party tools
# demand case insensitivity. If you’re working with such a tool,
# uncomment the following line.
#lower_case_table_names=1

# 5-10 minutes timeout is usually more than required for idle
# connections. If a connection can be idle for so long, then
# creating a new connection is usually very cheap. This applies
# for many common web based applications. However, depending
# on your application, you may wish to set this parameter to a
# higher value.
wait_timeout=300

# The following is unfortunately still used on many instances. If
# you need backwards compatability, you may need to uncomment
# the following line.
#old_passwords=1

# generic configuration options
port = 3306
# Make sure this is the same location as in the [client] section
socket = /tmp/mysql.sock

back_log = 50
max_connect_errors = 10

# Resources:
# The following values are liberal. You may reduce them
# according to your needs.
# NOTE: verify that your OS’s open files limit is at least as
# specified here.
# I usually set ‘ulimit -n’ to allow for 8192 files to mysql.
open_files_limit = 8000
# The following is usually cheap, so go ahead and set a high value.
table_cache = 2048
# Connections take memory, so don;t be too liberal here. A common,
# well tuned application, using proper connection pools, shouldn’t
# require more than 1000 concurrent connections.
max_connections = 500

# Memory:
# When required, the following parameters will be used to allocate
# more memory. The value specified is always the amount of memory
# allocated, regardless of the real need.
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
# The following value sets an upper limit only: MySQL will only
# allocate as much as required. Setting this parameters to a high
# value may be required if you’re handling very large statement,
# such as queries reading/writing BLOBs.
max_allowed_packet = 16M

# Even on a very busy server, a thread cache of 32 threads is
# usually enough to make for good thread recycling. I rarely go
# above this number.
thread_cache_size = 32
thread_concurrency = 8

# Query cache:
# Make sure you really need the query cache. I’ve seen many
# applications where the query cache almost utterly unused
# (due to frequent invalidation). Check for your hit:insert ratio.
# A query cache type of value “2″ (DEMAND) is a more programmatic
# oriented setting, where most queries are not cached, but a few,
# recognized to benefit from the query cache, are executed with
# the SQL_CACHE flag.
query_cache_type = 1
query_cache_size = 32M
query_cache_limit = 2M

ft_min_word_len = 4
# I usually go with InnoDB, which is ACID compliant and crash-safe.
default_table_type = InnoDB

thread_stack = 192K

transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M

# Logs
# Having log-bin enabled allows for replication and for point in time
# recovery. It’s a good idea to always have binary logs at hand.
# binary logs can also be reviewed to see what kind of modifications
# are made on your database (the general query log also logs
# SELECTs and other queries, which do not appear in the binary log)
# I usually name the logs after my host name. When dealing with
# replication, having master and slave logs named after the machines
# on which they are running makes for clearer distinction between
# them.
log-bin=mymachine-bin
relay-log = mymachine-relay-bin
relay-log-index = mymachine-relay-bin.index
# Since a slave can suddenly be promoted to Master’s role, I always
# take care that it also has the binary logs prepared.
log_slave_updates
log-error=mymachine-mysql-error.log
log_slow_queries
long_query_time = 2
log-queries-not-using-indexes
# Automatically remove binary logs. Make sure this number is not too
# high and not too low for you, depending on your backups frequency
# and maximum slave lag.
expire_logs_days=7

# Replication related settings:
# Unique server identification number between 1 and 2^32-1.
server-id = [a unique number]
# Setting the report_host allows for SHOW SLAVE HOSTS on the master
# to see which slaves are connected.
report_host=[machine’s ip]
# Limit the total size of the relay logs: set this value to a high enough
# value. When the slave can’t keep up with the master, the relay logs
# fill up. If no limit is set, all disk space can be consumed.
# If a value too low is set, the master’s binary logs cannot (shouldn’t)
# be purged, and so the master gets to have a disk space problem. So
# provide a high enough value, and monitor your disk space!
relay_log_space_limit=10G

# MyISAM:
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover = force,backup

concurrent_insert=2

# INNODB:

innodb_file_per_table
# Your machine’s memory capacity dictates many of the following
# values. ***Do NOT take these values as they are. Change them***
innodb_buffer_pool_size = 10G
innodb_additional_mem_pool_size = 16M
innodb_data_file_path = ibdata1:50M:autoextend
innodb_file_io_threads = 4
# Keep the following one commented, unless you need to recover
# from disaster.
#innodb_force_recovery=4
innodb_thread_concurrency = 8
# Set “1″ for full ACID compliance. Set “2″ for IO performance gain
# (will only flush transaction log to disk once per second, instead
# of at each commit). If you have battery backed disk cache ,set
# to “1″ and enjoy both worlds.
innodb_flush_log_at_trx_commit = 1
# Keep the following one commented, unless you really have an
# emergency. Fast shutdown makes for slower start up time.
#innodb_fast_shutdown
innodb_log_buffer_size = 8M
# The default here is 5M, which is really too low for our modern
# machines.
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
# Consult The manual. The desired values for the following
# parameter change according to your OS type, OS version and
# hardware types. Benchmark after changing it!
#innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 64M

default-character-set=utf8

#######################################################

Configuration Directives

back_log (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 50
Well tuned server is capable work simultaneously with many connections at the same time. Every connection must be delegated from the main thread of MySQL and get its own new thread. The back_log parameter determines the number of connections allowed in the stack when the main thread handles requests for new connection.

datadir (filename) | manual
Cmd-line: Yes | Option file: Yes | Default: /var/db/mysql/
It is common practice to have MySQL data separated on other partition or disc. Using this option we can set the path to directory where MySQL data will be stored. Keep in mind that you must move or copy the MySQL privilege tables to selected location too!

default-storage-engine (string) | manual
Cmd-line: Yes | Option file: Yes | Default: MyISAM
MySQL supports several types of engines (tables) from which everyone has some advantages. Based on your needs, you should choose the right one for your data. If you use very often some type of engine, you can set this as a default by this option.

init-file (filename) | manual
Cmd-line: Yes | Option file: Yes | Default: not set
There’s possibility to execute a series of commands at MySQL start-up by placing them into the file which name is set as a value of this option. For instance, we need to truncate some table when MySQL starts. So, put this command into the file mysqlinitcmds.sql and set option init-file="/path/to/the/file/mysqlinitcmds.sql".

key_buffer_size (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 8388608
This option determines amount of memory (buffer size) allocated for index blocks. The bigger buffer the more index blocks in there and the higher speed of index lookups (because of less need to read from disc).

log-slow-queries (boolean) | manual
Cmd-line: Yes | Option file: Yes | Default: false
This option defines the file (optional parameter) where all queries which execution exceeded thelong_query_time are logged. If optional parameter filename is omitted, slow queries are inserted into theslow_log table of mysql database. By exploring the slow_log table or mysqldumpslow output you can get an overview of your database server bottlenecks. There will be separate post dedicated to slow queries written on this blog.

max_allowed_packet (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 1048576
In the world of MySQL, a packet is equivalent to a single SQL statement or a row returned to the caller. Themax_allowed_packet can have a value equal to the server RAM. You probably will not concern this option unless storing extra large columns, such as BLOBs.

max_connections (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 100
This parameter determines maximum number of simultaneous database connections. You can check current database connections by reviewing the max_used_connections parameter in the SHOW STATUS output. If you see the number close to 100, you should consider bumping the maximum upward, but keep in mind that more allowed connections results to more memory consumption, because MySQL allocates additional memory to every opened connection.

net_buffer_length (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 16384
This parameter determines the initial buffer allocation for each client-server connection.

read_buffer_size (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 131072
This parameter determines the amount of memory available for any thread which sequentialy scans a table.

skip-name-resolve (none) | manual
Cmd-line: Yes | Option file: Yes | Default: disabled
If enabled, MySQL will not resolve hostnames, what means that all values in the Host column of privilege table must be an IP addresses or localhost. Resolving hostnames takes some time, so if you are OK with IP use only, enable this parameter by writing skip-name-resolve on separate line in the config file (most probably my.cnf).

skip-networking (none) | manual
Cmd-line: Yes | Option file: Yes | Default: disabled
If enabled, MySQL will not listen to TCP/IP connections. This is reasonable in cases when MySQL installation resides on the same server from which connections are iniciated.

thread_cache_size (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 0
This option determines the number of threads allowed to be stored in the cache for reuse. When new connection is initiated, MySQL first looks into the cache and if there’s available any thread, MySQL use it. If the cache is empty, MySQL creates new thread. So, if you deployed your distribution at the place where many connections is required, you should raise the value of this option from default zero.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Mysql Config parameters

  1. shrikant says:

    Hi.. the blog is very nice and helpfull very much.. Specially Replication in PostgreSQL is very helpful for me understand.. Can you please tell me whats the content of that trigger file to take over or is it available already..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s