[MySQL 101] Configuration guideline

MySQL allow users/administrators to modify configuration that is suitable for your company's services. Administrator can modify it in /etc/my.cnf

Below is some basic configuration key for you to get started with MySQL.

Basic configuration keys

Keys	Explanation
port	Listening port that mysql daemon will open.
bind-address	IP address that mysql will listen on
socket	Socket path for storing connection
user	The user that mysqld will use to work
default_storage_engine	The storage engine that mysql will use. E.g: InnoDB, MyISAM, CSV, Memory
pid_file	The path of file location that store process id of mysql process

Log configuration keys

Keys	Explanation
log_output= [FILE|TABLES|NONE]	Indicate log output type that you want to store. To use log configuration keys related to file below, set log_output=FILE
log_error	The path to file that store error log.
long_query_time = [value]	The maximum value that every query that exceed the [value] will be considered as long query. The value in second.
slow_query_log	Enable/Disable the slow query log.
slow_query_log_file	The path to file that store slow query log.
general_log	Enable/Disable the general log. Mention in previous article [MySQL 101] Audit MySQL User Activities with McAfee Audit Plugin.
general_log_file	The path to file that store general log.

Performance tuning configuration keys

Keys	Explanation
max_connections	​Maximum concurrent connections accepted by MySQL at the same time. If the concurrent connections exceed this number, other upcoming connections will receive "Too many connections" response.
max_allowed_packet=[value]	Define the min/max size of packets received by server. It's set to 4MB by default. We could modify it when needed. Example: max_allowed_packet=[Min: 1KB, Default: 4MB, Maximum: 1GB]. It's not just like innodb_buffer_pool_size, the memory allocated is not use for other purpose. With max_allowed_packet, the memory allocated can be used for other threads.
thread_cache_size	​How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads.
table_open_cache	​The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable.
open_files_limit	​The number of file descriptors available to mysqld from the operating system: At startup, mysqld reserves descriptors with setrlimit(), using the value requested at by setting this variable directly or by using the --open-files-limit option to mysqld_safe. If mysqld produces the error Too many open files, try increasing the open_files_limit value. Internally, the maximum value for this variable is the maximum unsigned integer value, but the actual maximum is platform dependent. At runtime, the value of open_files_limit indicates the number of file descriptors actually permitted to mysqld by the operating system, which might differ from the value requested at startup. If the number of file descriptors requested during startup cannot be allocated, mysqld writes a warning to the error log. The effective open_files_limit value is based on the value specified at system startup (if any) and the values of max_connections and table_open_cache, using these formulas: 10 + max_connections + (table_open_cache * 2) max_connections * 5 The operating system limit if that limit is positive but not Infinity. If the operating system limit is Infinity: open_files_limit value if specified at startup, 5000 if not. The server attempts to obtain the number of file descriptors using the maximum of those values. If that many descriptors cannot be obtained, the server attempts to obtain as many as the system permits. The effective value is 0 on systems where MySQL cannot change the number of open files. On Unix, the value cannot be set greater than the value displayed by the ulimit -n command.
innodb_buffer_pool_size	​The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 134217728 bytes (128MB). The maximum value depends on the CPU architecture; the maximum is 4294967295 (2^32-1) on 32-bit systems and 18446744073709551615 (2^64-1) on 64-bit systems. On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server.
innodb_log_file_size	​The size in bytes of each log file in a log group. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 512GB. A pair of 255 GB log files, for example, approaches the limit but does not exceed it. The default value is 48MB.
innodb_flush_method	​Defines the method used to flush data to InnoDB data files and log files, which can affect I/O throughput. If innodb_flush_method is set to NULL on a Unix-like system, the fsync option is used by default. If innodb_flush_method is set to NULL on Windows, the async_unbuffered option is used by default.
innodb_file_per_table	​When innodb_file_per_table is enabled, tables are created in file-per-table tablespaces by default. When disabled, tables are created in the system tablespace by default.