This article illustrates how to write data to a MySQL database from a Java Extension, and how to use a background thread to minimize latency and control the load on the database.
Being Lazy with Java Extensions
With a Java Extension, you can log data in real time to an external database. The example in this article describes how to log the ‘referring’ source that each visitor comes in from when they enter a website. Logging is done to a MySQL database, and it maintains a count of how many times each key has been logged, so that you can determine which sites are sending you the most traffic.
The article then presents a modification that illustrates how to lazily perform operations such as database writes in the background (i.e. asynchronously) so that the performance the end user observes is not impaired.
Overview - let's count referers!
It’s often very revealing to find out which web sites are referring the most traffic to the sites that you are hosting. Tools like Google Analytics and web log analysis applications are one way of doing this, but in this example we’ll show an alternative method where we log the frequency of referring sites to a local database for easy access.
When a web browser submits an HTTP request for a resource, it commonly includes a header called "Referer" which identifies the page that linked to that resource. We’re not interested in internal referrers – where one page in the site links to another. We’re only interested in external referrers. We're going to log these 'external referrers' to a MySQL database, counting the frequency of each so that we can easily determine which occur most commonly.
Create the database
Create a suitable MySQL database, with limited write access for a remote user:
% mysql –h dbhost –u root –p
Enter password: ********
mysql> CREATE DATABASE website;
mysql> CREATE TABLE website.referers ( data VARCHAR(256) PRIMARY KEY, count INTEGER );
mysql> GRANT SELECT,INSERT,UPDATE ON website.referers TO 'web'@'%' IDENTIFIED BY 'W38_U5er';
mysql> GRANT SELECT,INSERT,UPDATE ON website.referers TO 'web'@'localhost' IDENTIFIED BY 'W38_U5er';
mysql> QUIT;
Verify that the table was correctly created and the ‘web’ user can access it:
% mysql –h dbhost –u web –p
Enter password: W38_U5er
mysql> DESCRIBE website.referers;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| data | varchar(256) | NO | PRI | | |
| count | int(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM website.referers;
Empty set (0.00 sec)
The database looks good...
Create the Java Extension
We'll create a Java Extension that writes to the database, adding rows with the provided 'data' value, and setting the 'count' value to '1', or incrementing it if the row already exists.
CountThis.java
Compile up the following 'CountThis' Java Extension:
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class CountThis extends HttpServlet {
private static final long serialVersionUID = 1L;
private Connection conn = null;
private String userName = null;
private String password = null;
private String database = null;
private String table = null;
private String dbserver = null;
public void init( ServletConfig config) throws ServletException {
super.init( config );
userName = config.getInitParameter( "username" );
password = config.getInitParameter( "password" );
table = config.getInitParameter( "table" );
dbserver = config.getInitParameter( "dbserver" );
if( userName == null || password == null || table == null || dbserver == null )
throw new ServletException(
"Missing username, password, table or dbserver config value" );
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch( Exception e ) {
throw new ServletException( "Could not initialize mysql: "+e.toString() );
}
}
public void doGet( HttpServletRequest req, HttpServletResponse res )
throws ServletException, IOException
{
try {
String[] args = (String[])req.getAttribute( "args" );
String data = args[0];
if( data == null ) return;
if( conn == null ) {
conn = DriverManager.getConnection(
"jdbc:mysql://"+dbserver+"/", userName, password);
}
PreparedStatement s = conn.prepareStatement(
"INSERT INTO " + table + " ( data, count ) VALUES( ?, 1 ) " +
"ON DUPLICATE KEY UPDATE count=count+1" );
s.setString(1, data);
s.executeUpdate();
} catch( Exception e ) {
conn = null;
log( "Could not log data to database table '" + table + "': " + e.toString() );
}
}
public void doPost( HttpServletRequest req, HttpServletResponse res )
throws ServletException, IOException
{
doGet( req, res );
}
}
Upload the resulting CountThis.class file to Traffic Manager's Java Catalog. Click on the class name to configure the following initialization properties:
You must also upload the mysql connector (I used mysql-connector-java-5.1.24-bin.jar) from dev.mysql.com to your Traffic Manager Java Catalog.
Add the TrafficScript rule
You can test the Extension very quickly using the following TrafficScript rule to log the each request:
java.run( "CountThis", http.getPath() );
Check the Traffic Manager event log for any error messages, and query the table to verify that it is getting populated by the extension:
mysql> SELECT * FROM website.referers ORDER BY count DESC LIMIT 5;
+--------------------------+-------+
| data | count |
+--------------------------+-------+
| /media/riverbed.png | 5 |
| /articles | 3 |
| /media/puppies.jpg | 2 |
| /media/ponies.png | 2 |
| /media/cats_and_mice.png | 2 |
+--------------------------+-------+
5 rows in set (0.00 sec)
mysql> TRUNCATE website.referers;
Query OK, 0 rows affected (0.00 sec)
Use 'Truncate' to delete all of the rows in a table.
Log and count referer headers
We only want to log referrers from remote sites, so use the following TrafficScript rule to call the Extension only when it is required:
# This site
$host = http.getHeader( "Host" );
# The referring site
$referer = http.getHeader( "Referer" );
# Only log the Referer if it is an absolute URI and it comes from a different site
if( string.contains( $referer, "://" ) && !string.contains( $referer, "://".$host."/" ) ) {
java.run( "CountThis", $referer );
}
Add this rule as a request rule to a virtual server that processes HTTP traffic.
As users access the site, the referer header will be pushed into the database. A quick database query will tell you what's there:
% mysql –h dbhost –u web –p
Enter password: W38_U5er
mysql> SELECT * FROM website.referers ORDER BY count DESC LIMIT 4;
+--------------------------------------------------+-------+
| referer | count |
+--------------------------------------------------+-------+
| http://www.google.com/search?q=stingray | 92 |
| http://www.riverbed.com/products/stingray | 45 |
| http://www.vmware.com/appliances | 26 |
| http://www.riverbed.com/ | 5 |
+--------------------------------------------------+-------+
4 rows in set (0.00 sec)
Lazy writes to the database
This is a useful application of Java Extensions, but it has one big drawback. Every time a visitor arrives from a remote site, his first transaction is stalled while the Java Extension writes to the database. This breaks one of the key rules of website performance architecture – do everything you can asynchronously (i.e. in the background) so that your users are not impeded (see "Lazy Websites run Faster").
Instead, a better solution would be to maintain a separate, background thread that wrote the data in bulk to the database, while the foreground threads in the Java Extension simply appended the Referer data to a table:
CountThisAsync.java
The following Java Extension (CountThisAsync.java) is a modified version of CountThis.java that illustrates this technique:
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.LinkedList;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class CountThisAsync extends HttpServlet {
private static final long serialVersionUID = 1L;
private Writer writer = null;
protected static LinkedList theData = new LinkedList();
protected class Writer extends Thread {
private Connection conn = null;
private String table;
private int syncRate = 20;
public void init( String username, String password, String url, String table )
throws Exception {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection( url, username, password);
this.table = table;
start();
}
public void run() {
boolean running = true;
while( running ) {
try { sleep( syncRate*1000 ); } catch( InterruptedException e ) { running = false; };
try {
PreparedStatement s = conn.prepareStatement(
"INSERT INTO " + table + " ( data, count ) VALUES( ?, 1 )" +
"ON DUPLICATE KEY UPDATE count=count+1" );
conn.setAutoCommit( false );
synchronized( theData ) {
while( !theData.isEmpty() ) {
String data = theData.removeFirst();
s.setString(1, data);
s.addBatch();
}
}
s.executeBatch();
} catch ( Exception e ) {
log( e.toString() );
running = false;
}
}
}
}
public void init( ServletConfig config ) throws ServletException {
super.init( config );
String userName = config.getInitParameter( "username" );
String password = config.getInitParameter( "password" );
String table = config.getInitParameter( "table" );
String dbserver = config.getInitParameter( "dbserver" );
if( userName == null || password == null || table == null || dbserver == null )
throw new ServletException(
"Missing username, password, table or dbserver config value" );
try {
writer = new Writer();
writer.init( userName, password, "jdbc:mysql://"+dbserver+"/", table );
} catch( Exception e ) {
throw new ServletException( e.toString() );
}
}
public void doGet( HttpServletRequest req, HttpServletResponse res )
throws ServletException, IOException
{
String[] args = (String[])req.getAttribute( "args" );
String data = args[0];
if( data != null && writer.isAlive() ) {
synchronized( theData ) {
theData.add( data );
}
}
}
public void doPost( HttpServletRequest req, HttpServletResponse res )
throws ServletException, IOException
{
doGet( req, res );
}
public void destroy() {
writer.interrupt();
try { writer.join( 1000L ); } catch( InterruptedException e ) {};
super.destroy();
}
}
When the Extension is invoked by Traffic Manager, it simply stores the value of the Referer header in a local list and returns immediately. This minimizes any latency that the end user may observe.
The Extension creates a separate thread (embodied by the Writer class) that runs in the background. Every syncRate seconds, it removes all of the values from the list and writes them to the database.
Compile the extension:
$ javac -cp servlet.jar:zxtm-servlet.jar CountThisAsync.java
$ jar -cvf CountThisAsync.jar CountThisAsync*.class
... and upload the resulting CountThisAsync.jar Jar file to your Java catalog. Remember to apply the four configuration parameters to the CountThisAsync.jar Java Extension so that it can access the database, and modify the TrafficScript rule so that it calls the CountThisAsync Java Extension.
You’ll observe that database updates may be delayed by up to 20 seconds (you can tune that delay in the code), but the level of service that end users experience will no longer be affected by the speed of the database.
