Thread: [GENERAL] pg_stat_statements -- Historical Query

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.

On Thu, Aug 10, 2017 at 6:23 AM, anand086 <anand086@gmail.com> 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

On Thu, Aug 10, 2017 at 6:41 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Thu, Aug 10, 2017 at 6:23 AM, anand086 <anand086@gmail.com> 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.

On Thu, Aug 10, 2017 at 3:00 PM, Melvin Davidson <melvin6925@gmail.com> 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