Обсуждение: database lagging

Поиск
Список
Период
Сортировка

database lagging

От
Pepe TD Vo
Дата:
I am doing CIS Benchmart on Postgreql 12.  After alter parameters and when I tried to create a new role.  It's lagging and didn't prompt out the result, until I hit the ctl C key.  Even alter role.  All I needed to hit ctl C key if not, it hung there for ever.  Is there a way to fix it?

postgres=# create role appuser;
^CCancel request sent
CREATE ROLE

postgres=# ALTER ROLE appuser NOSUPERUSER;
^CCancel request sent
ALTER ROLE

 
postgres=# ALTER ROLE appuser NOREPLICATION;
^CCancel request sent
ALTER ROLE

postgres=# ALTER ROLE appuser NOCREATEROLE;
^CCancel request sent
ALTER ROLE

postgres=# ALTER ROLE appuser NOHERIT;
^CCancel request sent
ALTER ROLE



RE: database lagging

От
Alvaro Aguayo
Дата:

Is there any other thing running with that database? If so, I would consider a locking problem. CREATE/ALTER ROLE should never take too much time, unless there is some kind of lock in a global catalog.

 

Regards,

 

Alvaro Aguayo

Operations Manager

Open Comb Systems E.I.R.L.

 

From: Pepe TD Vo <pepevo@yahoo.com>
Sent: 23 July 2021 12:52
To: pgsql-admin@lists.postgresql.org
Subject: database lagging

 

I am doing CIS Benchmart on Postgreql 12.  After alter parameters and when I tried to create a new role.  It's lagging and didn't prompt out the result, until I hit the ctl C key.  Even alter role.  All I needed to hit ctl C key if not, it hung there for ever.  Is there a way to fix it?

 

postgres=# create role appuser;

^CCancel request sent

CREATE ROLE

 

postgres=# ALTER ROLE appuser NOSUPERUSER;

^CCancel request sent

ALTER ROLE

 

 

postgres=# ALTER ROLE appuser NOREPLICATION;

^CCancel request sent

ALTER ROLE

 

postgres=# ALTER ROLE appuser NOCREATEROLE;

^CCancel request sent

ALTER ROLE

 

postgres=# ALTER ROLE appuser NOHERIT;

^CCancel request sent

ALTER ROLE

 

 

 

Re: database lagging

От
Pepe TD Vo
Дата:
nothing is running. Only psql and background writer, checkpointermain, walwritermain, autovacuummain, logicalLauncherMain from pg_stat_activity (6 rows) nothing else is running

no row select on "select relation::regclass, * from pg_locks where not granted;

select on "select relation::regclass, * from pg_locks;
2 rows for pg_locks relation for AccessShareLock and virtualxid for ExclusiveLock.

What else can I check and correct ?





Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love, and forgive more.EmojiEmojiEmoji
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success




On Friday, July 23, 2021, 02:07:15 PM EDT, Alvaro Aguayo <aaguayo@opensysperu.com> wrote:


Is there any other thing running with that database? If so, I would consider a locking problem. CREATE/ALTER ROLE should never take too much time, unless there is some kind of lock in a global catalog.

 

Regards,

 

Alvaro Aguayo

Operations Manager

Open Comb Systems E.I.R.L.

 

From: Pepe TD Vo <pepevo@yahoo.com>
Sent: 23 July 2021 12:52
To: pgsql-admin@lists.postgresql.org
Subject: database lagging

 

I am doing CIS Benchmart on Postgreql 12.  After alter parameters and when I tried to create a new role.  It's lagging and didn't prompt out the result, until I hit the ctl C key.  Even alter role.  All I needed to hit ctl C key if not, it hung there for ever.  Is there a way to fix it?

 

postgres=# create role appuser;

^CCancel request sent

CREATE ROLE

 

postgres=# ALTER ROLE appuser NOSUPERUSER;

^CCancel request sent

ALTER ROLE

 

 

postgres=# ALTER ROLE appuser NOREPLICATION;

^CCancel request sent

ALTER ROLE

 

postgres=# ALTER ROLE appuser NOCREATEROLE;

^CCancel request sent

ALTER ROLE

 

postgres=# ALTER ROLE appuser NOHERIT;

^CCancel request sent

ALTER ROLE

 

 

 

Re: database lagging

От
Vijaykumar Jain
Дата:

On Fri, 23 Jul 2021 at 23:37, Alvaro Aguayo <aaguayo@opensysperu.com> wrote:

Is there any other thing running with that database? If so, I would consider a locking problem. CREATE/ALTER ROLE should never take too much time, unless there is some kind of lock in a global catalog.


I am not so sure this is lock issue? Ctrl C would not complete the command if it were a lock issue?
Pepe can confirm if he sees any locks though ?

Hey Pepe,
Can you let us know
what postgres version?
what psql version?
what OS?

also, is it possible for you to collect stack trace of the hanging process?

also is it only hanging in interactive mode? 
psql -c 'alter role foobar superuser;'   
does this complete fine ?



 

 

Regards,

 

Alvaro Aguayo

Operations Manager

Open Comb Systems E.I.R.L.

 

From: Pepe TD Vo <pepevo@yahoo.com>
Sent: 23 July 2021 12:52
To: pgsql-admin@lists.postgresql.org
Subject: database lagging

 

I am doing CIS Benchmart on Postgreql 12.  After alter parameters and when I tried to create a new role.  It's lagging and didn't prompt out the result, until I hit the ctl C key.  Even alter role.  All I needed to hit ctl C key if not, it hung there for ever.  Is there a way to fix it?

 

postgres=# create role appuser;

^CCancel request sent

CREATE ROLE

 

postgres=# ALTER ROLE appuser NOSUPERUSER;

^CCancel request sent

ALTER ROLE

 

 

postgres=# ALTER ROLE appuser NOREPLICATION;

^CCancel request sent

ALTER ROLE

 

postgres=# ALTER ROLE appuser NOCREATEROLE;

^CCancel request sent

ALTER ROLE

 

postgres=# ALTER ROLE appuser NOHERIT;

^CCancel request sent

ALTER ROLE

 

 

 



--
Thanks,
Vijay
Mumbai, India

Re: database lagging

От
Vijaykumar Jain
Дата:


On Fri, 23 Jul 2021 at 23:59, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:

On Fri, 23 Jul 2021 at 23:37, Alvaro Aguayo <aaguayo@opensysperu.com> wrote:

Is there any other thing running with that database? If so, I would consider a locking problem. CREATE/ALTER ROLE should never take too much time, unless there is some kind of lock in a global catalog.


I am not so sure this is lock issue? Ctrl C would not complete the command if it were a lock issue?
Pepe can confirm if he sees any locks though ?

Hey Pepe,
Can you let us know
what postgres version?
what psql version?
what OS?

also, is it possible for you to collect stack trace of the hanging process?

also is it only hanging in interactive mode? 
psql -c 'alter role foobar superuser;'   
does this complete fine ?


also forgot to ask.
does this only hang only alter role commands ?
or any other command /query like 
\du
select 1;
create table t(id int);


Re: database lagging

От
Pepe TD Vo
Дата:
I'm running rh-postgresql12 
psql version 12.5
OS RHEL 7
I can run select query but lagging to create role, table,

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love, and forgive more.EmojiEmojiEmoji
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success




On Friday, July 23, 2021, 02:40:39 PM EDT, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:




On Fri, 23 Jul 2021 at 23:59, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:

On Fri, 23 Jul 2021 at 23:37, Alvaro Aguayo <aaguayo@opensysperu.com> wrote:

Is there any other thing running with that database? If so, I would consider a locking problem. CREATE/ALTER ROLE should never take too much time, unless there is some kind of lock in a global catalog.


I am not so sure this is lock issue? Ctrl C would not complete the command if it were a lock issue?
Pepe can confirm if he sees any locks though ?

Hey Pepe,
Can you let us know
what postgres version?
what psql version?
what OS?

also, is it possible for you to collect stack trace of the hanging process?

also is it only hanging in interactive mode? 
psql -c 'alter role foobar superuser;'   
does this complete fine ?


also forgot to ask.
does this only hang only alter role commands ?
or any other command /query like 
\du
select 1;
create table t(id int);


Re: database lagging

От
Vijaykumar Jain
Дата:


On Sat, 24 Jul 2021 at 00:29, Pepe TD Vo <pepevo@yahoo.com> wrote:
I'm running rh-postgresql12 
psql version 12.5
OS RHEL 7
I can run select query but lagging to create role, table,

both in interactive and non interactive mode, it is hanging? all write based queries but not read?
if only writes hang, but not reads, do you see any issues/ slowdown with writes on your storage where pg_wal resides?
is it network storage or local?




 

Re: database lagging

От
Rui DeSousa
Дата:
To me that looks like you have synchronous replication and the replica is behind thus your command is waiting for the alter command to be written to the replica’s WAL file. Doing a Ctrl-C terminates the waiting on synchronous replication but the command was already completed on the primary node just not replicated yet.

Can you confirm or deny the use of synchronous replication?  

On Jul 23, 2021, at 1:52 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:

I am doing CIS Benchmart on Postgreql 12.  After alter parameters and when I tried to create a new role.  It's lagging and didn't prompt out the result, until I hit the ctl C key.  Even alter role.  All I needed to hit ctl C key if not, it hung there for ever.  Is there a way to fix it?

postgres=# create role appuser;
^CCancel request sent
CREATE ROLE

postgres=# ALTER ROLE appuser NOSUPERUSER;
^CCancel request sent
ALTER ROLE

 
postgres=# ALTER ROLE appuser NOREPLICATION;
^CCancel request sent
ALTER ROLE

postgres=# ALTER ROLE appuser NOCREATEROLE;
^CCancel request sent
ALTER ROLE

postgres=# ALTER ROLE appuser NOHERIT;
^CCancel request sent
ALTER ROLE




Re: database lagging

От
Avinash Kumar
Дата:
Hi, 

On Fri, Jul 23, 2021 at 2:52 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
I am doing CIS Benchmart on Postgreql 12.  After alter parameters and when I tried to create a new role.  It's lagging and didn't prompt out the result, until I hit the ctl C key.  Even alter role.  All I needed to hit ctl C key if not, it hung there for ever.  Is there a way to fix it?

postgres=# create role appuser;
^CCancel request sent
CREATE ROLE
I would only expect such a behaviour when there is Synchronous Replication enabled but the Standby is not reachable. 
Check the parameters : synchronous_mode combined with synchronous_standby_names. 

postgres=# ALTER ROLE appuser NOSUPERUSER;
^CCancel request sent
ALTER ROLE

 
postgres=# ALTER ROLE appuser NOREPLICATION;
^CCancel request sent
ALTER ROLE

postgres=# ALTER ROLE appuser NOCREATEROLE;
^CCancel request sent
ALTER ROLE

postgres=# ALTER ROLE appuser NOHERIT;
^CCancel request sent
ALTER ROLE





--
Regards,
Avinash Vallarapu
CEO,
MigOps Inc.

Re: database lagging

От
Pepe TD Vo
Дата:
yes, I'm running replication database.  Parameters below are on both master and slave's postgresql.conf

wal_level = replica
synchronous_commit = on
synchronous_standby_names ='*'

server storage is fine.  Plenty of spaces on pg_wal (local), only 5% used from 80G.  Look like only all write have problems and read is ok.


Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love, and forgive more.EmojiEmojiEmoji
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success




On Friday, July 23, 2021, 03:11:58 PM EDT, Avinash Kumar <avinash.vallarapu@gmail.com> wrote:


Hi, 

On Fri, Jul 23, 2021 at 2:52 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
I am doing CIS Benchmart on Postgreql 12.  After alter parameters and when I tried to create a new role.  It's lagging and didn't prompt out the result, until I hit the ctl C key.  Even alter role.  All I needed to hit ctl C key if not, it hung there for ever.  Is there a way to fix it?

postgres=# create role appuser;
^CCancel request sent
CREATE ROLE
I would only expect such a behaviour when there is Synchronous Replication enabled but the Standby is not reachable. 
Check the parameters : synchronous_mode combined with synchronous_standby_names. 

postgres=# ALTER ROLE appuser NOSUPERUSER;
^CCancel request sent
ALTER ROLE

 
postgres=# ALTER ROLE appuser NOREPLICATION;
^CCancel request sent
ALTER ROLE

postgres=# ALTER ROLE appuser NOCREATEROLE;
^CCancel request sent
ALTER ROLE

postgres=# ALTER ROLE appuser NOHERIT;
^CCancel request sent
ALTER ROLE





--
Regards,
Avinash Vallarapu
CEO,
MigOps Inc.

Re: database lagging

От
Vijaykumar Jain
Дата:
yeah, ignore what I asked. Glad others jumped in before it was deviating.
as others said, it would be the replica is lagging or is not reachable/down in synchronous replication.
select * from pg_stat_replication/pg_replication_slots would help show the same.



On Sat, 24 Jul 2021 at 00:33, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:


On Sat, 24 Jul 2021 at 00:29, Pepe TD Vo <pepevo@yahoo.com> wrote:
I'm running rh-postgresql12 
psql version 12.5
OS RHEL 7
I can run select query but lagging to create role, table,

both in interactive and non interactive mode, it is hanging? all write based queries but not read?
if only writes hang, but not reads, do you see any issues/ slowdown with writes on your storage where pg_wal resides?
is it network storage or local?




 


--
Thanks,
Vijay
Mumbai, India

Re: database lagging

От
Pepe TD Vo
Дата:
thank you for information url
both primary_conninfo on master and slave are not set
max_wal_senders = 10
max_replication_slots = 1
's 
when I run 'select * from pg_stat_replication/pg_replication_slots;'
0 rows

archive_command='/opt/rh/rh-postgresql12/root/bin/syncwal.sh %p %f'

syncwal.sh contains:
#!/bin/bash
scp $1 slave_ip:/var/opt/rh/rh-postgres12/lib/pgsql/walarchive/$2
if [ $? != 0 ]
then 
echo "Archiver error:"
exit 1
fi
exit 0

do I need to set the restore_command and archive_cleanup_command in slave's postgresql.conf only?

another issue is when I restart postgresql.service in master, it takes a little longer as usual but in slave, prompt out as soon as I hit enter to restart the postgresql.service

after modify the postgresql.conf with archive_cleanup_command and restore_command on slave, master is still lagging for write.


Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love, and forgive more.EmojiEmojiEmoji
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success




On Friday, July 23, 2021, 03:44:07 PM EDT, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:


yeah, ignore what I asked. Glad others jumped in before it was deviating.
as others said, it would be the replica is lagging or is not reachable/down in synchronous replication.
select * from pg_stat_replication/pg_replication_slots would help show the same.



On Sat, 24 Jul 2021 at 00:33, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:


On Sat, 24 Jul 2021 at 00:29, Pepe TD Vo <pepevo@yahoo.com> wrote:
I'm running rh-postgresql12 
psql version 12.5
OS RHEL 7
I can run select query but lagging to create role, table,

both in interactive and non interactive mode, it is hanging? all write based queries but not read?
if only writes hang, but not reads, do you see any issues/ slowdown with writes on your storage where pg_wal resides?
is it network storage or local?




 


--
Thanks,
Vijay
Mumbai, India