LogMiner Tuning: _log_read_buffers & _log_read_buffer_size - averemee-si/oracdc GitHub Wiki
Redo Apply Best Practices, and Oracle GoldenGate Performance Best Practices mention _log_read_buffers & _log_read_buffer_size hidden parameters and suggest to increase them. We decided to check how these parameters affect speed of reading of archived redo logs using LogMiner.
Equipment
AWS EC2 m5d.large instance.
lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 2
On-line CPU(s) list: 0,1
Thread(s) per core: 2
Core(s) per socket: 1
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) Platinum 8259CL CPU @ 2.50GHz
Stepping: 7
CPU MHz: 3191.140
BogoMIPS: 4999.99
Hypervisor vendor: KVM
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 1024K
L3 cache: 36608K
NUMA node0 CPU(s): 0,1
Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc cpuid aperfmperf tsc_known_freq pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch invpcid_single pti fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid mpx avx512f avx512dq rdseed adx smap clflushopt clwb avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 xsaves ida arat pku ospke
Instance store characteristics:
hdparm -tT /dev/nvme2n1p1
/dev/nvme2n1p1:
Timing cached reads: 18902 MB in 1.99 seconds = 9503.74 MB/sec
Timing buffered disk reads: 648 MB in 3.00 seconds = 215.67 MB/sec
time dd if=/dev/nvme2n1p1 of=/dev/null ibs=1024k obs=1024k iflag=direct count=4096
4096+0 records in
4096+0 records out
4294967296 bytes (4.3 GB) copied, 30.0176 s, 143 MB/s
real 0m30.019s
user 0m0.454s
sys 0m0.150s
Oracle Database
19c EE, with datafiles, redo logs, and archive log files on instance store sga_target = 2G
Redo read
Testing was performed using already prepared set of archived redo logs with total size of 10 GiB.
- Horizontally in bold - value of _log_read_buffer_size
- Vertically in italic- value of _log_read_buffers
- Cell contains redo read rate in MiB/s from oracdc JMX metrics
- Default value for both _log_read_buffers & _log_read_buffer_size - 8
| 8 | 16 | 32 | 64 | 128 | 256 | |
|---|---|---|---|---|---|---|
| 8 | 31.745 | 31.981 | 31.863 | 31.707 | 31.821 | 31.935 |
| 16 | 31.935 | 31.967 | 32.085 | 32.085 | 32.051 | 31.707 |
| 32 | 31.811 | 31.967 | 31.967 | 31.967 | 32.085 | 31.925 |
| 64 | 31.365 | 31.733 | 31.617 | 31.274 | 30.939 | 30.720 |
| 128 | 30.931 | 30.611 | 30.611 | 30.829 | 31.670 | 30.943 |
| 256 | 29.599 | 29.599 | 29.599 | 29.680 | 29.580 | 29.580 |
Conclusion
- No significant improvement or degradation of redo read performance with various settings of _log_read_buffers & _log_read_buffer_size parameters
- It seems to us that the best way to tune LogMiner performance is in overall IO tuning of hardware that attempts to improve performance using hidden parameters