Thursday, November 29, 2012

Performance Testing | Database Monitoring | Mysql

Following are some commands that we we can use to monitor mysql database on linux server.

To Monitor Current Queries :
  • show processlist;
  • show full processlist;

Query Execution Plan
  • explain QueryStatement
  • explain extended QueryStatement

To show index present in table

           show index from TableName

To Monitor Locks,Waits and Deadlocks
  •  show engine innodb status;  (See semaphore section).
  • SELECT r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,        b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b  ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r  ON r.trx_id = w.requesting_trx_id\G
Find Largest Tables in Mysql

SELECT CONCAT(table_schema, '.', table_name), CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')    DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,ROUND(index_length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER  BY data_length + index_length DESC LIMIT  10;

Enabling Slow Query Log*:

1) Enter following lines in the file[my.cnf]
                Default Location:[ Location :/etc/my.cnf]


datadir                         = /var/lib/mysql
log_slow_queries                = mysql-slow.log
long_query_time                 = 1     
2) After making changes restart the mysql .

*Require Root access to make this change

Tool for monitoring: Spotlight (

Please feel free to add anything regarding Mysql Monitoring to this thread.

Tuesday, November 27, 2012

linux top command -- For Beautiful Monitoring of server Hardware Resources

The linux top command is the Command Line equivalent to Task Manager in windows.

Question / Scenario:

How do I determine CPU and Memory utilization, based on running processes.

Answer / Solution:

Use the TOP command in linux.


Top command provides a real-time look at what is happening with your system. Top produces so much output that a new user may get over whelmed with all thats presented and what it means.
Lets take a look at TOP one line at a time. The server has been flooded with http requests to create some load on the server.
top output:
top - 22:09:08 up 14 min,  1 user,  load average: 0.21, 0.23, 0.30
Tasks:  81 total,   1 running,  80 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.5%us, 31.2%sy,  0.0%ni, 27.0%id,  7.6%wa,  1.0%hi, 23.7%si,  0.0%st
Mem:    255592k total,   167568k used,    88024k free,    25068k buffers
Swap:   524280k total,        0k used,   524280k free,    85724k cached

 3166 apache    15   0 29444 6112 1524 S  6.6  2.4   0:00.79 httpd
 3161 apache    15   0 29444 6112 1524 S  5.9  2.4   0:00.79 httpd
 3164 apache    15   0 29444 6112 1524 S  5.9  2.4   0:00.75 httpd
 3169 apache    15   0 29444 6112 1524 S  5.9  2.4   0:00.74 httpd
 3163 apache    15   0 29444 6112 1524 S  5.6  2.4   0:00.76 httpd
 3165 apache    15   0 29444 6112 1524 S  5.6  2.4   0:00.77 httpd
 3167 apache    15   0 29444 6112 1524 S  5.3  2.4   0:00.73 httpd
 3162 apache    15   0 29444 6112 1524 S  5.0  2.4   0:00.77 httpd
 3407 root      16   0  2188 1012  816 R  1.7  0.4   0:00.51 top
  240 root      15   0     0    0    0 S  0.3  0.0   0:00.08 pdflush
  501 root      10  -5     0    0    0 S  0.3  0.0   0:01.20 kjournald
 2794 root      18   0 12720 1268  560 S  0.3  0.5   0:00.73 pcscd
    1 root      15   0  2060  636  544 S  0.0  0.2   0:03.81 init
    2 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 migration/0
    3 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/0
    4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0
    5 root      10  -5     0    0    0 S  0.0  0.0   0:00.07 events/0
The first line in top:
top - 22:09:08 up 14 min,  1 user,  load average: 0.21, 0.23, 0.30
“22:09:08″ is the current time; “up 14 min” shows how long the system has been up for; “1 user” how many users are logged in; “load average: 0.21, 0.23, 0.30″ the load average of the system (1minute, 5 minutes, 15 minutes).
Load average is an extensive topic and to understand its inner workings can be daunting. The simplest of definitions states that load average is the cpu utilization over a period of time. A load average of 1 means your cpu is being fully utilized and processes are not having to wait to use a CPU. A load average above 1 indicates that processes need to wait and your system will be less responsive. If your load average is consistently above 3 and your system is running slow you may want to upgrade to more CPU’s or a faster CPU.
The second line in top:
Tasks:  82 total,   1 running,  81 sleeping,   0 stopped,   0 zombie
Shows the number of processes and their current state.
The third lin in top:
Cpu(s):  9.5%us, 31.2%sy,  0.0%ni, 27.0%id,  7.6%wa,  1.0%hi, 23.7%si,  0.0%st
Shows CPU utilization details. “9.5%us” user processes are using 9.5%; “31.2%sy” system processes are using 31.2%; “27.0%id” percentage of available cpu; “7.6%wa” time CPU is waiting for IO.
When first analyzing the Cpu(s) line in top look at the %id to see how much cpu is available. If %id is low then focus on %us, %sy, and %wa to determine what is using the CPU.
The fourth and fifth lines in top:
Mem:    255592k total,   167568k used,    88024k free,    25068k buffers
Swap:   524280k total,        0k used,   524280k free,    85724k cached
Describes the memory usage. These numbers can be misleading. “255592k total” is total memory in the system; “167568K used” is the part of the RAM that currently contains information; “88024k free” is the part of RAM that contains no information; “25068K buffers and 85724k cached” is the buffered and cached data for IO.
So what is the actual amount of free RAM available for programs to use ?
The answer is: free + (buffers + cached)
88024k + (25068k + 85724k) = 198816k
How much RAM is being used by progams ?
The answer is: used – (buffers + cached)
167568k – (25068k + 85724k) = 56776k
The processes information:
Top will display the process using the most CPU usage in descending order. Lets describe each column that represents a process.
3166 apache    15   0 29444 6112 1524 S  6.6  2.4   0:00.79 httpd
PID – process ID of the process
USER – User who is running the process
PR – The priority of the process
NI – Nice value of the process (higher value indicates lower priority)
VIRT – The total amount of virtual memory used
RES – Resident task size
SHR – Amount of shared memory used
S – State of the task. Values are S (sleeping), D (uninterruptible sleep), R (running), Z(zombies), or (stopped or traced)
%CPU – Percentage of CPU used
%MEM – Percentage of Memory used
TIME+ – Total CPU time used
COMMAND – Command issued

Interacting with TOP

Now that we are able to understand the output from TOP lets learn how to change the way the output is displayed.
Just press the following key while running top and the output will be sorted in real time.
M – Sort by memory usage
P – Sort by CPU usage
T – Sort by cumulative time
z – Color display
k – Kill a process
q – quit
If we want to kill the process with PID 3161, then press “k” and a prompt will ask you for the PID number, and enter 3161.

Command Line Parameters with TOP

You can control what top displays by issuing parameters when you run top.
- d – Controls the delay between refreshes
- p – Specify the process by PID that you want to monitor
-n – Update the display this number of times and then exit
If we want to only monitor the http process with a PID of 3166
$ top -p 3166
If we want to change the delay between refreshes to 5 seconds
$ top -d 5

Sunday, November 18, 2012

Dealing with dynamic boundaries in LoadRunner using Text Flags

When we talk about LoadRunner, we generally hear the term Correlation (which is nothing but capturing dynamic data from the server response to be used as input in further requests). LoadRunner is good at auto-correlation capabilities, but sometimes we come across situations, where only your scripting capabilities and the correct use of some advanced features can help.
We generally talk about dynamic data, but what if the strings by which that dynamic data is bound, are themselves dynamic. To put it simply, what if the left and right boundaries of the string to be captured are dynamic? For complex changes, we first capture a subset of the server response, based on some unique boundaries identified, and then with the aid of custom-built string handling functions, we get the desired substring out of the captured string. I will discuss that approach in a separate article.
Many a times, the solution can be much simpler. If you come across dynamic boundaries like the following, then instead of doing lot of string operations, you can use text flags in LoadRunner.
Suppose you have the response data as follows, where Captured is the string you want to capture, but issue is that the left boundary is changing every time. You get the left boundary as axb, where x ranges between 0 and 9, as follows:
You can capture the desired string by putting the following correlation function in place, using the /DIG text flag in combination with LB:
web_reg_save_param(“DynamicCapture”, “LB/DIG=a#b\=”, “RB=rb”, LAST);

The corresponding place, which you expect to be dynamically filled in with a digit, should be replaced by a pound sign ( # ).
If letters are changing case, you can modify the function as below to include the /IC flag:
web_reg_save_param(“DynamicCapture”, “LB/IC/DIG=a#b\=”, “RB/IC=rb”, LAST);
Extending the argument further, if there are multiple digits, you have to put a pound sign (#) sing for each digit:
web_reg_save_param(“DynamicCapture”, “LB/IC/DIG=a####b\=”, “RB/IC=rb”, LAST);
Till now we were discussing about dynamic digits. If you find a case, where you expect a place to be filled in dynamically by a digit or a letter, then modify the function to use /ALNUM instead of /DIG text flag, and use caret sign(^) instead of # :
web_reg_save_param(“DynamicCapture”, “LB/ALNUM=a^b\=”, “RB/IC=rb”, LAST);

To deal with the case while, matching alphanumeric dynamic boundaries, there are three versions of /ALNUM flag as – ALNUMIC to ignore case, ALNUMLC to match only lower case, and ALNUMUC to match only upper case. In the above example, ALNUMIC has been used.
So, go back experiment! But while you do that, keep in mind two things:
1.One sign (# or ^) for each place
2.If you have a literal # or ^ sign, it will not be interpreted as a literal, if you use the corresponding flags. E.g. LB/DIG=a#b will not match “a#b”.