Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6dsK-001mwe-5I for pgsql-general@arkaria.postgresql.org; Fri, 24 Nov 2023 21:38:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r6dsH-00FWZ7-Lh for pgsql-general@arkaria.postgresql.org; Fri, 24 Nov 2023 21:38:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6dsH-00FWYz-2B for pgsql-general@lists.postgresql.org; Fri, 24 Nov 2023 21:38:09 +0000 Received: from wout5-smtp.messagingengine.com ([64.147.123.21]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6ds9-007CQr-Op for pgsql-general@postgresql.org; Fri, 24 Nov 2023 21:38:07 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.west.internal (Postfix) with ESMTP id 0861A3200A72; Fri, 24 Nov 2023 16:37:59 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Fri, 24 Nov 2023 16:38:00 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:sender :subject:subject:to:to; s=fm3; t=1700861879; x=1700948279; bh=/m quh2wfUhmLh9cS8W41w8g/0yBku7pScJ4/7KGSBgE=; b=XOVuuKh7UYFFd+03SE cS6sKYLl4LRkLvb0Dfgl+1VUOekKHMJS6mS4IPKvbu6+9Y+0GStHn9R2FTXhM/oz gfxYbz9HJBHSd6pv/7H1ucbwfnd9b0AlD+URDupTxR34HbcbrUm0n8g6pwpQNMnB M2EY+j1p3prPYZiaKHx10HfVTOQ4hnZt/gMI3kFvXhJnBbm72wfcRQeY9Fdh44oh j7tWKi+wdP5ieWQ1zcRESXWIBsMEwMrB63STwsC2oM10xgjCQ6z4aucrwfnrqhFx caBhvEpsh894Q4VOjjnlLtaUYcj4oh0pG4wmjWDBeLvhFWh+0sC8Ie9C2OzIpTJD ws6g== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm1; t=1700861879; x=1700948279; bh=/mquh2wfUhmLh 9cS8W41w8g/0yBku7pScJ4/7KGSBgE=; b=MjElNcMrYecwyijzuk6jqWqmUj1Ea qwrfkJIQj/MGjsU869xI9OFVpTO5gF7xaF/RomoCthEzw3VOgluAHz80A/zxy1nm dt86io56vwL9KjbkTCm5vuRZGyt9ODzb54Dzu/w5BMNJZohZh+1cXB5DcgnxwLdj 4g9y2bNtvPiV1aFzyUbu0KoF/j/5g1hkul2Tv21bM1yl13uOIhmFeBILbqYTW+aR l4b44mLlX5Pj/OHXACqgLPGP1cQHLQy2RckrQuIW4/D+5YoO+rmYDe2xvUNvPcPL pa+iGLj0hDpfNujOX8c/3xn43vgqhZeFbhFAEv3t4n9VWqgyuHuqXZuVg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrudehhedgudehudcutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurheptgfkffggfgfuvfevfhfhjgesrg dtreertddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdr khhlrghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpedukeejgf dtfeekvefhueekvefhheefveefkeelvddvffeukeekueeiieffudejteenucffohhmrghi nhepphhoshhtghhrvghsqhhlrdhorhhgnecuvehluhhsthgvrhfuihiivgeptdenucfrrg hrrghmpehmrghilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdr tghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Fri, 24 Nov 2023 16:37:58 -0500 (EST) Content-Type: multipart/alternative; boundary="------------FtTr8fIAB8Qwhy0NF5wzh1bN" Message-ID: <592a4e9d-0d12-40ac-ad2c-0ddc4caa760a@aklaver.com> Date: Fri, 24 Nov 2023 13:37:56 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: replication primary writting infinite number of WAL files To: Les Cc: pgsql-general References: <38f7d87c-ae22-48e8-a4c4-0acde1ad6eb9@aklaver.com> <430d83f0-65ff-47b0-9600-26094099fc17@aklaver.com> <1aec39e7-f64e-4afc-8ab2-3c723dec2b0b@aklaver.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------FtTr8fIAB8Qwhy0NF5wzh1bN Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/24/23 1:05 PM, Les wrote: > > > >> Sequence is incremented by 100,  so for example, between > 2023-11-24 > >> 10:20:00 and 2023-11-24 10:21:00 it went up 62188671 steps. I > think it > >> is not possible to insert 62188671 rows into a table. A psql > function > >> might be able to increment a sequence 62M times / minute, I'm > not sure. > > Am I correct in assuming id has as it's default > nextval()? > > All primary keys are "id int8 NOT NULL". They don't have a default, > but all of these identifiers are generated from the same (global) > sequence. We are using a single global sequence, because there are > only a few writes. 99% of I/O operations are read. A single sequence for all id columns across all tables? How is the sequence value landing in the id column? > > If so it would seem to me something was doing a lot of INSERTS > between > 2023-11-24 10:20:00.000 and 2023-11-24 10:21:00.000. > > Yes. But is it possible to insert 62M rows within 1 minute? Is has not been determined yet that 62 million inserts where done, just that the sequence jumped by that number of steps. > > It might be possible to "select nextval" 1M times per second from an > Pl/SQL function that does nothing else in an infinite loop. But that > would not write too much data on the disk. On an old laptop: create table insert_test(id int); insert into insert_test select val from generate_series(1, 1000000) as t(val); INSERT 0 1000000 Time: 943.918 ms I would  say there is more then just the id being inserted, unless all the other fields allow NULL. > > And there is nothing in the logs in that time period besides > "checkpoints are happening too frequently"? > > > > The "set transaction" warning is due to a bug in an application that > calls SET TRANSACTION before starting a transaction. And what is that app doing when it does SET TRANSACTION? > > > https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT > > log_statement > > set to at least 'mod'? > > Unfortunately we don't, but now that you asked, we will turn this on. > > > > >> We are using dev databases that are created from snapshots of the > >> standby. There is a possibility that a dev database instance > (created > >> from a snapshot of the standby) might have connected the > primary just > >> before it was reconfigured to be standalone. Can this be a problem? > > Was your original report for the dev databases also? > > No, every log and metric was for the prod db. So how do the dev databases enter into this" > > How are the snapshots being taken? > > zfs snapshot is taken on the standby, then zfs clone is created on the > snapshot, and a new postgresql instance is started on top of the > clone. It recovers within one minute. In a very few cases (two or > three times in a year), it fails to recover. Then we use a different > snapshot. When the dev instance starts up, then we immediately delete > everything from repmgr nodes, and disable repmgr completely. Today we > noticed that the dev db was created in a network that made it possible > to connect to the prod primary. (dev db's network was not separated > from the prod db's network, fixed today). The dev db might have > connected to the prod/primary after startup. But that dev instance was > created 7 days ago, so probably it is not related. What I know about ZFS would fit in the navel of flea, so someone else will have to comment on this. Dev connected to prod/primary how? > > Actually, we need this standby because we are using zfs snapshots for > making frequent database backups. Without this standby, we can't make > frequent backups. (The primary and the other standby are not using zfs.) > > Thanks, > >     Laszlo > > --------------FtTr8fIAB8Qwhy0NF5wzh1bN Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 11/24/23 1:05 PM, Les wrote:

>> Sequence is incremented by 100,  so for example, between 2023-11-24
>> 10:20:00 and 2023-11-24 10:21:00 it went up 62188671 steps. I think it
>> is not possible to insert 62188671 rows into a table. A psql function
>> might be able to increment a sequence 62M times / minute, I'm not sure.

Am I correct in assuming id has as it's default nextval(<the_sequence>)?
All primary keys are "id int8 NOT NULL". They don't have a default, but all of these identifiers are generated from the same (global) sequence. We are using a single global sequence, because there are only a few writes. 99% of I/O operations are read. 


A single sequence for all id columns across all tables?

How is the sequence value landing in the id column?


If so it would seem to me something was doing a lot of INSERTS between
2023-11-24 10:20:00.000 and 2023-11-24 10:21:00.000.

Yes. But is it possible to insert 62M rows within 1 minute?

Is has not been determined yet that 62 million inserts where done, just that the sequence jumped by that number of steps.



It might be possible to "select nextval" 1M times per second from an Pl/SQL function that does nothing else in an infinite loop. But that would not write too much data on the disk.

On an old laptop:

create table insert_test(id int);

insert into insert_test select val from generate_series(1, 1000000) as t(val);
INSERT 0 1000000
Time: 943.918 ms

I would  say there is more then just the id being inserted, unless all the other fields allow NULL.




 

And there is nothing in the logs in that time period besides "checkpoints are happening too frequently"?



The "set transaction" warning is due to a bug in an application that calls SET TRANSACTION before starting a transaction.

And what is that app doing when it does SET TRANSACTION?




https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

log_statement

set to at least 'mod'?

Unfortunately we don't, but now that you asked, we will turn this on. 


>> We are using dev databases that are created from snapshots of the
>> standby. There is a possibility that a dev database instance (created
>> from a snapshot of the standby) might have connected the primary just
>> before it was reconfigured to be standalone. Can this be a problem?

Was your original report for the dev databases also?
No, every log and metric was for the prod db.


So how do the dev databases enter into this"


How are the snapshots being taken?
zfs snapshot is taken on the standby, then zfs clone is created on the snapshot, and a new postgresql instance is started on top of the clone. It recovers within one minute. In a very few cases (two or three times in a year), it fails to recover. Then we use a different snapshot. When the dev instance starts up, then we immediately delete everything from repmgr nodes, and disable repmgr completely. Today we noticed that the dev db was created in a network that made it possible to connect to the prod primary. (dev db's network was not separated from the prod db's network, fixed today). The dev db might have connected to the prod/primary after startup. But that dev instance was created 7 days ago, so probably it is not related.

What I know about ZFS would fit in the navel of flea, so someone else will have to comment on this.


Dev connected to prod/primary how?



Actually, we need this standby because we are using zfs snapshots for making frequent database backups. Without this standby, we can't make frequent backups. (The primary and the other standby are not using zfs.)

Thanks,

    Laszlo


--------------FtTr8fIAB8Qwhy0NF5wzh1bN--