Chapter 12. Health

Table of Contents

12.1. Introduction
12.2. Connection Health
12.3. Memory Health
12.4. Creating Custom Health Graphs
12.5. Status Variables
12.6. System Variables

12.1. Introduction

The Health section of MySQL Administrator shows you how to graphically monitor the real-time status of your MySQL server. A selection of pre-made status graphs are available, and you can easily create your own status graphs and organize them into custom pages and groups.

The default graphs provide graphical information about the MySQL server's current performance, load, and memory usage. Additional pages list all server variables, with tabs for memory health, status variables and system variables. Note that clicking on the status pages will open an additional connection to the MySQL server, which you can see when viewing threads and users in the Server Connections section.

We'll begin by covering the different pre-made status graphs, and then describe how to create your own status graphs. Finally we'll describe the pages in the Health section dedicated to the various status and system variables.

12.2. Connection Health

This tab provides information about connections to the MySQL server. The following three pre-made graphs are displayed:

  • Connection Usage: Displays a percentage of the maximum number of simultaneous clients allowed (threads connected / max_connections). If this value gets close to 100%, it could indicate that you should increase the value of max_connections, which you can do in the Startup Variables section. The maximum number of connections since MySQL Administrator was loaded is also shown. For more information on server connections, see SHOW VARIABLES Syntax, and Too many connections.

  • Traffic: This is a representation of the bytes_sent status variable. This does not represent total bytes sent since server startup, but instead represents the current number of bytes sent (the bytes_sent variable is a cumulative total). The initial maximum value of the graph is 102400, this maximum value will be automatically increased as needed.

  • Number of SQL Queries: This is a representation os the com_select status variable. This does not represent total queries since server startup, but instead represents the current number of queries (the com_select variable is a cumulative total). The initial maximum value of the graph is 10, this maximum value will be automatically increased as needed.

12.3. Memory Health

This tab provides information about memory caches that affect the performance of the MySQL server. For each cache, there is a display that shows the current hit rate or usage of the cache, and a display that shows the hit rate or usage for the last few minutes.

  • Query Cache Hitrate: When in use, the query cache of the MySQL server stores the text of a SELECT query together with the corresponding result that was sent to the client. If an identical query is later received, the server will retrieve the results from the query cache rather than parsing and executing the same query again. The hit rate indicates the ratio of queries that were cached and queries that were not cached. The higher the hit rate, the better the performance of the server regarding SELECT queries. See The MySQL Query Cache.

  • Key Efficiency: The horizontal bar graph indicates how much of the allocated key buffer is in use. If a fairly large percentage of the key buffer is in use you may wish to increase the key_cache_size setting in the Startup Variables section. The line graph is an indication of the number of key_read_requests that resulted in actual key_reads. The higher the hit rate the more effective your key buffer is. A low hit rate can indicate that you need to increase the size of your keycache.

12.4. Creating Custom Health Graphs

While the pre-made status graphs can provide you with information of the current state of your MySQL server, there may be additionally information that you wish to visually track. The MySQL Administrator Health section allows you to add additional tabs and graphs to track any information that is contained within the Status Variables.

The first step in creating custom health graphs is to create a new page in the Health section to store your graphs. To create a new page right click anywhere in the working area and choose the Add a Page option. You will be prompted for a page name and description and a new page will be created.

Once you have created a new page, you will need to create a group. All graphs are organized into groups. For example, the Key buffer usage and Key buffer hit rate graphs are both members of the group titled Key Efficiency. To create a group, right click within your newly created page and choose the Add a Group option.

To create a custom graph, right click within a group and choose the Add a graph option. The following dialog will appear:

Figure 12.1. The Graph Settings dialog

The Graph Settings dialog

The Line-Graph is appropriate for showing trends over time, while the Bar-Graph option will be more appropriate for showing percentage information. If you choose to create a bar-graph, you will need to choose a caption for your graph. Captions are not allowed for line-graphs.

The data on your graph is set using the Value Formula. You can create a formula using any of the variables available in the Status Variables and System Variables tabs. To use the value of a variable, wrap the variable name in square brackets (i.e. [com_select]), if you want the relative value of the variable prepend the square brackets with a ^ character.

For example, if we wanted to graph the percentage of temporary tables that were created on disk we could use the following formula:

  [created_tmp_disk_tables] / [created_tmp_tables]
  

If we wanted to track the number of temporary tables created on a continuous basis, we could use the following as our formula:

  ^[created_tmp_tables]
  

After creating your formula, select the Value Unit that best represents your data. You can choose from Percentage, Count, Byte, and Seconds. In our examples we would use Percentage for the first example, and Count for the second. You can also assign a caption to the value.

Once your formula is assigned you should configure the Max. Value and Min. Value for the graph, so that your data is spread evenly across your graph. You can set arbitrary values based on your estimates of how large the values will grow, and check the Autoextend Max. Value option to allow MySQL Administrator to automatically increase the Max Value setting automatically to prevent your data from extending off of your graph.

You can also set the Max. Value option by way of a formula assigned in the Max Formula field. The same syntax applies in this field as applies in the Value Formula field. For example, if you were creating a bar graph tracking the number of temporary disk tables created, you could use [created_tmp_disk_tables] as the Value Formula and [created_tmp_tables] as the Max. Formula.

12.5. Status Variables

This tab provides information about the status variables of the MySQL server. In the left box of the working area, you find categories and subcategories. Double click on a category name to expand or collapse its subcategories.

For each category, and each subcategory, the status variables are listed in the right box of the working area, together with their values, and with a short explanation. See SHOW STATUS Syntax, for more detail on server status variables.

Use the Refresh button to make MySQL Administrator retrieve the variables and their values once again.

Right clicking in the right box allows you to copy variables to the clipboard.

12.6. System Variables

This tab provides information about the system variables of the MySQL server. In the left box of the working area, you can find categories and subcategories. Double click on a category name to expand or collapse its subcategories.

For each category, and each subcategory, the system variables are listed in the right box of the working area, together with their values and a short explanation. See SHOW VARIABLES Syntax, for more information on system variables.

System variables that can be set at runtime are marked as editable, that is they are prefixed with an icon different from the variables that cannot be set at runtime. Double clicking on editable variables will open a dialog box allowing you to specify another value for the variable. The changes you make will take effect as soon as you confirm the changes by clicking the OK button. Click Abort if you want to keep the current value. Changes made in this manner will only persist until the server is restarted, after which the server will revert to it's startup settings. In MySQL Administrator, system variables are set on a global level only. Note that you need appropriate privileges to change variables globally. See SET Syntax.

Use the Refresh button to make MySQL Administrator retrieve the variables and their values once again.

Right clicking in the right box allows you to copy variables to the clipboard.