9. Additional Driver Script Options for Stored Procedures and Server Side Reports: PostgreSQL, MySQL, MariaDB, Oracle, Db2 and EnterpriseDB PostgreSQL

9.1. PostgreSQL Stored Procedures

With PostgreSQL by default the 5 TPROC-C transactions are implemented using PostgreSQL functions. From PostgreSQL v11.0 there is the option to use PostgreSQL stored procedures instead. However prepared statements are not supported by PostgreSQL for stored procedures only for functions and therefore if using the XML connect pool feature only PostgreSQL functions are supported. Conversely PgBouncer does not at the time of writing keep track of prepared statements and therefore when using event driven scaling, stored procedures are recommended.

9.2. MySQL Prepare Statements

With MySQL there is the option to use server side prepared statements. This option is mandatory if using the XML connect pool feature.

9.3. MariaDB Prepare Statements

With MariaDB there is the option to use server side prepared statements. This option is mandatory if using the XML connect pool feature.

9.4. No Stored Procedures Option for MySQL and MariaDB

From version 4.9 for MySQL and MariaDB there is an advanced option of No Stored Procedures. When selected this option will drive the TPROC-C workload entirely from the client, running the same SQL statements with the application logic within HammerDB. This option is useful when looking to run a workload with an increased network load and in tests increases the Bytes received and Bytes sent by 6X and 8X respectively. This option should not be expected to deliver NOPM/TPM values as high as using stored procedures due to the additional time spent at the network.

Figure 4.41. No Stored Procedures Option

No Stored Procedures Option


9.5. Oracle AWR Reports

The Generation of Oracle AWR reports is built-in functionality with the Oracle Timed Test. At the end of the test HammerDB will report the snapshot numbers between which the report corresponds to the test.

9.6. Db2 MONREPORT

In the Db2 driver script options the Minutes for Test Duration is shown as monreportinterval in the Driver Script. This defines the period of time taken from the minutes for test duration that the monitoring user runs a monreport capture. The results are output at the end of the test and therefore selecting this option should be done in conjunction with the logfile enabled. While the MONREPORT is being captured the monitoring virtual user cannot bet terminated as control is handed over to the DB2 database and therefore shorter periods of report are optimal. In all cases in the MONREPORT interval specified is longer than the minutes for test duration then no MONREPORT will be captured.

9.7. EnterpriseDB PostgreSQL DRITA

If you have Enterprise DB installed and DRITA functionality enabled, by selecting this option HammerDB will automatically take DRITA snapshots for performance analysis of the workload between tests. For DRITA functionality to work you need the parameter timed_statistics = on set in your postgresql.conf file. With the test complete and the values you recorded if you selected the DRITA option you should next generate the DRITA report that corresponds to the reported SNAPIDs to show the PostgreSQL wait events, in the example below snapshots 2 and 3.

edb=# select * from sys_rpt(2,3,1000);
                                   sys_rpt                                   
-----------------------------------------------------------------------------
 WAIT NAME                                COUNT      WAIT TIME       % WAIT
 ---------------------------------------------------------------------------
 wal insert lock acquire                  1054357    2.300713        88.25
 xid gen lock acquire                     83471      0.195263        7.49
 db file read                             5523       0.067953        2.61
 buffer free list lock acquire            11133      0.029317        1.12
 query plan                               205        0.013703        0.53
 freespace lock acquire                   3          0.000007        0.00
 rel cache init lock acquire              0          0.000000        0.00
(9 rows)

edb=#