Dear Tomas,
> I did some micro-benchmarking today, trying to identify cases where this
> would cause unexpected problems, either due to having to maintain all
> the relfilenodes, or due to having to do hash lookups for every sequence
> change. But I think it's fine, mostly ...
>
I did also performance tests (especially case 3). First of all, there are some
variants from yours.
1. patch 0002 was reverted because it has an issue. So this test checks whether
refactoring around ReorderBufferSequenceIsTransactional seems really needed.
2. per comments from Amit, I also measured the abort case. In this case, the
alter_sequence() is called but the transaction is aborted.
3. I measured with changing number of clients {8, 16, 32, 64, 128}. In any
cases,
clients executed 1000 transactions. The performance machine has 128 core so
that
result for 128 clients might be saturated.
4. a short sleep (0.1s) was added in alter_sequence(), especially between
"alter sequence" and nextval(). Because while testing, I found that the
transaction is too short to execute in parallel. I think it is reasonable
because ReorderBufferSequenceIsTransactional() might be worse when the
parallelism
is increased.
I attached one backend process via perf and executed
pg_slot_logical_get_changes().
Attached txt file shows which function occupied CPU time, especially from
pg_logical_slot_get_changes_guts() and ReorderBufferSequenceIsTransactional().
Here are my observations about them.
* In case of commit, as you said, SnapBuildCommitTxn() seems dominant for 8-64
clients case.
* For (commit, 128 clients) case, however, ReorderBufferRestoreChanges() waste
many times. I think this is because changes exceed logical_decoding_work_mem,
so we do not have to analyze anymore.
* In case of abort, CPU time used by ReorderBufferSequenceIsTransactional() is
linearly
longer. This means that we need to think some solution to avoid the overhead
by
ReorderBufferSequenceIsTransactional().
```
8 clients 3.73% occupied time
16 7.26%
32 15.82%
64 29.14%
128 46.27%
```
* In case of abort, I also checked CPU time used by
ReorderBufferAddRelFileLocator(), but
it seems not so depends on the number of clients.
```
8 clients 3.66% occupied time
16 6.94%
32 4.65%
64 5.39%
128 3.06%
```
As next step, I've planned to run the case which uses setval() function,
because it
generates more WALs than normal nextval();
How do you think?
Best Regards,
Hayato Kuroda
FUJITSU LIMITED
# commit case
## 128 clients
- 97.77% 0.00% postgres postgres [.]
pg_logical_slot_get_changes_guts
- 97.77% pg_logical_slot_get_changes_guts
- 97.77% LogicalDecodingProcessRecord
- 97.71% xact_decode
- 54.95% ReorderBufferProcessTXN
- 32.64% ReorderBufferRestoreChanges
+ 23.37% FileRead
4.58% __memcpy_ssse3_back
+ 4.14% MemoryContextAllocZero
+ 7.73% ReorderBufferCopySnap.isra.21
+ 5.94% ReorderBufferSerializeTXN
+ 5.78% ReorderBufferCleanupTXN
+ 2.04% RelationIdGetRelation
- 42.75% SnapBuildCommitTxn
- 34.56% ReorderBufferQueueChange
- 34.36% ReorderBufferSerializeTXN
27.64% __write_nocancel
4.42% __memcpy_ssse3_back
+ 1.52% OpenTransientFilePerm
+ 7.97% SnapBuildBuildSnapshot
0.04% 0.00% postgres postgres [.]
ReorderBufferSequenceIsTransactional
## 64 clients
- 86.49% 0.04% postgres postgres [.]
pg_logical_slot_get_changes_guts
- 86.45% pg_logical_slot_get_changes_guts
- 86.37% LogicalDecodingProcessRecord
- 84.79% xact_decode
- 51.05% SnapBuildCommitTxn
+ 49.77% SnapBuildBuildSnapshot
0.53% ReorderBufferXidHasBaseSnapshot
- 33.45% ReorderBufferProcessTXN
+ 21.37% ReorderBufferCopySnap.isra.21
+ 4.86% RelationIdGetRelation
+ 2.31% RelidByRelfilenumber
+ 0.84% AbortCurrentTransaction
0.81% ReorderBufferCleanupTXN
- 1.10% seq_decode
0.65% ReorderBufferSequenceIsTransactional
- 1.04% 0.09% postgres postgres [.]
ReorderBufferSequenceIsTransactional
## 32 clients
- 82.20% 0.13% postgres postgres [.]
pg_logical_slot_get_changes_guts
- 82.09% pg_logical_slot_get_changes_guts
- 81.86% LogicalDecodingProcessRecord
- 80.01% xact_decode
- 49.38% SnapBuildCommitTxn
+ 48.10% SnapBuildBuildSnapshot
0.50% ReorderBufferXidHasBaseSnapshot
- 29.98% ReorderBufferProcessTXN
+ 10.52% ReorderBufferCopySnap.isra.21
+ 7.64% RelationIdGetRelation
+ 4.11% RelidByRelfilenumber
+ 1.70% AbortCurrentTransaction
1.01% ReorderBufferCleanupTXN
0.92% LocalExecuteInvalidationMessage
0.87% commit_cb_wrapper
- 1.01% seq_decode
0.56% ReorderBufferSequenceIsTransactional
+ 0.85% 0.08% postgres postgres [.]
ReorderBufferSequenceIsTransactional
## 16 clients
- 78.30% 0.10% postgres postgres [.]
pg_logical_slot_get_changes_guts
- 78.24% pg_logical_slot_get_changes_guts
- 77.89% LogicalDecodingProcessRecord
- 75.11% xact_decode
- 38.84% SnapBuildCommitTxn
- 37.54% SnapBuildBuildSnapshot
+ 37.09% pg_qsort
0.56% ReorderBufferXidHasBaseSnapshot
- 35.34% ReorderBufferProcessTXN
+ 11.17% RelationIdGetRelation
+ 7.05% RelidByRelfilenumber
+ 6.33% ReorderBufferCopySnap.isra.21
+ 2.78% AbortCurrentTransaction
+ 1.27% commit_cb_wrapper
+ 1.22% LocalExecuteInvalidationMessage
0.93% ReorderBufferCleanupTXN
0.79% begin_cb_wrapper
0.74% hash_create
0.57% UpdateDecodingStats
+ 0.99% seq_decode
+ 0.74% ReorderBufferAddRelFileLocator
0.77% 0.08% postgres postgres [.] ReorderBufferSequenceIsTransactional
## 8 clients
- 75.95% 0.16% postgres postgres [.]
pg_logical_slot_get_changes_guts
- 75.78% pg_logical_slot_get_changes_guts
- 75.06% LogicalDecodingProcessRecord
- 71.67% xact_decode
- 40.29% ReorderBufferProcessTXN
+ 14.88% RelationIdGetRelation
+ 8.87% RelidByRelfilenumber
+ 3.36% AbortCurrentTransaction
+ 2.05% ReorderBufferCopySnap.isra.21
+ 1.71% LocalExecuteInvalidationMessage
+ 1.70% begin_cb_wrapper
+ 1.52% ReorderBufferCleanupTXN
+ 1.46% commit_cb_wrapper
1.10% hash_create
0.54% BeginInternalSubTransaction
+ 29.97% SnapBuildCommitTxn
1.07% seq_decode
+ 0.82% ReorderBufferAddRelFileLocator
0.71% 0.11% postgres postgres [.] ReorderBufferSequenceIsTransactional
# abort case
## 128 clients
- 64.15% 0.62% postgres postgres [.]
pg_logical_slot_get_changes_guts
- 63.71% pg_logical_slot_get_changes_guts
- 62.17% LogicalDecodingProcessRecord
- 46.95% seq_decode
- 23.71% ReorderBufferSequenceIsTransactional
+ 13.63% hash_search
8.64% hash_search_with_hash_value
- 22.34% ReorderBufferQueueSequence
+ 20.95% ReorderBufferSequenceIsTransactional
+ 0.53% AbortCurrentTransaction
+ 6.23% xact_decode
+ 3.06% ReorderBufferAddRelFileLocator
+ 2.25% heap2_decode
+ 1.65% heap_decode
+ 0.76% smgr_decode
1.27% XLogReadRecord
- 46.27% 4.60% postgres postgres [.]
ReorderBufferSequenceIsTransactional
- 42.57% ReorderBufferSequenceIsTransactional
+ 25.68% hash_search
15.98% hash_search_with_hash_value
+ 1.86% _start
+ 1.13% ServerLoop
+ 0.71% 0x34
## 64 clients
- 38.08% 1.45% postgres postgres [.]
pg_logical_slot_get_changes_guts
- 36.89% pg_logical_slot_get_changes_guts
- 34.41% LogicalDecodingProcessRecord
+ 9.88% xact_decode
- 6.35% seq_decode
+ 2.47% ReorderBufferQueueSequence
- 1.73% ReorderBufferSequenceIsTransactional
+ 0.91% hash_search
0.72% hash_search_with_hash_value
+ 0.85% ReorderBufferGetTupleBuf
0.62% ReorderBufferTXNByXid
- 5.39% ReorderBufferAddRelFileLocator
+ 5.14% hash_create
+ 4.91% heap2_decode
+ 3.26% heap_decode
+ 1.76% smgr_decode
1.09% ReorderBufferTXNByXid
+ 1.97% XLogReadRecord
+ 1.19% _start
- 29.14% 3.89% postgres postgres [.]
ReorderBufferSequenceIsTransactional
- 25.60% ReorderBufferSequenceIsTransactional
+ 14.29% hash_search
10.94% hash_search_with_hash_value
+ 2.89% _start
+ 0.60% 0x34
## 32 clients
- 51.65% 1.37% postgres postgres [.]
pg_logical_slot_get_changes_guts
- 50.81% pg_logical_slot_get_changes_guts
- 47.23% LogicalDecodingProcessRecord
- 19.72% seq_decode
+ 9.36% ReorderBufferSequenceIsTransactional
- 8.73% ReorderBufferQueueSequence
+ 6.20% ReorderBufferSequenceIsTransactional
+ 1.49% AbortCurrentTransaction
0.50% ReorderBufferGetTupleBuf
+ 10.70% xact_decode
+ 6.26% heap2_decode
+ 4.65% ReorderBufferAddRelFileLocator
2.05% heap_decode
+ 1.19% smgr_decode
+ 0.98% standby_decode
+ 2.54% XLogReadRecord
+ 0.84% _start
- 15.82% 1.76% postgres postgres [.]
ReorderBufferSequenceIsTransactional
- 14.05% ReorderBufferSequenceIsTransactional
+ 8.10% hash_search
5.95% hash_search_with_hash_value
+ 1.51% _start
## 16 clients
- 47.89% 0.62% postgres postgres [.]
pg_logical_slot_get_changes_guts
- 47.51% pg_logical_slot_get_changes_guts
- 44.85% LogicalDecodingProcessRecord
- 13.51% seq_decode
- 5.84% ReorderBufferQueueSequence
+ 3.84% ReorderBufferSequenceIsTransactional
+ 0.71% AbortCurrentTransaction
+ 2.88% ReorderBufferSequenceIsTransactional
+ 1.83% ReorderBufferGetTupleBuf
1.22% __memcpy_ssse3_back
+ 0.52% ReorderBufferTXNByXid
+ 11.06% xact_decode
+ 6.94% ReorderBufferAddRelFileLocator
+ 5.00% heap2_decode
+ 2.73% heap_decode
+ 2.58% standby_decode
+ 1.82% smgr_decode
+ 1.66% XLogReadRecord
- 7.26% 1.04% postgres postgres [.]
ReorderBufferSequenceIsTransactional
- 6.49% ReorderBufferSequenceIsTransactional
+ 3.95% hash_search
2.27% hash_search_with_hash_value
+ 0.50% _start
## 8 clients
- 36.33% 1.49% postgres postgres [.]
pg_logical_slot_get_changes_guts
- 35.35% pg_logical_slot_get_changes_guts
- 32.91% LogicalDecodingProcessRecord
- 8.31% seq_decode
- 4.54% ReorderBufferQueueSequence
+ 2.35% ReorderBufferSequenceIsTransactional
+ 0.99% AbortCurrentTransaction
+ 1.38% ReorderBufferSequenceIsTransactional
0.97% __memcpy_ssse3_back
0.95% ReorderBufferGetTupleBuf
+ 8.02% xact_decode
+ 5.63% heap2_decode
+ 3.66% ReorderBufferAddRelFileLocator
+ 3.53% standby_decode
+ 2.25% heap_decode
0.99% ReorderBufferTXNByXid
+ 1.22% CreateDecodingContext
+ 0.51% InvalidateSystemCachesExtended
+ 0.98% _start
- 3.73% 0.00% postgres postgres [.]
ReorderBufferSequenceIsTransactional
- ReorderBufferSequenceIsTransactional
2.37% hash_search_with_hash_value
+ 1.36% hash_search
