MySQL : Useful Startup Options
The MySQL documentation contains an extensive list of startup options. This article lists a few of them that will probably jump out at you during the early days of learning MySQL.
Unless otherwise stated, I will assume the following parameters are used in the "[mysqld]" section of the "/etc/my.cnf" option file.
- File Placement (datadir, log_bin)
- InnoDB Memory Options (innodb_buffer_pool_size etc.)
- Reverse DNS Lookups (skip-name-resolve)
- Password Verification (plugin-load, validate-password)
File Placement (datadir, log_bin)
InnoDB Memory Options (innodb_buffer_pool_size etc.)
You should probably start by reading this top-level view of Optimizing InnoDB Disk I/O.
The innodb_buffer_pool_size parameter is similar to the
DB_CACHE_SIZE in Oracle databases. It defines the pool of memory to hold table and index data, which helps reduce file I/O. You should probably make this value as large as possible, up to about 80% of the physical memory on a dedicated MySQL server. Remember, multiple MySQL instances or multiple applications running on the server will mean you have to scale back this figure. Make sure the setting is not so high that you use up all the physical memory on the server force it to start swapping.
The innodb_additional_mem_pool_size parameter determines how much memory is available for the data dictionary. If this is not enough, more is allocated from the OS memory and a message is written to the error log.
The innodb_log_file_size parameter determines the size of the log files in each log group. Larger log files will reduce the amount of checkpoints, which will increase performance by reducing I/O load, but may decrease crash recovery time in older versions of MySQL.
The innodb_log_buffer_size parameter adjusts the size of the log buffer. Applications that perform DML statements that touch large numbers of rows may see performance improvements by increasing the log buffer size, thereby reducing the number of flushes to disk.
The innodb_flush_log_at_trx_commit parameter allows you to balance ACID compliance against performance. The default setting of "1" provides full ACID compliance, but other values may improve performance at the risk of losing approximately 1 seconds worth of data in a crash situation.
innodb_buffer_pool_size = 4096M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_file_per_table
The innodb_file_per_table parameter is not a memory parameter, but it is included here because it is quite important. It prevents table and index data being stored in the "ibdata1" file, so it doesn;t grow so rapidly.
Reverse DNS Lookups (skip-name-resolve)
When a new connection is made, MySQL performs a reverse lookup to get the machine name associated with the IP address. At best this is wasteful. In some situations it is a complete pain. If you have slow DNS response times you are negatively impacting on the server. In some network setups, the reverse lookup can result in multiple machine names, which makes the process list look confusing.
Using the skip-name-resolve parameter prevents the reverse lookup, so the process list always contains the IP address.
# Prevent reverse DNS lookup. # Process list will just contain IP addresses. skip-name-resolve
Password Verification (plugin-load, validate-password)
The The Password Validation Plugin allows MySQL to force a greater degree of security around password management. The following entries load and force the server to run using the password validation plugin.
# Password verification. plugin-load=validate_password.so validate-password=FORCE_PLUS_PERMANENT
There are a number of options available to modify the default functionality of the plugin. For more details, check out the online documentation.
For more information see:
- InnoDB Startup Options and System Variables
- Optimizing InnoDB Disk I/O
- The Password Validation Plugin
Hope this helps. Regards Tim...