On 30.07.24 14:04, Kihaguru Gathura wrote:
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:35 63 processes: 61 idle, 2 on processor up 63 days 06:11:29 CPU0 states: 3.0% user, 0.0% nice, 1.6% sys, 0.0% spin, 0.0% intr, 95.4% idle CPU1 states: 47.9% user, 0.0% nice, 6.2% sys, 0.6% spin, 0.0% intr, 45.3% idle CPU2 states: 33.0% user, 0.0% nice, 4.0% sys, 0.0% spin, 0.0% intr, 63.0% idle CPU3 states: 5.8% user, 0.0% nice, 0.6% sys, 0.0% spin, 0.0% intr, 93.6% idle Memory: Real: 199M/3048M act/tot Free: 12G Cache: 1672M Swap: 0K/16G PID USERNAME PRI NICE SIZE RES STATE WAIT TIME CPU COMMAND 77669 _postgre 58 0 169M 80M onproc/2 - 0:10 35.60% postgres 77771 _relayd 2 0 5112K 8456K sleep/0 kqread 17:38 0.05% relayd 16136 www 2 0 8272K 12M sleep/1 kqread 40:05 0.00% httpd 15178 www 2 0 8140K 11M sleep/3 kqread 39:43 0.00% httpd 41797 www 2 0 7892K 11M sleep/3 kqread 39:40 0.00% httpd 30439 _relayd 2 0 1572K 4176K sleep/2 kqread 36:41 0.00% relayd 48133 _relayd 2 0 1564K 4148K sleep/1 kqread 35:55 0.00% relayd 84898 _relayd 2 0 1572K 4164K sleep/1 kqread 35:34 0.00% relayd 88687 _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% relayd 58290 www 2 0 1612K 2556K sleep/1 kqread 6:24 0.00% slowcgi 86189 _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% sshd 45035 www 2 0 2388K 4532K sleep/2 kqread 1:21 0.00% httpd 82363 _syslogd 2 0 1228K 1648K sleep/2 kqread 1:10 0.00% syslogd 38604 _relayd 2 0 1624K 4040K sleep/2 kqread 1:08 0.00% relayd 59324 _postgre 2 0 146M 7596K sleep/1 kqread 1:02 0.00% postgres 58070 _postgre 2 0 146M 15M sleep/3 kqread 1:01 0.00% postgres 11204 _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% init 68961 _postgre 2 0 146M 8984K sleep/2 kqread 0:13 0.00% postgres 28371 _postgre 2 0 146M 61M idle kqread 0:10 0.00% postgres 12040 _postgre 2 0 146M 5604K idle kqread 0:07 0.00% postgres 45123 _ntp 2 0 1060K 2748K idle kqread 0:07 0.00% ntpd 67766 root 2 0 888K 1516K idle kqread 0:06 0.00% cron 11185 root 2 -20 992K 1296K idle kqread 0:05 0.00% ntpd 48084 kihaguru 28 0 2792K 4904K onproc/0 - 0:02 0.00% top 33981 _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% sshd 61657 root 2 0 3024K 5120K idle kqread 0:00 0.00% relayd 44011 kihaguru 3 0 1764K 6332K idle ttyin 0:00 0.00% psql 88332 www 2 0 2616K 2720K sleep/1 kqread 0:00 0.00% ShelveAssortItem 57595 kihaguru 18 0 880K 904K idle sigsusp 0:00 0.00% ksh 64781 kihaguru 2 0 1412K 3572K idle kqread 0:00 0.00% sshd 1477 root 2 0 1220K 4348K idle kqread 0:00 0.00% sshd 52393 _smtpq 2 0 1676K 3548K idle kqread 0:00 0.00% smtpd 43301 _dhcp 2 0 868K 1760K idle kqread 0:00 0.00% dhcpleased 6184 _dhcp 2 0 856K 1780K idle kqread 0:00 0.00% dhcpleased 31249 root 2 0 728K 1480K idle netio 0:00 0.00% pflogd 20367 root 2 0 752K 2492K idle netio 0:00 0.00% syslogd 85654 _smtpd 2 0 2128K 4916K idle kqread 0:00 0.00% smtpd 30336 root 2 0 1728K 2296K idle kqread 0:00 0.00% smtpd 31500 _postgre 2 0 147M 14M idle kqread 0:00 0.00% postgres 58901 root 2 0 3640K 5068K idle kqread 0:00 0.00% httpd 54630 root 2 0 848K 1552K idle kqread 0:00 0.00% dhcpleased 87873 root 2 0 828K 1500K idle kqread 0:00 0.00% slaacd 72058 root 2 0 1204K 4312K idle kqread 0:00 0.00% sshd 94363 _smtpd 2 0 1548K 3480K idle kqread 0:00 0.00% smtpd 33820 root 3 0 508K 1524K idle ttyin 0:00 0.00% getty 35003 _smtpd 2 0 1724K 3580K idle kqread 0:00 0.00% smtpd 78288 _slaacd 2 0 848K 1720K idle kqread 0:00 0.00% slaacd 35269 root 2 0 532K 1224K idle kqread 0:00 0.00% resolvd 8090 _smtpd 2 0 1460K 3332K idle kqread 0:00 0.00% smtpd 96891 _slaacd 2 0 832K 1736K idle kqread 0:00 0.00% slaacd 1916 _sndio 2 -20 2660K 960K idle kqread 0:00 0.00% sndiod 10010 kihaguru 18 0 880K 904K idle sigsusp 0:00 0.00% ksh 28465 _smtpd 2 0 1448K 3228K idle kqread 0:00 0.00% smtpd 40558 root 3 0 508K 1520K idle ttyin 0:00 0.00% getty 36854 root 3 0 504K 1504K idle ttyin 0:00 0.00% getty 77907 root 3 0 512K 1528K idle ttyin 0:00 0.00% getty 86780 root 3 0 508K 1516K idle ttyin 0:00 0.00% getty 86210 _sndiop 2 0 2652K 1056K idle kqread 0:00 0.00% sndioddmesg output:OpenBSD 7.3 (GENERIC.MP) #1125: Sat Mar 25 10:36:29 MDT 2023 deraadt@amd64.openbsd.org:/usr/src/sys/arch/amd64/compile/GENERIC.MP real mem = 16850751488 (16070MB) avail mem = 16320638976 (15564MB) random: good seed from bootblocks mpath0 at root scsibus0 at mpath0: 256 targets mainbus0 at root bios0 at mainbus0: SMBIOS rev. 3.1 @ 0x78805000 (52 entries) bios0: vendor Dell Inc. version "2.7.1" date 10/12/2021 bios0: Dell Inc. PowerEdge T340 efi0 at bios0: UEFI 2.7 efi0: Dell Inc. rev 0x1070201 acpi0 at bios0: ACPI 6.1 acpi0: sleep states S0 S5 acpi0: tables DSDT FACP SSDT SSDT SSDT SSDT TPM2 WD__ SLIC HPET APIC MCFG SSDT LPIT WSMT SSDT DBGP DBG2 HEST BERT ERST EINJ DMAR acpi0: 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 bits acpihpet0 at acpi0: 23999999 Hz acpimadt0 at acpi0 addr 0xfee00000: PC-AT compat cpu0 at mainbus0: apid 0 (boot processor) cpu0: Intel(R) Xeon(R) E-2124 CPU @ 3.30GHz, 3292.33 MHz, 06-9e-0a cpu0: 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,3DN OWP,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,MELTDOWN cpu0: 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 cache cpu0: smt 0, core 0, package 0 mtrr: Pentium Pro MTRR support, 10 var ranges, 88 fixed ranges cpu0: apic clock running at 24MHz cpu0: mwait min=64, max=64, C-substates=0.2.1.2.4.1.1.1, IBE cpu1 at mainbus0: apid 2 (application processor) cpu1: Intel(R) Xeon(R) E-2124 CPU @ 3.30GHz, 3292.34 MHz, 06-9e-0a cpu1: 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,3DN OWP,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,MELTDOWN cpu1: 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 cache cpu1: smt 0, core 1, package 0 cpu2 at mainbus0: apid 4 (application processor) cpu2: Intel(R) Xeon(R) E-2124 CPU @ 3.30GHz, 3292.33 MHz, 06-9e-0a cpu2: 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,3DN OWP,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,MELTDOWN cpu2: 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 cache cpu2: smt 0, core 2, package 0 cpu3 at mainbus0: apid 6 (application processor) cpu3: Intel(R) Xeon(R) E-2124 CPU @ 3.30GHz, 3292.33 MHz, 06-9e-0a cpu3: 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,3DN OWP,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,MELTDOWN cpu3: 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 cache ioapic0 at mainbus0: apid 2 pa 0xfec00000, version 20, 120 pins acpimcfg0 at acpi0 acpimcfg0: addr 0x80000000, bus 0-255 acpiprt0 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)
Postgres CPU usage also depends on connections. just one connection will (in most cases) just use one CPU.
You may want to look at these here for a better analysis:
https://www.postgresql.org/docs/current/pgstatstatements.html
But in general (and imho) stretching hardware when the DB is slow is mostly the last option.
Analyse config and querys usually helps ;)
Best, Matthias
No comments:
Post a Comment