How to save mongoDB server status into a file

MongoDB serverStatus command provides a useful information about mongod instance. This information includes fields such as mem.virtual, globalLock.currentQueue.writers, extra_info.page_faults. This information can be accessed by executing db.serverStatus() command from the shell.

If you want to save the above result into a file, you can do as follows:

>mongo --eval "printjson(db.serverStatus())" >>{outfile path}

e.g.
>mongo --eval "printjson(db.serverStatus())" >>C:\harinder\log.txt

You can also execute the same using javascript file as an argument. The advantage of this approach is that you can have multiple commands in the file.

>mongo <{Javascript path} >>{Outfile path}

e.g.
>mongo < C:\harinder\mongo.js >>C:\harinder\log.txt

Example of Javascript file:

db.getProfilingLevel()
db.getLastError()
db.foo.find()

Output example:

MongoDB shell version: 2.0.4
connecting to: test
> 0
> null
> { "_id" : ObjectId("4f6e92311c7ef28999436998"), "a" : 1 }
> bye

Saving vmstat with timestamp into a file on Redhat Linux Machine

By default vmstat output doesnot include timestamp and therefore, if you want to save it with the timestamp into a file on Redhat Linux machine, you can use following command.

$vmstat -n [delay [count]] | awk '{now=strftime("%Y-%m-%d %T "); print now $0}'>{file path}

e.g. vmstat -n 1 30 | awk '{now=strftime("%Y-%m-%d %T "); print now $0}'>/tmp/vmstat.txt
where:

• n -> display vmstat header once
• 1 -> update vmstat every second
• 30 ->only display 30 records
• awk '{now=strftime("%Y-%m-%d %T "); print now $0}' -> print timestamp
• >/tmp/vmstat.txt -> path where the vmstat is saved

Final result:

2012-03-09 12:05:25 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
2012-03-09 12:05:25  r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
2012-03-09 12:05:25  1  0      0 23199964 473756 6719956    0    0     0     2    0    0  0  0 100  0  0
2012-03-09 12:05:26  0  0      0 23199964 473756 6719956    0    0     0     0  118  232  0  0 100  0  0
2012-03-09 12:05:27  0  0      0 23199964 473756 6719956    0    0     0     0  123  260  0  0 100  0  0
2012-03-09 12:05:28  0  0      0 23199964 473756 6719956    0    0     0     0  111  234  0  0 100  0  0
2012-03-09 12:05:29  0  0      0 23199964 473756 6719956    0    0     0     0  133  276  0  0 100  0  0
2012-03-09 12:05:30  1  0      0 23200088 473756 6719956    0    0     0    88  115  263  0  0 100  0  0
2012-03-09 12:05:31  1  0      0 23200088 473756 6719956    0    0     0    12  135  251  0  0 100  0  0
2012-03-09 12:05:32  0  0      0 23200088 473756 6719964    0    0     0     0  113  313  0  0 100  0  0
2012-03-09 12:05:33  0  0      0 23200088 473756 6719964    0    0     0     0  127  270  0  0 100  0  0
2012-03-09 12:05:34  0  0      0 23200088 473756 6719964    0    0     0     0  110  271  0  0 100  0  0
2012-03-09 12:05:35  1  0      0 23200088 473756 6719964    0    0     0     0  142  297  0  0 100  0  0
2012-03-09 12:05:36  0  0      0 23200088 473756 6719964    0    0     0    44  127  264  0  0 100  0  0
2012-03-09 12:05:37  0  0      0 23200088 473756 6719968    0    0     0    12  144  349  0  0 100  0  0
2012-03-09 12:05:38  0  0      0 23200088 473756 6719928    0    0     0     0  143  259  0  0 100  0  0
2012-03-09 12:05:39  0  0      0 23200088 473756 6719928    0    0     0     0  137  273  0  0 100  0  0
2012-03-09 12:05:40  0  0      0 23200088 473756 6719928    0    0     0     0  123  252  0  0 100  0  0
2012-03-09 12:05:41  0  0      0 23200088 473756 6719928    0    0     0   104  198  269  0  0 100  0  0
2012-03-09 12:05:42  0  0      0 23200088 473756 6719928    0    0     0     0  166  280  0  0 100  0  0
2012-03-09 12:05:43  0  0      0 23200088 473756 6719928    0    0     0     0  183  322  0  0 100  0  0
2012-03-09 12:05:44  0  0      0 23200088 473756 6719932    0    0     0     0  205  330  0  0 100  0  0
2012-03-09 12:05:45  0  0      0 23200088 473756 6719932    0    0     0     0  157  260  0  0 100  0  0
2012-03-09 12:05:46  0  0      0 23200088 473756 6719932    0    0     0     0  112  219  0  0 100  0  0
2012-03-09 12:05:47  0  0      0 23200088 473756 6719932    0    0     0    52  144  304  0  0 100  0  0
2012-03-09 12:05:48  0  0      0 23200088 473756 6719932    0    0     0     0  117  230  0  0 100  0  0
2012-03-09 12:05:49  0  0      0 23200088 473756 6719932    0    0     0     0  139  280  0  0 100  0  0
2012-03-09 12:05:50  0  0      0 23200088 473756 6719932    0    0     0     0  126  241  0  0 100  0  0
2012-03-09 12:05:51  0  0      0 23200088 473756 6719932    0    0     0     0  129  243  0  0 100  0  0
2012-03-09 12:05:52  0  0      0 23200088 473756 6719932    0    0     0     0  111  237  0  0 100  0  0
2012-03-09 12:05:53  0  0      0 23200088 473756 6719932    0    0     0    32  132  265  0  0 100  0  0
2012-03-09 12:05:54  0  0      0 23200088 473756 6719932    0    0     0    16  119  260  0  0 100  0  0

Runtime Statistics in CouchDB

CouchDB  is an open source document-oriented database. More information on CouchDB is available here. 

CouchDB 0.9 and above comes with a list of counters that lets you inspect how CouchDB performs. On windows (local machine where CouchDB is installed), you can view these counters by navigating to the following URL: http://localhost:5984/_stats. More information is available here.

The information returned is in JSON format. There are free tools and websites available to display the stats in a more user friendly format.

Group	Key	Description
Couchdb	database_writes	Number  of times a database was changed
	open_databases	Number  of open databases
	auth_cache_hits	Number  of authentication cache hits
	auth_cache_misses	Number  of authentication cache misses
	database_reads	Number  of times a document was read from a database
	request_time	Length of a request inside CouchDB without MochiWeb
	open_os_files	Number  of file descriptors CouchDB has open
httpd	Requests	Number  of HTTP requests
	bulk_requests	Number  of bulk requests
	view_reads	Number  of view reads
	clients_requesting_changes	Number  of clients for continuous _changes
	temporary_view_reads	Number  of temporary view reads
httpd_request_methods	DELETE	Number  of HTTP DELETE requests
	HEAD	Number  of HTTP HEAD requests
	POST	Number  of HTTP POST requests
	PUT	Number  of HTTP PUT requests
	GET	Number  of HTTP GET requests
	COPY	Number  of HTTP COPY requests
httpd_status_codes	400	Number  of HTTP 400 Bad Request responses
	201	Number  of HTTP 201 Created responses
	403	Number  of HTTP 403 Forbidden responses
	409	Number  of HTTP 409 Conflict responses
	200	Number  of HTTP 200 OK responses
	202	Number  of HTTP 202 Accepted responses
	404	Number  of HTTP 404 Not Found responses
	301	Number  of HTTP 301 Moved Permanently responses
	405	Number  of HTTP 405 Method Not Allowed responses
	500	Number  of HTTP 500 Internal Server Error responses
	401	Number  of HTTP 401 Unauthorized responses
	304	Number  of HTTP 304 Not Modified responses
	412	Number  of HTTP 412 Precondition Failed responses

Below is a formated JSON output example:

{ "couchdb" : { "auth_cache_hits" : { "current" : 2089.0,
          "description" : "number of authentication cache hits",
          "max" : 222,
          "mean" : 0.83199999999999996,
          "min" : 0,
          "stddev" : 10.222,
          "sum" : 2089.0
        },
      "auth_cache_misses" : { "current" : 12.0,
          "description" : "number of authentication cache misses",
          "max" : 3,
          "mean" : 0.0050000000000000001,
          "min" : 0,
          "stddev" : 0.113,
          "sum" : 12.0
        },
      "database_reads" : { "current" : 643.0,
          "description" : "number of times a document was read from a database",
          "max" : 131,
          "mean" : 0.25700000000000001,
          "min" : 0,
          "stddev" : 4.1050000000000004,
          "sum" : 643.0
        },
      "database_writes" : { "current" : 205.0,
          "description" : "number of times a database was changed",
          "max" : 28,
          "mean" : 0.082000000000000003,
          "min" : 0,
          "stddev" : 1.056,
          "sum" : 205.0
        },
      "open_databases" : { "current" : 10.0,
          "description" : "number of open databases",
          "max" : 7,
          "mean" : 0.0040000000000000001,
          "min" : -5,
          "stddev" : 0.21099999999999999,
          "sum" : 10.0
        },
      "open_os_files" : { "current" : 10.0,
          "description" : "number of file descriptors CouchDB has open",
          "max" : 7,
          "mean" : 0.0040000000000000001,
          "min" : -5,
          "stddev" : 0.28699999999999998,
          "sum" : 10.0
        },
      "request_time" : { "current" : 13881.477999999999,
          "description" : "length of a request inside CouchDB without MochiWeb",
          "max" : 12043.0,
          "mean" : 115.679,
          "min" : 0.0,
          "stddev" : 1098.511,
          "sum" : 13881.477999999999
        }
    },
  "httpd" : { "bulk_requests" : { "current" : 27.0,
          "description" : "number of bulk requests",
          "max" : 10,
          "mean" : 0.010999999999999999,
          "min" : 0,
          "stddev" : 0.253,
          "sum" : 27.0
        },
      "clients_requesting_changes" : { "current" : null,
          "description" : "number of clients for continuous _changes",
          "max" : null,
          "mean" : null,
          "min" : null,
          "stddev" : null,
          "sum" : null
        },
      "requests" : { "current" : 2395.0,
          "description" : "number of HTTP requests",
          "max" : 221,
          "mean" : 0.95299999999999996,
          "min" : 0,
          "stddev" : 10.359999999999999,
          "sum" : 2395.0
        },
      "temporary_view_reads" : { "current" : 92.0,
          "description" : "number of temporary view reads",
          "max" : 33,
          "mean" : 0.036999999999999998,
          "min" : 0,
          "stddev" : 0.94199999999999995,
          "sum" : 92.0
        },
      "view_reads" : { "current" : 144.0,
          "description" : "number of view reads",
          "max" : 37,
          "mean" : 0.058000000000000003,
          "min" : 0,
          "stddev" : 1.1579999999999999,
          "sum" : 144.0
        }
    },
  "httpd_request_methods" : { "COPY" : { "current" : 1.0,
          "description" : "number of HTTP COPY requests",
          "max" : 1,
          "mean" : 0.0,
          "min" : 0,
          "stddev" : 0.02,
          "sum" : 1.0
        },
      "DELETE" : { "current" : 187.0,
          "description" : "number of HTTP DELETE requests",
          "max" : 81,
          "mean" : 0.074999999999999997,
          "min" : 0,
          "stddev" : 1.6919999999999999,
          "sum" : 187.0
        },
      "GET" : { "current" : 1672.0,
          "description" : "number of HTTP GET requests",
          "max" : 222,
          "mean" : 0.66600000000000004,
          "min" : 0,
          "stddev" : 9.1310000000000002,
          "sum" : 1672.0
        },
      "HEAD" : { "current" : null,
          "description" : "number of HTTP HEAD requests",
          "max" : null,
          "mean" : null,
          "min" : null,
          "stddev" : null,
          "sum" : null
        },
      "POST" : { "current" : 190.0,
          "description" : "number of HTTP POST requests",
          "max" : 35,
          "mean" : 0.075999999999999998,
          "min" : 0,
          "stddev" : 1.1719999999999999,
          "sum" : 190.0
        },
      "PUT" : { "current" : 345.0,
          "description" : "number of HTTP PUT requests",
          "max" : 85,
          "mean" : 0.13700000000000001,
          "min" : 0,
          "stddev" : 2.1110000000000002,
          "sum" : 345.0
        }
    },
  "httpd_status_codes" : { "200" : { "current" : 1734.0,
          "description" : "number of HTTP 200 OK responses",
          "max" : 221,
          "mean" : 0.68999999999999995,
          "min" : 0,
          "stddev" : 9.2690000000000001,
          "sum" : 1734.0
        },
      "201" : { "current" : 245.0,
          "description" : "number of HTTP 201 Created responses",
          "max" : 31,
          "mean" : 0.098000000000000004,
          "min" : 0,
          "stddev" : 1.2170000000000001,
          "sum" : 245.0
        },
      "202" : { "current" : 3.0,
          "description" : "number of HTTP 202 Accepted responses",
          "max" : 1,
          "mean" : 0.001,
          "min" : 0,
          "stddev" : 0.035000000000000003,
          "sum" : 3.0
        },
      "301" : { "current" : 3.0,
          "description" : "number of HTTP 301 Moved Permanently responses",
          "max" : 1,
          "mean" : 0.001,
          "min" : 0,
          "stddev" : 0.035000000000000003,
          "sum" : 3.0
        },
      "304" : { "current" : 25.0,
          "description" : "number of HTTP 304 Not Modified responses",
          "max" : 8,
          "mean" : 0.01,
          "min" : 0,
          "stddev" : 0.215,
          "sum" : 25.0
        },
      "400" : { "current" : 23.0,
          "description" : "number of HTTP 400 Bad Request responses",
          "max" : 8,
          "mean" : 0.0089999999999999993,
          "min" : 0,
          "stddev" : 0.22900000000000001,
          "sum" : 23.0
        },
      "401" : { "current" : 5.0,
          "description" : "number of HTTP 401 Unauthorized responses",
          "max" : 2,
          "mean" : 0.002,
          "min" : 0,
          "stddev" : 0.059999999999999998,
          "sum" : 5.0
        },
      "403" : { "current" : 14.0,
          "description" : "number of HTTP 403 Forbidden responses",
          "max" : 6,
          "mean" : 0.0060000000000000001,
          "min" : 0,
          "stddev" : 0.156,
          "sum" : 14.0
        },
      "404" : { "current" : 100.0,
          "description" : "number of HTTP 404 Not Found responses",
          "max" : 80,
          "mean" : 0.040000000000000001,
          "min" : 0,
          "stddev" : 1.6220000000000001,
          "sum" : 100.0
        },
      "405" : { "current" : 3.0,
          "description" : "number of HTTP 405 Method Not Allowed responses",
          "max" : 1,
          "mean" : 0.001,
          "min" : 0,
          "stddev" : 0.035000000000000003,
          "sum" : 3.0
        },
      "409" : { "current" : 3.0,
          "description" : "number of HTTP 409 Conflict responses",
          "max" : 1,
          "mean" : 0.001,
          "min" : 0,
          "stddev" : 0.035000000000000003,
          "sum" : 3.0
        },
      "412" : { "current" : null,
          "description" : "number of HTTP 412 Precondition Failed responses",
          "max" : null,
          "mean" : null,
          "min" : null,
          "stddev" : null,
          "sum" : null
        },
      "500" : { "current" : 87.0,
          "description" : "number of HTTP 500 Internal Server Error responses",
          "max" : 75,
          "mean" : 0.035000000000000003,
          "min" : 0,
          "stddev" : 1.508,
          "sum" : 87.0
        }
    }
} 

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.