Thread: Profile what the production server is doing

Is there a tool which does this for PostgreSQL?

Take a "snapshot" of what the server is doing about 10 times per second.
Write this to a file.
After N hours you can aggregate the file.
What does the server do most of the time?
Which tables/index gets used the most.

Before optimizing a database, I would like to know what is going
on in the production system.

I know that there are internal tables like pg_stat_statements.
But I guess doing a snapshot every N millseconds will present a
better picture of what is going in in real life.

Is there already a tool which goes this way?

Or is there a better way?

Regards,
   Thomas Güttler

-- 
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines

Hi,

On Mon, Jul 23, 2018 at 1:18 PM, Thomas Güttler
<guettliml@thomas-guettler.de> wrote:
> Is there a tool which does this for PostgreSQL?
>
> Take a "snapshot" of what the server is doing about 10 times per second.
> Write this to a file.
> After N hours you can aggregate the file.
> What does the server do most of the time?
> Which tables/index gets used the most.
>
> Before optimizing a database, I would like to know what is going
> on in the production system.
>
> I know that there are internal tables like pg_stat_statements.
> But I guess doing a snapshot every N millseconds will present a
> better picture of what is going in in real life.
>
> Is there already a tool which goes this way?

You can look at powa (https://powa.readthedocs.io/) which aims to
provide this kind of information.

Am 23.07.2018 um 13:38 schrieb Julien Rouhaud:
> Hi,
> 
> On Mon, Jul 23, 2018 at 1:18 PM, Thomas Güttler
> <guettliml@thomas-guettler.de> wrote:
>> Is there a tool which does this for PostgreSQL?
>>
>> Take a "snapshot" of what the server is doing about 10 times per second.
>> Write this to a file.
>> After N hours you can aggregate the file.
>> What does the server do most of the time?
>> Which tables/index gets used the most.
>>
>> Before optimizing a database, I would like to know what is going
>> on in the production system.
>>
>> I know that there are internal tables like pg_stat_statements.
>> But I guess doing a snapshot every N millseconds will present a
>> better picture of what is going in in real life.
>>
>> Is there already a tool which goes this way?
> 
> You can look at powa (https://powa.readthedocs.io/) which aims to
> provide this kind of information.
> 

AFAIK powa is based on pg_stat_statements not on statistical samples.
But maye I am wrong.

Regards,
   Thomas Güttler

-- 
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines

Am 23.07.2018 um 16:01 schrieb Baron Schwartz:
> I'm biased, but I think VividCortex (my company's product) is amazing at this.

Looks goog, but "Contact us for pricing options" from https://www.vividcortex.com/product/pricing

Why do you hide your prices?

Regards,
   Thomas Güttler

> 
> On Mon, Jul 23, 2018 at 7:18 AM Thomas Güttler <guettliml@thomas-guettler.de <mailto:guettliml@thomas-guettler.de>>
wrote:
> 
>     Is there a tool which does this for PostgreSQL?
> 
>     Take a "snapshot" of what the server is doing about 10 times per second.
>     Write this to a file.
>     After N hours you can aggregate the file.
>     What does the server do most of the time?
>     Which tables/index gets used the most.
> 
>     Before optimizing a database, I would like to know what is going
>     on in the production system.
> 
>     I know that there are internal tables like pg_stat_statements.
>     But I guess doing a snapshot every N millseconds will present a
>     better picture of what is going in in real life.
> 
>     Is there already a tool which goes this way?
> 
>     Or is there a better way?
> 
>     Regards,
>         Thomas Güttler
> 
>     -- 
>     Thomas Guettler http://www.thomas-guettler.de/
>     I am looking for feedback: https://github.com/guettli/programming-guidelines
> 

-- 
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines

Am 23.07.2018 um 17:16 schrieb Flo Rance:
> pgobserver might do that as well, particulary useful for functions performances.
> 
> https://github.com/zalando/PGObserver
> 


Thank you for pointing me to this.

After googling for "PGObserver powa" I found nice collection of current tools:

    https://www.quora.com/What-are-the-best-graphical-Monitoring-tools-for-Postgresql

BTW, PGObserver seems a bit dated. There are only very few updates during the last months.
Is there an successor?

Regards,
   Thomas

-- 
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines

On Wed, Jul 25, 2018 at 11:14 AM, Thomas Güttler
<guettliml@thomas-guettler.de> wrote:
>
> AFAIK powa is based on pg_stat_statements not on statistical samples.
> But maye I am wrong.

Indeed, it's based on pg_stat_statements, but other extensions are
supported too.  Since pg_stat_statements already provides cumulated
counters, there's no need to do sampling.  But if you're interested in
wait events information for instance, it supports (in development
version) pg_wait_sampling extension, which does sampling to provide
efficient and informative informations.

This sound good. Looks like an automated bootleneck detection
could be possible with pg_wait_sampling.

Regards,
   Thomas

Am 25.07.2018 um 12:25 schrieb Julien Rouhaud:
> On Wed, Jul 25, 2018 at 11:14 AM, Thomas Güttler
> <guettliml@thomas-guettler.de> wrote:
>>
>> AFAIK powa is based on pg_stat_statements not on statistical samples.
>> But maye I am wrong.
> 
> Indeed, it's based on pg_stat_statements, but other extensions are
> supported too.  Since pg_stat_statements already provides cumulated
> counters, there's no need to do sampling.  But if you're interested in
> wait events information for instance, it supports (in development
> version) pg_wait_sampling extension, which does sampling to provide
> efficient and informative informations.
> 

-- 
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines

Wow, freakin cool, can't wait to start fiddling with pg_wait_sampling.  Reckon we can get lightweight locks and spinlocks history with this cool new extension instead of awkwardly and repeatedly querying the pg_stat_activity table.

Regards,
Michael Vitale

Thomas Güttler

Thursday, July 26, 2018 7:27 AM

This sound good. Looks like an automated bootleneck detection
could be possible with pg_wait_sampling.

Regards,
  Thomas