<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Varun Jain on Medium]]></title>
        <description><![CDATA[Stories by Varun Jain on Medium]]></description>
        <link>https://medium.com/@varunjain2108?source=rss-6d1f4be183a3------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*1urpS6dnniYVcA3qAYOoCg.jpeg</url>
            <title>Stories by Varun Jain on Medium</title>
            <link>https://medium.com/@varunjain2108?source=rss-6d1f4be183a3------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 01:42:14 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@varunjain2108/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Unraveling Disk I/O with PostgreSQL Reads: Does Every Query Trigger a Write?]]></title>
            <link>https://medium.com/@varunjain2108/unraveling-disk-i-o-with-postgresql-reads-does-every-query-trigger-a-write-ab331362c715?source=rss-6d1f4be183a3------2</link>
            <guid isPermaLink="false">https://medium.com/p/ab331362c715</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[technology]]></category>
            <dc:creator><![CDATA[Varun Jain]]></dc:creator>
            <pubDate>Mon, 03 Jun 2024 13:01:00 GMT</pubDate>
            <atom:updated>2024-06-03T13:01:00.378Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="An elephant wisely carrying a huge load, including a PostgreSQL server on its back" src="https://cdn-images-1.medium.com/max/1024/1*ZE4pVP7EB4OM3GURkIZlag.jpeg" /><figcaption>An elephant wisely carrying a huge load, including a PostgreSQL server on its back</figcaption></figure><p>Much like its counterparts, PostgreSQL aims for efficient query processing. One of the ways it does this is by using a shared buffer cache. This stores frequently accessed data in memory, which helps speed up information retrieval as opposed to continually reading from the disk. However, even straightforward SELECT queries can sometimes lead to disk write IO. So, let’s dig a little deeper to understand why this happens and see how it ties into external sorting. We will discuss about two scenarios: Dirty pages in Shared Buffers and External Sorting.</p><h3>Shared Buffers and Disk Reads</h3><p>PostgreSQL places a high importance on data integrity. It handles modifications, whether they’re INSERT, UPDATE, or DELETE, in a particular way. The data page affected by these changes in the shared buffer doesn’t get an immediate update. Instead, it becomes “<a href="https://www.postgresql.org/docs/current/wal-configuration.html">dirty</a>”, signifying a difference between the in-memory version of the data page and its disk-stored counterpart.</p><p>The <a href="https://minervadb.xyz/understanding-shared-buffers-implementation-in-postgresql/">shared buffer</a> pool is a tool PostgreSQL uses to store data pages that are frequently accessed, keeping them in memory. This buffer works as a caching mechanism, offering a speedier information retrieval than constant disk reading. However, maintaining this cache is a careful act of balance between performance and data integrity.</p><p>While PostgreSQL does its best to keep frequently accessed data in the shared buffer, space is limited. When the buffer fills up and a new page needs to be loaded for a read query, we might see the following:</p><p><strong>No Dirty Pages:</strong> If there are no dirty pages in the buffer, PostgreSQL can just remove the least recently used clean page to make room for the new page needed by the SELECT query. No disk writes needed.</p><p><strong>Dirty Pages Present:</strong> If there are dirty pages in the buffer, and the new page needed by the SELECT query isn’t there in a clean state, PostgreSQL has to make a choice:</p><ul><li><strong>Evict a Clean Page:</strong> Getting rid of a clean page might be quicker, but if it’s needed again soon, we’ll have to re-read the same data from the disk, which could slow things down.</li><li><strong>Flush a Dirty Page:</strong> Flushing a dirty page keeps data consistent and makes space for the new page. But, it does mean an extra disk write compared to just removing a clean page.</li></ul><p><strong>PostgreSQL’s Choice:</strong></p><p>In this scenario, PostgreSQL often prioritizes data consistency and flushes a dirty page to disk before loading the new page required by the SELECT query. This ensures the read operation works with the latest committed data and avoids potential inconsistencies.</p><h4>Hands On Time</h4><ul><li>Run the Postgres server locally using the docker compose yaml</li></ul><pre>version: &#39;3&#39;<br><br>services:<br>  postgres:<br>    container_name: postgres<br>    image: postgres:16.0<br>    environment:<br>      - POSTGRES_PASSWORD=postgres<br>      - POSTGRES_USER=postgres<br>    ports:<br>      - &quot;5432:5432&quot;<br>    volumes:<br>      - ./volumes/postgres_data:/data/postgres<br>      - ./init.sh:/docker-entrypoint-initdb.d/init.sh</pre><ul><li>In the same directory add this file <strong><em>init.sh</em></strong>. We are intentionally reducing the <a href="https://postgresqlco.nf/doc/en/param/shared_buffers/">shared_buffers</a> size and increasing the <a href="https://postgresqlco.nf/doc/en/param/checkpoint_timeout/">checkpoint_timeout</a> for our experimentation.</li></ul><pre>#!/bin/bash<br><br>echo &#39;checkpoint_timeout=900&#39; &gt;&gt; /var/lib/postgresql/data/postgresql.conf<br>echo &#39;shared_buffers=50MB&#39; &gt;&gt; /var/lib/postgresql/data/postgresql.conf</pre><ul><li>Setup python env to run python script</li></ul><pre>- python -m venv &lt;venv_name&gt;<br>- source &lt;venv_name&gt;/bin/activate<br>- echo &quot;psycopg2-binary&quot; &gt;&gt; requirement.txt<br>- pip3 install -r requirement.txt</pre><ul><li>Create the file with name <strong><em>postgres_dummy_data.py</em></strong></li></ul><pre>import calendar<br>import random<br>from multiprocessing import Pool<br><br>import psycopg2<br>import datetime<br><br>dbname = &quot;mydatabase&quot;<br>dbuser = &quot;postgres&quot;<br>dbpassword = &quot;postgres&quot;<br>dbhost = &quot;localhost&quot;<br><br>TABLE_NAME = &quot;my_table&quot;<br>IDX_NAME = &quot;idx_created_at&quot;<br>ID_COL = &quot;id&quot;<br>TEST_COL = &quot;test&quot;<br>TIMESTAMP_COL = &quot;created_at&quot;<br><br>CONN = psycopg2.connect(dbname=dbname, user=dbuser, password=dbpassword, host=dbhost)<br><br><br>def create_table():<br>    cursor = CONN.cursor()<br>    cursor.execute(f&quot;&quot;&quot;<br>        CREATE TABLE IF NOT EXISTS {TABLE_NAME} (<br>            id INT,<br>            test VARCHAR(255),<br>            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP<br>        );<br>    &quot;&quot;&quot;)<br>    CONN.commit()<br>    cursor.close()<br><br><br>def create_index():<br>    cursor = CONN.cursor()<br>    cursor.execute(f&quot;&quot;&quot;CREATE INDEX IF NOT EXISTS {IDX_NAME} ON {TABLE_NAME}(created_at);&quot;&quot;&quot;)<br>    CONN.commit()<br>    cursor.close()<br><br><br>def generate_random_data(num_rows):<br>    data = []<br>    for _ in range(num_rows):<br>        data.append((random.randint(1, 100000),<br>                     f&quot;Test Data {random.randint(1, 1000)}&quot;,<br>                     datetime.datetime.now() - datetime.timedelta(days=random.randint(0, 30))))<br>    return data<br><br><br>def insert_data(data):<br>    cursor = CONN.cursor()<br>    insert_query = f&quot;&quot;&quot;INSERT INTO {TABLE_NAME} ({ID_COL}, {TEST_COL}, {TIMESTAMP_COL}) VALUES (%s, %s, %s)&quot;&quot;&quot;<br>    cursor.executemany(insert_query, data)<br>    CONN.commit()<br>    cursor.close()<br><br><br>def process_month(month_data):<br>    try:<br>        insert_data(month_data)<br>        print(f&quot;Inserted 100,000 rows&quot;)<br>    except Exception as e:<br>        print(f&quot;Error processing data for month: {e}&quot;)<br><br><br>if __name__ == &quot;__main__&quot;:<br>    try:<br>        create_table()<br>        create_index()<br>        month_data_list = []<br><br>        for i in range(12):<br>            current_date = datetime.datetime.now() - datetime.timedelta(days=i * 30)<br>            first_day = current_date.replace(day=1)<br>            last_day = (first_day + datetime.timedelta(days=31)) if first_day.month == 12 else (<br>                    first_day + datetime.timedelta(days=calendar.monthrange(first_day.year, first_day.month)[1]))<br>            data = generate_random_data(100000)<br>            month_data_list.append(data)<br><br>        with Pool() as pool:<br>            pool.map(process_month, month_data_list)<br><br>    except Exception as e:<br>        print(f&quot;Error: {e}&quot;)<br>    finally:<br>        CONN.close()</pre><ul><li>Run: <strong><em>docker compose up</em></strong></li><li>Execute Python script to Create the table and insert row 100K rows in for last 12 months. Total 1.2M rows.</li><li>Run:<strong><em> python3 run postgres_dummy_data.py</em></strong></li></ul><p>Our DB setup is complete. Let’s do some experimentations</p><p><strong>Let’s execute following queries:</strong></p><ul><li>Steps to check dirty pages in the shared buffer.</li><li><strong>create extension </strong><a href="https://tomasz-gintowt.medium.com/postgresql-extensions-pg-buffercache-b38b0dc08000"><strong>pg_buffercache</strong></a><strong>;</strong></li><li><strong>SELECT pg_class.relname, pg_buffercache.isdirty, COUNT(*) AS buffers FROM pg_buffercache JOIN pg_class ON pg_buffercache.relfilenode = pg_class.relfilenode GROUP by pg_class.relname, pg_buffercache.isdirty order by 3 desc;</strong></li></ul><p><strong>Image 1</strong>: Displays the number of dirty pages in the shared buffer for both the my_table and the idx_created_at index. The total count is <strong>3017 dirty pages</strong> for my_table and <strong>1849 dirty pages</strong> for the idx_created_at index.</p><p><strong>Image 2</strong>: After allowing some time for the Postgres background write process to flush a portion of dirty pages to the Disk, we reran the query. The current state reveals a total of <strong>1412 dirty pages</strong> for my_table and no dirty pages for the idx_created_at index.</p><p><strong>Image 3</strong>: Illustrates that the dirty pages have been successfully flushed to Disk.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/786/1*TR6eNqZ1WSoBnWVSBHsK9w.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/806/1*IAVplIkREV66x4zZYTPozA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/996/1*oYU4RBCiuzp48PPf0kbeAw.png" /><figcaption>SELECT pg_class.relname, pg_buffercache.isdirty, COUNT(*) AS buffers FROM pg_buffercache JOIN pg_class ON pg_buffercache.relfilenode = pg_class.relfilenode GROUP by pg_class.relname, pg_buffercache.isdirty order by 3 desc;</figcaption></figure><p>In the process, we also executed our Read SQL query with <a href="https://www.postgresql.org/docs/current/sql-explain.html">Explain</a> to understand what was happening behind the scenes.</p><blockquote><strong>SQL:</strong> select avg(id) from my_table where created_at between ‘2023–05–04 17:41:45’ and ‘2024–05–04 17:41:45’;</blockquote><blockquote><strong>QUERY PLAN:</strong> <a href="https://www.postgresql.org/docs/current/sql-explain.html">explain</a> (analyze, buffers, verbose, settings, timing, summary) select avg(id) from my_table where created_at between ‘2023–05–04 17:41:45’ and ‘2024–05–04 17:41:45’;</blockquote><pre>Finalize Aggregate  (cost=16817.02..16817.03 rows=1 width=32) (actual time=54.292..55.854 rows=1 loops=1)<br>  Output: avg(id)<br>  Buffers: shared hit=234 read=9524 written=1412<br>  -&gt;  Gather  (cost=16816.80..16817.01 rows=2 width=32) (actual time=54.165..55.844 rows=3 loops=1)<br>        Output: (PARTIAL avg(id))<br>        Workers Planned: 2<br>        Workers Launched: 2<br>        Buffers: shared hit=234 read=9524 written=1412<br>        -&gt;  Partial Aggregate  (cost=15816.80..15816.81 rows=1 width=32) (actual time=47.665..47.666 rows=1 loops=3)<br>              Output: PARTIAL avg(id)<br>              Buffers: shared hit=234 read=9524 written=1412<br>              Worker 0:  actual time=44.621..44.621 rows=1 loops=1<br>                Buffers: shared read=2794 written=460<br>              Worker 1:  actual time=44.613..44.613 rows=1 loops=1<br>                Buffers: shared read=2770 written=458<br>              -&gt;  Parallel Bitmap Heap Scan on public.my_table  (cost=5598.62..15618.20 rows=79439 width=4) (actual time=18.619..44.215 rows=64586 loops=3)<br>                    Output: id, test, created_at<br>                    Recheck Cond: ((my_table.created_at &gt;= &#39;2023-04-04 17:41:45&#39;::timestamp without time zone) AND (my_table.created_at &lt;= &#39;2024-05-04 17:41:45&#39;::timestamp without time zone))<br>                    Heap Blocks: exact=3264<br>                    Buffers: shared hit=234 read=9524 written=1412<br>                    Worker 0:  actual time=15.639..41.354 rows=61387 loops=1<br>                      Buffers: shared read=2794 written=460<br>                    Worker 1:  actual time=15.652..41.315 rows=60728 loops=1<br>                      Buffers: shared read=2770 written=458<br>                    -&gt;  Bitmap Index Scan on idx_created_at  (cost=0.00..5550.96 rows=190653 width=0) (actual time=22.775..22.775 rows=193758 loops=1)<br>                          Index Cond: ((my_table.created_at &gt;= &#39;2023-04-04 17:41:45&#39;::timestamp without time zone) AND (my_table.created_at &lt;= &#39;2024-05-04 17:41:45&#39;::timestamp without time zone))<br>                          Buffers: shared hit=234 read=696<br>Planning:<br>  Buffers: shared hit=2 read=2<br>Planning Time: 0.422 ms<br>Execution Time: 55.927 ms</pre><p>To demonstrate that a Postgres read query may result in a disk write, refer to the above SQL output explanation.</p><p>The “<strong><em>Buffers: shared hit=234 read=9524 written=1412</em></strong>” line in a PostgreSQL query execution plan provides insights into how PostgreSQL accesses data during query execution. Let’s break down these components:</p><ul><li><strong>Shared Hit (234)</strong>: This indicates that 234 blocks of data were found in the shared buffer cache. A block in PostgreSQL is typically 8 KB in size. When a block is found in the cache, it means that accessing the data did not require reading from the disk, which is significantly slower than accessing data from memory. This is beneficial because accessing data from the cache is much faster, contributing to the overall performance improvement of the query.</li><li><strong>Read (9524)</strong>: This represents the total number of blocks that were read from the disk. Even though some of these blocks were subsequently found in the cache (leading to shared hits), others were not. Reading from the disk is slower compared to accessing data from the cache or shared buffers.</li><li><strong>Written (1412)</strong>: This shows the number of blocks that were written to the disk. Writing data to the disk is generally slower than reading from it, especially if it involves updating existing data or creating new data structures on the disk.</li></ul><blockquote><strong>Written (1412): </strong>This write IO was triggered by our Read SQL query because of Dirty pages in the shared buffer.</blockquote><p>Executing our Read SQL query multiple times could potentially flush all dirty pages to the disk. Consequently, our read query may cease to perform Disk Write I/O.</p><p>In future, our read query might initiate Disk Write if the Postgres query planner deems it necessary to flush the dirty pages to the disk first. This action is needed to free up some space in the shared buffer before reading the pages from the disk to the shared buffer.</p><p>Understanding these metrics is crucial for optimizing query performance, particularly in scenarios where the goal is to minimize I/O operations. The ratio of shared hits to total reads gives an indication of how effectively the query is utilizing the cache. A high percentage of shared hits relative to total reads suggests that the query is benefiting from the cache, reducing the need for slow disk reads.</p><h3>External Disk Sorting</h3><p>Sorting data is a fundamental operation in database queries. In PostgreSQL, the ORDER BY clause allows you to sort results based on specific columns. However, what happens when the data you want to sort surpasses the available memory in the shared buffer (<a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM">work_mem</a>)?</p><blockquote>Note: Several types of PostgreSQL queries utilize the worker memory:</blockquote><blockquote><strong>Sorting</strong>: The ORDER BY clause requires sorting the results. PostgreSQL uses worker memory to perform this in-memory sorting before returning the data.</blockquote><blockquote><strong>Aggregation</strong>: Functions like DISTINCT, GROUP BY, and aggregate functions (e.g., COUNT, SUM, AVG) often involve temporary in-memory processing. Worker memory facilitates this.</blockquote><blockquote><strong>Joining Tables</strong>: When joining multiple tables, PostgreSQL might use worker memory to create temporary hash tables or perform merges if the data doesn’t fit entirely in the shared buffers.</blockquote><blockquote><strong>Hashing</strong>: Certain operations like hash joins or window functions that rely on hashing intermediate results use worker memory for efficiency.</blockquote><p>In essence, any operation that necessitates temporary in-memory structures for processing data before returning the final results leverages worker memory.</p><p><strong>Sorting Process Breakdown:</strong></p><p><strong>In-Memory Sorting (when data fits in </strong><a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM"><strong>work_mem</strong></a><strong>):</strong></p><ul><li>If the data to be sorted is smaller than <a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM">work_mem</a>, PostgreSQL performs the entire sorting process within this dedicated memory space.</li><li>Sorting algorithms like quicksort, well-suited for in-memory operations, are employed for efficient sorting.</li></ul><p><strong>External Sorting (when data exceeds </strong><a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM"><strong>work_mem</strong></a><strong>):</strong></p><ul><li>If the data surpasses <a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM">work_mem</a> capacity, PostgreSQL switches to external sorting.</li><li>This involves reading data pages from the table and potentially writing them back if they contain dirty writes (uncommitted changes).</li><li>Temporary files on disk are used to store intermediate sorting results as the entire dataset can’t be held in memory at once.</li><li>Merging of these sorted partitions and writing the final sorted result involve additional disk access, separate from the shared buffer.</li></ul><p><strong>Shared Buffer and External Sorting:</strong></p><p>While the shared buffer doesn’t directly participate in sorting, it can indirectly influence external sorting:</p><ul><li><strong>Dirty Page Writes:</strong> If data pages involved in sorting contain dirty writes, they might need to be flushed to disk before sorting to ensure data consistency. This can increase disk writes before external sorting even begins.</li><li><strong>Temporary File Management:</strong> For large sorts, temporary files can consume significant disk space. Depending on the shared buffer size and workload, the database might need to evict data from the shared buffer to free up space for temporary files related to external sorting.</li></ul><h4><strong>Hands on Time</strong></h4><ul><li>Using the existing DB setup, we’ll demonstrate how executing a Read SQL query with sorting can lead to Disk Write I/O.</li><li>Please execute the query below</li></ul><blockquote>explain (analyze, buffers, verbose, settings, timing, summary) select * from my_table where created_at between ‘2023–07–04 17:41:45’ and ‘2024–05–04 17:41:45’ order by id desc;</blockquote><ul><li><strong>Explain SQL Output:</strong></li></ul><pre>Sort  (cost=38571.25..39047.88 rows=190653 width=25) (actual time=113.729..122.501 rows=193758 loops=1)<br>  Output: id, test, created_at<br>  Sort Key: my_table.id DESC<br>  Sort Method: external merge  Disk: 7960kB<br>  Buffers: shared hit=6 read=9755, temp read=995 written=998<br>  -&gt;  Bitmap Heap Scan on public.my_table  (cost=5598.62..17286.42 rows=190653 width=25) (actual time=25.265..82.521 rows=193758 loops=1)<br>        Output: id, test, created_at<br>        Recheck Cond: ((my_table.created_at &gt;= &#39;2023-07-04 17:41:45&#39;::timestamp without time zone) AND (my_table.created_at &lt;= &#39;2024-05-04 17:41:45&#39;::timestamp without time zone))<br>        Heap Blocks: exact=8828<br>        Buffers: shared hit=3 read=9755<br>        -&gt;  Bitmap Index Scan on idx_created_at  (cost=0.00..5550.96 rows=190653 width=0) (actual time=24.007..24.007 rows=193758 loops=1)<br>              Index Cond: ((my_table.created_at &gt;= &#39;2023-07-04 17:41:45&#39;::timestamp without time zone) AND (my_table.created_at &lt;= &#39;2024-05-04 17:41:45&#39;::timestamp without time zone))<br>              Buffers: shared hit=3 read=927<br>Planning:<br>  Buffers: shared hit=9 read=12<br>Planning Time: 2.433 ms<br>Execution Time: 126.568 ms<br><br>---&gt; Sort Method: external merge  Disk: 7960kB = 7.96MB<br>---&gt; 7.96 MB is required for the Sorting the Data, while worker memory is 4MB by default<br>---&gt; So, this query ended up in using 7.96MB of Disk Write I/O.</pre><p>We can see the information about the external files by using the following query:</p><blockquote>select datid, datname, temp_files, temp_bytes from pg_stat_database;</blockquote><p><strong>Image 1:</strong> Temp file and Temp bytes usage stats before running the query</p><p><strong>Image 2:</strong> Temp file and Temp bytes usage stats after running the query. Total 8126464 Bytes = 7.96 MB and Temp File Count = 1.</p><p><strong>Image 3:</strong> We ran the Sorting SQL query again. Total 8126464*2 Bytes = 15.92MB and Temp File Count = 2.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/744/1*VnWo8AMFShdwLYIQRgIA2A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/724/1*yQfGrvK0VKB_NYkrpm-3xw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/722/1*1w0ppeaATfUokibD9_L84Q.png" /><figcaption>select datid, datname, temp_files, temp_bytes from pg_stat_database;</figcaption></figure><p><strong>How can avoid the External Disk Sorting?</strong></p><ul><li>Its easy, just increase the work work_mem.</li><li>Before that stop the existing running docker containers</li><li>Update the <a href="http://init.sh/">init.sh</a> file with the following</li></ul><pre>echo &#39;checkpoint_timeout=900&#39; &gt;&gt; /var/lib/postgresql/data/postgresql.conf<br>echo &#39;shared_buffers=50MB&#39; &gt;&gt; /var/lib/postgresql/data/postgresql.conf<br>echo &#39;work_mem=15MB&#39; &gt;&gt; /var/lib/postgresql/data/postgresql.conf</pre><ul><li><strong>Run:</strong> docker compose up</li><li>Run the Read SQL query with sorting again</li></ul><blockquote>explain (analyze, buffers, verbose, settings, timing, summary) select * from my_table where created_at between ‘2023–07–04 17:41:45’ and ‘2024–05–04 17:41:45’ order by id desc;</blockquote><ul><li><strong>Output</strong></li></ul><pre>Increasing the Worker Mem to 15MB<br><br><br>Sort  (cost=33996.99..34473.43 rows=190576 width=25) (actual time=99.649..107.632 rows=193519 loops=1)<br>  Output: id, test, created_at<br>  Sort Key: my_table.id DESC<br>  Sort Method: quicksort  Memory: 15182kB<br>  Buffers: shared hit=3 read=9748<br>  -&gt;  Bitmap Heap Scan on public.my_table  (cost=5597.83..17283.47 rows=190576 width=25) (actual time=22.506..76.478 rows=193519 loops=1)<br>        Output: id, test, created_at<br>        Recheck Cond: ((my_table.created_at &gt;= &#39;2023-07-04 17:41:45&#39;::timestamp without time zone) AND (my_table.created_at &lt;= &#39;2024-05-04 17:41:45&#39;::timestamp without time zone))<br>        Heap Blocks: exact=8826<br>        Buffers: shared hit=3 read=9748<br>        -&gt;  Bitmap Index Scan on idx_created_at  (cost=0.00..5550.19 rows=190576 width=0) (actual time=20.954..20.955 rows=193519 loops=1)<br>              Index Cond: ((my_table.created_at &gt;= &#39;2023-07-04 17:41:45&#39;::timestamp without time zone) AND (my_table.created_at &lt;= &#39;2024-05-04 17:41:45&#39;::timestamp without time zone))<br>              Buffers: shared hit=3 read=922<br>Settings: work_mem = &#39;15MB&#39;<br>Planning:<br>  Buffers: shared hit=3 read=1<br>Planning Time: 0.353 ms<br>Execution Time: 111.562 ms<br><br>---&gt; Sort Method: quicksort  Memory: 15182kB<br>---&gt; Sorting was done in the Memory. no Disk Wrire I/O performed</pre><blockquote>Here, we observe how sorting a substantial amount of rows can lead to External Sorting, which in turn results in Disk Write I/O.</blockquote><p><strong>External Sorting Key Points:</strong></p><ul><li>In-memory sorting with work_mem is preferred for smaller datasets due to its efficiency.</li><li>External sorting relies on disk-based operations when data exceeds work_mem, leading to increased disk IO and potentially slower query execution.</li><li>The shared buffer plays a supporting role in external sorting by managing dirty page writes and potentially impacting temporary file management.</li></ul><h3><strong>Key Takeaways:</strong></h3><ul><li>‘Dirty pages’ cause disk writes during read requests (SELECT) to maintain data accuracy. SELECT requests may cause disk writing if they involve dirty pages in the shared buffer.</li><li>The size of the shared buffer matters as dirty pages might have to be cleared out to create space for new pages required by read requests.</li><li>PostgreSQL gives priority to data accuracy, frequently choosing to clear out dirty pages to guarantee reliable data for reading, even if it results in extra disk writes.</li><li>External sorting heavily relies on disk writes for both reading intermediate results and writing the final sorted data.</li><li>Understanding these write operations is crucial for optimizing query performance and managing disk IO in PostgreSQL.</li></ul><h3>Further Considerations</h3><ul><li>Optimizing shared buffer size can help minimize disk writes by keeping frequently accessed data readily available in memory.</li><li>Analyzing query execution plans can reveal potential buffer misses and opportunities for indexing or query optimization.</li><li><a href="https://medium.com/devops-dev/supercharge-your-postgres-monitoring-iops-with-prometheus-and-grafana-d75cf6b51538">Monitoring disk IO</a> can help identify bottlenecks and determine if external sorting is contributing significantly to the workload.</li></ul><p>By understanding the interplay between shared buffers, dirty writes, and external sorting, you can effectively manage disk IO in PostgreSQL and ensure optimal query performance for your database.</p><h3>References</h3><ul><li><a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM">https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM</a></li><li><a href="https://www.elephantsql.com/blog/what-is-work_mem-in-postgresql.html">https://www.elephantsql.com/blog/what-is-work_mem-in-postgresql.html</a></li><li><a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM">https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM</a></li><li><a href="https://medium.com/devops-dev/supercharge-your-postgres-monitoring-iops-with-prometheus-and-grafana-d75cf6b51538">https://medium.com/devops-dev/supercharge-your-postgres-monitoring-iops-with-prometheus-and-grafana-d75cf6b51538</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ab331362c715" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Gain Unparalleled Insights into Your Postgres: IOPS Monitoring Made Easy]]></title>
            <link>https://blog.devops.dev/supercharge-your-postgres-monitoring-iops-with-prometheus-and-grafana-d75cf6b51538?source=rss-6d1f4be183a3------2</link>
            <guid isPermaLink="false">https://medium.com/p/d75cf6b51538</guid>
            <category><![CDATA[monitoring]]></category>
            <category><![CDATA[docker]]></category>
            <category><![CDATA[grafana]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[prometheus]]></category>
            <dc:creator><![CDATA[Varun Jain]]></dc:creator>
            <pubDate>Mon, 27 May 2024 12:08:20 GMT</pubDate>
            <atom:updated>2024-05-29T16:30:54.693Z</atom:updated>
            <content:encoded><![CDATA[<p>In the world of databases, keeping an eye on Input/Output Operations Per Second (IOPS) is crucial. It tells you how efficiently your PostgreSQL server interacts with its storage, impacting performance, especially for write-intensive workloads. This article dives into using Prometheus and Grafana alongside PostgreSQL 16’s new feature pg_stat_io to create a robust IOPS monitoring system.</p><p>This powerful combination allows you to:</p><ul><li><strong>Centralized Monitoring:</strong> Monitor IOPS alongside other database metrics from a single platform.</li><li><strong>Visualization Power:</strong> Create visually appealing dashboards to understand IOPS trends and identify potential bottlenecks.</li><li><strong>Alerting:</strong> Set up alerts based on IOPS thresholds to proactively address performance issues.</li></ul><h4>Understanding the <a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW"><strong>pg_stat_io</strong></a><strong> key points</strong>:</h4><ul><li><strong>pg_stat_io tracks logical I/O</strong>: It monitors database requests for reading and writing data, not necessarily the physical disk interactions themselves.</li><li><strong>OS caching and batching</strong>: The operating system might cache frequently accessed data or combine multiple smaller requests into larger ones before writing to disk. This can differ from what pg_stat_io reports.</li><li><strong>Example</strong>: An 8KB write request in Postgres might translate to two separate 4KB writes on the disk due to OS optimizations.</li></ul><p>Generally we can assume that this captures all I/O issued by Postgres, except for:</p><ul><li>I/O for writing the Write-Ahead-Log (WAL)</li><li>Special cases such as tables being moved between tablespaces</li><li>Temporary files (such as used for sorts, or extensions like pg_stat_statements)</li></ul><blockquote>With pg_stat_io you can simply look at the writes field, and see both an accurate aggregate number, as well as exactly which process in Postgres actually ended up writing your data to disk. If you have postgres 16 running instance just run thhis query: “<strong><em>select * from pg_stat_io</em></strong>”</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bzPptyF8zvjfl2SyysVPCg.png" /></figure><p>At a high level, this information can be interpreted as:</p><ul><li>Statistics are tracked for a given backend type, I/O object type (i.e. whether it’s a temporary table), and I/O context (more on that later)</li><li>The main statistics are counting I/O operations: reads, writes and extends (a special kind of write to resize data files)</li><li>For each I/O operation the size in bytes is noted to help interpret the statistics (currently always block size, i.e., usually 8kB)</li><li>Additionally, the number of shared buffer evictions, ring buffer re-uses and fsync calls are tracked</li><li>For more details see: <a href="https://www.cybertec-postgresql.com/en/pg_stat_io-postgresql-16-performance/">https://www.cybertec-postgresql.com/en/pg_stat_io-postgresql-16-performance/</a></li></ul><p><strong>pg_stat_io view Columns we are going to use:</strong></p><ul><li><strong>backend_type</strong>: source of information. This could be <em>autovacuum launcher, autovacuum worker, background worker, background writer, checkpointer, client backend, standalone backend, startup, walsender.</em></li><li><strong>reads and writes</strong>: Number of reads and writes. These fields we are going to use for monitoring the IOPS.</li></ul><p><strong>Some Usecase:</strong></p><p>With pg_stat_io, you can now see a system-wide measurement of the impact of VACUUM, by looking at everything marked as io_context = &#39;vacuum&#39;, or associated to the autovacuum worker backend type:</p><pre>SELECT * FROM pg_stat_io WHERE backend_type = &#39;autovacuum worker&#39; OR (io_context = &#39;vacuum&#39; AND (reads &lt;&gt; 0 OR writes &lt;&gt; 0 OR extends &lt;&gt; 0));</pre><h3><strong>Capturing pg_stat_io metrics using Prometheus and Grafana</strong></h3><p>Note: For the following setup I am using Mac M3</p><p><strong>Tools we are using:</strong></p><ul><li>prometheus: <a href="https://prometheus.io/">https://prometheus.io/</a></li><li>grafana: <a href="https://grafana.com/">https://grafana.com/</a></li><li>postgres-exporter: <a href="https://github.com/prometheus-community/postgres_exporter">https://github.com/prometheus-community/postgres_exporter</a></li></ul><p><strong>Architecture of this Monitoring Setup</strong></p><ul><li>Data originates from a Postgres database container running on <em>0.0.0.0:5432</em></li><li>A Postgres exporter container scrapes metrics from the postgres server at regular intervals (every 5 seconds according to the diagram). postgres-exporter is running on <em>0.0.0.0:9187. </em>Postgres exporter is configured to also pull the custom metrics defined in the queries.yaml located at postgres-exporter/queries.yaml.</li><li>For our use case we have to pull metrics from the pg_stat_io view. See the query below</li></ul><pre>Query: select backend_type, object, context, reads as reads_total, writes as writes_total, op_bytes, fsyncs as fsyncs_total from pg_stat_io</pre><ul><li>The scraped metrics are exposed on port <em>0.0.0.0:9187</em> by the Postgres exporter container.</li><li>Prometheus scrapes metrics from the Postgres exporter on port 9187. You’ll need to configure Prometheus to recognize this endpoint as a scraping target. See: <strong>prometheus-config/prometheus.yaml</strong></li><li>The scraped metrics are stored in Prometheus, which serves as a time series database.</li><li>Grafana can then query Prometheus to visualize the scraped metrics (presumably IOPS related metrics in this case). You’ll need to configure a data source in Grafana that points to your Prometheus instance.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/917/1*AcXifvUpcDH4y75CbYsxiQ.png" /></figure><p><strong>Steps to setup the Monitoring on the Postgres IOPS</strong></p><ul><li>Checkout the github repo: <a href="https://github.com/varun-2108/postgres-16-iops">https://github.com/varun-2108/postgres-16-iops</a></li><li>This repo will help you in setting up Grafana Dashboards for the Postgres I/O in couple of steps</li><li>Follow the <em>README.md</em> file</li></ul><p><strong>Useful Links:</strong></p><ul><li>Metrics: <a href="http://0.0.0.0:9187/metrics">http://0.0.0.0:9187/metrics</a>:</li><li>Grafana: <a href="http://localhost:9100/">http://localhost:9100/</a></li><li>Prometheus: <a href="http://localhost:9090/">http://localhost:9090/</a></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VLZ10TAidy2v-gkbPP38Ug.png" /><figcaption>Metrics: <a href="http://0.0.0.0:9187/metrics">http://0.0.0.0:9187/metrics</a>:</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2MOmROG48-qYh_QhiIanXw.png" /><figcaption>Grafana: <a href="http://localhost:9100/">http://localhost:9100/</a></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*w5t6WLiTqYOPIw_mRu2UnA.png" /><figcaption>Prometheus: <a href="http://localhost:9090/">http://localhost:9090/</a></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nSx4kY31r530Mqf2-0i9MA.png" /><figcaption>Grafana: <a href="http://localhost:9100/">http://localhost:9100/</a>dashboards</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7a34Avgr6wMPVIky_LTuqA.png" /><figcaption>PromQL for Read IOPS</figcaption></figure><p><strong>Prometheus Metrics being Captured: (</strong>postgres-exporter/queries.yaml<strong>)</strong></p><p>Read more here: <a href="https://www.ongres.com/blog/create-prometheus-integrated-postgres-custom-metrics/">https://www.ongres.com/blog/create-prometheus-integrated-postgres-custom-metrics/</a></p><ul><li><strong>query</strong>: Specifies the SQL query to execute against the PostgreSQL database. This query selects various statistics related to input/output operations (reads, writes, fsyncs) along with metadata about where these operations occurred (backend_type). These statistics are crucial for understanding the I/O performance of the database.</li><li><strong>reads_total, writes_total</strong>: These are the core metrics being collected. Each is aliased in the query to simplify access and is marked with usage: &quot;COUNTER&quot;, indicating they represent counters that increment over time. Collecting these metrics helps monitor the workload on the database, especially regarding disk I/O operations.</li><li><strong>backend_type</strong> : These are labels extracted from the query results. They provide additional context about where the I/O operations are occurring within the database. Marked with usage: &quot;LABEL&quot;, these fields help categorize and filter the metrics based on their origin or purpose.</li></ul><p><strong>Final O/P prometheus metrics will look like this:</strong></p><p>Metrics: <a href="http://0.0.0.0:9187/metrics">http://0.0.0.0:9187/metrics</a>:</p><pre>pg_io_writes_total{backend_type=&quot;standalone backend&quot;, server=&quot;postgres:5432&quot;} 1010<br><br>pg_io_reads_total{backend_type=&quot;standalone backend&quot;, server=&quot;postgres:5432&quot;} 1010<br><br>&gt;&gt;&gt; We will use these metrics to create IOPS Dashboards on Grafana &lt;&lt;&lt;</pre><p><strong>PromQL Write IOPS Explaination:</strong></p><p>To calculate the Write IOPS (Input/Output Operations Per Second) per second for different backend_types using Prometheus Query Language (PromQL), you need to sum the values of pg_io_writes grouped by backend_type. Since IOPS typically represents the number of read/write operations completed per second, you&#39;ll want to aggregate the count over a time window to get the rate.</p><p>Assuming you’re interested in the last 1minute, here’s how you can construct your PromQL query:</p><blockquote>sum(rate(pg_io_writes_total[5m])) by (backend_type)</blockquote><p>This query does the following:</p><ul><li>rate(pg_io_writes_total[1m]): Calculates the per-second average rate of increase of the pg_io_writes_total metric over the last 1 minute1. The [1m] part specifies the range vector selector, indicating that the rate calculation should consider the change in the metric value over the past 1minutes.</li><li>sum(...) by (backend_type): Sums up the rates calculated above, grouping them by the backend_type label. This gives you the total write IOPS per second for each type of backend.</li></ul><p>Same logic applies to Read IOPS PromQL</p><p>These PromQL will give use no. of IO Blocks being read or written to the Disk. If we multiply this with Block Size (8KB) then we will get the Read Throughput and Write Througput in kB/s</p><blockquote>Write Throughput in kB/s:</blockquote><blockquote><strong><em>sum(rate(pg_io_writes_total[1m])) by (backend_type) * 8</em></strong></blockquote><blockquote>Read Throughput in kB/s:</blockquote><blockquote><strong><em>sum(rate(pg_io_reads_total[1m])) by (backend_type) * 8</em></strong></blockquote><h3>Summary</h3><p>In conclusion, monitoring IOPS in PostgreSQL is crucial for optimizing database performance, especially under heavy write loads. By leveraging Prometheus and Grafana alongside PostgreSQL 16’s pg_stat_io, you can achieve centralized monitoring, visual analytics, and proactive alerting for IOPS. This setup not only enhances your ability to track I/O operations but also provides actionable insights into backend activities, enabling you to identify and mitigate potential bottlenecks. If you found this article helpful, please share your thoughts or experiences in the comments below. For those interested in diving deeper, I recommend exploring the GitHub repository linked in the article for setting up Grafana dashboards tailored to PostgreSQL I/O monitoring.</p><h3><strong>References</strong></h3><ul><li>Github: <a href="https://github.com/varun-2108/postgres-16-iops">https://github.com/varun-2108/postgres-16-iops</a></li><li><a href="https://pganalyze.com/blog/pg-stat-io">https://pganalyze.com/blog/pg-stat-io</a></li><li><a href="https://andyatkinson.com/blog/2023/11/01/PostgreSQL-IO-Visibility-wehack-pg_stat_io">https://andyatkinson.com/blog/2023/11/01/PostgreSQL-IO-Visibility-wehack-pg_stat_io</a></li><li><a href="https://www.cybertec-postgresql.com/en/pg_stat_io-postgresql-16-performance/">https://www.cybertec-postgresql.com/en/pg_stat_io-postgresql-16-performance/</a></li><li><a href="https://www.crunchydata.com/blog/understanding-postgres-iops">https://www.crunchydata.com/blog/understanding-postgres-iops</a></li><li><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW">https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW</a></li><li><a href="https://www.ongres.com/blog/create-prometheus-integrated-postgres-custom-metrics/">https://www.ongres.com/blog/create-prometheus-integrated-postgres-custom-metrics/</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d75cf6b51538" width="1" height="1" alt=""><hr><p><a href="https://blog.devops.dev/supercharge-your-postgres-monitoring-iops-with-prometheus-and-grafana-d75cf6b51538">Gain Unparalleled Insights into Your Postgres: IOPS Monitoring Made Easy</a> was originally published in <a href="https://blog.devops.dev/">DevOps.dev</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Guide to Resilient 3rd Party Webhook]]></title>
            <link>https://medium.com/@varunjain2108/guide-to-resilient-3rd-party-webhook-207f5086b5f7?source=rss-6d1f4be183a3------2</link>
            <guid isPermaLink="false">https://medium.com/p/207f5086b5f7</guid>
            <category><![CDATA[resiliency-patterns]]></category>
            <category><![CDATA[consumer]]></category>
            <category><![CDATA[webhooks]]></category>
            <category><![CDATA[monitoring]]></category>
            <category><![CDATA[kafka]]></category>
            <dc:creator><![CDATA[Varun Jain]]></dc:creator>
            <pubDate>Thu, 28 Dec 2023 19:28:40 GMT</pubDate>
            <atom:updated>2023-12-28T19:28:40.541Z</atom:updated>
            <content:encoded><![CDATA[<h4><strong>Introduction:</strong></h4><p>In the ever-evolving landscape of digital connectivity, third-party webhooks play a pivotal role in enabling seamless integrations between disparate systems. These dynamic communication channels provide a mechanism for real-time data exchange, powering a myriad of applications, services, and workflows. However, with great connectivity comes the need for robustness and resilience.</p><h3>Vendor-to-Service Communication</h3><p>For third-party vendor-to-service integration, the vendor sometimes cannot push messages to our topic directly due to compliance, township, etc. Instead, we use a proxy webhook server. The vendor sends the message to the proxy server, which then pushes the message to our topic.</p><p><strong>This process involves the following steps:</strong></p><ul><li><strong>Message Creation</strong>: The vendor creates a message based on a specific event or trigger.</li><li><strong>Proxy Server Push</strong>: !!Respond quickly!!<br> → The vendor sends the message to our proxy webhook server HTTPS authenticated requests.<br> → This layer should be as thin as possible with minimal latency and high throughput.<br> → The webhook server validates the vendor credentials and message format.<br> → Webhook Endpoint should always be prepared for a Burst of Requests.<br> → If webhooks are very crucial like payment events, in such cases, we can’t afford the downtime of the Kafka. Assuming, we can’t rely on the Vendor to retry the webhooks or the Vendor retries the webhook after some time that may be greater than or defined SLO for the payment processing. To handle such cases, we can dump the incoming events to Databases like RDS in case of Kafka failure and process those events back when Kafka comes back.</li><li><strong>Topic Push</strong>: The proxy server pushes the message to our topic. Validated events are published to the Kafka topic for downstream processing. This approach decouples vendors from the internal Kafka infrastructure.</li><li><strong>Message Processing</strong>: Consumers can directly subscribe to the Kafka topic. Consumer processes the received message and performs the necessary actions.</li></ul><pre>Centralized Proxy Webhook Server?<br><br>The centralized Proxy Webhook server may become the Single Point of Failure <br>and bottleneck it one of the external Vendors tries to abuse this centralized <br>Proxy Service.</pre><p>Solution 1: This solution should fit most of the use cases we have, provided all of our vendors have a reliable retry mechanism. See the image below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1Bvcv-GM5K75d3XVcVf_lg.png" /></figure><p>Solution 2: For a robust and reliable system. The probability of failing both DB and Kafka are very low. See the image below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8dd9SAM8CyecfoZP12jtSw.png" /></figure><h3>Let’s discuss some use cases:</h3><p><strong>Use Case 1: Global Topic for generic webhook events</strong></p><p>In this scenario, you have events that are general and can be consumed by various consumers or applications for different purposes. These events are not specific to any particular business unit or use case. Here’s how you can structure your Kafka topics:</p><p>Example: User deletion request from the Ecom App.</p><ul><li>The customer places the deletion request from the App.</li><li>After the customer deletion, all the services in the Ecom Backend like Payment Service, Recommendation Service, Logistics Service, etc want to listen to these customer deletion events.</li><li>In this use case, we can create the topic let’s say customer.delete, and interested consumers can start consuming from this topic as per their requirements.</li></ul><p><strong>Use Case 2: Business-Specific Topics</strong></p><p>In this scenario, you have events that are highly specific to different business units or use cases within your organization. These events may not be relevant, non-compliant or useful to other parts of the organization. Here’s how you can structure your Kafka topics:</p><p>Example: Order Generator Service sends the event upon order creation to Payment Service, Order History Service.</p><ul><li>In this use case, we can’t send the order creation event to all the services. Only concerned service should consume these events</li><li>For such cases, we can create the topic for each business flow.</li><li>This approach will ensure that we are not exposing the PII data that belongs to a specific service.</li></ul><h3>Decide No. of Partition</h3><ul><li>Use this tool: <a href="https://eventsizer.io/partitions">https://eventsizer.io/partitions</a></li></ul><h3>Producer Side Implementation</h3><ul><li><strong>Producer Configuration:</strong> Configure Kafka producer settings, including broker addresses and delivery guarantees. A few important configurations to consider:</li><li><a href="https://docs.confluent.io/platform/current/installation/configuration/producer-configs.html">Kafka producer configuration reference | Confluent Documentation</a></li><li>buffer.memory, retries, batch.size, delivery.timeout.ms, acks</li></ul><h3>Consumer Side Implementation</h3><ul><li><strong>Consumer configuration:</strong> <a href="https://docs.confluent.io/platform/current/installation/configuration/consumer-configs.html">Kafka Consumer configuration reference | Confluent Documentation</a><br>fetch.min.bytes, heartbeat.interval.ms, max.partition.fetch.bytes, session.timeout.ms</li><li><strong>Consumer Groups:</strong> Organize consumers into groups for parallel message processing.</li><li><strong>Ordering: </strong>Consumer should be implemented to process out-of-order messages. There shouldn’t be any dependency on the order of the messages.</li><li><strong>Idempotency:</strong> All consumers should be idempotent. Assuming producers may publish duplicate events.</li><li><strong>Consumer Error Handling:</strong> Define error handling mechanisms, including retries and error logging.</li><li><strong>Retry Mechanism:</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VD3xqcLCopuz3LrH3oIv5Q.png" /></figure><p>Details about the image:</p><ul><li><strong>Primary Consumer</strong>:<br>- You have a primary consumer who listens to a target topic in Kafka.<br>- The primary consumer is responsible for processing messages from this topic.</li><li><strong>Processing and Error Handling</strong>:<br>- When the primary consumer processes a message from the target topic, it may encounter an error during processing. This could be due to various reasons such as temporary issues, network problems, or data validation errors.</li><li><strong>Retry Mechanism</strong>:<br>- When an error occurs during processing, the primary consumer doesn’t immediately give up on the message.<br>- Instead, it increments a “retry_count” header in the Kafka message. This header keeps track of how many times the message has been retried.</li><li><strong>Retry Limit</strong>:<br>- The primary consumer will continue to process the message as long as the “retry_count” header is less than a certain threshold, often referred to as the “retry limit” (in your case, 3).</li><li><strong>Retrying the Message</strong>:<br>- If the “retry_count” is less than the retry limit, the primary consumer produces the message back to the same target topic.<br>- The message essentially goes back to the beginning of the processing pipeline and will be reprocessed.</li><li><strong>Retry Delays</strong>:<br>- In some implementations, you might introduce delays between retries to avoid overwhelming the system. For example, you can wait for a specific amount of time (exponential backoff) before retrying the message.</li><li><strong>DLQ Handling</strong>:<br>- If the “retry_count” exceeds the retry limit without successful processing, it indicates that the message cannot be processed successfully even after multiple attempts.<br>- At this point, the primary consumer produces the message to a Dead Letter Queue (DLQ) topic.<br>- The DLQ may include additional information, such as the error message that provides details about why the message couldn’t be processed.</li><li><strong>DLQ for Further Analysis</strong>:<br>- The DLQ allows you to capture and analyze failed messages, helping you understand why they couldn’t be processed.<br>- It can be useful for debugging, auditing, or manual intervention.</li></ul><p>This retry mechanism helps improve the robustness of your event processing system by allowing temporary errors to be resolved through retries while ensuring that problematic messages are eventually moved to a DLQ for further investigation or handling.</p><p>Using Kafka message header to keep track of the Retry count:</p><p><a href="https://alexandrecastro.tech/post/kafka-retries-go/">Kafka Retries: Implementing Consumer Retry with Go | Alexandre Castro</a></p><h3>Ownership and Responsibilities</h3><ul><li><strong>Topic Ownership: </strong>The topic should be owned by the service who are producing the events. In a lot of cases, there will be one producer and multiple consumers of the same topic.</li><li><strong>Responsibilities and Maintenance:<br>- </strong>Retention policy<br>- Data Ownership<br>- Managing the partition count. Partition count is directly related to the consumer scaling and throughput.</li></ul><h3>Monitoring and Observability</h3><p>Kafka Producer Metrics</p><ul><li>Producer throughput</li></ul><h4>Webhook Server Metrics</h4><ul><li>Monitor the health and performance of the existing webhook server, including request rates, response times, and error rates</li></ul><h4>Consumer Application Metrics</h4><ul><li>Monitor the performance and health of downstream applications consuming data from Kafka topics.</li><li>Track metrics like processing latency, error rates, and consumer lag.</li><li>How to track Error Rates?<br>- We can capture the custom metrics on the consumer side like success_count, failure_count, status_code</li></ul><h4>Alerting</h4><ul><li>Setup alerting on the error rate, consumer lag</li></ul><h3>References:</h3><ul><li><a href="https://blog.pragmatists.com/retrying-consumer-architecture-in-the-apache-kafka-939ac4cb851a">Retrying consumer architecture in the Apache Kafka</a></li><li><a href="https://www.confluent.io/blog/error-handling-patterns-in-kafka/">Error Handling Patterns in Kafka</a></li><li><a href="https://alexandrecastro.tech/post/kafka-retries-go/">Kafka Retries: Implementing Consumer Retry with Go | Alexandre Castro</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=207f5086b5f7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Monitoring Docker Containers with cAdvisor, Prometheus, and Grafana]]></title>
            <link>https://medium.com/@varunjain2108/monitoring-docker-containers-with-cadvisor-prometheus-and-grafana-d101b4dbbc84?source=rss-6d1f4be183a3------2</link>
            <guid isPermaLink="false">https://medium.com/p/d101b4dbbc84</guid>
            <category><![CDATA[cadvisor]]></category>
            <category><![CDATA[metrics]]></category>
            <category><![CDATA[observability]]></category>
            <category><![CDATA[docker]]></category>
            <category><![CDATA[grafana-prometheus]]></category>
            <dc:creator><![CDATA[Varun Jain]]></dc:creator>
            <pubDate>Thu, 28 Dec 2023 13:20:17 GMT</pubDate>
            <atom:updated>2023-12-28T13:35:37.160Z</atom:updated>
            <content:encoded><![CDATA[<h3>Monitoring Docker Containers with cAdvisor, Prometheus, and Grafana Using Docker Compose</h3><h4>Introduction</h4><p>This article introduces a trio — cAdvisor for real-time container metrics, Prometheus for data aggregation and storage, and Grafana for insightful visualization. We’ll guide you through setting up a monitoring system for your Docker containers using Docker Compose.</p><h4>Understanding the Tools</h4><ul><li><a href="https://github.com/google/cadvisor">cAdvisor</a>: cAdvisor (Container Advisor) is an open-source tool by Google that provides container users with an understanding of the resource usage and performance characteristics of their running containers, offering detailed real-time metrics about CPU, memory, file and network usage.</li><li><a href="https://github.com/prometheus/prometheus">Prometheus</a>: Prometheus is an open-source monitoring and alerting toolkit that collects and stores its metrics as time series data, allowing for powerful querying, alerting, and visualization of metrics from various sources including Docker containers.</li><li><a href="https://github.com/grafana/grafana">Grafana</a>: Grafana is a highly versatile open-source analytics and monitoring solution that allows you to query, visualize, alert on, and understand your metrics no matter where they are stored, making it an ideal tool for creating insightful and interactive dashboards based on data aggregated by Prometheus.</li></ul><p>Note: For the following setup I am using Mac M1</p><p>Let’s start with cAdvisor <strong><em>docker-compose.yaml</em></strong></p><pre>version: &#39;3&#39;<br><br>services:<br>  cadvisor:<br>      image: gcr.io/cadvisor/cadvisor:v0.47.1<br>      hostname: cadvisor<br>      platform: linux/aarch64<br>      volumes:<br>        - &quot;/:/rootfs:ro&quot;<br>        - &quot;/var/run:/var/run:ro&quot;<br>        - &quot;/sys:/sys:ro&quot;<br>        - &quot;/var/lib/docker/:/var/lib/docker:ro&quot;<br>        - &quot;/dev/disk/:/dev/disk:ro&quot;<br>      ports:<br>        - &quot;8080:8080&quot;</pre><p>Now, run the following command</p><blockquote><strong>docker compose -f docker-compose.yaml up</strong></blockquote><p>Open the cAdvisot UI in your browser: <a href="http://http//0.0.0.0:8080">HTTP://0.0.0.0:8080</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BrrPFQdaJ1yzF5l_NXSoqA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/894/1*rckjqNuRAkC033C7q8AylQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/926/1*9jzZwmxirm1xexAR4-nbbw.png" /></figure><p>Whenever cAdvisor detects the new container running on the host machine, it will automatically start capturing the metrics of the new container.</p><p>cAdvisor itself does not have built-in storage for the metrics it collects. Instead, cAdvisor is primarily used for real-time monitoring of container performance, gathering detailed information about resource usage (like CPU, memory, network, and storage) for running containers.</p><p>Metrics exposed by the cAdvisor: <a href="http://http//0.0.0.0:8080/metrics">HTTP://0.0.0.0:8080/metrics</a></p><p>While configuring the Prometheus we will use the above /metrics endpoint for the scraping purpose.</p><h4>Setup Prometheus</h4><p>To store the metrics collected by cAdvisor over time, you would typically integrate it with a time-series database like Prometheus. Prometheus can scrape and store the data provided by cAdvisor, allowing for historical data analysis, querying, and alerting. This integration is a common setup for container monitoring, where cAdvisor collects the metrics, and Prometheus stores them.</p><p>Let’s set up the Prometheus using the same docker-compose.yaml</p><p>Update docker-compose.yaml with Prometheus</p><pre>version: &#39;3&#39;<br><br>services:<br>  cadvisor:<br>      image: gcr.io/cadvisor/cadvisor:v0.47.1<br>      hostname: cadvisor<br>      platform: linux/aarch64<br>      volumes:<br>        - &quot;/:/rootfs:ro&quot;<br>        - &quot;/var/run:/var/run:ro&quot;<br>        - &quot;/sys:/sys:ro&quot;<br>        - &quot;/var/lib/docker/:/var/lib/docker:ro&quot;<br>        - &quot;/dev/disk/:/dev/disk:ro&quot;<br>      ports:<br>        - &quot;8080:8080&quot;<br>  <br>  prometheus:<br>    image: prom/prometheus<br>    ports:<br>      - &quot;9090:9090&quot;</pre><blockquote>Run the <strong>docker compose -f docker-compose.yaml up </strong>again<strong>.</strong></blockquote><p>Open the Prometheus UI: <a href="http://http//0.0.0.0:9090">HTTP://0.0.0.0:9090</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gjmOpzYjTDa4XqSUa5NkqA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mJ1vLtAjJUh-D4S8DmgD3Q.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-rlc4uMqiKdzKOVd2Asl5g.png" /></figure><p>Next step: Configure the Prometheus to scrape metrics exposed by the cAdvisor.</p><p>Create the prometheus-scrape-config.yaml like this in the same directory as docker-compose.yaml file.</p><pre>global:<br>  scrape_interval:     15s<br>scrape_configs:<br>  - job_name: &#39;cadvisor&#39;<br>    scrape_interval: 5s<br>    static_configs:<br>      - targets: [&#39;cadvisor:8080&#39;]</pre><p>This scrape config file will pull the metrics from the advisor docker container exposed through port <strong><em>8080</em></strong>, endpoint <strong><em>/metrics</em></strong> every <strong><em>5s</em></strong>.</p><p>Update the <strong><em>docker-compose.yaml</em></strong> file. Mount the <strong><em>prometheus-scrape-config.yaml</em></strong> using the docker volume like this</p><pre>version: &#39;3&#39;<br><br>services:<br>  cadvisor:<br>      image: gcr.io/cadvisor/cadvisor:v0.47.1<br>      hostname: cadvisor<br>      platform: linux/aarch64<br>      volumes:<br>        - &quot;/:/rootfs:ro&quot;<br>        - &quot;/var/run:/var/run:ro&quot;<br>        - &quot;/sys:/sys:ro&quot;<br>        - &quot;/var/lib/docker/:/var/lib/docker:ro&quot;<br>        - &quot;/dev/disk/:/dev/disk:ro&quot;<br>      ports:<br>        - &quot;8080:8080&quot;<br>  <br>  prometheus:<br>    image: prom/prometheus<br>    volumes:<br>      - ./prometheus.yml:/etc/prometheus/prometheus-scrape-config.yaml<br>    ports:<br>      - &quot;9090:9090&quot;</pre><p>Restart the dockers services again and Open the Prometheus UI: <a href="http://http//0.0.0.0:9090">HTTP://0.0.0.0:9090</a>. Now we should be able to see cAdvisor in the target and service discovery section of the Prometheus UI. Refer to the images below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*m4i09F9_iMwKjumZOlDoOQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*51on41dgTYoWbbf22aoL1Q.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Zb2j2XaDdKRhpGO_-d_aog.png" /></figure><p>Go, and play with the Prometheus metrics query explorer.</p><p>Click here for <a href="http://0.0.0.0:9090/graph?g0.expr=container_memory_usage_bytes&amp;g0.tab=0&amp;g0.stacked=0&amp;g0.show_exemplars=0&amp;g0.range_input=1h">Sample Metrics Link</a></p><h4>What we have achieved so far?</h4><ul><li>cAdvisor is running locally and capturing all the real-time metrics using the docker-compose.</li><li>Prometheus is running locally and configured to scrape metrics from the cAdvisor /metrics endpoint and dump them into the Prometheus Time Series DB.</li></ul><h4>Setup Grafana</h4><p>Although Prometheus excels in data aggregation and storage, it offers limited visualization features. For advanced visualization, we’ll use Grafana, renowned for its comprehensive and interactive dashboards. Grafana transforms Prometheus metrics into detailed, easily interpretable visual formats, enhancing our monitoring capabilities.</p><p>Update the docker-compose.yaml file to add grafan container</p><pre>version: &#39;3&#39;<br><br>services:<br>  cadvisor:<br>      image: gcr.io/cadvisor/cadvisor:v0.47.1<br>      hostname: cadvisor<br>      platform: linux/aarch64<br>      volumes:<br>        - &quot;/:/rootfs:ro&quot;<br>        - &quot;/var/run:/var/run:ro&quot;<br>        - &quot;/sys:/sys:ro&quot;<br>        - &quot;/var/lib/docker/:/var/lib/docker:ro&quot;<br>        - &quot;/dev/disk/:/dev/disk:ro&quot;<br>      ports:<br>        - &quot;8080:8080&quot;<br>  <br>  prometheus:<br>    image: prom/prometheus<br>    volumes:<br>      - ./prometheus.yml:/etc/prometheus/prometheus-scrape-config.yaml<br>    ports:<br>      - &quot;9090:9090&quot;<br>  <br>  grafana:<br>    image: grafana/grafana<br>    environment: <br>      GF_SECURITY_DISABLE_INITIAL_ADMIN_CREATION: &quot;true&quot;<br>      GF_AUTH_ANONYMOUS_ENABLED: &quot;true&quot;<br>      GF_AUTH_ANONYMOUS_ORG_ROLE: &quot;Admin&quot;<br>      GF_AUTH_DISABLE_SIGNOUT_MENU: &quot;true&quot;<br>      GF_AUTH_DISABLE_LOGIN_FORM: &quot;true&quot;<br>    ports:<br>      - &quot;9100:3000&quot;</pre><blockquote>Run the <strong>docker compose -f docker-compose.yaml up </strong>again<strong>.</strong></blockquote><p>Open the Grafana UI: <a href="http://http//0.0.0.0:9100">HTTP://0.0.0.0:9100</a></p><p>Now, we need to configure the Datasource as Prometheus in the Grafana.</p><p>Prometheus is our metrics storage layer and Grafana is the visualization tool. Grafana can configured with different types of data sources like Postgres, AWS Cloudwatch, Datadog, ElasticSearch, etc. Feel free to explore the Data source here: <a href="http://0.0.0.0:9100/connections/add-new-connection"><strong><em>http://0.0.0.0:9100/connections/add-new-connection</em></strong></a></p><p>For integrating Prometheus with Grafana, we have two options: manually adding the Prometheus data source through Grafana’s UI, or automating the setup using a grafana-datasource-config.yaml file. This file can be configured to automatically establish the Prometheus data source when executing docker-compose up, streamlining the integration process.</p><p>Create the <strong><em>grafana-datasource-config.yaml</em></strong> in the same directory as docker-compose.yaml</p><pre>apiVersion: 1<br><br>datasources:<br>  - name: Prometheus<br>    type: prometheus<br>    access: proxy<br>    url: http://prometheus:9090<br>    isDefault: true</pre><p>Now, we have to tell the Grafana docker service to use this file during the service boot-up.</p><p>Final updated docker-compose.yaml file</p><pre>version: &#39;3&#39;<br><br>services:<br>  cadvisor:<br>      image: gcr.io/cadvisor/cadvisor:v0.47.1<br>      hostname: cadvisor<br>      platform: linux/aarch64<br>      volumes:<br>        - &quot;/:/rootfs:ro&quot;<br>        - &quot;/var/run:/var/run:ro&quot;<br>        - &quot;/sys:/sys:ro&quot;<br>        - &quot;/var/lib/docker/:/var/lib/docker:ro&quot;<br>        - &quot;/dev/disk/:/dev/disk:ro&quot;<br>      ports:<br>        - &quot;8080:8080&quot;<br>  <br>  prometheus:<br>    image: prom/prometheus<br>    volumes:<br>      - ./prometheus.yml:/etc/prometheus/prometheus-scrape-config.yaml<br>    ports:<br>      - &quot;9090:9090&quot;<br>  <br>  grafana:<br>    image: grafana/grafana<br>    environment: <br>      GF_SECURITY_DISABLE_INITIAL_ADMIN_CREATION: &quot;true&quot;<br>      GF_AUTH_ANONYMOUS_ENABLED: &quot;true&quot;<br>      GF_AUTH_ANONYMOUS_ORG_ROLE: &quot;Admin&quot;<br>      GF_AUTH_DISABLE_SIGNOUT_MENU: &quot;true&quot;<br>      GF_AUTH_DISABLE_LOGIN_FORM: &quot;true&quot;<br>    volumes:<br>      - ./datasource.yaml:/etc/grafana/provisioning/datasources/datasource.yaml<br>    ports:<br>      - &quot;9100:3000&quot;</pre><blockquote>Run the <strong>docker compose -f docker-compose.yaml up </strong>again<strong>.</strong></blockquote><p>We should be able to see the Prometheus DS here <a href="http://0.0.0.0:9100/connections/datasources">http://0.0.0.0:9100/connections/datasources</a></p><h4>Final Step</h4><p>Let’s create the Dashboard on the Grafana.</p><ul><li>Open: <a href="http://0.0.0.0:9100/dashboard/import">http://0.0.0.0:9100/dashboard/import</a></li><li>Load the Grafana Dashboard ID = 193, taken from here: <a href="https://grafana.com/grafana/dashboards/193-docker-monitoring/">https://grafana.com/grafana/dashboards/193-docker-monitoring/</a></li><li>Select the DS as Prometheus. Hit Save.</li><li>Voila, we have the Grafana dashboard showing the real-time metrics of the containers.</li><li>Refer, to the images below.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CAFJC92lVMp_gQR9XTGF1Q.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/872/1*1DwfqYPtsUKADa64j5MvYA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qOzUMIJeW1zO9vgdliFswQ.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d101b4dbbc84" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>