On Thu, Aug 10, 2017 at 3:30 AM, Julien Rouhaud <rjuju123@xxxxxxxxx> wrote:
On Thu, Aug 10, 2017 at 6:41 AM, Michael Paquier
<michael.paquier@xxxxxxxxx> wrote:
> On Thu, Aug 10, 2017 at 6:23 AM, anand086 <anand086@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
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 ofmost used queries, slowest queries, etc.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
