Optimal MySQL configurations

I read a book one time called the "Seven Habits of Highly Successful People". After I read it I thought what a waste of money I already knew everything that was in the book. There were no new ideas in it. I then realized a number of the seven habits I was not doing. And if I did them I would be more successful. I then realized maybe there is a reason the book stays in the New York Times best seller list year after year.

The following points are similar to the seven habits. They are a set of good practices and guidelines that if you follow, will make your database management a lot easier to support. If you follow them, you'll spend a lot less time working nights and weekends.

Any set of database management best practices need to include:

• A standard way of organizing software and data on disks.
  
• Separate database software from operating system and other third party software.
• Separate data from log files. The files that store your data and the files need to recover your data should never reside on the same disk drives and controllers.
• Data, index files and log files should be distributed to reduce I/O bottlenecks and to maximize recovery and reduce administration.
• Having proven and tested (on a regular basis) backups.
• Facilitate the management of installs, upgrades and backups. The routine tasks required of DBAs.
• Prepare for and manage the growth of data and databases.
• Minimize the fragmentation of tables and indexes.
• Have appropriate safe guards so disk or hardware failures will have a minimal impact. DBAs have to work with the people making the purchases so they have the right hardware and disk configurations. Distributing data across multiple disk drives reduces the impact of individual disk failures.
• There should be a consistent pattern for directory structures for software, backups, data files, log files, administration scripts, start up files and other other files associated with MySQL databases.
• The change in software directories, data directories, login directories should have minimal if no impact on database scripts and applications.
• Administrative information for each database such as logs, backup scripts, administration scripts should be separate for each database in a well organized directory structure pattern.
• Files of different types should be organized in separate directory structures.
• Separate file systems should exist for software, data, log files, backups, etc. as much as possible.
• Protecting the hardware platforms and operating systems that are running MySQL databases.
• Use a consistent naming pattern for all data files, index files, log files, scripts, database names and file systems supporting files related to MySQL databases.
• If your organization is using storage drives, make sure you understand are are working closely with the storage management teams to optimize any striping and mirroring of database storage.
• Software to manage and monitor a complex environment. The Enterprise Monitor tool from MySQL can be found at http://mysql.com/products/enterprise/benefits.html.

Database and software configurations need to address the constant tasks a DBA performs.

• Adding new users.
• Duplicating databases.
• Managing changes for development, test and production database environments.
• Upgrades of operating systems, databases, applications and hardware.
• Adding storage for data increases.
• Creating new databases.
• Adding new hardware, disk drives, etc.
• Constantly distributing I/O workloads.

I realize if you are a new DBA this can be a little overwhelming. Starting simple can be the best approach and as you get more experience following more of the above practices will become important. If you are an experienced DBA review the above guidelines and your best practices to make sure your management practices are honed to a fine edge.