Thread: database lagging

database lagging

From

Pepe TD Vo

Date:

23 July 2021, 17:52:21

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

From

Alvaro Aguayo

Date:

23 July 2021, 18:07:08

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

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

From

Pepe TD Vo

Date:

23 July 2021, 18:27:01

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.

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:

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

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

From

Vijaykumar Jain

Date:

23 July 2021, 18:29:29

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?

Getting a stack trace of a running PostgreSQL backend on Linux/BSD - PostgreSQL wiki

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

From

Vijaykumar Jain

Date:

23 July 2021, 18:40:07

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?
Getting a stack trace of a running PostgreSQL backend on Linux/BSD - PostgreSQL wiki

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

From

Pepe TD Vo

Date:

23 July 2021, 18:58:21

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.

**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?
Getting a stack trace of a running PostgreSQL backend on Linux/BSD - PostgreSQL wiki

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

From

Vijaykumar Jain

Date:

23 July 2021, 19:03:49

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

From

Rui DeSousa

Date:

23 July 2021, 19:08:55

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

From

Avinash Kumar

Date:

23 July 2021, 19:11:33

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.

Email: avi@migops.com

Re: database lagging

From

Pepe TD Vo

Date:

23 July 2021, 19:28:23

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.

**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.

Email: avi@migops.com

Re: database lagging

From

Vijaykumar Jain

Date:

23 July 2021, 19:43:55

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.

PostgreSQL: Documentation: 13: 26.2. Log-Shipping Standby Servers

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

From

Pepe TD Vo

Date:

23 July 2021, 20:26:01

thank you for information url

both primary_conninfo on master and slave are not set

max_wal_senders = 10

max_replication_slots = 1

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

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.

**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.

PostgreSQL: Documentation: 13: 26.2. Log-Shipping Standby Servers

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