Monitoring Wordpress (And Other Database-Backed PHP Apps) With Hyperic HQ
Introduction
This howto is for users and admins of PHP/MySQL web applications who are looking for a way to monitor the data from these applications. This howto is not geared specificially to monitoring the system resource usage of the web server and database, although that is one piece of the puzzle. Instead, the focus of this howto is using Hyperic HQ's SQL Query plugin to monitor the data contained within the backing database, in this case the number of posts, comments and users from a Wordpress blog deployment. Then, we will view this data in the context of system resource usage, to help admins correlate the information from the Wordpress plugin we're about to create with whatever other data they're monitoring from that system.
This howto assumes that you are at least familiar with Hyperic HQ. For more information on how to install the HQ monitoring system, see support.hyperic.com. Also, note that this was created with Hyperic HQ 3.2 Beta 4, but the process is very similar for HQ 3.1.x.
Writing the Wordpress Plugin
Writing the Wordpress plugin is pretty simple - create an XML file that imports the SQL Query plugin to do the hard labor. Since we're using Wordpress as our example, here's the entire XML plugin:
<!--
A plugin extension that monitors posts, comments and users in Wordpress
-->
-
<plugin>
-
<service name="Wordpress">
-
<!--
"sql:" domain required to proxy through sqlquery-plugin
-->
<filter name="template" value="sql:${query}:${name}"/>
<!-- config defined by the sqlquery-plugin -->
<config include="sql"/>
<!-- use filters for common sql -->
<filter name="count" value="SELECT COUNT(*) FROM"/>
<metric name="Availability" query="${count} wp_term_taxonomy WHERE 1=1" indicator="true"/>
<metric name="Number of Users" query="${count} wp_users" indicator="true"/>
<metric name="Number of Posts Including Drafts" query="${count} wp_posts" indicator="true"/>
<metric name="Number of Comments" query="${count} wp_comments" indicator="true"/>
<metric name="Number of Published Posts" query="${count} wp_posts where post_status = 'publish'" indicator="true"/>
</service>
</plugin>
And that's it - because the SQL Query plugin is included by default in every HQ deployment, that's all the coding you need to do. The rest of the howto demonstrates how to take this XML file and put it to use.
Deploying Your New Plugin
Now that you've created the plugin, how do you use it? Hyperic HQ is an agent based monitoring system, and you must deploy the plugin on both the server and agent side. On the server side, HQ supports hot deployment of plugins if you place them in the directory <path to HQ home>/hq-plugins. However, if you have not already created that directory, creating that directory and placing your new plugin there will require a restart of HQ server.
On the agent side, HQ does not support hot deployment, so deploying a new plugin will require an agent restart. You may place the plugin in either <HQ root directory>/hq-plugins or in <HQ root directory>/<HQ agent dir>/pdk/plugins - which is where all of the plugins are placed by default.
Using the Plugin
Now that you've restarted your agent (necessary) and server (maybe necessary), it's time to check if your new plugin shows up.
To make sure the plugin is deployed, go to the platform name of the machine on which you installed the agent plugin - you can find this by clicking, from the Dashboard, 'Resources' -> 'Browse' and then 'Platforms'. There, you should see the platform on which you deployed your plugin. Click on that platform, which brings you to the default monitoring view of that platform.
In the upper right of the screen, click the "Tools Menu" and select "New Platform Service". On the next screen, add the name of your wordpress instance, whatever descriptive text you feel is necessary, and select "Wordpress" from the "Service Type" drop-down list. If you don't find "Wordpress" in the list of Service Types, then something went wrong with your plugin - check your agent logs.
After clicking 'Ok' you'll come to a screen with the 'General Properties' section open. Scrolling to the bottom of the page, you should see 'Configuration Properties' - click it to open that section. Select the 'Edit' button, and you will see a screen with 4 fields: jdbcDriver, jdbcURL, jdbcUser, and jdbcPassword. jdbcDriver is a drop-down menu of jdbc drivers for supported databases. In my case, I selected 'com.mysql.jdbc.Driver'. The jdbcURL field should be, for a MySQL-based environment, jdbc:mysql://database.host.name/database-name. jdbcUser and jdbcPassword are the user and password needed to view your particular wordpress database.
Getting the Most From the Plugin
Once enabling the plugin, you'll start collecting metrics for users, posts, and comments, which is useful in its own right. However, to really make the most of your plugin, as well as spotlight a bit of what makes HQ so cool, we'll use this plugin as the central piece of a 'wordpress' composite application, that includes MySQL database information, CPU and network metrics, and any other services you may wish to add to form a complete picture.
This is where you can view your Wordpress data in the context of the system resources consumed by your database-backed web apps.
- Click 'Resources -> Browse' in the top navigation
- In the Tools Menu in the upper right, select 'New Application'
- Next, name your application, filling in whatever information is applicable - nothing else is required - then click 'Ok'.
- You should see a 'General Properties' screen that is open. Scroll down to the bottom and select the bar for 'Services' (there should be none) and then click 'Add to List'
- Select the services from this list that you want to include in your application, including the Wordpress service you just created. If you have trouble finding it, use the 'Filter by Name' box to search for it. I recommend you include, at the least, the database used by Wordpress
And now you should have an application that includes your Wordpress service and adjunct services you've just added.
Conclusions
The SQL Query style plugins are very easy to implement. The same steps could be taken for most database-backed PHP web applications, including MediaWiki, Gallery, etc.