One of the nice features that was added in the 10.1 release of our BIG-IP software was the addition of native monitors for both PostgreSQL and MySQL. These new monitors allow you to more accurately monitor your databases using specific queries that suit your needs.  This is far better than being required to call an external script to do your monitoring or relying only on a TCP monitor to determine if your database is listening on a specific port.

In general, there are just a few things you need to do to successfully monitor your database:

1. Create a dedicated database user to use for monitoring.  This is not required, but is generally recommended.  I personally would never use a root or admin login for monitoring a database.
2. Create your monitor.  This step requires you to have a specific piece of information you want to know about in mind -- essentially a SQL query.  This can be anything from a simple query that just confirms that the database is up to a complex one that returns information about processes, application status, etc, etc.  The sky really is the limit here.
3. Attach your monitor to a node or a pool of nodes.
   

Without further ado, let us jump straight in.

Table of Contents

1 Creating a dedicated database user for monitoring.
    1.1 Creating a user in PostgreSQL.
    1.2 Creating a user in MySQL.
2 Creating your monitor.
3 Attaching your monitor.

Creating a dedicated database user for monitoring.

This is the only step that is different depending on which database you use.

Creating a user in PostgreSQL.

Using the command line on your PostgreSQL server, run the following command:

$ createuser -U postgres -W -PE bigip

You will be prompted to enter a password for the bigip user and asked to reenter it.  You will then be asked three questions concerning the bigip user's privileges to the database.  I recommend answering No to all of them.  Finally, you will be prompted for the password of the user you are connecting to the database as.

Here is the breakdown of the command I used above:

• createuser   -- The command we are using.

• -U postgres  -- Connect as the 'postgres' user.

• -W           -- Prompt me for a password for the 'postgres' user.

• -PE          -- Prompt me for a password for the new 'bigip' user and encrypt it.

• bigip        -- The name of the new user.

Depending on your setup, you may need to grant your new bigip user access to certain tables, views, or other objects.  For information on how to do that, refer to the PostgreSQL documentation for GRANT at http://www.postgresql.org/docs/current/interactive/sql-grant.html

In this example, I do not need to grant the bigip user any extra privileges since I will be querying a function that is available to everyone; I will be querying the number of backend processes for a particular database using the pg_stat_get_db_numbackends function.  This function, however requires another piece of information, the database OID, in order to run.  So, first let us find that OID.

$ psql -U bigip -W postgres -c 'SELECT datname,oid FROM pg_database'

This should return something like the following:

  datname  | oid

-----------+-------

 template1 |     1

 template0 | 11510

 postgres  | 11511

 pgbench   | 16853

(4 rows)

Good, I have the OID I need.  In this case, it is 16853 since the pgbench database is the one I want to monitor.  Now, let us run the same query we would with the BIG-IP monitor to make sure we get an expected result:

$ psql -U bigip -W postgres -c 'SELECT pg_stat_get_db_numbackends(16853)'

This should return something like the following:

 pg_stat_get_db_numbackends

----------------------------

                          7

(1 row)

I knew ahead of time, in this example, that the number of backends is 7.  So this query just confirmed what the output should be.

The only other thing that my be required before continuing on is to add the new user to your pg_hba.conf file.  Depending on your environment, an entry for the new user may be required in order to make a remote connection over the network from the self-IP of your BIG-IP to your database server.  Find the documentation at http://www.postgresql.org/docs/current/interactive/auth-pg-hba-conf.html.  For my configuration, I added the following line:

host    postgres    bigip    10.1.1.251/32    md5

Please note that this file is only read at PostgreSQL startup.  If/when you add an entry to your pg_hba.conf file, you will need to restart the main server process via pg_ctl reload or kill -HUP .

Now we can continue on to Creating your monitor.

Creating a user in MySQL.

Using the command line on your MySQL server, run the following command:

$ mysql -u root -p

This will drop you into the MySQL interactive shell.  From there, run the following command:

mysql> GRANT SELECT ON database.table TO 'bigip'@'' IDENTIFIED BY '';

In our example, the GRANT statement looked like this:

mysql> GRANT SELECT ON test_db.people TO 'bigip'@'10.1.1.251' IDENTIFIED BY 'bigip1';

mysql> exit;

Unlike PostgreSQL, this command creates the user, sets its password, and sets its privileges all at once.  By default, MySQL users are not allowed access to any information.  This GRANT statement tells the database to allow the user bigip to connect from the IPv4 address 10.1.1.251 and run a SELECT statement on the people table in the test_db database.

Now, let us run the same query we would with the BIG-IP monitor to make sure we get an expected result:

$ mysql -u bigip -p -e "SELECT id FROM test_db.people WHERE first_name='George'"

This should return something like the following:

+----+

| id |

+----+

|  1 |

+----+

Which is what I expect.  The person with a first name of George in the people table of the test_db database does indeed have an id of 1.

Now we can continue on to Creating your monitor.

2 Creating your monitor.

Since the UI for creating the monitor for both PostgreSQL and MySQL is identical, I'm just going to step through creating the PostgreSQL one with screenshots.  I will note the differences accordingly.

1. Log on to your BIG-IP and navigate the left sidebar to Local Traffic -> Monitors
2. The Monitors UI will display, click Create in the upper right hand corner of the frame.
3. The New Monitor UI will display.
4. In the Name box, type foss-db-pgsql_monitor.
5. From the Type list, select PostgreSQL.
   1. The UI should refresh showing you all the options for the monitor.
6. Leave the Interval and Timeout values at their defaults unless you have a specific need to change them.
7. In the Send String box, type the SQL query you want to perform at regular intervals for monitoring.  You could use the one we discussed earlier, but it is just an example.
8. In the Receive String box, type the response you expect to get back from your SQL query.
9. The Username and Password boxes should be defined accordingly to the user we created earlier.
10. The Database box shoud be defined to the database you created your new user in.  In our example this would be postgres for PostgreSQL or test_db for MySQL.
11. The Receive Row and Receive Column boxes are how you would deal with a SQL query that returns multiple results.  If you know that what you are looking for is in a specific spot in a multi-line, multi-column result; this is where you would define it.  For example, take the following result from a not-as-simple SQL query:
    

        SQL> SELECT id,first_name,last_name FROM test_db.people;

    
        +----+------------+------------+

        | id | first_name |  last_name |

        +----+------------+------------+

        |  1 |     George | Washington |

        |  2 |       John |      Adams |

        |  3 |     Thomas |  Jefferson |

        +----+------------+------------+

    
    
        ... and say we want to monitor that Thomas Jefferson's first name is always Thomas.  So we would set Receive Row to 3 and Receive Column to 2.
    
12. Lastly, the Count box allows you to control the behavior of the monitor in terms of how it handles connections.  The default of 0 will tell the monitor to keep open monitor connections and reuse them.  A setting of 1 will open and close a new connection for each monitor check.  Any positive value will keep the monitor connection open for that many instances.
13. Click Finished.
    

3 Attaching your monitor.

Now that we've created our new monitor, it should be available to attach to any node or pool that you wish.  Simple find said node or pool and add your new monitor to the list of Active Health Monitors.



If all goes as planned, your node or monitor's status should shortly appear in a happy, green Up state.



If things are not going well, remember that the database monitors allow you to turn Debug on.  Go back to the properties of your new monitor, change Configuration from Basic to Advanced, change Debug from No to Yes, and click Update.  Doing this will log debug information to a file on your BIG-IP located at /var/log/DBDaemon.log.  Additionally, turning on debug may also create some log files in /var/log that are named according to the monitor type and each node it is attempting to monitor.  Check these files for details on failed logins, invalid credentials, denied connections, etc.



snip...



Good Luck!