Re: How to reduce latency with fast short queries in Postgresql 15.3 on a NUMA server - Mailing list pgsql-performance

Thanks for your replies, you were totally right, it was due to the CPU
governor: the governor was set to 'powersave'. I've changed it to
'performance' and the server is flying now. I'm still working on it,
but the first quick tests I've made are showing much better numbers.
Those simple short queries are running faster now, the latency is now
basically the same or even lower than the old server. The server feels
more responsive overall.

I've finally installed cpupower, to simplify the process, but you can
use basic shell commands. Here are the output for some commands:

    # cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_available_governors
    =>
    performance powersave

    # cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor
    =>
    performance

    # cpupower -c all frequency-info
    =>
    analyzing CPU 0:
      driver: intel_pstate
      CPUs which run at the same hardware frequency: 0
      CPUs which need to have their frequency coordinated by software: 0
      maximum transition latency:  Cannot determine or is not supported.
      hardware limits: 1000 MHz - 3.90 GHz
      available cpufreq governors: performance powersave
      current policy: frequency should be within 1000 MHz and 3.90 GHz.
                      The governor "performance" may decide which speed to use
                      within this range.
      current CPU frequency: Unable to call hardware
      current CPU frequency: 1.94 GHz (asserted by call to kernel)
      boost state support:
        Supported: yes
        Active: yes

    analyzing CPU 1:
      driver: intel_pstate
      CPUs which run at the same hardware frequency: 1
      CPUs which need to have their frequency coordinated by software: 1
      maximum transition latency:  Cannot determine or is not supported.
      hardware limits: 1000 MHz - 3.90 GHz
      available cpufreq governors: performance powersave
      current policy: frequency should be within 1000 MHz and 3.90 GHz.
                      The governor "performance" may decide which speed to use
                      within this range.
      current CPU frequency: Unable to call hardware
      current CPU frequency: 1.91 GHz (asserted by call to kernel)
      boost state support:
        Supported: yes
        Active: yes

    analyzing CPU 2:
      driver: intel_pstate
      CPUs which run at the same hardware frequency: 2
      CPUs which need to have their frequency coordinated by software: 2
      maximum transition latency:  Cannot determine or is not supported.
      hardware limits: 1000 MHz - 3.90 GHz
      available cpufreq governors: performance powersave
      current policy: frequency should be within 1000 MHz and 3.90 GHz.
                      The governor "performance" may decide which speed to use
                      within this range.
      current CPU frequency: Unable to call hardware
      current CPU frequency: 2.14 GHz (asserted by call to kernel)
      boost state support:
        Supported: yes
        Active: yes

    ... (cropped)

    analyzing CPU 9:
      driver: intel_pstate
      CPUs which run at the same hardware frequency: 9
      CPUs which need to have their frequency coordinated by software: 9
      maximum transition latency:  Cannot determine or is not supported.
      hardware limits: 1000 MHz - 3.90 GHz
      available cpufreq governors: performance powersave
      current policy: frequency should be within 1000 MHz and 3.90 GHz.
                      The governor "performance" may decide which speed to use
                      within this range.
      current CPU frequency: Unable to call hardware
      current CPU frequency: 2.95 GHz (asserted by call to kernel)
      boost state support:
        Supported: yes
        Active: yes

    ... (cropped)

    analyzing CPU 26:
      driver: intel_pstate
      CPUs which run at the same hardware frequency: 26
      CPUs which need to have their frequency coordinated by software: 26
      maximum transition latency:  Cannot determine or is not supported.
      hardware limits: 1000 MHz - 3.90 GHz
      available cpufreq governors: performance powersave
      current policy: frequency should be within 1000 MHz and 3.90 GHz.
                      The governor "performance" may decide which speed to use
                      within this range.
      current CPU frequency: Unable to call hardware
      current CPU frequency: 1.00 GHz (asserted by call to kernel)
      boost state support:
        Supported: yes
        Active: yes

    analyzing CPU 27:
      driver: intel_pstate
      CPUs which run at the same hardware frequency: 27
      CPUs which need to have their frequency coordinated by software: 27
      maximum transition latency:  Cannot determine or is not supported.
      hardware limits: 1000 MHz - 3.90 GHz
      available cpufreq governors: performance powersave
      current policy: frequency should be within 1000 MHz and 3.90 GHz.
                      The governor "performance" may decide which speed to use
                      within this range.
      current CPU frequency: Unable to call hardware
      current CPU frequency: 1000 MHz (asserted by call to kernel)
      boost state support:
        Supported: yes
        Active: yes

    ... (cropped)

---

Before this change, with the CPU governor set to 'powersave',
basically all the CPU cores were at 1.00 GHz. I haven't listed all the
cores, but I'm seeing very different frequencies now. I noticed that
some of the cores are still at 1 GHz, which is good if they're idle,
otherwise the server would get really hot!

> Could you post the pgbench results for both systems? Which one is this from?
Andres, I ran pgbench on the new server. Unfortunately the old server
is in production and quite busy, so I can't run any benchmark over
there.

Thanks!