Sunday, September 23, 2007

Skillsets for the successful MySQL DBA

DBAs have spent years developing their expertise in database management best practices, backup/recovery, performance tuning and writing really cool scripts. Today's successful DBA needs skills beyond just the management of their databases. These skills include:
  • Operating Systems: Having sufficient knowledge in the operating systems their databases run in.
  • Best Practices: Maintaining best practices for database management.
  • Automation: Automating your database management.
  • Supporting Multi-tiered Infrastructures: Being able to manage databases in a multi-tiered environment.
  • Supporting Development: Being able to support or a least understand the development environment for your databases.

Operating Systems

If your database is running on a Windows, Linux or Unix systems the more knowledgeable you are in the operating systems of your database platforms the better you are at troubleshooting and performance tuning your database environment. The successful DBA will
optimize the operating systems their MySQL databases are running on.
  • Todays operating systems such as Windows, Linux and Unix are very robust operating systems. Most operating systems are installed with services and RPMs that are not needed for the database platform. For most production servers running MySQL, OS features such as FTP, Telnet, Print Servers, etc. should not be running on the server platform running MySQL. Having the operating systems loaded with unnecessary features creates security violations and has an overweight operating system taking unnecessary resources away from the databases. A server running databases should have the operating system optimized for running database servers.
Best Practices

Most DBAs are not supporting a single database. They are probably part of a team supporting multiple databases. DBAs need to have a consistent set of management guide lines (see Optimal Configuration for MySQL article) for managing their databases. Setting up an optimal configuration for MySQL database environments and following a set of consistent guide lines will minimize mistakes, reduce down time and ensure optimal configurations for performance.

DBAs also need to have a method for managing their infrastructure information. Errors that occur once are likely to occur again. Each DBA should be logging errors and everything they do to manage their database environments. DBAs need to periodically review how they are spending their time and the issues they address on a daily basis. This approach will often have a DBA recognize consistent patterns of their management that can help them identify areas that need to be worked on. Sometimes seeing the forest through the trees can be difficult. Periodically (weekly, monthly) reviewing daily tasks can help identify trouble areas that need to be worked on.

Automation

The traditional DBA has worked on writing scripts to manage enterprise environments. DBAs get excited when they write a really cool script for managing their databases. They show this script to other DBAs, and its like a caveman showing other cavemen fire for the first time. There are a lot of ohhhs and aughhhs. But writing scripts is no longer the best way to manage databases. The more scripts you write:
  • The more you are building a high maintenance environment. More scripts will keep adding to the complexity and maintenance of their environments. I was at a client site that was running over a hundred databases. They were upgrading their operating system and databases to a higher release in a multi-phased approach. They wanted me to go out and update their backup scripts on 140 platforms. It would be better to develop a centralized method to running backups versus having different scripts that have to be constantly updated. Using a scheduler to facilitate tasks that need to be automated is much better than constantly updating cron scripts across different platforms.
  • The more you are dependent on the skills of an individual person. Once a new DBA moves in they often replace the scripts written by a previous DBA. They don't want to bet their job on scripts written by someone else. This reoccurring pattern can become a significant waste for organizations over a period of time.
Software should be used for:
  • Scheduling reoccurring tasks. Backups, batch jobs, maintenance routines, reports, etc. should all be coordinated through a central repository.
  • Monitoring your databases as well as the operating systems, file systems, application servers, software services and all other components of an environment that impact the database.
I realize smaller systems may have not the budget or infrastructure for automation software. However there is a lot of free software out there that can help you with automation and monitoring.


Supporting Multi-tiered Infrastructures

Web applications are the standard way to develop applications today. Most new applications are web applications and not client/server applications. Web applications include application servers. Web applications are written in Java, PHP, Ruby on Rails, Groovy, etc. DBAs don't have to be an expert in Java and the other web development environments. However they should at least be conversational about the technologies surrounding their databases.

The application server is also becoming a central piece of the architecture. Internet applications are retrieving thousands or millions of records across the network and then processing them on the application server. Middleware developers are very knowledgeable on developing applications for the middle tier, however their priority is usually not how to optimize these applications for database performance. The issues between the database server and the application server are creating a lot of issues in organizations. The more a MySQL DBA can support Apache, JBoss or other application servers accessing MySQL the more valuable they are in the market place.

Stored procedures can be a great way to reduce network traffic. Processing and filtering the database in the database server before sending the data to the application server can reduce the network traffic between the database server and the application server. Also a database server is usually much more efficient processing and filtering data that Java, PHP or any other web development language.

Supporting Development

Java, PHP, Ruby on Rails, Groovy and other web development languages are becoming common place in MySQL development environments. A DBA that can support developers provide a lot of increased value to an organization.

Conclusion

The one constant for MySQL DBAs is that the following areas are going to continue to grow in their MySQL environments:
  • The number of databases they have to support.
  • The volume of data in databases is going continue to increase at ever increasing rates.
  • Clients are going to request faster and faster response times.
  • Requirements for less and less down time.
The world of database administration requires that MySQL DBAs constantly work smarter and just not harder. MySQL DBAs that are constantly working on improving their best practices, automation and skills in the environments their MySQL databases run in are going to increase in value to their organizations and in the market place.

The good thing is that MySQL databases are becoming more and more popular in today's competitive environments. Open source environments using Linux, Apache, JBoss, PHP and Java are becoming more and more attractive to corporate America as well as the small business and government areas. The skill sets identified in this article will definitely help separate you from other DBAs in the marketplace.

No comments: