Skip navigation links

MySQL Newsletter - Subscribe Today!

MySQL Administrator Best Practices

Delicious Digg StumbleUpon Reddit Twitter Facebook

Database administration serves a number of important functions from improving the performance of your database to avoiding a disaster that would result in lost data or costly system downtime. This article will describe some of the fundamental tools and practices programmers, as well as DBAs and SysAdmins, can use with the powerful new MySQL Administrator tool to ensure a healthy database environment.

The role of the administrative function will largely depend on the size of the organization and size of the IT department. In larger organizations there are DBA (Database Administrators), Data Analysts, and SysAdmins that would work alongside programmers to create a well designed database systems and then ultimately be responsible for managing the health and availability of the system on a daily basis. However, in smaller organization, or where IT departments are more resource limited, the duty of administering MySQL falls into the hands of the database programmer.

For programmers less familiar with the administration functions, their responsibility must also cover the following areas:

  • Optimizing the server for performance which includes, space management, monitoring memory usage, and diagnosing problems.

  • Administering the server on a daily basis to ensure server availability and the ongoing health of the system.

  • Disaster prevention and recovery including creating consistent backups and restoring databases in case of network, application, or hardware failure.

Until now, a powerful set of command line tools have been used to administer MySQL servers. However, they have been cumbersome and time-consuming to use for individuals not familiar or well-versed in the utilities. The result, in many cases, has been a general neglect of the administration function leaving the database system at risk to security breaches, data loss, and downtime.

To make administration easier and faster, MySQL AB recently announced MySQL Administrator, an integrated visual environment for managing MySQL servers. It is an ideal tool that not only greatly simplifies the life of DBAs but also empowers developers to effectively manage a networked environment of MySQL servers.

Below are 5 critical functions of database administration that must be addressed to ensure the level of availability your users will demand of your database system.

1. Optimizing Performance

Designing a well structured, normalized database schema is just half of the optimization task. The other half is building and fine tuning a server to run MySQL. MySQL provides a wealth of information regarding the tuning of server parameters. This information which was traditionally accessible in a configuration file called my.cnf, but can now be accessed and tuned visually using the MySQL Administrator graphical interface.

MySQL default parameters are very small so it can run on almost any box as an additional service without slowing things down. MySQL provides my-small/medium/huge.cnf files that come with the standard binary distribution as general parameter guidelines for small, medium, and huge servers.

Many of the optimal parameter values will depend a lot on the load you put on the server, as well as the storage engines(s) you use. But, here are some generic guidelines to optimize performance regardless of which storage engine you are using. We will take a look at storage engine specific tuning options in a future article so please, continue to watch this space.

Set max_connections to the number of concurrent connections you need. The default value is only 100 connections, which is very small.

Note: connections take memory and your OS might not be able to handle a lot of connections. MySQL binaries for Linux/x86 allow you to have up to 4096 concurrent connections, but self compiled binaries often have less of a limit.

Set table_cache to match the number of your open tables and concurrent connections. Watch the open_tables value and if it is growing quickly you will need to increase its size.

Note: The 2 previous parameters may require a lot of open files. 20+max_connections+table_cache*2 is a good estimate for what you need. MySQL on Linux has an open_file_limit option to set this limit.

If you have complex queries sort_buffer_size and tmp_table_size are likely to be very important. Values will depend on the query complexity and available resources, but 4Mb and 32Mb, respectively are recommended starting points.

Note: These are "per connection" values, among read_buffer_size, read_rnd_buffer_size and some others, meaning that this value might be needed for each connection. So, consider your load and available resource when setting these parameters. For example sort_buffer_size is allocated only if MySQL nees to do a sort. Note: be careful not to run out of memory.

If you have many connects established (i.e. a web site without persistent connections) you might improve performance by setting thread_cache_size to a non-zero value. 16 is good value to start with. Increase the value until your threads_created do not grow very quickly.

2. Preventing a Disaster and Ensuring Recoverability

Backing up your database is the single most important element to preventing a data loss disaster as a result of network, application, or hardware failure. Backing up your database is your first step towards ensuring recoverability and should be done on a regular basis. Since your MySQL tables are stored as actual files in the MySQL installation directory, making a backup may seem as easy making as backup of your personal files on your home computer. However this method does not apply to a live database system. If someone is accessing a table at the moment you try to copy the file, you may end up with a partially updated file.

Using the graphical environment, you can ensure consistent backups and that can be quickly restored data by simply selecting the schemas and tables you want to back/restore from a hierarchical data directory. The graphical environment gives you additional visibility into Tabletype, Rows, Data Length, and Update time. What's more, you can back up tables remotely, eliminating the need to log into each machine locally.

3. Administering Users and Granting Privileges

In larger organizations, DBAs would typically be responsible for implementing a complete security strategy and privilege system to prevent possible security breaches. Regardless of whose responsibility it is, adopting a proactive approach is crucial to preventing violations. The graphical interface of MySQL Administrator leverages the full power of MySQL's user management and privilege capabilities and significantly simplifies and reduces the time it takes to set up Access Control Lists and grant privileges. The graphical interface provides a simple method for adding users, assigning passwords and includes user profile information. Assigning and revoking global, database, and table/column privileges is as easy as adding and removing privilege items from an available list.

Our most important piece of advice on this topic is the learn and use the powerful authentication and privilege system. Some specific guidelines include:

  • Do not have an empty root password. Otherwise anyone can connect as root without a password and be granted all privileges.

  • Always use passwords and make sure that passwords are not plain text passwords. If your computer becomes compromised, the intruder can take full advantage of passwords and use them.

  • Give permissions only as needed and use different logins for different applications.

    Note: From a performance point of view it better to have table level permissions. Column level permissions tend to slow things down a bit.

  • Users are granted privileges on a "per host" basis. peter@myhost has nothing to do with peter@yourhost. You should avoid granting users privileges to all hosts unless absolutely necessary.

    Note: Users, usernames, and passwords have nothing to do with OS users. You can specify any user name you want.

  • Avoid having MySQL Server open to the Internet. Remote exploits are uncommon, but why risk it when it can be easily avoided.

4. Monitoring Health

There are some basic, yet key variables you will want to keep your eye on that will help better diagnose potential problems and ensure you system runs smoothly in a production environment. Particularly, developers and DBAs will want to monitor hitrate percentages, number of SQL queries, and connection usage to ensure you system is not experiencing a bottleneck. The new health monitoring facility gives you improved visibility from centralized location, using dynamic graphs to chart usage. You can also use the visual health monitoring feature to get a hierarchical view into status and server variables such as the number of outstanding connections, number of bytes sent and received by clients, buffer allocation size and more. There are a few Status Variables for InnoDB and MyISAM that you will want to monitor. For example, IO rate is a good one to monitor to see database activity. In addition, threads_connected and threads_running are good indicators to see how loaded the server is.

5. Managing Space

Typically databases grow and naturally accumulate information. It's important to make sure there is always enough free space to handle the day's expected growth. You should consider keeping at least 30% of your hard disk space free. Another good way to avoid running out of space is to keep the automatic extensions of the data files on and only set the maximum to 90% of the hard disk. Again, optimal values are going to depend largely on your specific implementation and usage of the database.

The Startup Parameters of the MySQL Administrator allows you to easily allocate the size of the memory buffer InnoDB users to cache data and indexes of its tables as well as allocate the size of a memory pool InnoDB uses to store data dictionary information and other internal data structures.

Conclusion

MySQL Administrator provides an easy to use facility to manage your database servers. This integrated graphical tool will enable to you harness all the power of the administrative functions, while reducing the learning curve and simplifying the overall administration task. Ultimately, MySQL Administrator will help you minimize problems and maximize database availability — which is one of the key goals of good database administration and user satisfaction.

The tips and guidelines in this article come directly from the MySQL support team which is made up of MySQL experts. For busy developers and tech staffs, the services they provide can save considerable time otherwise spent in research and experimentation. MySQL support team can help ensure that you get the level of support you need when you need it on a 24x7 basis worldwide. Contact MySQL support at http://www.mysql.com/about/contact/ to get more information on the range of support and training services available.