Monitoring specific MySQL counters using LoadRunner

You can monitor MySQL database using Sitescope. However, if you don't have access to the Sitescope application then you can write a simple LoadRunner script to monitor specific MySQL counters and plot the values using lr_user_data_point LR function. The following script is an enhancement to MySQL script detailed in my earlier post.

NOTE:
All session related information in MySQL is stored in a table called "SESSION_STATUS". Therefore, we will be querying this table for MySQL counter values.

For demonstration purpose, the script below will query the values for LAST_QUERY_COST, OPENED_TABLES, QCACHE_HITS, SELECT_FULL_JOIN and SELECT_SCAN counters.

Global declaration

#include "C:\\Program Files\\MySQL\\oldfiles\\include\\mysql.h" //mySQL.h path is included 

/*MySQL structure defined*/
MYSQL *mySQL;
MYSQL_ROW row;
MYSQL_RES *result;
int MyRC;

char *MySQLserver = "localhost"; // Location where server is running
char *MySQLuser = "root"; // User name used to connect to the database
char *MySQLpassword = ""; // Not a good idea to leave password empty
char *MySQLdatabase = "information_schema"; //Database name 
int MySQLport = 3306; // Database port

Vvuser_int

//libmysql.dll file loaded using lr_load_dll function
 MyRC= lr_load_dll("C:\\Program Files\\MySQL\\MySQL Connector C 6.0.2\\lib\\opt\\libmysql.dll"); 

 //Initialise mySQL connection handler
 mySQL= mysql_init(NULL);

 // Connect to the database
 mysql_real_connect(mySQL,MySQLserver, MySQLuser, MySQLpassword, MySQLdatabase, MySQLport,NULL,0);

 return 0;

Query Function

double atof(const char *string); // Explicit declaration
Query()
{  
    int i=0;
    float VarValue[5]; //Float variable array
    /*Save SQL statement into variable into sqlQuery.
     This query returns values for the variable name ={LAST_QUERY_COST,OPENED_TABLES,QCACHE_HITS,SELECT_FULL_JOIN,SELECT_SCAN}*/ 
 lr_param_sprintf("sqlQuery","select variable_value from session_status where variable_name IN"
      "('LAST_QUERY_COST','OPENED_TABLES','QCACHE_HITS','SELECT_FULL_JOIN','SELECT_SCAN')");

 mysql_query(mySQL, lr_eval_string ("{sqlQuery}")); //Execute SQL statement

 result = mysql_store_result(mySQL);   //Result of the sql statement is placed into MYSQL_RES result structure
  
 row=mysql_fetch_row(result);   
     
 while(row!=NULL) //Iterate through to last row
 {
  VarValue[i]=atof(row[0]);  //Save float row value to VarValue
  row=mysql_fetch_row(result);  //Retrive next row of the fetched result
  i++;
 }

/*Use lr_user_data_point function to generate the graph*/
 lr_user_data_point("LAST_QUERY_COST", VarValue[0]);
 lr_user_data_point("OPEN_TABLES", VarValue[1]);
 lr_user_data_point("QCACHE_HITS", VarValue[2]);
 lr_user_data_point("SELECT_FULL_JOIN", VarValue[3]);
 lr_user_data_point("SELECT_SCAN", VarValue[4]);

 return 0;
}

vuser_end

/*Free the memory allocated to result structure and close the database connection*/
 mysql_free_result(result);
 mysql_close(mySQL);
 return 0;

NOTE: Make sure you are explicitly declaring the atof function before using it or else you will get totally different values. See the screenshot of the values received for the above counters when atof was not declared explicitly before using it.