The MySQL Optimal Configuration Architecture

The MySQL Optimal Configuration Architecture (MOCA)

I am a firm believer in establishing best practices for database management and following them. As your databases grow in numbers and size, it becomes critical to establish best practices for your database management. I was asked recently to write down some of these best practices for managing MySQL databases.

Experienced DBAs have been following similar rules for years. They've learned that following these rules can minimize down time, reduce errors, minimize administration costs and improve performance. I am just sharing rules that DBAs have learned for years that to avoid pain and anguish as a DBA these rules or similar rules should be followed.

Key goals of the MySQL Optimal Configuration Architecture (MOCA) include:

• Define a set of guidelines for how to layout your MySQL software, data files, binary log files, administration logs, error files and administration files.
• Separate operating system administration files from database administration files.
  
• Emphasize the importance of standards and naming conventions.
• Define a physical layout to minimize downtime.
• Separate data files from the files needed to recover the data files.
• Defining a physical layout that is scalable to support multiple MySQL servers.
• Minimize administration costs of supporting multiple MySQL servers.
  

MOCA not GOCA

I thought it might be a good idea to name this layout. I initially wanted to name it George's Optimal Configuration Architecture (GOCA) but thought the MySQL Optimal Configuration Architecture (MOCA) rolled off the tongue better. It's easier to say "moca" versus "goca".

The nice thing about MySQL is that it can be a very simple install on platforms like Windows (note it can be an involved install on some platform and releases). Point click and it is up and running on some releases and platforms. If your environment is going to have a large number of MySQL installations your configuration will be more detailed to support larger environments. The configuration layout below is not designed to solve the meaning of life. The goal is to point you in the right direction and to have you consider the ramifications of your database layouts.

MOCA is not a trivial install

If you are new to MySQL or database administration, a default install may be more appropriate for getting comfortable with MySQL. It is important to understand that MOCA is a non-default install is not a trivial install. This install assumes you understand how to configure a MySQL server and you are comfortable with working with a database environment spread across different locations. If you are learning MySQL and it is your first install I would recommend doing a default install.

MOCA is set of best practices that should be used by experienced MySQL DBAs that are comfortable with setting up the MySQL environment and working with a MySQL environment.

Default Installs versus MOCA

As a MySQL DBA its important to note that MySQL is extremely portable across platforms but the default installs in Windows, Linux, Mac OS and Solaris are completely different. The default installs are great for the beginning DBA to learn MySQL. However the default install configurations should not be used for production environments running on different servers.

Developing a standard like the MySQL Optimal Configuration Architecture (MOCA) is important for defining consistency in configurations across different platforms. In different platforms looking for MySQL files (default installation locations) in /var/lib/mysql, /usr/local/mysql, /usr/sbin, C:/Program Files is not a consistent configuration. Read about MOCA and make sure you have consistent configurations across all hardware platforms.

Operating System Filesystem Hierarchy Standards

A key goal of MOCA is to define a set of guidelines and standards for how to layout your MySQL software, data files, binary logs, logs, administration files and to stress the importance of naming conventions.

Developing filesystem hierarchy standards for any operating system is important. The nice thing is OS administrators have usually taken care of this for the DBA. OS filesystem guidelines should not need to change significantly for databases.

Using the Linux Filesystem Hierarchy Standard as an example looks like this:
/initrd /proc /tmp
/bin /lib /root
/boot /lost+found /sbin
/dev /media /usr
/etc /mnt /var
/home /opt /srv

Using Linux as an example:
/etc - Will add startup scripts to make sure MySQL boots at startup, etc.
/home - Will contain home directory for MySQL userid.
/opt - Will contain MySQL software.

The big question is what to name the mount point directories that will hold the database files. I like them to have a pattern naming convention that shows these directories are dedicated to database files. Setting your own pattern is fine as long as it makes sense and is used consistently. A numbering convention should be used so all directories are the same length.

• Less than 100 directories us 00 - 99.
• Less than 1000 directories use 000 - 999.
• Less than 10000 directories use 0000 - 9999.

Example directory names:
Syntax:

• /[dbxx]/[software]/[db server name]/[specific type of directory]

Begin directory hierarchies for database files with (/db01)
Define database directory for mysql (/db01/mysql)
Define database server name for specific server (/db01/mysql/mysql01)
Define specific type of directory (/db01/mysql/mysql01/data)

Example directory structure

/db01/mysql/mysql01/data - MySQL data directory
/db02/mysql/mysql01/binlogs - location of binary log files
/db03/mysql/mysql01/ - administration directory HOME

/db04/mysql/mysql01/backups - backup files, export files and miscellaneous backups

These rules are dependent on the amount of hardware, disks, controllers and memory you have for your database environments. Remember more disks and memory makes you a better DBA. Here are my top recommendations for configuring a MySQL environment. A Unix/Linux format is shown but a similar naming convention should be used with Windows, etc. The naming conventions below are guidelines. The important thing is that once naming conventions are defined they be followed consistently on all database servers.

• Install the MySQL software (MYSQL_HOME) on its own disk partition. This should be a fairly static environment and not have any dynamic files in its location. Use a standard naming convention for each version of MySQL software. Try to avoid putting MySQL software in default operating system locations. Example:

MYSQL_BASE=/opt/software/mysql
MYSQL_HOME=$MYSQL_BASE/4.0 -- Home directory for version 4.0
MYSQL_HOME=$MYSQL_BASE/5.1 -- Home directory for version 5.1
MYSQL_HOME=$MYSQL_BASE/6.0 -- Home directory for version 6.0

• Each MySQL server should have its own administration directory. Directories for log files, scripts, backup startup files, etc. should be defined. The log directories here are only for the binary logs not the system logs (general, slow, query, ...).
  

-- Administration directory structure for server mysql01.
/db03/mysql/mysql01/logs
/db03/mysql/mysql01/scripts
/db03/mysql/mysql01/sql
/db03/mysql/mysql01/startup
/db03/mysql/mysql01/run

• Separate the binary logs location from the system logs. System logs such as the general log, slow query log, error log, etc. should be in a separate admin log.
  
• Place data files and binary log files on separate disks and controllers if possible. Treat data files and binary log files as matter and anti-matter. Never put data files and the files needed to recover the data on the same disks. Develop a consistent naming convention for where to place your data files and your binary log files.
  

-- Location of data files for MySQL servers mysql01 and mysql02.
/db01/mysql/mysql01/data
/db01/mysql/mysql02/data

--Location of binary log files for MySQL servers mysql01 and mysql02.
/db02/mysql/mysql01/binlogs
/db02/mysql/mysql02/binlogs

• Develop a consistent location for disk backups on all your database servers. Additional database exports should be stored in a consistent location.
  

-- Backup location for server mysql01.
/dbbackup/mysqldata/mysql01/backups
/dbbackup/mysqldata/mysql01/exports

-- Backup location for server mysql02.
/dbbackup/mysqldata/mysql02/backups
/dbbackup/mysqldata/mysql02/exports

• Always backup your startup configuration files. Using a version control system like RCS can be a good solution.
• When tuning or changing your database configuration, make small changes and see how they go. Try to avoid making a number of changes at once.
• Before installing a new release of software make sure you understand the current bug report on the new release and you have sufficiently tested the new environment the applications will run in.
• Periodically test your backups and recovery scenarios.
• Don't type DBA passwords on the command line prompt, ever! This also includes avoid using mysqladmin to set passwords at the command line prompt.
  
• Avoid running DML statements on the mysql system tables. For example, use the SET PASSWORD command, not the UPDATE command on the mysql.user table. Another example is use the DROP USER command not the DELETE command on the mysql.user table.
  
• Separate operating system administration from MySQL administration. Avoid putting MySQL files in directories like /etc (/etc/my.cnf), /usr/local (/usr/local/mysql) and /tmp (/tmp/mysql.sock).
• Be operating system neutral. For example, don't name your startup file my.ini and my.cnf on different platforms. Be consistent across all systems (i.e. name the startup file my.cnf on all platforms).
  
• Set SQL_MODE to an appropriate value for your environment. The "traditional" value makes sense in most OLTP environments.
• Understand the benefits and management requirements of the different storage engines.
• For important databases strongly consider the Enterprise support offered by MySQL. It is relatively very inexpensive but very important for supporting a MySQL environment.
• Consider using software to help you manage and monitor a MySQL environment.
  
• Before implementing any configuration (including this one) make sure it is well-tested and has gone through a number of different scenarios.
• Use fully qualified host names (myhostname.mycoolcompany.com). Do not use unqualified host names (myhostname).
• Be nice and supportive to your users. Remember, there are more users and developers than DBAs. If you are in a small shop and you are the DBA and developer, be nice to yourself.
  
• Develop a set of database polices that new users have to read and sign to be given a new account. This should be part of the help desk ticketing system. Get the approval of management and their support of these policies.
  
• If there are a large number of MySQL databases and I/O performance is important it may be necessary to use a storage system that supports striping.
  

Conclusion

Remember that DBA work does not always occur first thing in the morning when you are bright eyed and bushy-tailed. It may occur after working a 12-hour day or at 2:00 a.m. in the morning. The more you develop a consistent method method for how database related files the more you will reduce errors and make it easier to maintain your database environment. Spreading I/O for high activity data files will also improve performance.

This consistent layout will make it easier to write scripts and setup administration practices. Just double check and make sure you are not copying or writing files into the wrong directories.

These guidelines are designed to point out methods of how you want to organize your databases in a large enviroment. Customers are running hundreds and thousands of MySQL databases. Larger environments are also running storage arrays supporting striping and mirroring. Work with your operating system administrators and storage management team to determine the best physical layout of your databases.