HammerDB v4.0 New Features Pt4: Connect Pooling for Clusters

Prior to HammerDB v4.0 for the TPROC-C test there was the option to connect to one database instance only. If it was required to connect to multiple instances in a cluster then the Primary/Replica modes were used to create multiple HammerDB instances to connect to the separate database instances simultaneously. HammerDB has introduced a connect pool feature whereby a single instance of HammerDB can create a pool of multiple database instance connections with policies defined at the stored procedure level to determine how the individual stored procedures are run on which connections to the database instances. For example in an environment where there are primary read-write instances and secondary read-only it would be possible to define a policy whereby the neworder, payment and delivery stored procedures run against the read-write instance and stocklevel and orderstatus run against the read-only instance. Where there are multiple instances serving a similar purpose the policy can determine how an individual transaction is assigned. For example if there are three read write-instances then the neworder stored procedure can be defined to execute a transaction at each in a round-robin fashion or instead select an instance at random.

Connect Pool

To define the connect pool there are new XML files in the config/connectpool directory. These provide a template for the multiple connections with the same connection options for the standard interface defined in the connections section. The connections are named c, c2, c3 and so on with no limit on the number of connections that you define. There is also an sprocs section where you define what connections each stored procedure should use and what policy to apply, the policy can be  first_named, last_named, random or round_robin.


When you have defined your configuration, select the XML Connect Pool option when loading the driver script. Your active Virtual Users will then use your XML defined connections and connect to each defined one thereby holding a pool of connections open to distribute the transactions to. For all databases the connect pool connections use prepared statements and once the connection is established will prepare statements for all of the stored procedures against each connection.

PostgreSQL XML Connect Pool

Within the driver script there is a commented line that can be uncommented to report details on all of the connections and prepared statements that are made.

postgresql connection information

Finally it is important to note that the main monitor connection continues to connect to the standard defined connection and reports NOPM and TPM from that single instance. Where a clustered environment such as Oracle RAC reports performance data for the entire cluster this will report cluster performance. If however you have defined connections to separate unrelated instances then this monitor connection will only report out for the instance it is connected to. For this reason where a database will not report performance data across the cluster the XML connect pool driver script will also report client side transactions for each Virtual User and in total to provide a guide to the workload directed to each instance.

The connect pool can be used in conjunction with other features such as use all warehouses and asynchronous connections. For more information on configuring the XML connect pool see the relevant section in the HammerDB documentation.