Optimizing MySQL - Tuning The Server (
Page 3 of 6 )
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.
