Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cvndo-0004Ie-Ge for pgsql-admin@arkaria.postgresql.org; Wed, 05 Apr 2017 16:18:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cvndn-0000Qh-C3 for pgsql-admin@arkaria.postgresql.org; Wed, 05 Apr 2017 16:18:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cvnby-0000mF-8g for pgsql-admin@postgresql.org; Wed, 05 Apr 2017 16:16:46 +0000 Received: from mail-vk0-x243.google.com ([2607:f8b0:400c:c05::243]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cvnbu-0006tN-Ej for pgsql-admin@postgresql.org; Wed, 05 Apr 2017 16:16:45 +0000 Received: by mail-vk0-x243.google.com with SMTP id y16so1347142vky.1 for ; Wed, 05 Apr 2017 09:16:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:subject:date:message-id:mime-version:thread-index :content-language; bh=dVCwgz8v1Kx6KK5B2bKlHZ1IQQNiE+lGNl4ufT2OLaw=; b=SvDF/ONCpoFR+vlyqbL+SmWUsMKnn+E0678LxXMFcHodsqd/QbYhyZE+YuEc30furt qo15f/IwXVNsV0JdTUnwURXMl+429kMyvx3h9r3TAMujRvbj6zL6YB5af3//czph88VG nNln/VFLAjFTQtp8vTkaFpoWgbsDfBtb0rzzOp62GS+haP9pHFnU0gkFZBb2cpXEKmaK iFbdB/sPJUHpVBRhrcHU+AuWngVviw3YjvJtaTm2OUcV1W5FlHbDr9qIUGFbkekcFl+C RBD5XdCmIPGTN49AgqtePotRZ8kEREnni6VrofAE2WWwwzwSYb2H8a6R/i7bTrLT5Bf7 VxfA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:subject:date:message-id:mime-version :thread-index:content-language; bh=dVCwgz8v1Kx6KK5B2bKlHZ1IQQNiE+lGNl4ufT2OLaw=; b=DT+PwKBqwIXjFvBGTALjuO0+Bh8Fzd3dSuUWLym5XBxD3uahi0K2p+rIoIq9sw7Zbj EIwIz4tRcVGWJG0HREgUItIl6fsFmFWsWQ5KahDXEB22XraPjw5xtxgAXPbxpE0MceWm N+Y3YbdlEHmvr01LXEXRpLUeXY3s4nK0fXmU2LBRBCPoOQSbBMiPQIxXWMr6yyWrqpqF Efmq16up9tFNsjNH2X3N2cOp5meJzdziqk0A1fvICKSIgIs7LckZgY+1Fl5Qo6PUK7P3 GxYk47tHacY5YwImZyT/z69jvUT7cs3PrKOcb+sv4BBew1ayqgBomTBQix3DHmH3Ipos fVeA== X-Gm-Message-State: AFeK/H3VgIXLtBlXr6I+pSU/wPBTUMeUGBwX1hcsGOVTBBJjZ2FLB/dyhIhb0aB1b4Y8ZA== X-Received: by 10.159.38.48 with SMTP id 45mr12264280uag.52.1491409000355; Wed, 05 Apr 2017 09:16:40 -0700 (PDT) Received: from irylachi ([200.55.161.75]) by smtp.gmail.com with ESMTPSA id l94sm4071175ual.1.2017.04.05.09.16.38 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Wed, 05 Apr 2017 09:16:39 -0700 (PDT) From: "Lazaro Garcia" To: Subject: What is the relationship between checkpoint_completion_target and the OS write cache (dirty_background_ratio, dirty_ratio )? Date: Wed, 5 Apr 2017 12:16:35 -0400 Message-ID: <002201d2ae28$00f00320$02d00960$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0023_01D2AE06.79E0D420" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AdKuJ/IFEBeea7WfSE+fVP/vk0xaxg== Content-Language: es-mx X-Pg-Spam-Score: -2.4 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_0023_01D2AE06.79E0D420 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello everyone, checking the checkpoints theme and the configuration of memory buffers for writing on to the operating system (vm.dirty_background_ratio and Vm.dirty_ratio = 2), I wanted to know what the relationship exists between these parameters and checkpoint_completion_target, because in this link https://blog.2ndquadrant.com/basics-of-tuning-checkpoints/ (quite interesting) mention that they should Configure these kernel parameters to prevent many dirty pages from accumulating in the cache. If synchronous_commit = on, it means that each write action will be persisted in the redo logs before the final commit of the transaction, and after a checkpoint all the generated Wal will be synchronized with the data files, then in this scenario: The bgwriter plays some role during bgwriter_delay by writing the changes of the shared buffers directly to the data files or the write cache of the operating system or simply does nothing in synchronous commit mode. Is Wal_buffers used in synchronous mode? If after each commit an fsync is executed to write to disk the data generated by the transaction, what information is stored in the operating system's write cache, and how much can this value grow if there is write after each transaction? Any documentation related to these issues will be well appreciated. Regards and many thanks to all for your time. ------=_NextPart_000_0023_01D2AE06.79E0D420 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello everyone, checking the = checkpoints theme and the configuration of memory buffers for writing on = to the operating system (vm.dirty_background_ratio = and

Vm.dirty_ratio =3D 2), I wanted to know what the = relationship exists between these parameters and = checkpoint_completion_target, because in this link = https://blog.2ndquadrant.com/basics-of-tuning-checkpoints/ (quite = interesting) mention that they should Configure these kernel parameters = to prevent many dirty pages from accumulating in the = cache.

 

If synchronous_commit =3D on, it means that each write = action will be persisted in the redo logs before the final commit of the = transaction, and after a checkpoint all the generated Wal will be = synchronized with the data files, then in this = scenario:

 

The bgwriter plays some role during bgwriter_delay by = writing the changes of the shared buffers directly to the data files or = the write cache of the operating system or simply does nothing in = synchronous commit mode.

 

Is Wal_buffers used in synchronous = mode?

 

If after each commit an fsync is executed to write to disk = the data generated by the transaction, what information is stored in the = operating system's write cache, and how much can this value grow if = there is write after each transaction?

 

Any documentation related to these = issues will be well appreciated.

 

Regards and many thanks to all for = your time.

------=_NextPart_000_0023_01D2AE06.79E0D420--