Speed up transaction completion faster after many relations are accessed in a transaction

From: "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>
To: "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Speed up transaction completion faster after many relations are accessed in a transaction
Date: 2019-02-12 06:33:00
Message-ID: 0A3221C70F24FB45833433255569204D1FB976EF@G01JPEXMBYT05
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


The attached patch speeds up transaction completion when any prior transaction accessed many relations in the same session.

The transaction records its own acquired lock information in the LOCALLOCK structure (a pair of lock object and lock mode). It stores LOCALLOCKs in a hash table in its local memory. The hash table automatically expands when the transaction acquires many relations. The hash table doesn't shrink. When the transaction commits or aborts, it scans the hash table to find LOCALLOCKs to release locks.

The problem is that once some transaction accesses many relations, even subsequent transactions in the same session that only access a few relations take unreasonably long time to complete, because it has to scan the expanded hash table.

The attached patch links LOCALLOCKS to PGPROC, so that releasing locks should only scan the list instead of the hash table. The hash table is kept because some functions want to find a particular LOCALLOCK quickly based on its hash value.

This problem was uncovered while evaluating partitioning performance. When the application PREPAREs a statement once and then EXECUTE-COMMIT repeatedly, the server creates a generic plan on the 6th EXECUTE. Unfortunately, creation of the generic plan of UPDATE/DELETE currently accesses all partitions of the target table (this itself needs improvement), expanding the LOCALLOCK hash table. As a result, 7th and later EXECUTEs get slower.

Imai-san confirmed performance improvement with this patch:


Takayuki Tsunakawa

Attachment Content-Type Size
faster-locallock-scan.patch application/octet-stream 6.7 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2019-02-12 06:40:07 Re: restrict pg_stat_ssl to superuser?
Previous Message Michael Paquier 2019-02-12 06:24:31 Re: First-draft release notes for next week's releases