Home arrow MySQL arrow Page 3 - Optimizing MySQL

Tuning The Server - MySQL

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.

  1. Optimizing MySQL
  2. Compiling and Configuring MySQL
  3. Tuning The Server
  4. Table Types
  5. Even More Optimization...
  6. Conclusion
By: W.J. Gilmore
Rating: starstarstarstarstar / 9
January 29, 2001

print this article


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.

>>> More MySQL Articles          >>> More By W.J. Gilmore

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: