On Sat, Jun 8, 2024 at 2:51 PM sud wrote:
>
> Thank You so much Laurenz and Yudhi.
>
> Yes its RDS and as you mentioned there does exist a space limitation of
> ~64TB but as Laurenz mentioned the only time the second standby may crash
> would be probably because of the storage space saturation a
On Thu, Jun 6, 2024 at 12:52 AM yudhi s wrote:
> On Wed, Jun 5, 2024 at 3:52 PM Laurenz Albe
> wrote:
>
>>
>> There should never be a restart unless you perform one or the standby
>> crashes.
>> If you mean that you want to avoid a crash caused by a full disk on the
>> standby,
>> the answer is
On Wed, Jun 5, 2024 at 3:52 PM Laurenz Albe
wrote:
>
> There should never be a restart unless you perform one or the standby
> crashes.
> If you mean that you want to avoid a crash caused by a full disk on the
> standby,
> the answer is probably "no". Make sure that you have enough disk space an
On Wed, 2024-06-05 at 13:09 +0530, sud wrote:
> > > One follow up question i have , as we are setting one of the
> > > standby/replica
> > > with value idle_in_transaction_session_timeout=-1 which can cause the
> > > WAL's
> > > to be heavily backlogged in a scenario where we have a query running
On Wed, 5 Jun, 2024, 12:39 pm Simon Elbaz, wrote:
> Hi,
>
> I am following this very interesting thread.
>
> From the documentation
> https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT,
> the 0 value will disable the timeout (not -1).
>
>
>
Hi,
I am following this very interesting thread.
>From the documentation
https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT,
the 0 value will disable the timeout (not -1).
On Wed, Jun 5, 2024 at 8:25 AM sud wrote:
> Hello Laurenz,
>
> T
Hello Laurenz,
Thank you so much.This information was really helpful for us
understanding the working of these parameters.
One follow up question i have , as we are setting one of the
standby/replica with value idle_in_transaction_session_timeout=-1 which can
cause the WAL's to be heavily backlog
On Wed, 2024-05-29 at 01:34 +0530, yudhi s wrote:
> > The only way you can have no delay in replication AND no canceled queries is
> > if you use two different standby servers with different settings for
> > "max_standby_streaming_delay". One of the server is for HA, the other for
> > your long-ru
On Mon, May 27, 2024 at 2:50 PM Laurenz Albe
wrote:
>
> > > But again for HA , in case primary down we should not be in big lag
> for the standby
> > > and thus we want the standby also with minimal lag. And as you
> mentioned there will
> > > never be incorrect results but at amx it will be quer
> On Fri, May 24, 2024 at 10:34 AM sud wrote:
> > I am trying to understand these two parameters and each time it looks a bit
> > confusing to me.
> > If These two parameters complement or conflict with each other.
> >
> > Say for example, If we set hot_feedback_standby to ON (which is currently
On Mon, May 27, 2024 at 12:55 AM Torsten Förtsch
wrote:
> On Sun, May 26, 2024 at 8:46 PM sud wrote:
>
>> Would you agree that we should have two standby, one with default
>> max_standby_streaming_delay (say 10 sec ) which will be mainly used as high
>> availability and thus will be having minim
On Sun, May 26, 2024 at 8:46 PM sud wrote:
> Would you agree that we should have two standby, one with default
> max_standby_streaming_delay (say 10 sec ) which will be mainly used as high
> availability and thus will be having minimal lag. and another standby with
> max_standby_streaming_delay a
On Sun, May 26, 2024 at 11:18 PM Torsten Förtsch
wrote:
> Each query on the replica has a backend_xmin. You can see that in
> pg_stat_activity. From that backend's perspective, tuples marked as deleted
> by any transaction greater or equal to backend_xmin are still needed. This
> does not depend
On Sun, May 26, 2024 at 11:16 AM sud wrote:
> On Sun, May 26, 2024 at 1:43 PM Torsten Förtsch
> wrote:
>
>> On Sat, May 25, 2024 at 11:00 PM sud wrote:
>>
>>>
>>> But i have one question here , does max_standby_streaming_delay = 14 ,
>>> means the queries on the standby will get cancelled after
On 5/25/24 22:56, David HJ wrote:
anyone know how to describe from this mailing list?
See here:
https://lists.postgresql.org/unsubscribe/
--
Adrian Klaver
adrian.kla...@aklaver.com
On Sun, May 26, 2024 at 1:43 PM Torsten Förtsch
wrote:
> On Sat, May 25, 2024 at 11:00 PM sud wrote:
>
>>
>> But i have one question here , does max_standby_streaming_delay = 14 ,
>> means the queries on the standby will get cancelled after 14 seconds?
>>
>
> No, your query gets cancelled when i
On Sat, May 25, 2024 at 11:00 PM sud wrote:
>
> But i have one question here , does max_standby_streaming_delay = 14 ,
> means the queries on the standby will get cancelled after 14 seconds?
>
No, your query gets cancelled when it stalls replication for >14 sec. If
your master is idle and does n
anyone know how to describe from this mailing list?
On Thu, May 23, 2024 at 5:16 AM sud wrote:
> Hello ,
> It's RDS postgres version 15.4. We suddenly saw the
> "MaximumUsedTransactionIDs" reach to ~1.5billion and got alerted by team
> members who mentioned the database is going to be in shutdow
On Sun, May 26, 2024 at 2:24 AM yudhi s wrote:
>
>
> *hot_standby_feedback ON and max_standby_streaming_delay = -1:*
> Ensures that long-running queries on the standby are not interrupted. The
> primary waits indefinitely to avoid vacuuming rows needed by standby
> queries.
> But Can lead to sign
On Fri, May 24, 2024 at 10:34 AM sud wrote:
> I am trying to understand these two parameters and each time it looks a
> bit confusing to me. If These two parameters complement or conflict with
> each other.
>
> Say for example, If we set hot_feedback_standby to ON (which is currently
> set as def
On Thu, May 23, 2024 at 8:11 PM Laurenz Albe
wrote:
> On Thu, 2024-05-23 at 18:15 +0530, sud wrote:
> > On Thu, May 23, 2024 at 1:45 PM Laurenz Albe
> wrote:
> > > If a long running query on the standby influences the primary, that
> means that
> > > you have "hot_standby_feedback" set to "on".
On Thu, 2024-05-23 at 18:15 +0530, sud wrote:
> On Thu, May 23, 2024 at 1:45 PM Laurenz Albe wrote:
> > If a long running query on the standby influences the primary, that means
> > that
> > you have "hot_standby_feedback" set to "on". Set it to "off".
>
> Will the setting up of "hot_standby_fe
On Thu, May 23, 2024 at 1:45 PM Laurenz Albe
wrote:
>
>
> If a long running query on the standby influences the primary, that means
> that
> you have "hot_standby_feedback" set to "on". Set it to "off".
>
>
> Will the setting up of "hot_standby_feedback" value to OFF will cause the
reader instan
On Thu, 2024-05-23 at 13:41 +0530, sud wrote:
> > Yes, that is correct. You cannot run such long-running queries with a
> > transaction rate like that.
>
> When you mean transaction ,does it mean one commit ? For example if it's
> inserting+committing ~1000 rows in one batch then all the 1000 row
On Thu, May 23, 2024 at 1:22 PM Laurenz Albe
wrote:
> On Thu, 2024-05-23 at 02:46 +0530, sud wrote:
> > It's RDS postgres version 15.4. We suddenly saw the
> "MaximumUsedTransactionIDs"
> > reach to ~1.5billion and got alerted by team members who mentioned the
> database
> > is going to be in shu
On Thu, 2024-05-23 at 02:46 +0530, sud wrote:
> It's RDS postgres version 15.4. We suddenly saw the
> "MaximumUsedTransactionIDs"
> reach to ~1.5billion and got alerted by team members who mentioned the
> database
> is going to be in shutdown/hung if this value reaches to ~2billion and won't
> b
On Thu, May 23, 2024 at 11:42 AM sud wrote:
>
>> Calculation Rationale
>> Daily XID Usage: Approximately 4 billion rows per day implies high XID
>> consumption.
>> Buffer Time: At 1 billion XIDs, you would still have 1 billion XIDs
>> remaining, giving you roughly 12 hours to address the issue if
Greetings,
Yes, monitoring and alerting for VACUUM operations are crucial.
Track VACUUM Duration and Success:
SELECT pid, state, query_start, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE query LIKE 'VACUUM%'
ORDER BY duration DESC;
Check Autovacuum Activity:
SELECT relnam
Also,if i am getting it correct, it means we should not run any transaction
(even if it's legitimate one like for e.g. a big Reporting "SELECT" query)
beyond 10hrs, as that will end up consuming 10*200million XID per hour=
2billion XID limit saturation and thus causing system failure. Hope my
under
On Thu, May 23, 2024 at 10:42 AM Muhammad Salahuddin Manzoor <
salahuddi...@bitnine.net> wrote:
> Greetings,
>
> Running `VACUUM table_name;` on a partitioned table will vacuum each
> partition individually, not the whole table as a single unit.
>
> Yes, running `VACUUM table_name;` frequently on
Greetings,
Running `VACUUM table_name;` on a partitioned table will vacuum each
partition individually, not the whole table as a single unit.
Yes, running `VACUUM table_name;` frequently on tables or partitions with
heavy DML is recommended.
Regular `VACUUM` does not lock the table for reads or
On Thu, May 23, 2024 at 9:00 AM Muhammad Salahuddin Manzoor <
salahuddi...@bitnine.net> wrote:
> Greetings,
>
> In high-transaction environments like yours, it may be necessary to
> supplement this with manual vacuuming.
>
> Few Recommendations
>
> Monitor Long-Running Queries try to optimize.
> O
Greetings,
In high-transaction environments like yours, it may be necessary to
supplement this with manual vacuuming.
Few Recommendations
Monitor Long-Running Queries try to optimize.
Optimize Autovacuum.
Partitioning.
Adopt Vacuum Strategy after peak hours.
*Salahuddin (살라후딘**)*
On Thu, 23
Hello ,
It's RDS postgres version 15.4. We suddenly saw the
"MaximumUsedTransactionIDs" reach to ~1.5billion and got alerted by team
members who mentioned the database is going to be in shutdown/hung if this
value reaches to ~2billion and won't be able to serve any incoming
transactions. It was a p
34 matches
Mail list logo
