Hi, On 2023-05-31 14:40:05 +0200, Sergio Rus wrote: > I've been configuring a new server and tuning Postgresql 15.3, but I'm > struggling with a latency I'm consistently seeing with this new server when > running fast short queries, compared to the other server. > > We're running two different versions of Postgresql: > > - Server A: Postgresql 9.3 > - Server B: Postgresql 15.3 > > Server B is the new server and is way more powerful than server A: > > - Server A: 1x Intel Xeon E3-1270 3.5GHz, 2x 8GB DDR3, RAID0 > - Server B: 2x Intel Xeon Platinum 8260 2.4GHz, 4x 16GB DDR4, RAID1 > > We're running Linux Ubuntu 20.04 on server B and I've been tweaking some > settings in Linux and Postgresql 15.3. With the current setup, Postgresql > 15.3 is able to process more than 1 million transactions per second running > pgbench: > > # pgbench --username postgres --select-only --client 100 --jobs 10 > --time 20 test Could you post the pgbench results for both systems? Which one is this from? > As shown in pgbench, the performance is great. Also when testing individual > queries, heavy queries (those taking a few ms) run faster on server B than > server A. Unfortunately when we run fast short SELECT queries (< 1 ms), > server A is consistently running faster than server B, even if the query > plans are the same: One explanation for this can be the powersaving settings. Newer CPUs can throttle down a lot further than the older ones. Increasing the clock speed has a fair bit of latency - for a long running query that's not really visible, but if you run a short query in isolation, it'll likely complete before the clock speed has finished ramping up. You can query that with cpupower frequency-info Another thing is that you're comparing a two socket system with a one socket system. Latency between a program running on one node and a client on another, and similarly, a program running on one node and memory attached to the other CPU, is higher. You could check what happens if you bind both server and client to the same CPU socket. numactl --membind 1 --cpunodebind 1 <program> <parameters> forces programs to allocate memory and run on a specific CPU socket. Greetings, Andres Freund
