How-to configure MySQL database monitoring

Posted: April 28th, 2008 | Author: TnT Admin | Filed under: How-Tos | Tags: , , , | No Comments »

In LoadRunner (as of this writing which is version 9.0), the supported databases that can be monitored are mainly proprietary, such as, Microsoft SQL, Oracle, Sybase and DB2. Open sources such as MySQL are not supported. In order to monitor this type of databases, we have to employ alternative methods. And one of it, is to utilize SiteScope.

MySQL database monitoring will require SiteScope to be deployed acting as a bridging collector before it can be displayed in LoadRunner. The connection is illustrated in the following.

Fig. 1 Monitoring MySQL Setup

The specified statistics retrieval mechanism utilizes Database Query monitor in SiteScope. This is done by logging into MySQL database via SiteScope Database Query monitor and issuing a SHOW STATUS command to retrieve the statistics. Example of the command is as followed:

show status where variable_name like ’innodb_buffer_pool_pages_total’

Therefore, prior this monitor setup, SiteScope should already be deployed or considered in the deployment. Refer to SiteScope setup for more information. The ports between LoadRunner and SiteScope, SiteScope and MySQL should be opened on TCP 8888 and TCP 3306 respectively by default. On top of this basic requirement, monitoring a MySQL database requires the use of JDBC Driver, which SiteScope uses to interact with the MySQL database.

In summary, what you need before the setup is as followed.

    1. SiteScope to be deployed.
    2. Sufficient license points for Database Query monitor.
    3. TCP 8888 (default) opened two-way for LoadRunner to SiteScope.
    4. TCP 3306 (default) opened two-way for SiteScope to MySQL.
    5. Monitoring privileges for SiteScope to monitor MySQL.
    6. JDBC driver to be installed on SiteScope.
    7. Have knowledge of the counter you want to monitor.

Let’s go into the details!

    1. Download the JDBC driver from MySQL Download Connectors page.
    2. Uncompress the distribution file.
    3. Among all the other files, find and extract a file with .jar extension to Sitescope/java/lib/ext directory. (Source: SiteScope Monitor Guide)
    4. Gather information about the hostname of the server and the database instance prior this step. In SiteScope console, add Database Query monitor to the existing monitoring group for the server (refer to SiteScope User Guide for more information). You should have the Name the monitor as mysql-{status_variable} to standardize the monitors (e.g. mysql-innodb_buffer_pool_pages_total).
    5. In the settings enter the following:

Fig. 2 SiteScope Settings

Frequencies

What SiteScope documentation proposes is:

    - For most critical and common query 2-5min.
    - Database statistics that change less frequently can be monitored every 30 or 60min.

(Source: SiteScope Monitor Guide)

As for me, what I feel is to run the query at about 15-30secs interval as typical LoadRunner monitoring, it is defaulted at 15 secs. However by doing so, it may create additional overhead for making the query.

Database Connection

  • Enter in the following format: Jdbc:mysql://{database hostname}[:{tcp port}]/{database}
  • E.g. jdbc:mysql://DB-MySQL01:3306/mysql

Database Driver

  • Enter following: org.gjt.mm.mysql.Driver (This Database Driver only applies for MySQL JDBC driver)

Query

  • Enter the Command Line for Query to Database
  • E.g. show status where variable_name like ’innodb_buffer_pool_pages_total’
  • You can also run queries that directly execute on table based on your needs!

Advanced Settings

  • Enter Database User Name and Database Password for logging in of MySQL Database. I’m using root account to execute the query. I’m not too sure if there is any possibility of knowing the correct privilege needed to monitor. If you do, please let us know.

Upon successful connection, you will prompt with ‘Successful Connection’ and the time Sitescope took to Query MySQL Database.

If you need to monitor another MySQL counter, a new monitor needs to be created. Example, if you want to monitor the Qcache_hits, you will have to perform step 4 to step 5 again for this new counter. Do ensure you have sufficient SiteScope license points for another monitor!

This concludes the monitoring for MySQL database server. Point to note that the queries executed may cause overhead to the database server which may required you to balance the pros and cons to implement this monitor.

Related Posts

Leave a Reply