PPM Aurora Postgres Configuration Problem

Percona Monitoring and Management (PMM) PMM 2.x
, ,
1

Good morning everyone,
I’m starting to use PPM to monitor some databases we have on the AWS cloud,
in practice we have an Aurora PostgreSql Vers. 15.2 instance, with 15 databases inside.
We have installed and configured PPM Server 2.37.1
I’m trying to set up the databases so I can monitor them, so I followed the directions in the documentation,
but i’m probably doing something wrong.
When I add the instance, via “Add Service” → PostgreSql, I insert
Hostname XXXXXXXXX
Service name XXXXXXXX
Port 5432
Username AAAAAAA
Password BBBBBBB
Database (I enter the name of 1 of the 15 databases)
Max query length (NULL)
environment (WORK)
Region (NULL)
Availability Zones (NULL)
Replication set (NULL)
Clusters (NULL)
Custom labels (NULL)
Additional options (NONE)

For monitoring we have created a dedicated user (without associating the rds_superuser role)

CREATE ROLE percona_monitoring WITH
LOGIN
NOSUPERUSER
INHERIT
NO CREATEDB
NO CREATEROLE
NO REPLICATION
CONNECTION LIMIT 10
PASSWORD ‘BBBBBBBBBB’;

GRANT pg_monitor TO percona_monitoring;

The pg_state_statement is active on the instances, for monitoring

After confirming the entry, the service was not UP , but N/A due to the maximum number of connections that I brought to 30 at that point
and the service became green with status UP.

I immediately noticed on the DB side that the monitoring user used all the available connections, and trying to access QAN, going to the detail tables,
the message “too many connections” is displayed, connections which, looking at the db, result in all 30 being idle.

I realized that by adding this first service, the connections hooked up to all the db’s present, in some cases with 2/3 sessions per db.
So trying to add a new db of the other 14 left, it immediately tells me “too many connection”, and if I increase the maximum number of sessions to the percona_monitoring user
they are immediately occupied and it won’t let me add this DB.

I also set Metrics resolution, sec to RARE

I think there may be something unclear on my side in what I do, and I try to express my doubts:

Is it correct to have a dedicated user without rds_superuser role?
Is it correct to configure every single DB, by adding a new service and/or should I use other connection options for Aurora Postgres clusters?

I apologize if I put the post in the wrong area, but i’m new and it is my firs post :slight_smile:

Thanks in advance to anyone who can help me clarify my ideas and solve the problem.

Have a nice Day
Ivan

2

Hi, sorry, does anyone have any advice on this?
Did I post the request in the wrong thread?

Thank you
have a nice day

Ivan

3

Hi Ivan,
Welcome to the forums!
This is indeed how PMM works. It will use several connections per database to gather metrics.

Is it correct to have a dedicated user without rds_superuser role?

Yes, this should be fine.

Is it correct to configure every single DB, by adding a new service and/or should I use other connection options for Aurora Postgres clusters?

I didn’t understand this question, can you rephrase it or explain in more detail?

I also set Metrics resolution, sec to RARE

This shouldn’t be needed, though, unless you are worried about the load to the database.

4

Hi Augustin
thank you so much for your answer

Within my cluster I have more than 10 databases
postgres
rdsadmin
db1
db2
…
db10

Currently I have configured it by choosing the user dedicated to Percona (not rdssuperadmin with a limit of 15 connections)
and as default database I left Postgres

My question, given that with this configuration via “Amazon Rds” I still receive all the data from all the databases, I wanted to understand if this method was correct or if I had to make many individual connections with the dedicated Percona user for each single database via the PostgreSql service.

In addition, I continue to have the problem, when I connect to QAN, I select the query concerned and when I try to select the “tables” tab in the bottom tab, it brings me back the error “pq: too many” for the table and for the indexes connections for role “percona_monitoring”

Thanks again for your attention

Have a nice day
Ivan

5

Hi Mata,

Let me reply inline.

Yes, this is correct. You only need to add it once, and then PMM will try to connect to other DBs by itself. There is no need for you to add it several times.

Is it possible for you to increase the connection limit for the PMM user? As you have already noted, PMM will try to connect to all databases, and it may use several connections per DB. I would say you should start with setting connection limit per PMM user = 4 * database_count, and then increase until you don’t see issues anymore. Note that max_connections will have to be set accordingly, too, in case it’s low.

Let me know if this works.

6

Hi Agustin

thanks a lot for the advice, actually only one connection for each db was really too little.
The problem seems to have disappeared, if it were to recur again, I will try again to slowly raise the number of connections.

Thank you
Have a nice Day !
Mata

1 Like