Hi,
I am seeking advice on optimizing a PostgreSQL query that is consuming a
significant amount of CPU resources on my Dell PowerEdge T340 server. The
server has an Intel Xeon E-2124 CPU @ 3.30GHz (4 cores, no Hyper-Threading)
and 16GB RAM, running OpenBSD 7.3 (GENERIC.MP) #1125.
The query in question occasionally utilizes around 40% of the CPU, with
CPU1 and CPU2 being more utilized compared to CPU0 and CPU3. This suggests
that the workload might not be well-balanced across all cores.
I am monitoring the system to ensure that CPU usage by PostgreSQL does not
lead to performance issues, especially if more queries are executed
simultaneously. Other processes (such as httpd, relayd, etc.) are consuming
minimal CPU time, indicating they are not contributing significantly to the
overall load.
The *top output* and *dmesg output* are included below. Given this setup,
could someone please provide guidance on the best practices for optimizing
PostgreSQL performance under OpenBSD? Are there specific kernel or
PostgreSQL configurations that might help distribute the load more evenly
across all CPU cores, or is this a case that requires a more powerful CPU?
Thank you in advance for your assistance.
Best regards,
Kihaguru
*top Output:*
load averages: 0.27, 0.07, 0.02
if.my.domain 14:11:3563 processes: 61 idle, 2 on processor
up 63 days 06:11:29CPU0
states: 3.0% user, 0.0% nice, 1.6% sys, 0.0% spin, 0.0% intr,
95.4% idleCPU1 states: 47.9% user, 0.0% nice, 6.2% sys, 0.6% spin,
0.0% intr, 45.3% idleCPU2 states: 33.0% user, 0.0% nice, 4.0% sys,
0.0% spin, 0.0% intr, 63.0% idleCPU3 states: 5.8% user, 0.0% nice,
0.6% sys, 0.0% spin, 0.0% intr, 93.6% idleMemory: Real: 199M/3048M
act/tot Free: 12G Cache: 1672M Swap: 0K/16G
PID USERNAME PRI NICE SIZE RES STATE WAIT TIME CPU
COMMAND77669 _postgre 58 0 169M 80M onproc/2 - 0:10
35.60% postgres77771 _relayd 2 0 5112K 8456K sleep/0 kqread
17:38 0.05% relayd16136 www 2 0 8272K 12M sleep/1
kqread 40:05 0.00% httpd15178 www 2 0 8140K 11M sleep/3
kqread 39:43 0.00% httpd41797 www 2 0 7892K 11M
sleep/3 kqread 39:40 0.00% httpd30439 _relayd 2 0 1572K
4176K sleep/2 kqread 36:41 0.00% relayd48133 _relayd 2 0
1564K 4148K sleep/1 kqread 35:55 0.00% relayd84898 _relayd 2
0 1572K 4164K sleep/1 kqread 35:34 0.00% relayd88687 _relayd
2 0 5076K 8440K sleep/2 kqread 17:29 0.00% relayd
1500 _relayd 2 0 5276K 8600K sleep/0 kqread 16:52 0.00%
relayd58290 www 2 0 1612K 2556K sleep/1 kqread 6:24
0.00% slowcgi86189 _pflogd 4 0 764K 1588K sleep/2 bpf
3:40 0.00% pflogd
5982 root 2 0 1120K 1584K idle kqread 2:58 0.00%
sshd45035 www 2 0 2388K 4532K sleep/2 kqread 1:21
0.00% httpd82363 _syslogd 2 0 1228K 1648K sleep/2 kqread
1:10 0.00% syslogd38604 _relayd 2 0 1624K 4040K sleep/2
kqread 1:08 0.00% relayd59324 _postgre 2 0 146M 7596K
sleep/1 kqread 1:02 0.00% postgres58070 _postgre 2 0 146M
15M sleep/3 kqread 1:01 0.00% postgres11204 _relayd 2 0
1468K 3816K sleep/1 kqread 0:29 0.00% relayd
1 root 10 0 644K 632K idle wait 0:22 0.00%
init68961 _postgre 2 0 146M 8984K sleep/2 kqread 0:13
0.00% postgres28371 _postgre 2 0 146M 61M idle kqread
0:10 0.00% postgres12040 _postgre 2 0 146M 5604K idle
kqread 0:07 0.00% postgres45123 _ntp 2 0 1060K 2748K idle
kqread 0:07 0.00% ntpd67766 root 2 0 888K 1516K
idle kqread 0:06 0.00% cron11185 root 2 -20 992K
1296K idle kqread 0:05 0.00% ntpd48084 kihaguru 28 0
2792K 4904K onproc/0 - 0:02 0.00% top33981 _postgre 2 0
146M 4908K idle kqread 0:01 0.00% postgres
9380 _ntp 2 -20 1132K 2876K idle kqread 0:01 0.00% ntpd
3091 kihaguru 2 0 1412K 3576K sleep/2 kqread 0:00 0.00%
sshd61657 root 2 0 3024K 5120K idle kqread 0:00
0.00% relayd44011 kihaguru 3 0 1764K 6332K idle ttyin
0:00 0.00% psql88332 www 2 0 2616K 2720K sleep/1 kqread
0:00 0.00% ShelveAssortItem57595 kihaguru 18 0 880K 904K idle
sigsusp 0:00 0.00% ksh64781 kihaguru 2 0 1412K 3572K idle
kqread 0:00 0.00% sshd
1477 root 2 0 1220K 4348K idle kqread 0:00 0.00%
sshd52393 _smtpq 2 0 1676K 3548K idle kqread 0:00
0.00% smtpd43301 _dhcp 2 0 868K 1760K idle kqread
0:00 0.00% dhcpleased
6184 _dhcp 2 0 856K 1780K idle kqread 0:00 0.00%
dhcpleased31249 root 2 0 728K 1480K idle netio 0:00
0.00% pflogd20367 root 2 0 752K 2492K idle netio
0:00 0.00% syslogd85654 _smtpd 2 0 2128K 4916K idle
kqread 0:00 0.00% smtpd30336 root 2 0 1728K 2296K idle
kqread 0:00 0.00% smtpd31500 _postgre 2 0 147M 14M idle
kqread 0:00 0.00% postgres58901 root 2 0 3640K 5068K
idle kqread 0:00 0.00% httpd54630 root 2 0 848K
1552K idle kqread 0:00 0.00% dhcpleased87873 root 2
0 828K 1500K idle kqread 0:00 0.00% slaacd72058 root 2
0 1204K 4312K idle kqread 0:00 0.00% sshd94363 _smtpd
2 0 1548K 3480K idle kqread 0:00 0.00% smtpd33820 root
3 0 508K 1524K idle ttyin 0:00 0.00% getty35003 _smtpd
2 0 1724K 3580K idle kqread 0:00 0.00% smtpd78288
_slaacd 2 0 848K 1720K idle kqread 0:00 0.00%
slaacd35269 root 2 0 532K 1224K idle kqread 0:00
0.00% resolvd
8090 _smtpd 2 0 1460K 3332K idle kqread 0:00 0.00%
smtpd96891 _slaacd 2 0 832K 1736K idle kqread 0:00
0.00% slaacd
1916 _sndio 2 -20 2660K 960K idle kqread 0:00 0.00%
sndiod10010 kihaguru 18 0 880K 904K idle sigsusp 0:00
0.00% ksh28465 _smtpd 2 0 1448K 3228K idle kqread 0:00
0.00% smtpd40558 root 3 0 508K 1520K idle ttyin
0:00 0.00% getty36854 root 3 0 504K 1504K idle ttyin
0:00 0.00% getty77907 root 3 0 512K 1528K idle ttyin
0:00 0.00% getty86780 root 3 0 508K 1516K idle
ttyin 0:00 0.00% getty86210 _sndiop 2 0 2652K 1056K idle
kqread 0:00 0.00% sndiod
*dmesg output:*
OpenBSD 7.3 (GENERIC.MP) #1125: Sat Mar 25 10:36:29 MDT 2023
dera...@amd64.openbsd.org:/usr/src/sys/arch/amd64/compile/GENERIC.MPreal
mem = 16850751488 (16070MB)avail mem = 16320638976 (15564MB)random:
good seed from bootblocksmpath0 at rootscsibus0 at mpath0: 256
targetsmainbus0 at rootbios0 at mainbus0: SMBIOS rev. 3.1 @ 0x78805000
(52 entries)bios0: vendor Dell Inc. version "2.7.1" date
10/12/2021bios0: Dell Inc. PowerEdge T340efi0 at bios0: UEFI 2.7efi0:
Dell Inc. rev 0x1070201acpi0 at bios0: ACPI 6.1acpi0: sleep states S0
S5acpi0: tables DSDT FACP SSDT SSDT SSDT SSDT TPM2 WD__ SLIC HPET APIC
MCFG SSDT LPIT WSMT SSDT DBGP DBG2 HEST BERT ERST EINJ DMARacpi0:
wakeup devices PEG0(S0) PEG1(S0) PEG2(S0) XHC_(S0) XDCI(S0) RP01(S0)
RP02(S0) RP03(S0) RP04(S0) RP05(S0) RP06(S0) RP07(S0) RP08(S0)
RP09(S0) RP10(S0) RP11(S0) [...]acpitimer0 at acpi0: 3579545 Hz, 24
bitsacpihpet0 at acpi0: 23999999 Hzacpimadt0 at acpi0 addr 0xfee00000:
PC-AT compatcpu0 at mainbus0: apid 0 (boot processor)cpu0: Intel(R)
Xeon(R) E-2124 CPU @ 3.30GHz, 3292.33 MHz, 06-9e-0acpu0:
FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CFLUSH,DS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE,SSE3,PCLMUL,DTES64,MWAIT,DS-CPL,VMX,SMX,EST,TM2,SSSE3,SDBG,FMA3,CX16,xTPR,PDCM,PCID,SSE4.1,SSE4.2,x2APIC,MOVBE,POPCNT,DEADLINE,AES,XSAVE,AVX,F16C,RDRAND,NXE,PAGE1GB,RDTSCP,LONG,LAHF,ABM,3DNOWP,PERF,ITSC,FSGSBASE,TSC_ADJUST,BMI1,HLE,AVX2,SMEP,BMI2,ERMS,INVPCID,RTM,MPX,RDSEED,ADX,SMAP,CLFLUSHOPT,PT,SRBDS_CTRL,MD_CLEAR,TSXFA,IBRS,IBPB,STIBP,L1DF,SSBD,SENSOR,ARAT,XSAVEOPT,XSAVEC,XGETBV1,XSAVES,MELTDOWNcpu0:
32KB 64b/line 8-way D-cache, 32KB 64b/line 8-way I-cache, 256KB
64b/line 4-way L2 cache, 8MB 64b/line 16-way L3 cachecpu0: smt 0, core
0, package 0mtrr: Pentium Pro MTRR support, 10 var ranges, 88 fixed
rangescpu0: apic clock running at 24MHzcpu0: mwait min=64, max=64,
C-substates=0.2.1.2.4.1.1.1, IBEcpu1 at mainbus0: apid 2 (application
processor)cpu1: Intel(R) Xeon(R) E-2124 CPU @ 3.30GHz, 3292.34 MHz,
06-9e-0acpu1:
FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CFLUSH,DS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE,SSE3,PCLMUL,DTES64,MWAIT,DS-CPL,VMX,SMX,EST,TM2,SSSE3,SDBG,FMA3,CX16,xTPR,PDCM,PCID,SSE4.1,SSE4.2,x2APIC,MOVBE,POPCNT,DEADLINE,AES,XSAVE,AVX,F16C,RDRAND,NXE,PAGE1GB,RDTSCP,LONG,LAHF,ABM,3DNOWP,PERF,ITSC,FSGSBASE,TSC_ADJUST,BMI1,HLE,AVX2,SMEP,BMI2,ERMS,INVPCID,RTM,MPX,RDSEED,ADX,SMAP,CLFLUSHOPT,PT,SRBDS_CTRL,MD_CLEAR,TSXFA,IBRS,IBPB,STIBP,L1DF,SSBD,SENSOR,ARAT,XSAVEOPT,XSAVEC,XGETBV1,XSAVES,MELTDOWNcpu1:
32KB 64b/line 8-way D-cache, 32KB 64b/line 8-way I-cache, 256KB
64b/line 4-way L2 cache, 8MB 64b/line 16-way L3 cachecpu1: smt 0, core
1, package 0cpu2 at mainbus0: apid 4 (application processor)cpu2:
Intel(R) Xeon(R) E-2124 CPU @ 3.30GHz, 3292.33 MHz, 06-9e-0acpu2:
FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CFLUSH,DS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE,SSE3,PCLMUL,DTES64,MWAIT,DS-CPL,VMX,SMX,EST,TM2,SSSE3,SDBG,FMA3,CX16,xTPR,PDCM,PCID,SSE4.1,SSE4.2,x2APIC,MOVBE,POPCNT,DEADLINE,AES,XSAVE,AVX,F16C,RDRAND,NXE,PAGE1GB,RDTSCP,LONG,LAHF,ABM,3DNOWP,PERF,ITSC,FSGSBASE,TSC_ADJUST,BMI1,HLE,AVX2,SMEP,BMI2,ERMS,INVPCID,RTM,MPX,RDSEED,ADX,SMAP,CLFLUSHOPT,PT,SRBDS_CTRL,MD_CLEAR,TSXFA,IBRS,IBPB,STIBP,L1DF,SSBD,SENSOR,ARAT,XSAVEOPT,XSAVEC,XGETBV1,XSAVES,MELTDOWNcpu2:
32KB 64b/line 8-way D-cache, 32KB 64b/line 8-way I-cache, 256KB
64b/line 4-way L2 cache, 8MB 64b/line 16-way L3 cachecpu2: smt 0, core
2, package 0cpu3 at mainbus0: apid 6 (application processor)cpu3:
Intel(R) Xeon(R) E-2124 CPU @ 3.30GHz, 3292.33 MHz, 06-9e-0acpu3:
FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CFLUSH,DS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE,SSE3,PCLMUL,DTES64,MWAIT,DS-CPL,VMX,SMX,EST,TM2,SSSE3,SDBG,FMA3,CX16,xTPR,PDCM,PCID,SSE4.1,SSE4.2,x2APIC,MOVBE,POPCNT,DEADLINE,AES,XSAVE,AVX,F16C,RDRAND,NXE,PAGE1GB,RDTSCP,LONG,LAHF,ABM,3DNOWP,PERF,ITSC,FSGSBASE,TSC_ADJUST,BMI1,HLE,AVX2,SMEP,BMI2,ERMS,INVPCID,RTM,MPX,RDSEED,ADX,SMAP,CLFLUSHOPT,PT,SRBDS_CTRL,MD_CLEAR,TSXFA,IBRS,IBPB,STIBP,L1DF,SSBD,SENSOR,ARAT,XSAVEOPT,XSAVEC,XGETBV1,XSAVES,MELTDOWNcpu3:
32KB 64b/line 8-way D-cache, 32KB 64b/line 8-way I-cache, 256KB
64b/line 4-way L2 cache, 8MB 64b/line 16-way L3 cacheioapic0 at
mainbus0: apid 2 pa 0xfec00000, version 20, 120 pinsacpimcfg0 at
acpi0acpimcfg0: addr 0x80000000, bus 0-255acpiprt0 at acpi0: bus 0
(PCI0)acpiprt1 at acpi0: bus 1 (PEG0)acpiprt2 at acpi0: bus 2
(PEG1)acpiprt3 at acpi0: bus -1 (PEG2)acpiprt4 at acpi0: bus 3
(RP01)acpiprt5 at acpi0: bus 5 (RP02)acpiprt6 at acpi0: bus -1
(RP03)acpiprt7 at acpi0: bus -1 (RP04)acpiprt8 at acpi0: bus -1
(RP05)acpiprt9 at acpi0: bus -1 (RP06)acpiprt10 at acpi0: bus -1
(RP07)acpiprt11 at acpi0: bus -1 (RP08)acpiprt12 at acpi0: bus -1
(RP09)acpiprt13 at acpi0: bus -1 (RP10)acpiprt14 at acpi0: bus -1
(RP11)acpiprt15 at acpi0: bus -1 (RP12)acpiprt16 at acpi0: bus -1
(RP13)acpiprt17 at acpi0: bus -1 (RP14)acpiprt18 at acpi0: bus -1
(RP15)acpiprt19 at acpi0: bus -1 (RP16)acpiprt20 at acpi0: bus -1
(RP17)acpiprt21 at acpi0: bus -1 (RP18)acpiprt22 at acpi0: bus -1
(RP19)acpiprt23 at acpi0: bus -1 (RP20)acpiprt24 at acpi0: bus -1
(RP21)acpiprt25 at acpi0: bus -1 (RP22)acpiprt26 at acpi0: bus -1
(RP23)acpiprt27 at acpi0: bus -1 (RP24)
