Обсуждение: How to know how much CPU, RAM is used by existing 1 database

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

How to know how much CPU, RAM is used by existing 1 database

От
Yi Sun
Дата:
Hi,

There are many databases in our production patroni cluster and it seems it is overloaded, so we decide to migrate the busiest database to a new patroni cluster. 

pgwatch2 is implemented, how to know how much CPU, RAM is used by the database please? Then we can use it to prepare the new patroni cluster hardware. Thank you

Best regards
Dennis

Re: How to know how much CPU, RAM is used by existing 1 database

От
Yi Sun
Дата:
Hi Guys,

Who can help me with this please? I researched but still no result yet, thank you

On Tue, 25 Oct 2022 at 16:30, Yi Sun <yinan81@gmail.com> wrote:
Hi,

There are many databases in our production patroni cluster and it seems it is overloaded, so we decide to migrate the busiest database to a new patroni cluster. 

pgwatch2 is implemented, how to know how much CPU, RAM is used by the database please? Then we can use it to prepare the new patroni cluster hardware. Thank you

Best regards
Dennis

Re: How to know how much CPU, RAM is used by existing 1 database

От
jian he
Дата:


On Wed, Oct 26, 2022 at 11:07 AM Yi Sun <yinan81@gmail.com> wrote:
Hi Guys,

Who can help me with this please? I researched but still no result yet, thank you

On Tue, 25 Oct 2022 at 16:30, Yi Sun <yinan81@gmail.com> wrote:
Hi,

There are many databases in our production patroni cluster and it seems it is overloaded, so we decide to migrate the busiest database to a new patroni cluster. 

pgwatch2 is implemented, how to know how much CPU, RAM is used by the database please? Then we can use it to prepare the new patroni cluster hardware. Thank you

Best regards
Dennis

except min_dynamic_shared_memory (integer)
all other parameters are used to cap the memory. almost all parameters mentioned "database server" which means it's on cluster level.


--
 I recommend David Deutsch's <<The Beginning of Infinity>>

  Jian


Re: How to know how much CPU, RAM is used by existing 1 database

От
Yi Sun
Дата:


On Wed, 26 Oct 2022 at 18:10, jian he <jian.universality@gmail.com> wrote:


On Wed, Oct 26, 2022 at 11:07 AM Yi Sun <yinan81@gmail.com> wrote:
Hi Guys,

Who can help me with this please? I researched but still no result yet, thank you

On Tue, 25 Oct 2022 at 16:30, Yi Sun <yinan81@gmail.com> wrote:
Hi,

There are many databases in our production patroni cluster and it seems it is overloaded, so we decide to migrate the busiest database to a new patroni cluster. 

pgwatch2 is implemented, how to know how much CPU, RAM is used by the database please? Then we can use it to prepare the new patroni cluster hardware. Thank you

Best regards
Dennis

except min_dynamic_shared_memory (integer)
all other parameters are used to cap the memory. almost all parameters mentioned "database server" which means it's on cluster level.


--
 I recommend David Deutsch's <<The Beginning of Infinity>>

  Jian



 Hi Jian he

Thank you for your reply

The parameters are on cluster level, so we still can not know how much memory is used in a specific database, for example, total memory is 64GB
1. How to get how much memory is used on cluster level? For example 40GB
2. How to get how much memory is used in a specific database? For example 30GB, then we can prepare the new patroni cluster 32GB is enough

Thank you
Dennis

Re: How to know how much CPU, RAM is used by existing 1 database

От
Rob Sargent
Дата:
On 10/26/22 08:26, Yi Sun wrote:


On Wed, 26 Oct 2022 at 18:10, jian he <jian.universality@gmail.com> wrote:


On Wed, Oct 26, 2022 at 11:07 AM Yi Sun <yinan81@gmail.com> wrote:
Hi Guys,

Who can help me with this please? I researched but still no result yet, thank you

On Tue, 25 Oct 2022 at 16:30, Yi Sun <yinan81@gmail.com> wrote:
Hi,

There are many databases in our production patroni cluster and it seems it is overloaded, so we decide to migrate the busiest database to a new patroni cluster. 

pgwatch2 is implemented, how to know how much CPU, RAM is used by the database please? Then we can use it to prepare the new patroni cluster hardware. Thank you

Best regards
Dennis

except min_dynamic_shared_memory (integer)
all other parameters are used to cap the memory. almost all parameters mentioned "database server" which means it's on cluster level.


--
 I recommend David Deutsch's <<The Beginning of Infinity>>

  Jian



 Hi Jian he

Thank you for your reply

The parameters are on cluster level, so we still can not know how much memory is used in a specific database, for example, total memory is 64GB
1. How to get how much memory is used on cluster level? For example 40GB
2. How to get how much memory is used in a specific database? For example 30GB, then we can prepare the new patroni cluster 32GB is enough

Thank you
Dennis
You can see connection with pg*backend* functions.  You can log connections to see which db is most commonly accessed. You can log sql to see which table are being touched.  You'll have to assume a correlation to CPU/disc usage.  What have you tried?

Re: How to know how much CPU, RAM is used by existing 1 database

От
Atul Kumar
Дата:
I hope the below link helps...

https://www.enterprisedb.com/blog/monitor-cpu-and-memory-percentage-used-each-process-postgresqlppas-91

On 10/26/22, Rob Sargent <robjsargent@gmail.com> wrote:
> On 10/26/22 08:26, Yi Sun wrote:
>>
>>
>> On Wed, 26 Oct 2022 at 18:10, jian he <jian.universality@gmail.com>
>> wrote:
>>
>>
>>
>>     On Wed, Oct 26, 2022 at 11:07 AM Yi Sun <yinan81@gmail.com> wrote:
>>
>>         Hi Guys,
>>
>>         Who can help me with this please? I researched but still no
>>         result yet, thank you
>>
>>         On Tue, 25 Oct 2022 at 16:30, Yi Sun <yinan81@gmail.com> wrote:
>>
>>             Hi,
>>
>>             There are many databases in our production patroni cluster
>>             and it seems it is overloaded, so we decide to migrate the
>>             busiest database to a new patroni cluster.
>>
>>             pgwatch2 is implemented, how to know how much CPU, RAM is
>>             used by the database please? Then we can use it to prepare
>>             the new patroni cluster hardware. Thank you
>>
>>             Best regards
>>             Dennis
>>
>>
>>     manual:
>>     https://www.postgresql.org/docs/current/runtime-config-resource.html|
>>     |
>>     |except |min_dynamic_shared_memory| (|integer|)|
>>     |all other parameters are used to cap the memory. almost all
>>     parameters mentioned "database server" which means it's on cluster
>>     level.
>>     |
>>     |
>>     |
>>     |
>>     |
>>     --
>>      I recommend David Deutsch's <<The Beginning of Infinity>>
>>
>>       Jian
>>
>>
>>
>>  Hi Jian he
>>
>> Thank you for your reply
>>
>> The parameters are on cluster level, so we still can not know how much
>> memory is used in a specific database, for example, total memory is 64GB
>> 1. How to get how much memory is used on cluster level? For example 40GB
>> 2. How to get how much memory is used in a specific database? For
>> example 30GB, then we can prepare the new patroni cluster 32GB is enough
>>
>> Thank you
>> Dennis
> You can see connection with pg*backend* functions.  You can log
> connections to see which db is most commonly accessed. You can log sql
> to see which table are being touched.  You'll have to assume a
> correlation to CPU/disc usage.  What have you tried?



Re: How to know how much CPU, RAM is used by existing 1 database

От
Laurenz Albe
Дата:
On Wed, 2022-10-26 at 13:37 +0800, Yi Sun wrote:
> Who can help me with this please? I researched but still no result yet, thank you
> 
> On Tue, 25 Oct 2022 at 16:30, Yi Sun <yinan81@gmail.com> wrote:
> > Hi,
> > 
> > There are many databases in our production patroni cluster and it seems it is overloaded,
> > so we decide to migrate the busiest database to a new patroni cluster. 
> > 
> > pgwatch2 is implemented, how to know how much CPU, RAM is used by the database please?
> > Then we can use it to prepare the new patroni cluster hardware. Thank you

You can look how much *private* memory the backends for a certain database
use by examining /proc/<pid>/smaps, you can use the pg_buffercache extension
to see how much of which database is in cache, but you don't know how much
of the kernel page cache is used to cache files for that database.

Yours,
Laurenz Albe