Oracle Blog

Eclectic gatherings about data

Agents, Hosts and Instances, Oh My!

The MySQL Enterprise Monitor (MEM) is designed to collect data from installations of MySQL and report back to the service manager so that information can be correlated and displayed in an easy to read form on the dashboard. What does become confusing sometimes for people is that the item displayed on the dashboard gives a hostname, but you have multiple databases running on that host?! How does MEM distinguish between all these installs of MySQL versus the hosts they are running on and how the data is collected for each one?

MEM uses the term 'instance' to identify a single, unique item as part of its system. In collecting and defining data, there are essentially three main instances that identify where any data is coming from in the monitoring system. Each instance has a unique identifying number (uuid) to distinguish it from any other instance of data in the MEM repository. Although each instance has very similar identification and apparent operation, they do serve different purposes in the overall system.

Agent Instance

The agent instance is used to identify the agent process that will collect data from the MySQL service and send it back to the service manager. Each agent is uniquely identified, however, there can be more than one agent running at any one time on a server.

The agent instance is usually created upon installation and is found in the etc/mysql-monitor-agent.ini file in the MEM installation directory. It appears as:

agent-uuid = 1ee02740-ddd8-40ac-a7d4-8dc0ec880585

However, you can also specify the agent-uuid as a commandline option, if required.

Host Instance

The host instance is used to uniquely identify the hosting server on which the MySQL database is running. The host instance needs to be something that will never, or at least very rarely, change, as part of identifying the host is to tie which host the data came from in the repository. The default method currently is to use the ssh public host key of the server as the host uuid. This is stored in the mysql.inventory table of the monitored MySQL service. The name/value pair shows as:

hostid | ssh:{58:8c:21:3c:4f:2e:49:46:e8:fb:a4:d3:9c:92:cc:84}

Sometimes there can be problems with using the ssh public host key, however, so an alternative is to define the agent-host-id option to choose a different identifying string. For example:

agent-host-id=dbaserver.my.domain

There are a number of reasons why you may need, or want, to change the host id, but that is worthy of another article coming soon.

MySQL Instance

The mysqld instance uniquely identifies the MySQL service that we wish to monitor. This uuid will correspond directly to an ip.address:port value that is unique for that MySQL service. This value is generated on installation and is stored in the mysql.inventory table on the monitored service. It appears in the table as:

uuid | 558df0e5-1346-4f80-8454-c50de1969596

The combination of the mysqld instance and the host instance allows the agent to uniquely identify the service from which it is collecting the data.

Changing values of the instances can occur, but once established, they should not change that often, if at all. If the mysql.inventory table is lost or deleted in some way, then the host id will be generated again (using the standard ssh host key), but the mysqld instance (uuid) will have a newly generated value. As part of the installation process normally, a newly generated uuid makes sense. However, a new uuid value means that any previous historical data will no longer be associated with the new instance. This will show as graphs starting from the new point in time and no other associated data before the restart.

A word of warning for those who are looking at playing with the uuid values. Don't try and match uuid values by copying and pasting values between any type of instance! If you have duplicate uuid values in any of the three different areas, there could be resulting loss of data, inconsistent data, or even where the service will not be able to start. If you want a new value for say, the agent-uuid value, then there are options to generate one from the command line of the agent program. Messing around with the values by hand is going to cause more trouble than it is worth.

One addition for the agent-uuid, though: If the agent cannot figure out where the ssh_host_key.pub (or various other names used on various platforms), or ssh-keygen is not found, or the command given with the --agent-host-id-commandline option does not exist, it will fall back to using the MAC address of the primary network interface.

Now, you might wonder why that is not the default and the ssh host key is treated as a fallback. There are actually a couple of reasons, but the main ones are:
1) Which one the primary network interface is can change at runtime (think multiple NICs in servers or wired and wireless interfaces on desktops/laptops).
2) In many virtualized environments an application cannot get to the actual hardware underlying a network interface. Thus the code would fail to get a MAC address, since that's tied to a piece of actual hardware.

In most cases the ssh host key is the best bet, but if you already have something that uniquely identifies your servers and that piece of information never changes, you can use either --agent-host-id-commandline or --agent-host-id to set the id.

I realise there is a lot more to identification of the host to MEM than what I have mentioned in this article. I did not mention too much in regards to how it works as I plan to do another article that looks at this and how agent-host-id works, particularly in more complex situations such as failover scenarios.