Re: Tuning threshold for BAS_BULKREAD (large tables)

[Ron]

On 1/22/19 1:35 AM, Jamison, Kirk wrote:


Hi,

I have a source code-related question on BufferAccessStrategyType 
BAS_BULKREAD.


Currently, this access method is set internally to cache tables larger 
than 1/4 of shared_buffers.


src/backend/access/heap/heapam.c:initscan()

 if (!RelationUsesLocalBuffers(scan->rs_rd) &&

scan->rs_nblocks > NBuffers / 4)

    ...

 /* During a rescan, keep the previous strategy object. */

 if (scan->rs_strategy == NULL)

   scan->rs_strategy = GetAccessStrategy(BAS_BULKREAD);

Users can tune their shared_buffers size, but not able to tune this component.

I'm just wondering how it affects the current workload when the table size 
is larger than the database.




How can a subset of the database be larger than the database?


--
Angular momentum makes the world go 'round.

RE: Tuning threshold for BAS_BULKREAD (large tables)

[Jamison, Kirk]

On Tuesday, January 22, 2019 5:36 PM, Ron wrote:
>How can a subset of the database be larger than the database?
Oops. Sorry, I made a mistake on that part. What I meant was how does Postgres 
handle the large relations caching in terms of performance, especially if 
majority of the data it has to read would take up most of the shared buffers. 
Say, more than 3/4 of shared buffers. Is the current buffer access strategy 
(BAS_BULKREAD) optimal for this?


From: Ron [mailto:ronljohnso...@gmail.com]
Sent: Tuesday, January 22, 2019 5:36 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: Tuning threshold for BAS_BULKREAD (large tables)

On 1/22/19 1:35 AM, Jamison, Kirk wrote:

Hi,

I have a source code-related question on BufferAccessStrategyType BAS_BULKREAD.
Currently, this access method is set internally to cache tables larger than 1/4 
of shared_buffers.
src/backend/access/heap/heapam.c:initscan()
 if (!RelationUsesLocalBuffers(scan->rs_rd) &&
 scan->rs_nblocks > NBuffers / 4)
...
 /* During a rescan, keep the previous strategy object. */
 if (scan->rs_strategy == NULL)
 scan->rs_strategy = GetAccessStrategy(BAS_BULKREAD);

Users can tune their shared_buffers size, but not able to tune this component.
I'm just wondering how it affects the current workload when the table size is 
larger than the database.

How can a subset of the database be larger than the database?

--
Angular momentum makes the world go 'round.

PostgreSQL logical replication depends on WAL segments?

[Josef Machytka]

Hello, I already tried to ask on stackoverflow but so far without success.
(
https://stackoverflow.com/questions/54292816/postgresql-logical-replication-depends-on-wal-segments
)

Could someone help me please?



I am successfully using logical replication between 2 PG 11 cloud VMs for
latest data. But I tried to publish also some older tables to transfer data
between databases and got strange error about missing WAL segment.

These older partitions contain data 5-6 days old. I successfully published
them on master and refreshed subscription on logical replica. But now I am
getting these strange error messages on logical replica:

2019-01-21 15:03:14.713 UTC [17203] LOG:  logical replication table
synchronization worker for subscription "mysubscription", table
"mytable_20190115" has finished
2019-01-21 15:03:19.768 UTC [18877] LOG:  logical replication apply
worker for subscription "mysubscription" has started
2019-01-21 15:03:19.797 UTC [18877] ERROR:  could not receive data
from WAL stream: ERROR:  requested WAL segment
0001098E00CB has already been removed
2019-01-21 15:03:19.799 UTC [29534] LOG:  background worker "logical
replication worker" (PID 18877) exited with exit code 1
2019-01-21 15:03:24.806 UTC [18910] LOG:  logical replication apply
worker for subscription "mysubscription" has started
2019-01-21 15:03:24.824 UTC [18911] LOG:  logical replication table
synchronization worker for subscription "mysubscription", table
"mytable_20190116" has started
2019-01-21 15:03:24.831 UTC [18910] ERROR:  could not receive data
from WAL stream: ERROR:  requested WAL segment
0001098E00CB has already been removed
2019-01-21 15:03:24.834 UTC [29534] LOG:  background worker "logical
replication worker" (PID 18910) exited with exit code 1

Which is confusing for me. I tried to find some info but did not find
anything about logical replication depending on WAL segments.

There is no streaming replication running on that particular master and
these error messages I see on both master and replica connected with only
logical replication.

Am I doing something wrong? Is there some special way how to publish older
data? For newer data and latest data all works without problems.

Of course since I published like ~20 tables it took some time for replica
to process all tables - currently it processes always 2 at the time. But I
still do not understand why it should depend on WAL segments... Thank you
very much.

I tried to unpublished and unsubscribe these older tables and publish and
subscribe them again but getting still the same error message for the
exactly the same WAL segment number.

I unpublished and unsubscribed those problematic tables and error messages
stopped so they were definitely related to logical replication. Could they
be caused by snapshot?

I even made additional strange experience with WAL segments errors - my
logical replica had only quite small disk and during all that fiddling I
forgot to check disk usage. So postgresql on logical replica crashed due to
full disk. Since I use GCE I just resized root disk and after restart of
the instance got more space. But I also got back missing WAL segments
errors in connections with logical replication. My postgresql log on
replica is now full of sequence of these 3 lines:

2019-01-22 09:47:14.408 UTC [1946] LOG:  logical replication apply
worker for subscription "mysubscription" has started
2019-01-22 09:47:14.429 UTC [1946] ERROR:  could not receive data from
WAL stream: ERROR:  requested WAL segment 0001099D007A has
already been removed
2019-01-22 09:47:14.431 UTC [737] LOG:  background worker "logical
replication worker" (PID 1946) exited with exit code 1

Why logical replication depends on some old WAL segments? Today's data seem
to work perfectly although there cannot be all WAL segments for today
available on the logical master. But I am unable to publish older data...

Thanks for help.

Josef Machytka

Re: PostgreSQL logical replication depends on WAL segments?

[Achilleas Mantzios]

On 22/1/19 3:18 μ.μ., Josef Machytka wrote:

Hello, I already tried to ask on stackoverflow but so far without success.
(https://stackoverflow.com/questions/54292816/postgresql-logical-replication-depends-on-wal-segments)

Could someone help me please?



I am successfully using logical replication between 2 PG 11 cloud VMs for latest data. But I tried to publish also some older tables to transfer data between databases and got strange error about 
missing WAL segment.


These older partitions contain data 5-6 days old. I successfully published them on master and refreshed subscription on logical replica. But now I am getting these strange error messages on logical 
replica:


|2019-01-21 15:03:14.713 UTC [17203] LOG: logical replication table synchronization worker for subscription "mysubscription", table "mytable_20190115" has finished 2019-01-21 15:03:19.768 UTC 
[18877] LOG: logical replication apply worker for subscription "mysubscription" has started 2019-01-21 15:03:19.797 UTC [18877] ERROR: could not receive data from WAL stream: ERROR: requested WAL 
segment 0001098E00CB has already been removed 2019-01-21 15:03:19.799 UTC [29534] LOG: background worker "logical replication worker" (PID 18877) exited with exit code 1 2019-01-21 
15:03:24.806 UTC [18910] LOG: logical replication apply worker for subscription "mysubscription" has started 2019-01-21 15:03:24.824 UTC [18911] LOG: logical replication table synchronization worker 
for subscription "mysubscription", table "mytable_20190116" has started 2019-01-21 15:03:24.831 UTC [18910] ERROR: could not receive data from WAL stream: ERROR: requested WAL segment 
0001098E00CB has already been removed 2019-01-21 15:03:24.834 UTC [29534] LOG: background worker "logical replication worker" (PID 18910) exited with exit code 1 |


if you have WAL archiving enabled then try to find the missing WALs and copy 
them inside your data/pg_wal directory .
Normally the replication slot will retain all logs that have not been applied 
to the subscriber yet. So what you describe is not normal. Do you have any cron 
task that touches files in this dir?


Which is confusing for me. I tried to find some info but did not find anything 
about logical replication depending on WAL segments.

There is no streaming replication running on that particular master and these 
error messages I see on both master and replica connected with only logical 
replication.

Am I doing something wrong? Is there some special way how to publish older 
data? For newer data and latest data all works without problems.

Of course since I published like ~20 tables it took some time for replica to process all tables - currently it processes always 2 at the time. But I still do not understand why it should depend on 
WAL segments... Thank you very much.


I tried to unpublished and unsubscribe these older tables and publish and 
subscribe them again but getting still the same error message for the exactly 
the same WAL segment number.

**I unpublished and unsubscribed those problematic tables and error messages 
stopped so they were definitely related to logical replication. Could they be 
caused by snapshot?

I even made additional strange experience with WAL segments errors - my logical replica had only quite small disk and during all that fiddling I forgot to check disk usage. So postgresql on logical 
replica crashed due to full disk. Since I use GCE I just resized root disk and after restart of the instance got more space. But I also got back missing WAL segments errors in connections with 
logical replication. My postgresql log on replica is now full of sequence of these 3 lines:


|2019-01-22 09:47:14.408 UTC [1946] LOG: logical replication apply worker for subscription "mysubscription" has started 2019-01-22 09:47:14.429 UTC [1946] ERROR: could not receive data from WAL 
stream: ERROR: requested WAL segment 0001099D007A has already been removed 2019-01-22 09:47:14.431 UTC [737] LOG: background worker "logical replication worker" (PID 1946) exited with 
exit code 1 |


Why logical replication depends on some old WAL segments? Today's data seem to work perfectly although there cannot be all WAL segments for today available on the logical master. But I am unable to 
publish older data...


Thanks for help.

Josef Machytka




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Marc G. Fournier Invoice

[Stephen Frost]

Greetings,

Hopefully everyone realizes this but just to be clear- the below
(truncated) was spam and has been hidden from the archives.  Apologies
for it getting through, we'll look into what we can do to avoid having
it happen again in the future...

Please do *not* click the link that was in that email (or in any other
similar looking email...).

Thanks!

Stephen

* Marc G. Fournier (scra...@postgresql.org) wrote:
>Dear,
> 
>As the invoice pulls both payments across to one invoice, the best I can
>do is send you a photo copy of your
>receipts and invoice.
[...]


signature.asc
Description: PGP signature

Re: PostgreSQL logical replication depends on WAL segments?

[Adrian Klaver]

On 1/22/19 5:18 AM, Josef Machytka wrote:

Hello, I already tried to ask on stackoverflow but so far without success.
(https://stackoverflow.com/questions/54292816/postgresql-logical-replication-depends-on-wal-segments)

Could someone help me please?



I am successfully using logical replication between 2 PG 11 cloud VMs 
for latest data. But I tried to publish also some older tables to 
transfer data between databases and got strange error about missing WAL 
segment.


These older partitions contain data 5-6 days old. I successfully 
published them on master and refreshed subscription on logical replica. 
But now I am getting these strange error messages on logical replica:






Which is confusing for me. I tried to find some info but did not find 
anything about logical replication depending on WAL segments.


https://www.postgresql.org/docs/11/logical-replication-architecture.html



Thanks for help.

Josef Machytka




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PostgreSQL logical replication depends on WAL segments?

[Josef Machytka]

> https://www.postgresql.org/docs/11/logical-replication-architecture.html
>
>
Thanks, I see... So if I understand it correctly - since I have quite big
partitions like ~30 GB each in one parent table and from ~1GB to ~5 GB in
several others I presume I had to set wal_keep_segments to some really high
number and stop our security cronjob cleaning old WAL segments (because we
already had some problems with almost full disk due to old WAL segments)
until the whole transfer of snapshot is done. Because only after the whole
snapshot is transferred logical replication workers start to transfer WAL
logs reflecting changes done from the moment snapshot was taken...

jm

Re: PostgreSQL logical replication depends on WAL segments?

[Achilleas Mantzios]

On 22/1/19 6:01 μ.μ., Josef Machytka wrote:


https://www.postgresql.org/docs/11/logical-replication-architecture.html


Thanks, I see... So if I understand it correctly - since I have quite big partitions like ~30 GB each in one parent table and from ~1GB to ~5 GB in several others I presume I had to set 
wal_keep_segments to some really high number and stop our security cronjob cleaning old WAL segments (because we already had some problems with almost full disk due to old WAL segments)


I asked you if you had any cron jobs messing around with your WALs. The WALs in pg_wal dir is PostgreSQL's business to manage, not your scripts. Dont do that. Defining wal_keep_segments to a very 
large value or using replication slots (as with logical repl) won't help if your crons are deleting the WALs.

Now what you have to do :
- read up about the basic concepts of pgsql administration : 
https://www.postgresql.org/docs/11/admin.html
- understand how postgresql replication slots work (logical repl is based on 
those)
- understand how WAL archiving works
- understand how streaming replication works
- understand how logical replication works
- understand how the various runtime configuration params act on the number of 
WALs kept

until the whole transfer of snapshot is done. Because only after the whole snapshot is transferred logical replication workers start to transfer WAL logs reflecting changes done from the moment 
snapshot was taken...


jm



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Decrease time needed to CREATE INDEX and FOREIGN KEY on new table column which has all values NULL

[Denisa Cirstescu]

Hello all,

I am trying to add a new column to a really big table and to define an INDEX 
and a FOREIGN KEY on that new column using the following instructions:

ALTER TABLE Employee ADD COLUMN DepartmentId INTEGER;
CREATE INDEX IDX_Employee_DepartmentId ON Employee(DepartmentId);
ALTER TABLE Employee ADD CONSTRAINT FK_Employee_Department FOREIGN 
KEY(DepartmentId) REFERENCES Department(DepartmentId);

The table is huge and it takes a lot of time to add the INDEX and the FOREIGN 
KEY although all values are NULL.
Considering that the new DepartmentId column is NULL for all rows at this 
point, is there a way to make the INDEX and FOREIGN KEY creation run faster?

See below information about the size of the table and also the size of its 
associated Primary Key and Indexes:
  Employee339 GB
  Employee_PKEY  46 GB
  IDX_Employee_JobId 46 GB
  IDX_Employee_IsWFH   46 GB


Thank you,
Denisa Cîrstescu

Re: Decrease time needed to CREATE INDEX and FOREIGN KEY on new table column which has all values NULL

[Ravi Krishna]

> The table is huge and it takes a lot of time to add the INDEX and the FOREIGN 
> KEY although all values are NULL.
> Considering that the new DepartmentId column is NULL for all rows at this 
> point, is there a way to make the INDEX and FOREIGN KEY creation run faster?

In your script to create index add this:

set maintenance_work_mem = '1GB'
or any other figure you feel comfortable.

Re: Decrease time needed to CREATE INDEX and FOREIGN KEY on new table column which has all values NULL

[Alvaro Herrera]

On 2019-Jan-22, Denisa Cirstescu wrote:

> I am trying to add a new column to a really big table and to define an INDEX 
> and a FOREIGN KEY on that new column using the following instructions:
> 
> ALTER TABLE Employee ADD COLUMN DepartmentId INTEGER;
> CREATE INDEX IDX_Employee_DepartmentId ON Employee(DepartmentId);
> ALTER TABLE Employee ADD CONSTRAINT FK_Employee_Department FOREIGN 
> KEY(DepartmentId) REFERENCES Department(DepartmentId);
> 
> The table is huge and it takes a lot of time to add the INDEX and the FOREIGN 
> KEY although all values are NULL.
> Considering that the new DepartmentId column is NULL for all rows at this 
> point, is there a way to make the INDEX and FOREIGN KEY creation run faster?

The typical advice is to run CREATE INDEX CONCURRENTLY instead of plain
CREATE INDEX.  Also, use "ADD CONSTRAINT ... NOT VALID;" instead, and
later do another ALTER TABLE ... VALIDATE CONSTRAINT.  That decreases
the impact considerably in production scenarios.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: PostgreSQL logical replication depends on WAL segments?

[Jeremy Finzel]

>
> Thanks, I see... So if I understand it correctly - since I have quite big
> partitions like ~30 GB each in one parent table and from ~1GB to ~5 GB in
> several others I presume I had to set wal_keep_segments to some really high
> number and stop our security cronjob cleaning old WAL segments (because we
> already had some problems with almost full disk due to old WAL segments)
> until the whole transfer of snapshot is done. Because only after the whole
> snapshot is transferred logical replication workers start to transfer WAL
> logs reflecting changes done from the moment snapshot was taken...
>
> jm
>

Understand there are other downsides to just keeping around a huge amount
of WAL segments apart from only taking up disk space.  None of the data
held in those WAL segments can be vacuumed away while they are left around,
which can lead to significant bloat and performance issues over time.

I'm not exactly clear on your use case, but if you need to just
resychronize data for a single table, there is a built-in way to do that
(actually would be nice if the docs spelled this out).

On publisher:

ALTER PUBLICATION mypub DROP TABLE old_data_table;

On subscriber:

ALTER SUBSCRIPTION mysub REFRESH PUBLICATION WITH ( COPY_DATA = true);

On publisher:

ALTER PUBLICATION mypub ADD TABLE old_data_table;

On subscriber:

ALTER SUBSCRIPTION mysub REFRESH PUBLICATION WITH ( COPY_DATA = true);

The last command will resync the table from the current table data,
regardless of the WAL file situation.  This is the "normal" way you would
go about resynchronizing data between clusters when a long time has passed,
rather than trying to keep all that WAL around!

So far as I can tell from testing, above pattern is the easiest way to do
this, and it will not resynchronize any of the other tables in your
subscription.

P.S. do heed the advice of the others and get more familiar with the docs
around WAL archiving.

Thanks,
Jeremy

Re: PostgreSQL logical replication depends on WAL segments?

[Adrian Klaver]

On 1/22/19 9:10 AM, Jeremy Finzel wrote:

Thanks, I see... So if I understand it correctly - since I have
quite big partitions like ~30 GB each in one parent table and from
~1GB to ~5 GB in several others I presume I had to set
wal_keep_segments to some really high number and stop our security
cronjob cleaning old WAL segments (because we already had some
problems with almost full disk due to old WAL segments) until the
whole transfer of snapshot is done. Because only after the whole
snapshot is transferred logical replication workers start to
transfer WAL logs reflecting changes done from the moment snapshot
was taken...

jm


Understand there are other downsides to just keeping around a huge 
amount of WAL segments apart from only taking up disk space.  None of 
the data held in those WAL segments can be vacuumed away while they are 
left around, which can lead to significant bloat and performance issues 
over time.


That is news to me. Can you provide a citation for this?



I'm not exactly clear on your use case, but if you need to just 
resychronize data for a single table, there is a built-in way to do that 
(actually would be nice if the docs spelled this out).


On publisher:

ALTER PUBLICATION mypub DROP TABLE old_data_table;

On subscriber:

ALTER SUBSCRIPTION mysub REFRESH PUBLICATION WITH ( COPY_DATA = true);

On publisher:

ALTER PUBLICATION mypub ADD TABLE old_data_table;

On subscriber:

ALTER SUBSCRIPTION mysub REFRESH PUBLICATION WITH ( COPY_DATA = true);

The last command will resync the table from the current table data, 
regardless of the WAL file situation.  This is the "normal" way you 
would go about resynchronizing data between clusters when a long time 
has passed, rather than trying to keep all that WAL around!


So far as I can tell from testing, above pattern is the easiest way to 
do this, and it will not resynchronize any of the other tables in your 
subscription.


P.S. do heed the advice of the others and get more familiar with the 
docs around WAL archiving.


Thanks,
Jeremy



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PostgreSQL logical replication depends on WAL segments?

[Jeremy Finzel]

>
> That is news to me. Can you provide a citation for this?
>

I can see the confusion in what I said.  To clarify, I really meant that in
order to retrieve that data that you need in really old WAL segments, you
need to keep your replication slot in a position that will hold that WAL in
place.  And that is what will prevent old rows from being vacuumed away.
There is no way to create a replication slot in the past, so the only way
to actually use the old WAL for logical replication is to have an old slot
in place with an LSN position from that long time ago.

The bit about rows not being removed is found here:
https://www.postgresql.org/docs/9.4/warm-standby.html#STREAMING-REPLICATION-SLOTS

Thanks,
Jeremy

Re: PostgreSQL logical replication depends on WAL segments?

[Andres Freund]

Hi,

On 2019-01-22 14:18:12 +0100, Josef Machytka wrote:
> Hello, I already tried to ask on stackoverflow but so far without success.
> (
> https://stackoverflow.com/questions/54292816/postgresql-logical-replication-depends-on-wal-segments
> )
> 
> Could someone help me please?
> 
> 
> 
> I am successfully using logical replication between 2 PG 11 cloud VMs for
> latest data. But I tried to publish also some older tables to transfer data
> between databases and got strange error about missing WAL segment.

That ought not to happen, logical replication won't start in the past,
and the slot will prevent necessary WAL from being removed.  Are you
manually removing WAL from pg_wal/?


Greetings,

Andres Freund

Re: PostgreSQL logical replication depends on WAL segments?

[Andres Freund]

Hi,

On 2019-01-22 11:10:27 -0600, Jeremy Finzel wrote:
> P.S. do heed the advice of the others and get more familiar with the docs
> around WAL archiving.

Logical replication doesn't normally interact with WAL archiving in any
way, so that seems orthogonal.

Greetings,

Andres Freund

Re: PostgreSQL logical replication depends on WAL segments?

[Andres Freund]

Hi,

On 2019-01-22 11:57:00 -0600, Jeremy Finzel wrote:
> >
> > That is news to me. Can you provide a citation for this?
> >
> 
> I can see the confusion in what I said.  To clarify, I really meant that in
> order to retrieve that data that you need in really old WAL segments, you
> need to keep your replication slot in a position that will hold that WAL in
> place.  And that is what will prevent old rows from being vacuumed
> away.

Note replication slots only prevent old *catalog* rows from being
removed, not old row versions in user created tables.

Greetings,

Andres Freund

Re: PostgreSQL logical replication depends on WAL segments?

[Jeremy Finzel]

On Tue, Jan 22, 2019 at 12:52 PM Andres Freund  wrote:

> Hi,
>
> On 2019-01-22 11:10:27 -0600, Jeremy Finzel wrote:
> > P.S. do heed the advice of the others and get more familiar with the docs
> > around WAL archiving.
>
> Logical replication doesn't normally interact with WAL archiving in any
> way, so that seems orthogonal.
>

But he is manually removing WAL files via cron which should normally be
held until the replication slot doesn't need it anymore.  I do believe that
is why he is getting errors.  The suggestion is to rely on Postgres' WAL
archiving rather than removing WAL files manually...

Thanks,
Jeremy

Re: PostgreSQL logical replication depends on WAL segments?

[Jeremy Finzel]

>
> Note replication slots only prevent old *catalog* rows from being
> removed, not old row versions in user created tables.
>

Thank you for that clarification.  I can see this is noted clearly in the
CAUTION statement here:
https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS

Thanks,
Jeremy

RE: Memory and hard ware calculation :

[Rangaraj G]

Hi,

My question
Our connection is 1100 parallel connection and 1 GB I/p data and 1 GB o/p data 
in each connection, currently we are using 64 GB RAM and 8 core.

But we need all the reports below 3 seconds.

So kindly suggest expanding my hard ware and work memory.

Is there any possibility to get your mobile number ?

Regards,
RANGARAJ G


From: Cleiton Luiz Domazak 
Sent: Monday, January 21, 2019 11:13 PM
To: Rangaraj G 
Cc: pgsql-gene...@postgresql.org; robertmh...@gmail.com; 
pgsql-performa...@postgresql.org
Subject: Re: Memory and hard ware calculation :



On Mon, Jan 21, 2019 at 5:35 PM Rangaraj G 
mailto:ranga...@uniphore.com>> wrote:
Hi,

Memory and hard ware calculation :


How much memory required to achieve performance with the 6GB RAM, 8 Core, Max 
connection 1100 concurrent connection and O/p memory from procedure 1GB ?

https://pgtune.leopard.in.ua/#/

Is there any changes required in hardware and work memory expansion ?


Regards
RANGARAJ G

Re: Memory and hard ware calculation :

[Ron]

Have you analyzed the queries to ensure that they are efficient?
Have you examined the tables to ensure that they have indexes to support the 
joins?

Have you minimized the amount of data selected?

On 1/22/19 6:54 AM, Rangaraj G wrote:


Hi,

My question

Our connection is 1100 parallel connection and 1 GB I/p data and 1 GB o/p 
data in each connection, currently we are using 64 GB RAM and 8 core.


But we need all the reports below 3 seconds.

So kindly suggest expanding my hard ware and work memory.

Is there any possibility to get your mobile number ?

Regards,

RANGARAJ G

*From:* Cleiton Luiz Domazak 
*Sent:* Monday, January 21, 2019 11:13 PM
*To:* Rangaraj G 
*Cc:* pgsql-gene...@postgresql.org; robertmh...@gmail.com; 
pgsql-performa...@postgresql.org

*Subject:* Re: Memory and hard ware calculation :

On Mon, Jan 21, 2019 at 5:35 PM Rangaraj G > wrote:


Hi,

Memory and hard ware calculation :

How much memory required to achieve performance with the 6GB RAM, 8
Core, Max connection 1100 concurrent connection and O/p memory from
procedure 1GB ?

https://pgtune.leopard.in.ua/#/

Is there any changes required in hardware and work memory expansion ?

Regards

RANGARAJ G



--
Angular momentum makes the world go 'round.

Re: PostgreSQL logical replication depends on WAL segments?

[Achilleas Mantzios]

On 22/1/19 8:59 μ.μ., Jeremy Finzel wrote:

On Tue, Jan 22, 2019 at 12:52 PM Andres Freund > wrote:


Hi,

On 2019-01-22 11:10:27 -0600, Jeremy Finzel wrote:
> P.S. do heed the advice of the others and get more familiar with
the docs
> around WAL archiving.

Logical replication doesn't normally interact with WAL archiving
in any
way, so that seems orthogonal.


But he is manually removing WAL files via cron which should normally 
be held until the replication slot doesn't need it anymore.  I do 
believe that is why he is getting errors.  The suggestion is to rely 
on Postgres' WAL archiving rather than removing WAL files manually...


wal retention in pg_wal and wal archiving are different things. The OP 
got problems cause he deliberately went on to delete files in pg_wal 
which means he used the wrong method to address a wrong situation.


However, if the OP has still those WAL files archived he can use them to 
bring logical replication back in synch. Don't ask me how I know : 
https://www.postgresql.org/message-id/ae8812c3-d138-73b7-537a-a273e15ef6e1%40matrix.gatewaynet.com




Thanks,
Jeremy

Re: PostgreSQL logical replication depends on WAL segments?

[Jeremy Finzel]

>
> wal retention in pg_wal and wal archiving are different things. The OP got
> problems cause he deliberately went on to delete files in pg_wal which
> means he used the wrong method to address a wrong situation.
>
> However, if the OP has still those WAL files archived he can use them to
> bring logical replication back in synch. Don't ask me how I know :
> https://www.postgresql.org/message-id/ae8812c3-d138-73b7-537a-a273e15ef6e1%40matrix.gatewaynet.com
>
I don't think we are disagreeing.  My point is that WAL will be retained in
pg_wal if it is still needed by a replication slot, and any one of those
WAL files will not be archived and recycled as long as it no longer needed
by a replication slot.  So yes, they are different things, but they are
related in that sense.

It sounded to me like he had an old slot in place pointing to old WAL
files, which the cron job removed, thus leading to his error.

Sure, he can recover from this by moving those WAL files back into pg_wal.
But my point is simply that he should rely on WAL archiving rather than
archiving via cron because Postgres handles knowing whether or not a
replication slot still needs a WAL file.

Thanks,
Jeremy

Re: PostgreSQL logical replication depends on WAL segments?

[Jeremy Finzel]

On Tue, Jan 22, 2019 at 2:16 PM Jeremy Finzel  wrote:

> any one of those WAL files will not be archived and recycled as long as it
>> no longer needed by a replication slot
>>
>
:(.  s/no longer/still.  I meant to say any one of those WAL files will not
be archived and recycled as long as it still needed by a replication slot.

Re: PostgreSQL logical replication depends on WAL segments?

[Achilleas Mantzios]

On 22/1/19 10:18 μ.μ., Jeremy Finzel wrote:
On Tue, Jan 22, 2019 at 2:16 PM Jeremy Finzel > wrote:


any one of those WAL files will not be archived and
recycled as long as it no longer needed by a replication slot


:(.  s/no longer/still.  I meant to say any one of those WAL files 
will not be archived and recycled as long as it still needed by a 
replication slot.



A WAL segment will be archived as soon as it is completed. 
|https://www.postgresql.org/docs/11/continuous-archiving.html

|

Re: PostgreSQL logical replication depends on WAL segments?

[Jeremy Finzel]

On Tue, Jan 22, 2019 at 8:19 PM Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:

>
> On 22/1/19 10:18 μ.μ., Jeremy Finzel wrote:
>
> On Tue, Jan 22, 2019 at 2:16 PM Jeremy Finzel  wrote:
>
>> any one of those WAL files will not be archived and recycled as long as
>>> it no longer needed by a replication slot
>>>
>>
> :(.  s/no longer/still.  I meant to say any one of those WAL files will
> not be archived and recycled as long as it still needed by a replication
> slot.
>
>
> A WAL segment will be archived as soon as it is completed.
> https://www.postgresql.org/docs/11/continuous-archiving.html
>

Yep, you’re right. Let me try this again. It will be archived, but the
segment won’t be reused or removed as long as the slot needs it. Is that an
accurate statement?

Thanks,
Jeremy


>

>

Re: PostgreSQL logical replication depends on WAL segments?

[Achilleas Mantzios]

On 23/1/19 5:26 π.μ., Jeremy Finzel wrote:

On Tue, Jan 22, 2019 at 8:19 PM Achilleas Mantzios mailto:ach...@matrix.gatewaynet.com>> wrote:


On 22/1/19 10:18 μ.μ., Jeremy Finzel wrote:

On Tue, Jan 22, 2019 at 2:16 PM Jeremy Finzel mailto:finz...@gmail.com>> wrote:

any one of those WAL files will not be archived and recycled as 
long as it no longer needed by a replication slot


:(.  s/no longer/still.  I meant to say any one of those WAL files will not 
be archived and recycled as long as it still needed by a replication slot.



A WAL segment will be archived as soon as it is completed. 
|https://www.postgresql.org/docs/11/continuous-archiving.html|


Yep, you’re right. Let me try this again. It will be archived, but the segment 
won’t be reused or removed as long as the slot needs it. Is that an accurate 
statement?

That's better :) Also it depends on the previous checkpoint.



Thanks,
Jeremy

||

||




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt