Background


The QPP can be used to gather and analyze data from your production servers and then use the same data to benchmark test servers.

Querysniffer

Querysniffer is a query sniffer for mysql. You will need to install querysniffer on the server you are gathering queries. It sniffs the network traffic with pcap, extracts queries from mysql packets, and prints them to standard output. You can view the queries in real-time or just redirect the output to a file for use by either queryparse or querybench. Available options include the display of IP addesses (-i) and marking the end of logged queries with a NULL marker (-e). This enables you to use the output as input for the querybench program. You can also use the -q option to have suitable output for querybench. This ends queries with NULL, only prints SELECTs and USE statements and prints timing data.

Requirements


libpcap

Usage


querysniffer [-ie] [interface]
-ishow IP addresses
-qgenerate output suitable for querybench/parse (implies -esti)
-eend queries with NULL
-sonly print SELECTs (and USE db, and QUIT)
-tprint timing data
-ddebug

The interface is optional - it defaults to the interface returned by Net::Pcap::lookupdev().

Example

kmurphy@db5-a:~$ sudo mysqlsniff.x64 > output
This must be run from sudo or as root. In this example we send the results to an output file. You can also specify an Ethernet port to grab queries. This is done by adding an “eth0” or “eth1” before specifying the output file:

kmurphy@db5-a:~$ sudo mysqlsniff.x64 eth0 > output

Why would you want to do this? If you are running this on a server that is attached to the SAN then you should probably specify the interface that is not attached to the SAN.

The following would be used to gather data for input to querybench:

kmurphy@db3-a:~$ sudo mysqlsniff.x64 -eit > output

Querybench

Querybench is our tool developed around a core of the MyBench program of Jeremy Zawodny http://jeremy.zawodny.com/mysql/mybench/. The input of Querybench is the output of Querysniffer. Another words, I run Querysniffer to gather the data (and dump it into an output file) then I use that output file for Querybench. What this does is allow us to benchmark test a server with production data and production queries. This is much more informative than just using some random query against a test database. In addition, I want to set up a rollback filesystem so that you can take a snapshot of the filesystem and then run querysniff output against it. When the testing is over just roll back the filesystem to the starting point. The EVMS file manager program will provide this ability and will be demonstrated in this tutorial.

Usage


querybench
-nThe number of clients you wish to simulate (from 10 to ??)
-rnot currently working (ignored)
dsnthe conection string (DBI:mysql::hostname)
filethe output file from querysniffer
userthe username on the MySQL server that the queries will be executed under
passwordthe password of the user that the queries will be executed under on the MySQL server

When querybench is called it takes the input file, pulls in the first n query patterns generated by clients and runs them against the test database. Then it grabs the next n query patterns. It repeats this procedure until it reaches the end of the input data and displays gathered data.

Example

$ ./querybench -n50 DBI:mysql::hostname ~/queryfile user pass
forking: ++++++++++++++++++++++++++++++++++++++++++++++++++
sleeping for 6 seconds while kids get ready
waiting: --------------------------------------------------

Queryparser

Queryparser is our tool to examine production query traffic (captured by Querysniffer) and determine statistics about our query data. Queryparser takes the output from Querysniffer and injects it into a database. We have some simple php scripts to view the data. In addition, the querysniffer also logs which server the data is coming from. That way you can inject data from multiple database sniffers into one central database and then filter on hostname. It is important to realize the queryparser will work in conjunction with querysniffer to analyze your data.
Example
sudo ./querysniffer.x64 -q | ./queryparse

You will need to edit the binary file and insert the username, password and hostname of the database server you want the sniffed data to be injected into. In our situation we run a a cron job on the production server we wish to analyze. This cron job starts up queryparser periodically for 300 seconds and of course queryparser injects the gathered data into a database for reporting. Then we use some simple html/php pages to view the data.
An example cronjob file might look like this:

00 0,3,6,9,12,15,18,21 * * * root /usr/local/bin/querysniffer.x86 -q | /usr/local/bin/queryparse -t 300

Here are a couple of sample web pages for viewing this information.
Here is a sample of the 'index.html' page.


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="content-type">
<title>Database Information</title>
</head>
<body>

<p>
Take a look at our query stats!<br>
<br>
<br>
<form action="queries.php" method="POST">
<table style="text-align: left; width: 551px; height: 32px;" border="1" cellpadding="2" cellspacing="2">
<tbody>

<tr>
<td><input type="radio" name="query_type" value="slow" checked>Slow queries</td>
</tr>
<tr>
<td><input type="radio" name="query_type" value="most">Most executed queries</td>
</tr>
<tr>
<td><input type="radio" name="query_type" value="avg_total">Average & Total Execution Time for queries</td>
</tr>
</tbody>

</table>

<div align="left"><br>
<input type="radio" name="hostname" value="db1-a">db1-a<br>
<input type="radio" name="hostname" value="db3-a">Admin Server (db3-a)<br>
<input type="radio" name="hostname" value="db5-a">db5-a<br>
<input type="radio" name="hostname" value="all" checked>all publisher servers<br>
<input type="radio" name="hostname" value="ral-sql002">community<br>
</div>
<br>
<input type="submit" value="Submit">
</form>

</body>
</html>



Here is the code of the 'queries.php' page. It is called from the index.html page.

<?php

//set up database connection

@ $db = mysql_pconnect("localhost","db_username","db_password");

if (!$db) {

echo "Error: Could not connect to database. Please try again later.";
exit;
}

mysql_select_db("query_stat_database");

//get variables from main page

$query_type=$_REQUEST['query_type'];
$hostname=$_REQUEST['hostname'];

if ($hostname=='all') {
$hostname='db1-a\' or cap_host=\'db5-a';
}

if ($query_type=='slow') {

//slow queries

$query="select count(*) num_times_execute, avg(exec_time) as avg,sum(exec_time) as sum, query from query_parse where cap_host='{$hostname}' group by query order by avg desc";

$result = mysql_query($query);
$num_results = mysql_num_rows($result);
echo '<html><body>';
echo 'SLOW QUERIES<br>';
echo '<table border=2 width=95%>
<tr><td><b>Times Executed</b></td><td><b>Avg. Execution Time</b></td><td><b>Total Execution Time</b></td><td><b>Query</b></td></tr>';

} // end of slow queries

elseif ($query_type=='most') {

//most executed queries

$query="select count(*) exec_count, avg(exec_time) as avg,sum(exec_time) as sum, query from query_parse where cap_host='{$hostname}' group by query order by exec_count desc";

$result = mysql_query($query);
$num_results = mysql_num_rows($result);
echo '<html><body>';
echo 'MOST EXECUTED QUERIES<br>';
echo '<table border=2 width=95%>
<tr><td><b>Times Executed</b></td><td><b>Avg. Execution Time</b></td><td><b>Total Execution Time</b></td><td><b>Query</b></td></tr>';

} else {

//average execution time

$query="select count(*) num_times_execute, avg(exec_time) as avg,sum(exec_time) as sum, query from query_parse where cap_host='{$hostname}' group by query order by sum desc";

$result = mysql_query($query);
$num_results = mysql_num_rows($result);
echo '<html><body>';
echo 'AVERAGE EXECUTION TIME QUERIES<br>';
echo '<table border=2 width=95%>
<tr><td><b>Times Executed</b></td><td><b>Avg. Execution Time</b></td><td><b>Total Execution Time</b></td><td><b>Query</b></td></tr>';

}

$bg = '#eeeeee'; //set background color
for ($i=0; $i < $num_results; $i++) {
$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); //switch background colors
$row = mysql_fetch_array($result);
$query_chomp = substr($row[3], 0, 90);
echo '<tr bgcolor="' . $bg . '">
<td>'.$row[0].'</td>
<td>'.$row[1].'</td>
<td>'.$row[2].'</td>
<td>'.$query_chomp.'</td>
</tr>';
}

echo "</table>";

echo '</body></html>';

mysql_free_result ($result);
mysql_close();
?>

You will need to modify them to fit your needs.