Example of Q Replication overhead for replicating a DB2 OLTP workload

Here is an example of a 'resource footprint' added to a system when replicating a DB2 workload. We will be replicating the workload between two separate systems using Q Replication.

In this example, we took a common OLTP workload simulation which generates 'Order Entry' transactions. The transactions consist of entering orders, checking stock levels, marking orders as complete. While such workloads do not always translate well to real-life situations, they work just fine for generating a replication workload. We measured the performance of the workload, and the resources used on the system. We then added an second system with identical configuration of hardware and software, and replicated the workload in real-time from the primary system to the second.

The specifications of the two machines are as follows:

Power 5, 8 CPU @2.2GB
64 GB memory
DS4800 Disk Array Router, 22 Disk RAID for DB2 tablespaces
DB2 v9.7 Fixpak 3a

The characteristics of the workload are:

~9 different tables, 5 different transactions.
Transactions contain a mixture of operations across multiple tables.
Ratio of I/U/D is 2:5:1
Average 18 rows/trans, 2400 bytes/trans.

The first task was to measure the performance of the workload tool with no replication enabled. Archive logging was enabled during the test run. We ran the workload for 1 hour, and gathered the stats using nmon.

Source stats without replication

Workload (transactions per second) Max, Min, Avg 898, 200, 630 tps
CPU usage at source Avg, Max, Physical Avg 52.3%, 68.6%, 2.1
CPU breakdown by process (Avg) db2sysc (DB2) 35.7%
db2fmp (workload) 12.3%
Memory breakdown (Avg) db2fmp (workload) 160MB
db2sysc (DB2) 75MB
I/O read, written, r/w ratio 54,457MB, 173,764MB, .3

We then added a second database, and set up Q Replication for the 5 tables between the two databases. When applying the data, all transactions are replayed in the same manner they are created at the source - so no batching of transactions, or splitting rows up between transactions to optimize throughput. In fact, no tuning of Q Replication was required at all - only default parameters were used.

Source stats with replication

Workload (transactions per second) Max, Min, Avg 772, 428, 599 tps
CPU usage at source Avg, Max, Physical Avg 64.9%, 77.4%, 2.6
CPU breakdown by process (Avg) db2sysc (DB2) 36.9%
db2fmp (workload) 11.6%
asnqcap (Q Capture) 6.3%
amqzlaa (mq channel) 2.0%
Memory breakdown (Avg) db2fmp (workload) 160MB
db2sysc (DB2) 82MB
asnqcap (Q Capture) 82MB
I/O read, written, r/w ratio 163,253MB, 276,090MB, .6

So the kind of conclusions you can draw for the footprint of adding replication to a source system:

• Workload average throughput was reduced from 630 to 599 transactions per second (~5%)
• Overall CPU usage increased from 52% to 65% (due to Q Capture and MQ processes)
• Q Capture and MQ specifically added 8-9% of pure replication overhead
• DB2 CPU usage increased from 35 to 37% (overhead of db2ReadLog)
• Memory requirements increased by 82MB
• I/O read increased from 55 to 163 GB (reading the log, MQ)
• I/O write increased from 174 to 276GB (publishing changes to MQ)

But while we were capturing changes on the source system, these were also being replicated and applied at the same time to the target system.

Target stats with replication

Workload (transactions per second) Max, Min, Avg 41.2%, 62.7%, 1.6
CPU breakdown by process (Avg)  db2sysc (DB2) 23%
asnqapp (Q Apply) 12%
anqlzaa (mq channel) 4%
Memory breakdown (Avg) asnqapp (Q Apply) 54MB
db2sysc (DB2) 49MB
I/O read, written, r/w ratio 6,370MB, 94,484MB, .1

Average rows/s, trans/s 22175, 1228
Total rows in workload, trans 81161620 , 4494798
Average end2end, capture, queue, apply latency 2.7s, 2.0s, 0.5s, 0.2s

And the conclusions you can draw for the load on a secondary system where the workload is replicated to:

• DB2 CPU driven by the workload is lower at the secondary system than the primary - 23% vs 36%
• Overall system CPU utilization is lower at the secondary system than the primary - 41% vs 52%
• Q Apply and MQ CPU usage is 16%.
• I/O reads are significantly lower on the secondary system, 6 GB vs 54 GB
• I/O writes are lower also, 95 GB vs 174 GB.
• Average latency for the entire replicated workload was less than 3 seconds, @22k rows/s.

Conclusions

As you can see from these results, you can replicate a workload that is fairly I/O intensive for only a small (~10%) additional overhead at the source database. Also, the resources required to replay/apply the same workload in a transactionally consistent manner at the target are fewer than required to generate the workload at the source. Naturally your mileage will vary according to the type of workload being replicated.