Python SQL Runner

Recently, I have been working quite a bit with our taxonomy database, using the data in it as input to some data mining programs I am working on. Initially, I built in the database call into the programs themselves, but this approach turned out to be inconvenient for a number of reasons. One of these reasons is that the program is harder to restart in case of failures. Having these programs work with input files instead of a database query also results in cleaner and more extensible design, since now they can work with inputs other than SQL.

The databases I work with are MySQL and Oracle. The last time I needed this sort of stuff, I would whip up a Python script on-demand using either the MySQLdb or cx-oracle packages. One crashed hard disk and a couple of OS re-installs later, these scripts no longer work because the packages need to be re-installed as well. I have mostly gotten by since the crash using the MyEclipse Database Explorer and cut-n-paste, since the datasets I was working with were smaller, but the size of the data (~ 100K plus rows) I am dealing with now are likely to cause Eclipse to clutch its throat and do a crash-n-burn, taking my other unsaved code along with it, so I needed something that I could use from the command line.

This time around, I planned to do things a bit smarter than I did in the past, so I thought of building a generic script that would dump out rows given any SQL, something similar to my fledgling attempt many, many years ago. Funny how things come full circle from time to time. This time around, I also wanted to make sure its usage survives disk crashes and OS re-installs, so rather than have a script that requires extra dependencies, I wanted to use the JDBC drivers that I was going to install anyway. So my choices boiled down to either Jython or Scala.

I ended up writing it using Jython because my other scripts are all written in Python, and because writing database code in Python is slightly less verbose than in Java (or Scala, since that would have been the same thing). The name of the script is db2csv.py, and the code is shown below:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156

#!/usr/bin/env /opt/jython-2.2.1/jython import sys import getopt import os.path import traceback from com.ziclix.python.sql import zxJDBC class SqlRunner: """ Class to run the SQL and print it out into the output file as a CSV """ def __init__(self, dbconfig, sqlfile, outputfile, sepchar): """ @param dbconfig the database configuration file name @param sqlfile the name of the file containing the SQL to be executed @param outputfile the name of the file where output will be written @param sepchar the separator character (or string) to use """ self.dbconfig = dbconfig self.sqlfile = sqlfile self.outputfile = outputfile self.sepchar = sepchar def getDbProps(self): """ Return the database properties as a map of name value pairs. @return a map of name value pairs """ if (not os.path.exists(self.dbconfig)): raise Exception("File not found: %s" % (self.dbconfig)) props = {} pfile = open(self.dbconfig, 'rb') for pline in pfile: (name, value) = pline[:-1].split("=") props[name] = value pfile.close() return props def getSql(self): """ Validate the sql file name, and parse out the SQL to be run. The method will skip SQL single-line comments. Blocks enclosed by free standing multi-line comments are also skipped. @return the SQL as a string """ if (not os.path.exists(self.sqlfile)): raise Exception("File not found: %s" % (self.sqlfile)) sql = [] sfile = open(self.sqlfile, 'rb') incomment = False for sline in sfile: sline = sline.rstrip('\n') if (sline.startswith("--") or len(sline.rstrip()) == 0): # SQL Comment line, skip continue if (sline.rstrip() == "/*"): # start of SQL comment block incomment = True continue if (sline.rstrip() == "*/"): # end of SQL comment block incomment = False continue if (not incomment): sql.append(sline) sfile.close() return " ".join(sql) def runSql(self): """ Runs the SQL and prints it out into the specified output file as a CSV file delimited by sepchar. """ props = self.getDbProps() sql = self.getSql() print "Running SQL: %s" % (sql) ofile = open(self.outputfile, 'wb') db = zxJDBC.connect(props["url"], props["user"], props["password"], props["driver"]) cur = db.cursor(True) cur.execute(sql) # print the header meta = cur.description print "Writing output to: %s" % (self.outputfile) ofile.write(self.sepchar.join(map(lambda x: x[0], meta)) + "\n") for row in cur.fetchall(): strrow = map(lambda x: str(x), row) ofile.write(self.sepchar.join(strrow) + "\n") ofile.close() cur.close() db.close() def usage(error=""): """ Print the usage information. If an error message is supplied, print that on top of the usage information. """ if (len(str(error)) > 0): print "ERROR: %s" % (error) print "STACK TRACE:" traceback.print_exc() print "USAGE:" print "%s -d dbconfig -q queryfile -s sepchar -o outputfile" % (sys.argv[0]) print "OR: %s -h" % (sys.argv[0]) print "OPTIONS:" print "--dbconfig | -d : database configuration file" print " configuration file must be in properties format, with the following" print " keys defined: driver, url, user and password" print "--queryfile | -q : name of file containing SQL to be run" print "--outputfile | -o: name of file where results should be written" print "--sep | -s : the separator character to use in output" print "--help | -h : print this information" sys.exit(2) def extractOptions(argv): """ Extract command line options and return a tuple @param argv the sys.argv object @return a tuple containing the information for running the SQL """ try: (opts, args) = getopt.getopt(argv[1:], "d:q:s:o:h", ["dbconfig=", "queryfile=", "sep=", "outputfile=", "help"]) except getopt.GetoptError: usage() if (len(filter(lambda x: x[0] in ("-h", "--help"), opts)) == 1): usage() if (len(opts) != 4): usage() for opt in opts: (key, value) = opt if (key in ("-d", "--dbconfig")): dbconfig = value elif (key in ("-q", "--queryfile")): sqlfile = value elif (key in ("-o", "--outputfile")): outputfile = value elif (key in ("-s", "--sep")): sepchar = value else: usage() return (dbconfig, sqlfile, outputfile, sepchar) def main(): """ This is how we are called """ (dbconfig, sqlfile, outputfile, sepchar) = extractOptions(sys.argv) sqlrunner = SqlRunner(dbconfig, sqlfile, outputfile, sepchar) try: sqlrunner.runSql() except Exception, e: usage(e) if __name__ == "__main__": main()

As you can see, there is nothing really new here, you can get most of this from the relevant section of the Jython User Guide. I did start to use a bit of Python lambdas, which ironically, I learned on my recent foray into Scala-land.

In the spirit of not having to install anything but the base language, the zJDBC package comes standard with the Jython version (2.2.1) that I am using. Since I already use Java I will install it anyway, as well JDBC drivers for the different databases that I will talk to.

You can call the script using command line parameters as shown in the help output below:

1 2 3 4 5 6 7 8 9 10 11 12

sujit@sirocco:~$ ./db2csv.py -h USAGE: ./db2csv.py -d dbconfig -q queryfile -s sepchar -o outputfile OR: ./db2csv.py -h OPTIONS: --dbconfig | -d : database configuration file configuration file must be in properties format, with the following keys defined: driver, url, user and password --queryfile | -q : name of file containing SQL to be run --outputfile | -o: name of file where results should be written --sep | -s : the separator character to use in output --help | -h : print this information

I had described in a previous post how one can add JAR files to the Jython classpath by appending the path names to sys.path in the code, but the approach didn't work for me here, perhaps because the appropriate Driver JAR class is being loaded explicitly by the code here. So I fell back to having it be loaded from the Java CLASSPATH instead. It's not such a bad thing, though - with this approach, one can use the script as is, for any database, as long as the JDBC driver exists on the CLASSPATH when the script is invoked. And if the driver is not in the CLASSPATH, the script tells you:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

sujit@sirocco:~$ ./db2csv.py -d /path/to/config/file -q /path/to/sql/file.sql \ -o /path/to/report/file -s "|" Running SQL: select foo from bar where baz = 'baz' ERROR: driver [com.mysql.jdbc.Driver] not found STACK TRACE: Traceback (most recent call last): File "./db2csv.py", line 156, in main sqlrunner.runSql() File "./db2csv.py", line 83, in runSql db = zxJDBC.connect(props["url"], props["user"], props["password"], DatabaseError: driver [com.mysql.jdbc.Driver] not found USAGE: ./db2csv.py -d dbconfig -q queryfile -s sepchar -o outputfile OR: ./db2csv.py -h OPTIONS: --dbconfig | -d : database configuration file configuration file must be in properties format, with the following keys defined: driver, url, user and password --queryfile | -q : name of file containing SQL to be run --outputfile | -o: name of file where results should be written --sep | -s : the separator character to use in output --help | -h : print this information

I also had to increase the JVM heap size because Jython was running out of heap space when running the queries. I did this directly in the Jython script, by adding -Xmx2048m to the java call.

In spite of the simplicity of the script, I am already finding it immensely useful. If you have reached here looking for a similar script, I hope you will find it useful as well.

Externalizing Database Access with a ResultSet Iterator

I recently needed to structure some code such that a query against a database table and a sequential read from a (structured) flat file provided the same interface to client code, something like this:

1 2 3 4

public interface ISource { ... public SomeObject getNextDocument(); }

This API is a very natural fit for the flat file, since the implementation can hold a reference to the current position in the file advance it with each invocation of getNextDocument(). However, when the ISource implementation happens to be a SQL query running against a database table, things get a little hairy.

The classic JDBC data access pattern requires us to do all our processing within the ResultSet processing loop, and/or accumulate a Collection of objects for later use, something like this:

1 2 3 4 5 6 7 8 9 10

List<SomeObject> myObjects = new ArrayList<SomeObject>(); Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement("select * from mytable"); ResultSet rs = ps.executeQuery(); while (rs.next()) { SomeObject myObject = doSomethingWithResultSet(rs); myObjects.add(myObject); } rs.close(); ps.close();

The basic usage pattern for the Spring JdbcTemplate is the queryForList() method, which simply allows accumulation of results for later use. The result of this call is a List of Maps, where each Map represents a row of database results.

1 2 3 4 5 6 7 8

List<SomeObject> myObjects = new ArrayList<SomeObject>(); List<Map<String,Object>> rows = jdbcTemplate.queryForList("select * from mytable"); for (Map<String,Object> row : rows) { SomeObject myObject = new SomeObject(); myObject.setMyField(row.get("my_field")); ... myObjects.add(myObject); }

JdbcTemplate also allows processing the individual rows inline, using a RowMapper anonymous inner class, something like this:

1 2 3 4 5 6 7 8 9 10

List<SomeObject> myObjects = jdbcTemplate.query( "select * from mytable", new RowMapper() { public Object mapRow(ResultSet rs, int rowNum) throws SQLException { SomeObject myObject = new SomeObject(); myObject.setMyField(rs.getString("my_field")); ... return myObject; } });

None of these solutions were what I was after, however. What I needed was a way to tell a method to return me the "next row from the database" and hold a reference internally until I invoke the method again. Iterators came to mind almost instantly, but my first attempt was this rather pathetic one, of holding a private reference to the Iterator of the List object generated from a call to jdbcTemplate.queryForList().

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

public class MyFirstPatheticAttempt implements ISource { private String sql; // injected from configuration private Iterator it; ... public open() { List<Map<String,Object>> rows = jdbcTemplate.queryForList(sql); it = rows.iterator(); } public SomeObject getNextObject() { if (it.hasNext()) { Map<String,Object> row = it.next(); SomeObject myObject = new SomeObject(); myObject.setMyField(row.get("my_field")); ... return myObject; } return null; } ... }

Sure, it satisfied the ISource interface, but it felt horribly wrong. Looking up the Iterator Pattern on wikipedia led me to the Generator Pattern, which was basically what I was after. Turns out that Java does not have the "yield return" construct that is required to support this pattern, but C# and Python does, so perhaps its just a matter of time before it pops up here. In any case, it wasn't available now, so that did not solve my problem, so I kept looking.

I finally came across Ryan Brase's article "Deciding between iterators and lists for returned values in Java" on TechRepublic which had an example of what I needed to do (Listing 2 in the article). Unlike his example, however, my code would not know the SQL query it would be executing, so it would need to return the entire record in some way, without having to explicitly list them in the code.

Fortunately, the folks at the Apache commons-dbutils project have built a ResultSetIterator which is an Iterator implementation whose constructor takes a reference to a ResultSet, and which I gratefully re-used.

So here is what I finally came up with. Although it uses plain old JDBC, it is fairly clean and compact, and does the job of allowing the database access to be externalized into a method call.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

public class MyFinalAttempt implements ISource { private String sql; // injected from configuration private Iterator it; ... public open() { Connection conn = ds.getConnection(); PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery(); it = new ResultSetIterator(rs); } public SomeObject getNextObject() { if (it.hasNext()) { Object[] row = it.next(); SomeObject myObject = new SomeObject(); myObject.setMyField(row[0]); ... return myObject; } return null; } ... }

The only thing I did not quite like was that the ResultSetIterator.next() call returned an Object[] row. I would have liked it to return a Map<String,Object> instead, although that shortcoming can be worked around fairly easily in application code. The other thing I wanted to do was to use Spring JdbcTemplate, since that way a lot of resource management issues are taken care of automatically, but I could not figure out how to get a handle on the ResultSet.

If anyone has figured out how to get this pattern going using Spring, I would really like to know. Also, not sure how common such a use-case is, but if you have faced it and solved it, or you think my approach is lame and you have a better one, I would appreciate hearing from you.

Update Jan 26 2008

When I was trying to figure out various approaches to solve this problem, I came across a Java based "yield return" solution on Chaotic Java. It provides an abstract Yielder class which implements the Iterable interface. However, it requires bytecode instrumentation using ASM. The resulting code is quite elegant, something like this, although I could not make it work.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

public Iterable<SomeObject> getSomeObjects() { Connection conn = ds.getConnection(); PreparedStatement ps = conn.prepareStatement(sql); final ResultSet rs = ps.executeQuery(); return new Yielder<SomeObject>() { protected void yieldNextCore() { while (rs.next()) { SomeObject obj = new SomeObject(); obj.setName(rs.getString(1)); ... yieldReturn(obj); } yieldBreak(); } } } @Test public void processObjects() { for (SomeObject obj : getSomeObjects()) { // do something with obj } }

To run it, a -javaagent parameter set to the path to the yielder.jar file needs to be passed to the Java command. I was setting this in the configuration/property for the maven-surefire-plugin and calling this through a JUnit test. I was using asm-all-3.3.0 (downloaded from the yielder SVN repository). From my tests, I see that the first call to getSomeObject() results in all the rows being iterated. Obviously I am doing something wrong, would appreciate any pointers from readers if you have gotten this to work.

Spring JdbcTemplate with Autocommit

I recently ran across a situation where I was using Spring's JdbcTemplate and trying to insert a record into a table, then turn around and read from it some data to use for a subsequent insert into another table. Something like this:

1 2 3 4 5 6 7 8 9 10

while (someCondition) { jdbcTemplate.update("insert into table1..."); ... } List rows = jdbcTemplate.queryForList("select col1, col2 from table1 where..."); for (Map row : rows) { jdbcTemplate.update("insert into table2...", new Object[] { row.get("col1"), row.get("col2"), ... }); }

Inexplicably (when I started seeing the problem first), there would be no rows in table2. Digging deeper, I found that was because no rows were being returned by the queryForList call, so the code was not entering the for loop at all.

The reason for this strange behavior appears to be as follows. Since JdbcTemplate is configured with a DataSource, which is in turn configured with a pool of Connections, there is no guarantee that the same Connection object will be returned to the JdbcTemplate in subsequent calls. So the first update and the queryForList call may not work against the same Connection object, so the row that was INSERTed may not be visible to the SELECT call. At least, thats true for Oracle, where the default transaction isolation level is READ COMMITTED. I cannot speak for other databases, because the only other database where I have used Spring so far is with MySQL with MyISAM which does not support transactions.

Since the first update and the second queryForList and update are really two distinct code blocks, the correct approach is to either restrict the JdbcTemplate to use a SingleConnectionDataSource, or to put the two operations in their own TransactionTemplate callbacks. Both approaches would have required me to change some code, however. A codeless option would have been to turn auto commit on for my code, but I was using a pre-built reference to the enterprise datasource, so that was not something I could do either. Finally I hit upon the idea of using AOP to intercept update() calls in JdbcTemplate and commit() them on completion. Obviously, it is not ideal if multiple JDBC calls could be grouped into a single transaction, but the concepts used here could be extended to cover that scenario as well, although we would be intercepting DAO methods instead of JdbcTemplate methods.

First, here is the interceptor. It uses a TransactionTemplate callback to wrap all specified (in our case, update()) methods. The "BEGIN TRAN", "COMMIT TRAN" and "ROLLBACK TRAN" debug calls indicate the transaction boundaries for the update() call.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

public class AutocommitInterceptor implements MethodInterceptor { private static final Logger LOGGER = Logger.getLogger(AutocommitInterceptor.class); private List<String> autoCommitableMethods; private TransactionTemplate transactionTemplate; public AutocommitInterceptor() { super(); } public void setAutoCommitableMethods(List<String> autoCommitableMethods) { this.autoCommitableMethods = autoCommitableMethods; } public void setTransactionTemplate(TransactionTemplate transactionTemplate) { this.transactionTemplate = transactionTemplate; } public Object invoke(final MethodInvocation invocation) throws Throwable { if (isAutoCommitableMethod(invocation.getMethod().getName())) { return transactionTemplate.execute(new TransactionCallback() { public Object doInTransaction(TransactionStatus transactionStatus) { LOGGER.debug("BEGIN TRAN"); try { Object retVal = invocation.proceed(); LOGGER.debug("COMMIT TRAN"); return retVal; } catch (Throwable t) { LOGGER.error("A runtime exception has occured:", t); LOGGER.debug("ROLLBACK TRAN"); throw new RuntimeException(t); } } }); } else { return invocation.proceed(); } } private boolean isAutoCommitableMethod(String methodName) { boolean isAutoCommitable = false; for (String autoCommitableMethod : autoCommitableMethods) { if (autoCommitableMethod.equals(methodName)) { isAutoCommitable = true; break; } } return isAutoCommitable; } }

We configure a bean to be a Proxy for JdbcTemplate. Since we are proxying a class (not an interface) we need to have the CGLIB JAR in our classpath. A reference to this proxy can then be passed into the beans wherever the JdbcTemplate reference was being passed in. Here is the Spring configuration.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

<!-- The original JdbcTemplate definition --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource" /> </bean> <!-- Definition for the autocommit version of JdbcTemplate --> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate"> <property name="transactionManager" ref="transactionManager" /> <property name="propagationBehaviorName" value="PROPAGATION_REQUIRED" /> </bean> <bean id="autocommitInterceptor" class="com.mycompany.interceptors.AutocommitInterceptor"> <property name="autoCommitableMethods"> <list> <value>update</value> </list> </property> <property name="transactionTemplate" ref="transactionTemplate" /> </bean> <bean id="autoCommittingJdbcTemplate" class="org.springframework.aop.framework.ProxyFactoryBean"> <property name="target" ref="jdbcTemplate" /> <property name="proxyTargetClass" value="true" /> <property name="interceptorNames"> <list><value>autocommitInterceptor</value></list> </property> </bean>

So there you have it. With just a single additional interceptor class, and a few lines of configuration, all the update() calls from JdbcTemplate will be transactional, thereby fixing the problem I was seeing. Database purists may argue that this approach is too simple minded. I agree that splitting the application into two distinct transaction blocks may be a much better idea in terms of performance, and I may even end up using that approach, but for a lot of cases, the autoCommit behavior that is the JDBC default is quite acceptable.

Performance Tuning Thoughts

I recently attended the MySQL Users Conference at Santa Clara, CA. One of the tutorials I signed up for was Mark Matthew's talk on J2EE Performance Tuning. Of course, this was a MySQL conference, and the speaker happened to be the author of the original JDBC driver for MySQL, so understandably there was a lot of emphasis on the new performance monitoring aspects of MySQL/Connector-J version 5, the upcoming JDBC driver for MySQL version 5.x databases.

But the nice part of the talk was that it got me thinking again about how to monitor and address performance related issues in a holistic manner. In previous lives, I have been a developer and part time system administrator for console-based Unix systems, and more recently, an Informix DBA. In both these times, I have had to address performance issues, and I have done so in a non-holistic (for want of a better word) manner. For example, my first reaction to a performance issue as an Informix DBA would have been to check the database read-write statistics, looking for hot spots, and trying to address problems by splitting the reads and writes on different disks. The next place I would look for is to check cardinality of data in the tables, looking for missing indexes. Since Informix, to the best of my knowledge, did not log slow queries, analyzing the queries meant that I would have to scan the entire codebase looking for them, so that was something I would do after the other approaches did not deliver the required functionality.

As a developer in a J2EE environment, I still have to address performance issues, but the focus is developer oriented. Typically, I measure wall-clock times of various methods and find methods which take the longest times and see if there is SQL or code that can be optimized. I measure front end responses with the Apache Bench tool, which allows you to set the number of clients, and the number of requests each client will make, and returns (among other things), the requests per second the page could serve, and the average, minimum and maximum processing times. Although MySQL logs slow queries in the slow query log, it gets used only when I am reacting to a performance problem, not when I am being proactive about ensuring my code is performant, because getting the slow query log needs DBA involvement (on MySQL version 4.1). The important thing to note in all these cases is that the performance measurements are developer-centric.

Occasionally, when reacting to performance problems, I would also look at application server (Resin) thread dumps, and try to find and fix code bottlenecks by tracing the dump back to the offending code. Although we don't run Resin with the stock JVM settings (these settings are determined by another group, based on the machine capacity on which Resin will be running), the only JVM settings I have ever actually changed myself are the minimum and maximum heap sizes.

What the talk did for me was to highlight that a J2EE application is really a layered cake of potentially non-performant hardware and software. At the very bottom there is the CPU and the RAM, followed by the operating system, followed by the database, followed by the application server, followed by the application code. The operating system, the database and the application server could potentially be non-performant because they have been improperly tuned for the application.

Fortunately, however, one does not have to start from scratch when trying to optimize for performance. It really boils down to choosing the right sized components as a starting point. Based on the projected demands on your application, you can usually choose the appropriate number and type of CPUs, and the amount of memory on your system, and the size of your swap space (among other things) on a Linux (Unix) based operating system. Databases generally offer configuration profiles (for example, the tiny, small, large and huge memory models of MySQL) to suit the particular application and hardware. Java based application servers allow you to tune the starting and maximum heap sizes, the type of garbage collector you want to use, and the generation sizes within your heap to optimize garbage collection. So really, the starting point of delivering optimal performance is to set up the optimal capacity.

Still, a person who needs to diagnose and fix a problem with a J2EE application will need to be familiar with and be able to tweak all these subsystems. Because performance metrics are heavily application dependent, this person will also need to be familiar with the application itself. Finding such a person in an organization of even moderate size is next to impossible. Bringing together groups of people to do a performance audit or diagnose and fix performance issues is a possibility, but since fixing a performance problem involves an iterative cycle of observation, tweak and more observation, this is often a time consuming operation, and often not acceptable to a business, which is losing money every minute with the non-performing application.

There was a time when I would sneer at the practice of "throwing more hardware" at a problem to fix performance issues, but the more I think about the expenses in continuing to operate a non-performant application, and the logistics to try to fix this in the time provided, the more I lean towards this option as the simplest and most cost effective way to deliver performance. By that, I do not imply the sloppy and non-performant code is ok. If there are indexes that need to be applied to the database, or the SQL needs to be re-written, or the components appropriately sized, then these should by done first. However, if your application is serving 1000 pages per second, and it starts keeling over when it is required to serve 2000 pages per second, it is possible that a few days of performance tuning will allow you to scale to the new level, perhaps more. But if it did, then it is more than possible that your application was not performant to begin with, and that should have been addressed before the application was deployed.

What I mean by "throwing more hardware" is the ability to scale out using clustering technologies. Putting the application behind a webserver and setting up reverse proxies to multiple underlying application servers, all running the same application, is one way. While each application server will contain the exact same copy of the application, they will be serving different slices of the application. The slicing will be set up in the reverse proxy configuration of the webserver. Alternatively, each application server serves the full application, but through multiple webservers behind a hardware load balancer. A hybrid of these two approaches is also a possibility.

On the database side, the scale out can be achieved by clustering multiple database masters (the read-any, write-all approach) or database replicants (one master, multiple slaves). I personally prefer the multiple master clustering scenario, since the application does not need to be changed at all to accomodate the change. The application thinks it is talking to a single database. On the other hand, in a replicated setup, you will have to have separate configurations to read from the slaves and to read and write from and to the master. There is also a replication latency which you will have to account for if your application has a scenario where it writes and reads back within a very short time. Most databases (including MySQL) offer the ability to do replication. C-JDBC is an open source initiative to achieve multiple master clustering, but its performance leaves a lot to be desired. I am told that m/cluster from Continuent, a commercial offering based on C-JDBC is much better in that regard.

Another important component is the application cache. Most J2EE applications that serve dynamic (ie generated from a database) and have significant traffic tend to use caching of some kind. When we attempt to serve the traffic with multiple machines, the cache has to be shared between the application server JVMs. There are a variety of distributed caches available in the market. The best known of these is Coherence from Tangosol, but there are others, such as JBoss Cache and SwarmCache. These caches either replicate or distribute - replicated caches replicate the cached contents to all the caches in the cluster, while distributed caches store it in only one place, but are able to pull it from the right place when they are requested to.

I still think that performance reviews of application code and load testing on hardware comparable to actual production hardware have lots of value, but neither of these approaches are simple to set up. The new MySQL JDBC driver provides a lot more performance metrics (including a "local" list of slow queries), so a disciplined J2EE developer using MySQL will find this very helpful in finding and fixing code and SQL performance bottlenecks before pushing the code out of development.

So I think, my personal performance tuning mantra boils down to these two simple commandments:

• Find and fix bottlenecks in SQL and code in development.
• Simulate load using the Apache Bench tool.
• Design for clustering.