Re: [GENERAL] pg_stat_statements -- Historical Query

2017-08-10 Thread Julien Rouhaud 
On Thu, Aug 10, 2017 at 3:00 PM, Melvin Davidson  wrote:
>
> If you are interested in historical stats, you would probably fair a lot 
> better with PgBadger. It is free
> and highly customizable.  In addition to SQL call rates at different times, 
> it provides analysis of
> most used queries, slowest queries, etc.

So is doing powa, but in real time.

>
> https://sourceforge.net/projects/pgbadger/
>

FYI pgBadger is no longer available on sourceforge (see the
downladable file), you should now download it on
https://github.com/dalibo/pgbadger/releases


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_stat_statements -- Historical Query

2017-08-10 Thread Melvin Davidson 
On Thu, Aug 10, 2017 at 3:30 AM, Julien Rouhaud  wrote:

> On Thu, Aug 10, 2017 at 6:41 AM, Michael Paquier
>  wrote:
> > On Thu, Aug 10, 2017 at 6:23 AM, anand086  wrote:
> >> I was looking for a way to maintain historical query details in
> Postgres to
> >> answer questions like
> >>
> >> What was the sql call rate between time X and Y?
> >> Did the execution count increase for the query increase between time X
> and
> >> Y?
> >> In past 10mins what all queries were run in the db?
> >>
> >> and few others like this.
> >>
> >> What would be best way to do it? Any thoughts?
> >
> > pg_stat_statements has a function allowing to reset what the view
> > pg_stat_statements holds as information. You could copy periodically
> > the data of pg_stat_statements and then invoke
> > pg_stat_statements_reset to put everything back to zero. Then you
> > would just need to do your analysis work based on the amount of data
> > copied into your custom table.
>
> You can also use powa-archivist extension which does the aggregation,
> data retention and so on with a bgworker:
> https://github.com/dalibo/powa-archivist.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


*If you are interested in historical stats, you would probably fair a lot
better with PgBadger. It is free*
*and highly customizable.  In addition to SQL call rates at different
times, it provides analysis of*

*most used queries, slowest queries, etc.*


*https://sourceforge.net/projects/pgbadger/
*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] pg_stat_statements -- Historical Query

2017-08-10 Thread Julien Rouhaud 
On Thu, Aug 10, 2017 at 6:41 AM, Michael Paquier
 wrote:
> On Thu, Aug 10, 2017 at 6:23 AM, anand086  wrote:
>> I was looking for a way to maintain historical query details in Postgres to
>> answer questions like
>>
>> What was the sql call rate between time X and Y?
>> Did the execution count increase for the query increase between time X and
>> Y?
>> In past 10mins what all queries were run in the db?
>>
>> and few others like this.
>>
>> What would be best way to do it? Any thoughts?
>
> pg_stat_statements has a function allowing to reset what the view
> pg_stat_statements holds as information. You could copy periodically
> the data of pg_stat_statements and then invoke
> pg_stat_statements_reset to put everything back to zero. Then you
> would just need to do your analysis work based on the amount of data
> copied into your custom table.

You can also use powa-archivist extension which does the aggregation,
data retention and so on with a bgworker:
https://github.com/dalibo/powa-archivist.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_stat_statements -- Historical Query

2017-08-09 Thread Michael Paquier 
On Thu, Aug 10, 2017 at 6:23 AM, anand086  wrote:
> I was looking for a way to maintain historical query details in Postgres to
> answer questions like
>
> What was the sql call rate between time X and Y?
> Did the execution count increase for the query increase between time X and
> Y?
> In past 10mins what all queries were run in the db?
>
> and few others like this.
>
> What would be best way to do it? Any thoughts?

pg_stat_statements has a function allowing to reset what the view
pg_stat_statements holds as information. You could copy periodically
the data of pg_stat_statements and then invoke
pg_stat_statements_reset to put everything back to zero. Then you
would just need to do your analysis work based on the amount of data
copied into your custom table.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_stat_statements -- Historical Query

2017-08-09 Thread anand086 
Hi,

I was looking for a way to maintain historical query details in Postgres to
answer questions like

What was the sql call rate between time X and Y?
Did the execution count increase for the query increase between time X and
Y?
In past 10mins what all queries were run in the db?

and few others like this.

What would be best way to do it? Any thoughts? 







--
View this message in context: 
http://www.postgresql-archive.org/pg-stat-statements-Historical-Query-tp5977110.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general