Re: Increased iowait and blk_read_time with higher shared_buffers

Hello Jordan,

You don't have to set %25 for the best performance. You need to test
different values for your database. If I were you, I would

- try to enable huge pages. You probably will see better performance
with bigger shared_buffers when you configure huge pages. ->
https://www.postgresql.org/docs/14/kernel-resources.html#LINUX-HUGE-PAGES
- set effective_io_concurrency to 200. But, you need to test to figure
out the best value. It significantly depends on your disk's
metrics/configuration
- set random_page_cost to 2 and try to decrease it gradually until 1.2.
- set effective_cache_size to 24GB
- run pg_test_timing on the server to see the cost of asking time to the
system. Because track_io_timing is enabled in your configuration file. If
it is expensive, I would disable tracking io timing.

Note that I assumed that those resources/servers are reserved for
PostgreSQL and there is no other service running on them.

Best regards.
Samed YILDIRIM

On Wed, 14 Dec 2022 at 20:12, Jordan Hurwich <jhurwich(at)pulsasensors(dot)com>
wrote:

> Thanks for your thoughtful response Samed.
>
> I'm familiar with the article you linked to, and part of my surprise is
> that with these 32GB RAM machines we're seeing better performance at 12.5%
> (4GB) than the commonly recommended 25% (8GB) of system memory for
> shared_buffers. Your notes about disk read stats from Postgres potentially
> actually representing blocks read from the OS cache make sense, I just
> imagined that Postgres would be better at managing the memory when it was
> dedicated to it via shared_buffers than the OS (obviously with some point
> of diminishing returns); and I'm still hoping there's some Postgres
> configuration change we can make that enables better performance through
> improved utilization of shared_buffers at the commonly recommended 25% of
> system memory.
>
> You mentioned effective_cache_size, which we currently have set to 16GB
> (50% of system memory). Is it worth us experimenting with that value, if so
> would you recommend we try reducing it or increasing it? Are there other
> settings that we might consider to see if we can improve the utilization of
> shared_buffers at higher values like 8GB (25% of system memory)?
>
> On Wed, Dec 14, 2022 at 4:38 AM Samed YILDIRIM <samed(at)reddoc(dot)net> wrote:
>
>> Hi Jordan,
>>
>> Increased shared buffer size does not necessarily mean an increased
>> performance.
>>
>> Regarding the negative correlation between IOWait and shared_buffers'
>> size; if you don't increase memory of the system, it is an expected result
>> in my opinion. Because, PostgreSQL starts reserving a bigger portion of the
>> system memory, and the OS cache size decreases respectively. Smaller OS
>> cache can easily result with more disk access and higher IO demand and
>> bigger IOWait.
>>
>> As you can see in graphs, when you increase the size of shared_buffers,
>> you see higher block hits and lower block reads. "hits" refers to the
>> blocks that are already in shared_buffers. "reads" refers to the blocks
>> that are not in shared_buffers and *"read from* *disk"*. But, *"read
>> from disk"* that you see in PostgreSQL's statistic catalogs doesn't mean
>> all of those blocks were read from the disk. PostgreSQL requests data
>> blocks, which are not already in shared_buffers, from the kernel. And, if
>> the requested block is in the OS cache, the kernel provides it directly
>> from the memory. No disk access, therefore, happens at all. And, you
>> observe that through lower disk access (I/O) and lower IOWait on your
>> operating system.
>>
>> When you increase size of shared_buffers without increasing amount of the
>> system memory and with or without decreasing effective_cache_size,
>> PostgreSQL considers the possibility of the block to be requested on the
>> memory lower than previous configuration. So, it creates execution plans
>> with less index usages. Less index usage means more sequential scan. More
>> sequential scan means more disk read. We already have less OS cache. And
>> the system has to carry out more disk accesses.
>>
>> As you can see, they are all connected. Setting shared_buffers higher
>> than a threshold, which varies from database to database, actually
>> decreases your performance.
>>
>> To conclude, your results are expected results.
>>
>> A useful resource to read:
>> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
>>
>>> ..... given the way PostgreSQL also relies on the operating system
>>> cache, it's unlikely you'll find using more than 40% of RAM to work better
>>> than a smaller amount.
>>>
>>
>> Best regards.
>> Samed YILDIRIM
>>
>>
>> On Tue, 13 Dec 2022 at 02:29, Jordan Hurwich <jhurwich(at)pulsasensors(dot)com>
>> wrote:
>>
>>> Hi everyone,
>>> I'm writing to ask about a correlation I was surprised to observe on our
>>> PSQL machines (particularly read-only standbys) where increasing
>>> "shared_buffers" appears to result in
>>> increased pg_stat_database.blk_read_time and CPU iowait, which in turn
>>> seems to correlate with reduced throughput for our query-heavy services -
>>> details below.
>>>
>>> Is this expected, or are there configuration changes we might make to
>>> improve the performance at higher "shared_buffers" values?
>>>
>>> Thanks, let me know if I can provide any more info,
>>> Jordan
>>>
>>> - Tests and results - public Datadog dashboard here
>>> <https://p.datadoghq.com/sb/0d34b3451-8bde042f82c012981b94796cdc26e259>,
>>> screenshot attached:
>>> - Our beta system ("endor") was run with three different
>>> configurations over the ~30hrs from Dec 11 17:00 to Dec 13 0:00 (UTC)
>>> - The only changes between these deployments was the
>>> "shared_buffers" parameter for all PSQL instances (machine and
>>> configuration details below).
>>> - "shared_buffers" = "4000MB" - from Dec 10 19:00 to Dec 11
>>> 20:00 UTC
>>> - "shared_buffers" = "8000MB" - from Dec 11 21:00 to Dec 12
>>> 13:30 UTC
>>> - "shared_buffers" = "14000MB" - from Dec 12, 14:30 to Dec 13,
>>> 0:00 UTC
>>> - The datadog dashboard
>>> <https://p.datadoghq.com/sb/0d34b3451-8bde042f82c012981b94796cdc26e259>
>>> shows our results including cpu divided by usage and the cache hit vs disk
>>> read ratio including blk_read_time (additional metrics were enabled at
>>> about Dec 11, 10am PST)
>>> - Our most query heavy service is our "Trends worker" for
>>> which the average worker duration is shown in the top-left graph
>>> - We expect the workload to be relatively constant
>>> throughout this period, particularly focusing on the standby instances
>>> (PQSL2 and PSQL3) where all read-only queries should be sent.
>>> - We see the lowest duration, i.e. best performance, most
>>> consistently with the lowest setting for shared_buffers, "4000MB"
>>> - As we increase shared_buffers we see increased iowait on the
>>> standby instances (PSQL2 and PSQL3) and increased blk_read_time
>>> (per pg_stat_database), in the bottom-most graphs as "blks_read_time".
>>> - Even though we also see a higher ratio of cache hits on
>>> those instances. Our graphs show the per second change
>>> in pg_stat_database.blks_read abd blks_hit (as "all_hit/s" and
>>> "all_read/s") and pg_statio_user_tables.heap_blks_read, heap_blks_hit,
>>> idx_blks_read, and idx_blks_hit
>>> - Cluster contains 3 PSQL nodes, all on AWS EC2 instances,
>>> postgresql.conf attached
>>> - Version: psql (PostgreSQL) 14.1
>>> - Machine:
>>> - AWS "c6gd.4xlarge" (32GB RAM, 16 core 2.5 GHz, local storage
>>> 950 GB ssd)
>>> - uname -a: Linux ip-172-30-64-110 5.4.0-1038-aws #40-Ubuntu
>>> SMP Fri Feb 5 23:53:34 UTC 2021 aarch64 aarch64 aarch64 GNU/Linux
>>> - Replication via WAL:
>>> - Line configuration: PSQL1 (master), PSQL1 followed by PSQL2,
>>> PSQL2 followed by PSQL3
>>> - Managed by repmgr (version: repmgr 5.3.0), no failovers
>>> observed during timeframe of interest
>>> - Load balancing:
>>> - Managed by PGPool-II (version: 4.3.2 (tamahomeboshi)) on 3
>>> AWS instances
>>> - All write queries go to master. All read-only queries go to
>>> standbys unless WAL on standby > 10MB, falling back to read from master as
>>> last resort
>>>
>>>