Db2

Hello Roberto,

there is no "standard commit time" for reference. It all depends on your workload and your setup for transactional logging and the disk with the transaction logs.

What has to be done at commit internally when an application is executing this statement? Well, without HADR Db2 has at least to flush the logbuffer to the transaction logs. This might take some time, depending on the size of the logbuffer and the disks your transaction logs are on. Best practice is to have your fastest disk available for the logs and use them only for the logs. So no data or index containers on the same device. Some customers use NVMe SSD's for the logs.

You can monitor some commit time details via e.g. MON_GET_CONNECTION 

https://www.ibm.com/docs/en/db2/12.1.0?topic=routines-mon-get-connection-get-connection-metrics

Many years ago there was a config value of MIN_COMMIT, but it was removed/hidden because it caused so much troubles at unknowing customers having not a constant flood of many transactions per second. But set your logging time straight first. 

Cheers
Roland

Hello Roberto,

additionaly to Rolands comment you may want to use Db2 Monitoring Functions  MON_GET_TRANSACTION_LOG , MON_GET_UNIT_OF_WORK and MON_GET_ACTIVITY which give you more details on Log Disk Write time or Log Disk Wait time - see https://www.ibm.com/docs/en/db2/11.5.x?topic=mmr-mon-get-transaction-log-table-function-get-log-information,https://www.ibm.com/docs/en/db2/11.5.x?topic=mmr-mon-get-unit-work-get-unit-work-metrics and https://www.ibm.com/docs/en/db2/11.5.x?topic=routines-mon-get-activity-return-list-activities.

What size has the log buffer (DB CFG LOGBUFSZ)?

Joachim

Hello, thanks for the answers provided. In this case MIN_COMMIT could be helpful but as Roland write, without a constant flood of many transactions per second it cause a performance problem.

@Joachim, LOGBUFSZ is configured to 4096. I will try to reduce to 2048 and test how many time takes the COMMIT process.

Thanks. Ciao. Roberto.

Hi Roberto,

where do you store your online transaction logs ? do you use an EBS volumes or are you using local volume ? evry commit sync the io to the disk so often the pain point is io/s.

have you tried to run db2mon ?

Malek,

Roberto

As other said,  most probably, your disk is not handling the commit rate.  it's a common issue if you don't request a fast tier storage for your logs.  

As @Roland Schock said: "There is no standard commit time" ,  but that does not mean you can't compare you values from other systems 

Here are my values, so you can use for comparison. It's a big high IO intense system.  
It makes 580k transactions/minute   in average, constantly.  Small variation on this rate..  

and the Avg LOG_Write_TIME  taken from mon_get_transaction_log() is less then 0.3ms  

select LOG_WRITES, LOG_WRITE_TIME,   (dec(log_write_time, 20,5) / log_writes) avg_write_time  from table(mon_get_transaction_log(-1)) as t

LOG_WRITES  LOG_WRITE_TIME AVG_WRITE_TIME
----------- -------------- --------------
 1613067875      413812979          0.256

Yes,  We had to ask our host Vendor to supply their FASTEST disk, to save our active transaction logs. 
In other word, we have a small but FAST disk , just to hold our active transaction logs area.  Normally, you don't need that expensive disk for your entire db. 

Hi Roberto,

how do the developpers  make the update, one by one or they are using Batch Updates (https://jenkov.com/tutorials/jdbc/batchupdate.html). 

if update is done one by one, maybe the probleme is netwrok latency (RTT ), java need to wait for network ACK before sending the next update.

Hello,

thanks to all for the suggestions provided. About the storage we are using, this is our configuration: EBS gp3 volume 15.000 IOPS 500 Throughput (MiB/s). A single xfs filesystem has been configured over the EBS gp3 volume and some subdirectory have been created where to store all the database objects (dbpath, storagepath, logpath, mirrorlogpath, failarchpath).

Hello Roberto,

as you mentioned that you use MIRRORLOGPATH:

Beside Kostas really important recommendation "For log mirroring you really should use a separate disk and file system" you may also want to check Db2 Registry Variable DB2_USE_ASYNC_FOR_MIRRORLOG - see https://www.ibm.com/docs/en/db2/12.1.0?topic=variables-performance#r0005665__P_db2_use_async_for_mirrorlog

By default writes to log files are serialized when using MIRRORLOGPATH - first write to active log then write to mirror log - DB2_USE_ASYNC_FOR_MIRRORLOG changes this behaviour so that both active and mirror log writes happen in parallel.

And you wrote "LOGBUFSZ is configured to 4096. I will try to reduce to 2048 and test how many time takes the COMMIT process.":

Reducing the size of the log buffer could have rather negative effects. AFAIK the log buffer is not always written in its complete size but partially - starting with the first entry of the commiting transaction up to the last entry of the commiting transaction (possibly entries of other transactions may have slipped in between).

Only when the log buffer becomes full its written completely - so you might want to check with MON_GET_TRANSACTION_LOG table function if you see "num_log_buffer_full" monitoring element grow fast https://www.ibm.com/docs/en/db2/12.1.0?topic=reference-n#r0011684 - if it does it might be helpful to increase LOGBUFSZ

Joachim

Hello,

thanks for the latest updates, we have changed our environment based on the latest suggestions provided: we have moved the database Path to log files, Mirror log path to two different disks. The new disks used for transaction logs are AWS io2 model 50000 IOPS (before we are using a single ESB gp3 disk to store the entire database structure). We have also setted up DB2_USE_ASYNC_FOR_MIRRORLOG=ON to write log and mirrorlog in parallel. The first tests done shows that the batch job save around 60 seconds execution time.

A second test was then performed by setting only DB2_USE_ASYNC_FOR_MIRRORLOG=ON in a clone environment leaving the database physical structure on a single AWS ESB gp3 storage. The results were still good, although not as good as the first. Evidently, changing active and mirror log writes to happen in parallel brings benefits.

The idea si to separate current log and mirrorlog in separate storage and filesystems, AWS io2 storage has a high cost so we have to evaluate carefully which storage model to use.

Thanks. Ciao. Roberto