When installing MySQL, it is a good idea to set some key parameters upon setup to ensure that your database will run smoothly and efficiently. Setting these ahead of time can help you not end up in a situation where you have to update settings after your database has grown substantially and the application is already in production.
What are Parameters?
Parameters are values that are stored in the MySQL configuration file. This file is called my.cnf, and the location will vary from system to system. You will need to check the installation on your system to determine the location of the file.
One possible method of finding the location of the MySQL configuration file. Source: Stack Overflow.
Keep in mind; however, that many parameters can be set temporarily by running the SET GLOBAL or SET SESSION MySQL queries. It is a good idea to do this first (provided the parameter is part of the group of dynamic system variables) to ensure the changes are helpful before changing them in the more static configuration file.
With that in mind, here are some key parameters you can set in your MySQL configuration.
query_cache_size
Due to issues with the query cache actually making things slower in many cases, it is often recommended to disable it by setting this parameter to have a value of 0 (zero).
max_connections
With a default setup, you may often end up getting the Too many connections error, due to this parameter being set too low. However, setting it too high can also become problematic, so you will want to test at different settings to see what works best for your setup and applications.
One possible method of finding the current number of max connections. Source: Stack Overflow.
innodb_buffer_pool_size
If you are using InnoDB, then this is a very important parameter to set, as this buffer pool is where the system will cache indexes and data. A higher setting allows the memory to be used for reading rather than the disks, which will improve performance. The setting will depend on your available RAM; for example, if you have 128GB of RAM, a typical setting would be between 100 and 120GB.
innodb_log_file_size
This log file size determines how large the redo logs will be. Often, this will be set between 512 MB (for common usage) and 4GB (for applications that will do a large number of write operations).
log_bin
If you do not want the MySQL server to be a replication master, then the standard recommendation is to keep this disabled by commenting out all lines that begin with log_bin or expire_log_days in the MySQL configuration file.
Get Your Own MySQL Database
If you do not already have a MySQL database up and running, one way to easily obtain one is to use a service such as Morpheus, which offers databases as a service on the cloud. With Morpheus, you can easily set up one or more databases (including MySQL, MongoDB, and others).
In addition, all databases are deployed on a high performance infrastructure with Solid State Drives, and backed up, replicated, and archived. Open a free account today!
