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

Started by Sheena, Prabhjotalmost 11 years ago7 messagesgeneral

Sheena, Prabhjot

Prabhjot.Singh@classmates.com

almost 11 years ago

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

andy@squeakycode.net

almost 11 years ago

In reply to: Sheena, Prabhjot (#1)

Re: 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>

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

scott.marlowe@gmail.com

almost 11 years ago

In reply to: Sheena, Prabhjot (#1)

Re: 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.

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

Sheena, Prabhjot

Prabhjot.Singh@classmates.com

almost 11 years ago

In reply to: Scott Marlowe (#3)

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

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.

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

Melvin Davidson

melvin6925@gmail.com

almost 11 years ago

In reply to: Scott Marlowe (#3)

Re: 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>
wrote:

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.

--
Sent via pgsql-general mailing list (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.

Sheena, Prabhjot

Prabhjot.Singh@classmates.com

almost 11 years ago

In reply to: Melvin Davidson (#5)

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

Thanks a lot. That’s what I am looking for

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@aklaver.com

almost 11 years ago

In reply to: Sheena, Prabhjot (#6)

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

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

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