Thread: How to get total count of queries hitting DB per day or per hour?

On 7/9/2015 1:04 PM, Sheena, Prabhjot wrote:
> Guys
>
>                Is there a way to get total count of queries hitting
> Postgresql DB(verison 9.3) per day or per hour ? I cannot turn on
> log_statement=all coz that’s too much logging for our log file to
> handle. Is there another way to do that.
>
> Thanks
>
> Prabhjot
>

My collectd config:

<Plugin postgresql>
     <Query ttlbackends>
         Statement "SELECT sum(numbackends) AS count, sum(xact_commit)
as ttlcommit FROM pg_stat_database;"
         <Result>
             Type "pg_numbackends"
             ValuesFrom "count"
         </Result>
         <Result>
             Type "pg_xact"
             ValuesFrom "ttlcommit"
         </Result>
     </Query>

     <Database postgres>
         #Interval 60
         Host "/tmp"
         Port "5432"
         SSLMode "disable"
         Query ttlbackends
     </Database>
</Plugin>

On Thu, Jul 9, 2015 at 12:04 PM, Sheena, Prabhjot
<Prabhjot.Singh@classmates.com> wrote:
> Guys
>
>               Is there a way to get total count of queries hitting
> Postgresql DB(verison 9.3) per day or per hour ? I cannot turn on
> log_statement=all coz that’s too much logging for our log file to handle. Is
> there another way to do that.

If you just need totals, turn on just log_duration and then you can
grep the logs etc for that.

I did try that but that’s too much logging as well. 

Prabhjot Singh
Database Administrator

CLASSMATES
1501 4th Ave., Suite 400
Seattle, WA 98101
206.301.4937 o
206.301.5701 f

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com] 
Sent: Thursday, July 9, 2015 1:07 PM
To: Sheena, Prabhjot
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to get total count of queries hitting DB per day or per hour?

On Thu, Jul 9, 2015 at 12:04 PM, Sheena, Prabhjot <Prabhjot.Singh@classmates.com> wrote:
> Guys
>
>               Is there a way to get total count of queries hitting 
> Postgresql DB(verison 9.3) per day or per hour ? I cannot turn on 
> log_statement=all coz that’s too much logging for our log file to 
> handle. Is there another way to do that.

If you just need totals, turn on just log_duration and then you can grep the logs etc for that.

On 07/09/2015 01:26 PM, Sheena, Prabhjot wrote:
> Thanks a lot. That’s what I am looking for

Might also want to look at:

http://www.postgresql.org/docs/9.4/static/pgstatstatements.html

>
> Thanks
>
> */Prabhjot Singh/*
>
> *From:*Melvin Davidson [mailto:melvin6925@gmail.com]
> *Sent:* Thursday, July 9, 2015 1:24 PM
> *To:* Scott Marlowe
> *Cc:* Sheena, Prabhjot; pgsql-general@postgresql.org
> *Subject:* Re: [GENERAL] How to get total count of queries hitting DB
> per day or per hour?
>
> Run two cron jobs, each runs exacty 24 hours apart.
> Each gets the result of the following query:
>
> SELECT SUM(xact_commit + xact_rollback)
>    FROM pg_stat_database;
>
>   Then subtract the result of the first cron from the second.
>   That's the number of transactions in a 24 hr period.
>
> On Thu, Jul 9, 2015 at 4:07 PM, Scott Marlowe <scott.marlowe@gmail.com
> <mailto:scott.marlowe@gmail.com>> wrote:
>
>     On Thu, Jul 9, 2015 at 12:04 PM, Sheena, Prabhjot
>     <Prabhjot.Singh@classmates.com
>     <mailto:Prabhjot.Singh@classmates.com>> wrote:
>      > Guys
>      >
>      >               Is there a way to get total count of queries hitting
>      > Postgresql DB(verison 9.3) per day or per hour ? I cannot turn on
>      > log_statement=all coz that’s too much logging for our log file to
>     handle. Is
>      > there another way to do that.
>
>     If you just need totals, turn on just log_duration and then you can
>     grep the logs etc for that.
>
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
>
> *Melvin Davidson*
> *I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you. Image removed by sender.*
>


--
Adrian Klaver
adrian.klaver@aklaver.com