Re: overcommit_ratio setting

[Laurenz Albe]

On Tue, 2021-06-15 at 08:34 +0800, Yi Sun wrote:
> > overcommit_ratio < (RAM - swap) / RAM * 100
> > 
> > Here, RAM is the RAM available to PostgreSQL.
> 
> Thank you for your reply
> 
> 1. Our env RAM are 4GB, 8 GB, 16 GB... as below url suggestion, could we 
> configure swap as below?
> https://opensource.com/article/18/9/swap-space-linux-systems
> 
> RAM swap
> 
> 2GB – 8GB   = RAM
> >8GB8GB

I wouldn't change the swap space to fit overcommit_ratio, but
the other way around.
With a properly configured PostgreSQL, you won't need a lot of swap space.

> 2. If the RAM is 4GB and 8GB, the formula  (RAM - swap) / RAM * 100 result 
> will become to 0,
>how could we configure overcommit_ratio please?

You have to use floating point arithmetic.

The result will only be 0 if RAM = swap.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: overcommit_ratio setting

[Yi Sun]

Laurenz Albe  于2021年6月15日周二 下午3:07写道：

> On Tue, 2021-06-15 at 08:34 +0800, Yi Sun wrote:
> > > overcommit_ratio < (RAM - swap) / RAM * 100
> > >
> > > Here, RAM is the RAM available to PostgreSQL.
> >
> > Thank you for your reply
> >
> > 1. Our env RAM are 4GB, 8 GB, 16 GB... as below url suggestion, could we
> configure swap as below?
> > https://opensource.com/article/18/9/swap-space-linux-systems
> >
> > RAM swap
> >
> > 2GB – 8GB   = RAM
> > >8GB8GB
>
> I wouldn't change the swap space to fit overcommit_ratio, but
> the other way around.
> With a properly configured PostgreSQL, you won't need a lot of swap space.
>
> > 2. If the RAM is 4GB and 8GB, the formula  (RAM - swap) / RAM * 100
> result will become to 0,
> >how could we configure overcommit_ratio please?
>
> You have to use floating point arithmetic.
>
> The result will only be 0 if RAM = swap.
>

Got it, so should always use formula overcommit_ratio < (RAM - swap) / RAM
* 100  regardless of the value

Our prd env RAM are 4GB, 8 GB, 16 GB..., some env configured swap, some env
didn't configure swap
1. Is it OK if prd env didn't configure swap?
2. The linux OS is CentOS 7, what's the recommended value for swap setting
based on different RAM please?

 Thank you

Re: Master - Slave Replication Window Server

[Rory Campbell-Lange]

On 15/06/21, Haseeb Khan (khanhasee...@gmail.com) wrote:
> I have confusion below, Should we create an archive path on the standby
> server and then set it to recovery.conf file ?
> 
> restore_command = 'cp /path/to/archive/%f %p'

Hi Hasseb

Are you following this procedure?
https://www.postgresql.org/docs/13/continuous-archiving.html

If so please let us know what problem you are experiencing.

Also, this is the postgres performance list. Please move this conversation to 
postgresql general.

Cheers
Rory

Re: Master - Slave Replication Window Server

[Haseeb Khan]

Hello Rory,

Yes , I have followed the document and configured each and everything. But I 
can’t see archive_Wal_segments is copying to the folder which I have created. 
So the issue I am facing is that where should I create the archive folder 
should I create on master or slave server ? Might be I am missing something or 
doing some mistake.

Would appreciated, if you could help in this regard 

Kindly send me the Postgres general email, so I can raise this issue over there 
as well

Thanks in advance 

Br, 
Haseeb Ahmad

> On 15-Jun-2021, at 5:05 PM, Rory Campbell-Lange  
> wrote:
> 
> On 15/06/21, Haseeb Khan (khanhasee...@gmail.com) wrote:
>> I have confusion below, Should we create an archive path on the standby
>> server and then set it to recovery.conf file ?
>> 
>> restore_command = 'cp /path/to/archive/%f %p'
> 
> Hi Hasseb
> 
> Are you following this procedure?
> https://www.postgresql.org/docs/13/continuous-archiving.html
> 
> If so please let us know what problem you are experiencing.
> 
> Also, this is the postgres performance list. Please move this conversation to 
> postgresql general.
> 
> Cheers
> Rory
> 
> 

Re: waiting for client write

[Ayub Khan]

Would it be a cursor issue on postgres, as there seems to be a
difference in how cursors are handled in postgres and Oracle database. It
seems cursors are returned as buffers to the client side. Below are the
steps we take from jdbc side

below is the stored procedure code:

CREATE OR REPLACE PROCEDURE ."menu_pkg$get_menu_items_p_new"(
i_restaurant_id bigint,
i_category_id bigint,
i_check_availability text,
i_branch_id bigint,
INOUT o_items refcursor,
INOUT o_combo refcursor)
LANGUAGE 'plpgsql'

AS $BODY$

BEGIN

OPEN o_items FOR

SELECT

a.menu_item_id, a.menu_item_name, a.menu_item_category_id,
b.menu_item_category_desc, c.menu_item_variant_id,
c.menu_item_variant_type_id, c.price, c.size_id,
c.parent_menu_item_variant_id, d.menu_item_variant_type_desc, e.size_desc,
f.currency_code, a.image, a.mark_id, m.mark_name

FROM .menu_item_category AS b, .menu_item_variant AS c,
.menu_item_variant_type AS d, .item_size AS e, .restaurant AS f, .menu_item
AS a

  LEFT OUTER JOIN .mark AS m  ON (a.mark_id = m.mark_id) WHERE
a.menu_item_category_id = b.menu_item_category_id AND a.menu_item_id =
c.menu_item_id
  AND c.menu_item_variant_type_id = d.menu_item_variant_type_id AND
d.is_hidden = 'false' AND c.size_id = e.size_id AND a.restaurant_id =
f.restaurant_id AND f.restaurant_id = i_restaurant_id AND
(a.menu_item_category_id = i_category_id OR i_category_id IS NULL) AND
c.menu_item_variant_id =
(SELECT MIN(menu_item_variant_id) FROM .menu_item_variant   WHERE
menu_item_id = a.menu_item_id AND deleted = 'N') AND a.active = 'Y' AND
(CONCAT_WS('', ',', a.hidden_branch_ids, ',') NOT LIKE CONCAT_WS('', '%,',
i_branch_id, ',%') OR i_branch_id IS NULL) AND
.is_menu_item_available(a.menu_item_id, i_check_availability) = 'Y'

ORDER BY a.row_order, menu_item_id;

OPEN o_combo FOR

SELECT

mc.*, f.currency_code, (CASE

WHEN blob_id IS NOT NULL THEN 'Y'

ELSE 'N'

END) AS has_image

FROM .menu_combo AS mc, .restaurant AS f

WHERE mc.restaurant_id = i_restaurant_id AND active = 'Y' AND
mc.restaurant_id = f.restaurant_id AND (menu_item_category_id =
i_category_id OR i_category_id IS NULL)

ORDER BY combo_id;

END;

$BODY$;


   1. open connection
   2. set  auto commit to false
   3. create callable statement
   4. execute the call
   5. get the results
   6. set autocommit to true
   7. close the resultset,callable statement and connection



On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan  wrote:

> attached is the screenshot of RDS performance insights for AWS and it
> shows high waiting client writes. The api performance is slow. I read that
> this might be due to IOPS on RDS. However we have 80k IOPS on this test
> RDS.
>
> Below is the query which is being load tested
>
> SELECT
>
>a.menu_item_id,
>a.menu_item_name,
>a.menu_item_category_id,
>b.menu_item_category_desc,
>c.menu_item_variant_id,
>c.menu_item_variant_type_id,
>c.price,
>c.size_id,
>c.parent_menu_item_variant_id,
>d.menu_item_variant_type_desc,
>e.size_desc,
>f.currency_code,
>a.image,
>a.mark_id,
>m.mark_name
>
>  FROM .menu_item_category AS b, .menu_item_variant AS
> c,
>.menu_item_variant_type AS d, .item_size AS e,
> .restaurant AS f,
>.menu_item AS a
>
>LEFT OUTER JOIN .mark AS m
>  ON (a.mark_id = m.mark_id)
>
>  WHERE a.menu_item_category_id =
> b.menu_item_category_id AND a.menu_item_id = c.menu_item_id AND
>c.menu_item_variant_type_id =
> d.menu_item_variant_type_id AND d.is_hidden = 'false' AND
>c.size_id = e.size_id AND a.restaurant_id =
> f.restaurant_id AND f.restaurant_id = 1528 AND
>(a.menu_item_category_id = NULL OR NULL IS NULL)
>
>AND c.menu_item_variant_id = (SELECT
> min(menu_item_variant_id)
>  FROM
> .menu_item_variant
>  WHERE
> menu_item_id = a.menu_item_id AND deleted = 'N'
>  LIMIT 1) AND
> a.active = 'Y'
>AND (CONCAT_WS('', ',', a.hidden_branch_ids,
> ',') NOT LIKE CONCAT_WS('', '%,4191,%') OR
> NULL IS NULL)
>AND .is_menu_item_available(a.menu_item_id, 'Y') = 'Y'
>
>ORDER BY a.row_order, menu_item_id;
>
> --Ayub

Re: Master - Slave Replication Window Server

[Atul Kumar]

Hi Haseeb,

I had configured replication on windows and made a document in an easy way.
I am not expert but I hope it will help you.





Regards
Atul



On Tuesday, June 15, 2021, Haseeb Khan  wrote:

> Hello Rory,
>
> Yes , I have followed the document and configured each and everything. But
> I can’t see archive_Wal_segments is copying to the folder which I have
> created. So the issue I am facing is that where should I create the archive
> folder should I create on master or slave server ? Might be I am missing
> something or doing some mistake.
>
> Would appreciated, if you could help in this regard
>
> Kindly send me the Postgres general email, so I can raise this issue over
> there as well
>
> Thanks in advance
>
> *Br*,
> Haseeb Ahmad
>
> On 15-Jun-2021, at 5:05 PM, Rory Campbell-Lange 
> wrote:
>
> On 15/06/21, Haseeb Khan (khanhasee...@gmail.com) wrote:
>
> I have confusion below, Should we create an archive path on the standby
>
> server and then set it to recovery.conf file ?
>
>
> restore_command = 'cp /path/to/archive/%f %p'
>
>
> Hi Hasseb
>
> Are you following this procedure?
> https://www.postgresql.org/docs/13/continuous-archiving.html
>
> If so please let us know what problem you are experiencing.
>
> Also, this is the postgres performance list. Please move this conversation
> to postgresql general.
>
> Cheers
> Rory
>
>
>
<>

Re: waiting for client write

[Vijaykumar Jain]

On Tue, 15 Jun 2021 at 21:13, Ayub Khan  wrote:
>
>
> Would it be a cursor issue on postgres, as there seems to be a difference
in how cursors are handled in postgres and Oracle database. It seems
cursors are returned as buffers to the client side. Below are the steps we
take from jdbc side

i did this as well to understand what caused clientwrite wait event.
open a cursor, fetch some rows but not all, and not close them. run this
for multiple connections.
All i got was some client read, but no client write.
I think i might have to intentionally mangle some response packets from
server to client to see if that helps,
but I was thinking i am diverting from the main problem.
unless we have a reproducible dataset to work on, i was not sure it was
helping.

If you can have some sample table(s), and can create a proc on the same
lines as above to query data, and still get the same issues.
that would be helpful to debug further.

else,
you may have to give a stacktrace using pstack or gdb / perf etc to help
figure out what is going on at code level.
Profiling_with_perf 
trace-query-processing-internals-with-debugger


This may / may not help, but it'll help learn to eliminate noise :)