Wednesday, December 19, 2007

More MOCA please ...

There are certain facts about how database servers work. A database configuration should follow best practices to address these facts. Below is a list of certain truths about database servers that are self-evident:
  • Default installs by database vendors are designed to be simple to install and under configured to minimize the initial use of resources. Default installs should not be used to run production servers.
  • Relational database servers run faster when memory is configured properly.
  • Balancing disk I/O is important.
  • Treat your information and the data you need to do recovery as matter and anti-matter. Never put them together.
  • Separate your database software from your data.
  • Separate your information logs and administration files.
  • Database software should never be installed as root or as the operating system administrator.
  • Database software, data, logs, administration files, recovery files should be kept separate from other types of files.
Default installs do not address the above. The default software for MySQL is designed to be simple to install and minimize the use of initial resources. However new users perform the default install and then use the default install to run an important database. This default install creates a "lot" of problems if you want to use MySQL as a database server. This is why I created MOCA. MOCA is defined further down in this blog.

MOCA stands for the MySQL Optimal Configuration Architecture. This is a summary of best practices that apply to any database server. Every experienced production DBA from Oracle, DB2, SQL Server, Informix and MySQL uses something similar that contains a list of guidelines and best practices that they have learned from years of experience that should be followed. For years Oracle DBAs have followed OFA (the Optimial Flexible Architecture). MOCA is a summary of best practices modeled from OFA and that I have picked up from 20 years of DBA experience. It is not a complete listing but a set of common sense guidelines that should be considered for a database installation.

I strongly recommend that if you are new to database administration you review MOCA. It pains me to see customers using the default install of MySQL that they installed as an operating system administrator userid and then they wonder why they are having so many problems. The default install will create pain and anguish in your life if you use it as a production configuration.

No comments: