Re: replication primary writting infinite number of WAL files

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Les <nagylzs(at)gmail(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: replication primary writting infinite number of WAL files
Date: 2023-11-24 19:31:56
Message-ID: 1aec39e7-f64e-4afc-8ab2-3c723dec2b0b@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 11/24/23 09:51, Adrian Klaver wrote:
> On 11/24/23 09:32, Les wrote:
>
> Please Reply All to include list
> Ccing list to get information back there.
>
>>
>>
>> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> (2023. nov. 24., P, 17:50):
>>
>>     On 11/24/23 03:39, Les wrote:
>>      > The only exception is a sequence
>>      > value that was moved millions of steps within a single minute. Of
>>
>>     Did you determine this by looking at select * from some_seq?
>>
>>
>> select dd, (select max(id) from some_frequently_changed_table where
>> created < dd) as id
>> FROM generate_series
>>          ( '2023-11-24 10:50'::timestamp
>>          , '2023-11-22 10:30'::timestamp
>>          , '-1 minute'::interval) dd
>>          ;
>>
>> Here is a fragment from the first occasion:
>>
>> 2023-11-24 10:31:00.000|182920700600|
>> 2023-11-24 10:30:00.000|182920700500|
>> 2023-11-24 10:29:00.000|182920699900|
>> 2023-11-24 10:28:00.000|182920699900|
>> 2023-11-24 10:27:00.000|182920699900|
>> 2023-11-24 10:26:00.000|182920663400|
>> 2023-11-24 10:25:00.000|182920663400|
>> 2023-11-24 10:24:00.000|176038405400|
>> 2023-11-24 10:23:00.000|176038405400|
>> 2023-11-24 10:22:00.000|176038405400|
>> 2023-11-24 10:21:00.000|176038405400|
>> 2023-11-24 10:20:00.000|169819538300|
>> 2023-11-24 10:19:00.000|169819538300|
>> 2023-11-24 10:18:00.000|169819538300|
>> 2023-11-24 10:17:00.000|167912236800|
>> 2023-11-24 10:16:00.000|164226477100|
>> 2023-11-24 10:15:00.000|164226477100|
>> 2023-11-24 10:14:00.000|153516704200|
>> 2023-11-24 10:13:00.000|153516704200|
>> 2023-11-24 10:12:00.000|153516704200|
>> 2023-11-24 10:11:00.000|153516704200|
>> 2023-11-24 10:10:00.000|153516704200|
>> 2023-11-24 10:09:00.000|144613764500|
>> 2023-11-24 10:08:00.000|144613764500|
>> 2023-11-24 10:07:00.000|144613764500|
>> 2023-11-24 10:06:00.000|144613764500|
>> 2023-11-24 10:05:00.000|144312488400|
>>
>> 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>)?

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.

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

Do you have:

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

log_statement

set to at least 'mod'?

>> 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?

How are the snapshots being taken?

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeremy Schneider 2023-11-24 20:18:01 Re: Corruption or wrong results with 14.10?
Previous Message Tom Lane 2023-11-24 18:43:32 Re: Odd Shortcut behaviour in PG14