After reading part one and two of our Database Normalization and MySQL series, we could use a little follow up.In this article, we'll discuss those facets of optimizing a MySQL server that relate directly to it's compilation, configuration and subsequent administration.
While ensuring proper compilation is certainly important, it is only part of the battle. You may be interested to know that you can configure many of the MySQL variables that play an important role in the server’s proper functioning. What’s more, you can store these variable assignments within a configuration file, ensuring that they are in effect every time the MySQL server is started. This configuration file is known as the ‘my.cnf’ file.
The MySQL developers are so helpful that they have taken the time to provide you with several sample my.cnf files, found in the /usr/local/mysql/share/mysql/ directory. These files are titled my-small.cnf, my-medium.cnf, my-large.cnf and my-huge.cnf, the size specification found within each title describing the type of system that configuration file would be valid for. If you are running MySQL on a system with just relatively little RAM, and you only plan on using MySQL occasionally, then the my-small.cnf file would be ideal since it commands the mysqld daemon to use just a minimum amount of resources. Alternatively, if you were planning on building the next E-commerce superstore, and are using a system with 2G of RAM, then you probably want to use the mysql-huge.cnf file (among other things).
In order to use one of these files, you’ll need to make a copy of the one that best fits your needs, renaming the copy as my.cnf. You have the option of using this copy to one of three scopes:
Global: Copying this my.cnf file to the server’s /etc directory will make its variables global in scope, that is, valid for all MySQL database servers found on the server. Local: Copying this file to [MYSQL-INSTALL-DIR]/var/ will render the my.cnf file local to that particular server. [MYSQL-INSTALL-DIR] denotes the directory in which MySQL is installed. User: You can even limit the scope to a particular user, accomplished by copying the my.cnf file to the user’s root directory.
So how do you set these variables within the my.cnf file? Furthermore, exactly which variables can you set? Although all variables are relative generally to the MySQL server, each has a more specific relationship to some component found within MySQL. For example, the variable max_connections falls under the mysqld category. How do I know this? I executed the following command:
%>/usr/local/mysql/libexec/mysqld --help
This resulted in the display of the various options and
variables relative to mysqld. You’ll easily identify the variables, as all are found below the line:
Possible variables for option --set-variable (-O) are
You could then set any of these variables within the
my.cnf file as follows:
set-variable = max_connections=100
This would result in the maximum number of simultaneous
connections to the MySQL server being limited to 100. Be sure to insert the set-variable directive under the [mysqld] heading in the my.cnf file. If you don’t understand what I’m talking about here, take a moment to review the configuration file.
