Sun just post SpecjAppserver2004 with a very excellent result of 712.87 SPECjAppserver 2004 JOPS@Standard using the latest SJSAS 9.0 Platform Edition on Sun Fire X4100 Cluster with MySQL 5.0.20 yesterday(Also check out Tom Daly's blog, Scott Oak's blog and Robert Lee's blog for more information)

SPECjAppserver is industry standard benchmark for performance testing J2EE application server. As part of important component of SPECjAppserver testing, database's performance and scalability also greatly affect the overall performance result in the SPECjAppserver test.

On the database side, the MySQL's performance in the SpecjAppserver test was significantly impacted by the feature of server-side prepared Statement added to MySQL 4.1 and above. According to MySQL's article on Prepared Statement, from the performance perspective, the advantages of using server-side prepared statement are:

1. It parses the query only single time, so that for the following same queries(with different parameters), it save the CPU resource to directly execute the queries without parsing.

2. It uses the new binary protocol to reduce the CPU usage on converting everything into strings before sending them across network.

However, the disadvantage of using server-side prepared statement is that there is two round-trips to the server for the prepared statements in order to gain the security benefits of prepared statement, so that it could impact performance in some workloads with simple queries executed few times. In the SPECjAppserver test, with the help from MySQL engineers(Peter Zaitsev and Mark Mathew,), we tried with the correct URL settings for Connector/J 3.1.13 to disable the server-side prepared statement and enable caching prepared statement in domain.xml as bellow:

<property name="cachePreparedStatements" value="true" />
<property name="useServerPreparedStmts" value="false" />

After these changes, we solved the CPU bottleneck on the database system by reducing the CPU utilization from 100% to 57% in the same load(Dealer Injection Rate), so that we could get much better performance result by further utilized the free CPU resource after increasing the load.

Besides the Connector/J's setting, there were a few key MySQL server's configuration and tunning in the SPECjAppserver

1. MySQL Innodb buffer and maximum dirty page percentage size
According to Peter Zaitsev, there was a MySQL's bug in the Innodb's fuzzy Checkpointing implementation. This caused a sharp I/O spike we observed in the SPECjAppserver test after increasing the Dealer Injection Rate, which slowed down the response time of the Manufacturing transaction in the test. Fortunately, we could solve this problem by tuning the "innodb_pool_buffer_size" and "innodb_max_dirty_pages_pct" accordingly, so that the checkpoint could write more blocks to the disk in average, and flush less blocks at the point when the log file was full.

2. MySQL query cache
MySQL 4.0 and later version also has a nice feature called query cache that stores the identical SELECT queries issued by clients to the database server. This makes it possible to locate and re-issue the same queries without repetitive hard parsing activities. MySQL also stores the query's result set in the query cache, which can reduce the overhead of creating complex result sets for queries from the disk or memory caches, reducing both physical and logical I/O. However,in the SPECjAppserver test, we observed the qcache_hit as 0 at runtime, which indicated none query was serviced from the query cache. In such case, we completely turn off the query cache by setting query_cache_type as 0 to save the CPU as well as Memory resource on the query cache.