pg_stat_statements -- Historical Query

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

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

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.

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

On Thu, Aug 10, 2017 at 3:30 AM, Julien Rouhaud <rjuju123@gmail.com> wrote:

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.

--
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/
<https://sourceforge.net/projects/pgbadger/&gt;*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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

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