The test data below is from a non-virtualized (client system and database server) Postgres 14 environment, with no replication, no high availability, and with no load balancing. This environment has older and slower disk drives, and the test is driven by a single client process.
In this case 24% of the round trips (client to database and back) are for commit processing. However, commit processing is consuming 89% of the total database time. (All times are measured from within the client.)
In this non-virtualized environment, on the exact same
hardware, other RBMSs have a much lower commit-time/total-database-time ratio.
In a virtualized environment (both client system and database server) are running in separate VMs with faster disks and with possibly many other active VMs this number drops to about 70% for Postgres.
We see similar results in Linux environments as well.
What is a good approach to identifying what is happening within the commit processing?
Are there any known bugs in this area?
Any other thoughts would be greatly appreciated.
Thank you.
-Tim
Line Freq Cum.t Max.t Avg.t Rows Err. Statement
1 2268 301.908 0.243 0.133 2235 0 COMMIT
2 755 9.665 0.102 0.013 2326 0 INSERT INTO POMQUERY_U ( col0 ) VALUES (:1)
3 266 0.195 0.103 0.001 263 0 SELECT t_01.puid FROM PITEM t_01 WHERE ( UPPER ( t_01.pitem_id ) = UPPER( :1 ) )
4 244 0.186 0.002 0.001 260 0 INSERT INTO POM_TIMESTAMP (puid, ptimestamp, pdbtimestamp, pdeleted) (SELECT :1, :2, now() ...
[...snip...]
Sum: 9264 338.200 - - 12050 -
Percent Commit 24% 89%
My latest run was similar, in that its total database time was 14876.691 seconds with total commit time of 13032.575 seconds, or 88% commit time.
Postgres Version: PostgreSQL 14.5, compiled by Visual C++
build 1914, 64-bit
OS Name: Microsoft Windows Server 2019
Standard
OS Version: 10.0.17763 N/A Build 17763
