Обсуждение: performance bottlenecks on lock transactionid
hello,
We used benchmarksql 4.1.0 to test the performance of PG12 beta TPCC.
We found performance bottlenecks on lock transactionid.
help,
We hope to find a way to solve this bottleneck in order to improve performance.
Here are some of our configurations. Including operating system, PG database, and benchmark SQL：
database：
pgsql 12 beta2
Lock queues at runtime and top in the attachment。
operating system：
uname -a
Linux localhost.localdomain 4.20.0 #1 SMP Mon Mar 11 23:13:55 EDT 2019 x86_64 x86_64 x86_64 GNU/Linux
$ cat /etc/issue
\S
Kernel \r on an \m
$ cat /proc/version
Linux version 4.20.0 (root@localhost.localdomain) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-28) (GCC)) #1 SMP Mon Mar 11 23:13:55 EDT 2019
$ lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 112
On-line CPU(s) list: 0-111
Thread(s) per core: 2
Core(s) per socket: 28
Socket(s): 2
NUMA node(s): 2
Vendor ID: GenuineIntel
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) Platinum 8280L CPU @ 2.60GHz
Stepping: 5
CPU MHz: 1000.006
CPU max MHz: 3900.0000
CPU min MHz: 1000.0000
BogoMIPS: 5200.00
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 1024K
L3 cache: 39424K
NUMA node0 CPU(s): 0-27,56-83
NUMA node1 CPU(s): 28-55,84-111
Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc art arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc cpuid aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm 3dnowprefetch cpuid_fault epb cat_l3 cdp_l3 invpcid_single intel_ppin ssbd mba ibrs ibpb stibp tpr_shadow vnmi flexpriority ept vpid ept_ad fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm cqm mpx rdt_a avx512f avx512dq rdseed adx smap clflushopt clwb intel_pt avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 xsaves cqm_llc cqm_occup_llc cqm_mbm_total cqm_mbm_local dtherm ida arat pln pts hwp hwp_act_window hwp_epp hwp_pkg_req pku ospke flush_l1d arch_capabilities
benchmarksql：
version：4.1.0
result：
driver=org.postgresql.Driver
conn=jdbc:postgresql://localhost:5432/postgres
user=SYSTEM
warehouses=100
terminals=300
runMins=30
limitTxnsPerMin=0
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
Measured tpmC (NewOrders) = 543598.69
Measured tpmTOTAL = 1205694.86
Session Start = 2019-07-31 01:27:22
Session End = 2019-07-31 01:57:23
Transaction Count = 36191623
Вложения
王若楠 wrote: > We used benchmarksql 4.1.0 to test the performance of PG12 beta TPCC. > We found performance bottlenecks on lock transactionid. You included an attachment with results from the "pg_locks" view where "granted" is FALSE for all entries. I'll assume that these are not *all* the entries in the view, right? Since the locks are waiting for different transaction IDs, I'd assume that this is just a case of contention: many transactions are trying to modify the same rows concurrently. This is to be expected. Perhaps your benchmark is running with too many connections on too few table rows? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
hello,Laurenz Albe
Yes, pg_locks is only an item that does not get a lock in the view. The test data is 300 warehouses connections, and the CPU is only about 60%. I think the lock becomes a performance bottleneck at this time. I want to find a way to reduce the lock waiting and improve the performance.
------------------ 原始邮件 ------------------
王若楠 wrote:
> We used benchmarksql 4.1.0 to test the performance of PG12 beta TPCC.
> We found performance bottlenecks on lock transactionid.
You included an attachment with results from the "pg_locks" view
where "granted" is FALSE for all entries.
I'll assume that these are not *all* the entries in the view, right?
Since the locks are waiting for different transaction IDs, I'd
assume that this is just a case of contention: many transactions are
trying to modify the same rows concurrently.
This is to be expected.
Perhaps your benchmark is running with too many connections on
too few table rows?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
王若楠 wrote: > I want to find a way to reduce the lock waiting and improve the > performance. You either have to make the transactions shorter, or you let the different clients modify different rows, so that they don't lock each other. That concurrent writers on the same data lock each other is unavoidable, and all database management systems I know do it the same way. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com