Thread: Selects query stats?

All,

I might be completely crazy here, but it seems every other database
exposes select query stats. Postgres only exposes updates/deletes/
inserts. Is there something I am missing here?

Best Regards,
Dan Gorman

Dan Gorman wrote:
> All,
>
> I might be completely crazy here, but it seems every other database
> exposes select query stats. Postgres only exposes
> updates/deletes/inserts. Is there something I am missing here?

Perhaps.

You can EXPLAIN ANALYZE a SELECT, just like i/u/d -- but then you
don't get the normal result set back. Is that what you mean?

You can turn on log_min_duration_statement and get total SELECT duration
logged.

There's a thread in pgsql-hackers ("Re: Porting MSSQL to PGSQL: trace and
profile") about server-side logging of query plans and stats (for all four of
s/i/u/d), which is indeed not there in PG.

--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.

What I am looking for is that our DB is doing X selects a min.

Turning on logging isn't an option as it will create too much IO in
our enviornment.

Regards,
Dan Gorman

On May 23, 2006, at 11:15 AM, Mischa Sandberg wrote:

> Dan Gorman wrote:
>> All,
>> I might be completely crazy here, but it seems every other
>> database exposes select query stats. Postgres only exposes updates/
>> deletes/inserts. Is there something I am missing here?
>
> Perhaps.
>
> You can EXPLAIN ANALYZE a SELECT, just like i/u/d -- but then you
> don't get the normal result set back. Is that what you mean?
>
> You can turn on log_min_duration_statement and get total SELECT
> duration
> logged.
>
> There's a thread in pgsql-hackers ("Re: Porting MSSQL to PGSQL:
> trace and profile") about server-side logging of query plans and
> stats (for all four of s/i/u/d), which is indeed not there in PG.
>
> --
> Engineers think that equations approximate reality.
> Physicists think that reality approximates the equations.
> Mathematicians never make the connection.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq

Dan Gorman wrote:
> What I am looking for is that our DB is doing X selects a min.

What specifically would you like to measure?
Duration for specific queries?
Queries in an app for which you have no source?
There may be a way to get what you want by other means ...
Details?

I gather you cannot just time the app that's doing the selects,
nor extract those selects and run them via psql and time them
on their own?

>> Dan Gorman wrote:
>>> All,
>>> I might be completely crazy here, but it seems every other database
>>> exposes select query stats. Postgres only exposes
>>> updates/deletes/inserts. Is there something I am missing here?

On Tue, May 23, 2006 at 11:33:12AM -0700, Dan Gorman wrote:
> In any other DB (oracle, mysql) I know how many queries (selects) per
> second the database is executing. How do I get this
> number out of postgres?

You can't. You also can't know how many DML statements were executed
(though you can see how many tuples were inserted/updated/deleted), or
how many transactions have occured (well, you can hack the last one, but
it's a bit of a mess).

It would be nice if all of this was available.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Tue, 2006-05-23 at 11:33 -0700, Dan Gorman wrote:
> In any other DB (oracle, mysql) I know how many queries (selects) per
> second the database is executing. How do I get this
> number out of postgres?
>
>
> I have a perl script that can test this, but no way the db tells me
> how fast it's going.
>
>
> (e.g. in oracle: select sum(executions) from v$sqlarea;)

The Oracle query you show doesn't do that either. It tells you how many
statements have been executed since startup, not per second.

The main problem with what you ask is it only seems to have value. If
the value dips for some reason, you have no way of knowing whether that
occurred because the arrival rate dropped off, there is a system problem
or whether statements just happened to access more data over that time
period. You can collect information that would allow you to understand
what is happening on your system and summarise that as you choose.

--
  Simon Riggs
  EnterpriseDB          http://www.enterprisedb.com

Yeah, I'm not really concerned about the app or sys performance, just
a basic question of how do I get the rate of selects that are being
executed.

In a previous post from Jim, he noted it cannot be done. I am very
surprised postgres can't do this basic functionality. Does anyone
know if the postgres team is working on this?

(btw, I pasted in the wrong oracle query lol - but it can be done in
mysql and oracle)

Best Regards,
Dan Gorman

On May 23, 2006, at 11:51 AM, Simon Riggs wrote:

> On Tue, 2006-05-23 at 11:33 -0700, Dan Gorman wrote:
>> In any other DB (oracle, mysql) I know how many queries (selects) per
>> second the database is executing. How do I get this
>> number out of postgres?
>>
>>
>> I have a perl script that can test this, but no way the db tells me
>> how fast it's going.
>>
>>
>> (e.g. in oracle: select sum(executions) from v$sqlarea;)
>
> The Oracle query you show doesn't do that either. It tells you how
> many
> statements have been executed since startup, not per second.
>
> The main problem with what you ask is it only seems to have value. If
> the value dips for some reason, you have no way of knowing whether
> that
> occurred because the arrival rate dropped off, there is a system
> problem
> or whether statements just happened to access more data over that time
> period. You can collect information that would allow you to understand
> what is happening on your system and summarise that as you choose.
>
> --
>   Simon Riggs
>   EnterpriseDB          http://www.enterprisedb.com
>

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Tue, May 23, 2006 at 11:33:12AM -0700, Dan Gorman wrote:
>> In any other DB (oracle, mysql) I know how many queries (selects) per
>> second the database is executing. How do I get this
>> number out of postgres?

> You can't. You also can't know how many DML statements were executed
> (though you can see how many tuples were inserted/updated/deleted), or
> how many transactions have occured (well, you can hack the last one, but
> it's a bit of a mess).

Hack?  We do count commits and rollbacks (see pg_stat_database); doesn't
seem that hacky to me.

Counting individual statements would add overhead (which the OP already
declared unacceptable) and there are some definitional issues too, like
whether to count statements executed within functions.

            regards, tom lane

Tom Lane wrote:

> Counting individual statements would add overhead (which the OP already
> declared unacceptable) and there are some definitional issues too, like
> whether to count statements executed within functions.

Yeah, the problem seems underspecified.  How do you count statements
added or removed by rewrite rules?  Statements executed to answer RI
queries?  Do you count the statements issued by clients as part of the
startup sequence?  The hypothetical "reset session" of a connection pool
handler?  How do you count 2PC -- when they are executed, or when they
are committed?  What happens to statements in transactions that are
rolled back?  What happens to a statement that is executed partially
because it failed partway (e.g. because of division by zero)?


OTOH ISTM it would be easy to modify Postgres so as to count statements
in the stat collector, by turning pgstat_report_activity into a routine
that sent a count (presumably always 1) instead of the query string, and
then just add the count to a counter on receiving.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> OTOH ISTM it would be easy to modify Postgres so as to count statements
> in the stat collector, by turning pgstat_report_activity into a routine
> that sent a count (presumably always 1) instead of the query string, and
> then just add the count to a counter on receiving.

You wouldn't have to change the backends at all, just modify the
collector to count the number of report_activity messages received.
Might have to play some games with ignoring "<IDLE>" messages, but
otherwise simple (and simplistic...)

            regards, tom lane

On Tue, May 23, 2006 at 03:50:01PM -0400, Alvaro Herrera wrote:
> Tom Lane wrote:
>
> > Counting individual statements would add overhead (which the OP already
> > declared unacceptable) and there are some definitional issues too, like
> > whether to count statements executed within functions.
>
> Yeah, the problem seems underspecified.  How do you count statements
> added or removed by rewrite rules?  Statements executed to answer RI
> queries?  Do you count the statements issued by clients as part of the
> startup sequence?  The hypothetical "reset session" of a connection pool
> handler?  How do you count 2PC -- when they are executed, or when they
> are committed?  What happens to statements in transactions that are
> rolled back?  What happens to a statement that is executed partially
> because it failed partway (e.g. because of division by zero)?
>
>
> OTOH ISTM it would be easy to modify Postgres so as to count statements
> in the stat collector, by turning pgstat_report_activity into a routine
> that sent a count (presumably always 1) instead of the query string, and
> then just add the count to a counter on receiving.

Yeah, I doubt any other database gets mired neck-deep in exact details
of statment execution counts; a simple count of queries executed via a
client connection would be a great start.

I often run into situations where people are having a performance issue
because they're building web pages that make 50 queries to the database.
Being able to identify that and determine how many were selects vs. DML
would be useful.

Bonus points if there are seperate counters for statements from
functions.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On 5/23/06, Dan Gorman <dgorman@hi5.com> wrote:
> What I am looking for is that our DB is doing X selects a min.

If you're using 7.4, you can use log_duration to only log duration. It
won't log all the query text, only one short line per query. Then you
can use pgFouine to analyze this and having a graph such like that
http://pgfouine.projects.postgresql.org/reports/sample_hourly.html .
If you only log duration, you won't be able to separate
insert/delete/update from select though. So it can be interesting only
if they negligible.

Note that this is not possible in 8.x. You'll have to log the
statement to log the duration. I proposed a patch but it was refused
as it complexified the log configuration.

> Turning on logging isn't an option as it will create too much IO in
> our enviornment.

What we do here is logging on another machine via the network using
syslog. From our experience, it's not the fact to log that really
slows down the db but the generated I/O load. So if you do that, you
should be able to log the statements without slowing down your
database too much.

On our production databases, we keep the log running all the time and
we generate reports daily.

Regards,

--
Guillaume

On Tue, 2006-05-23 at 15:55 -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > OTOH ISTM it would be easy to modify Postgres so as to count statements
> > in the stat collector, by turning pgstat_report_activity into a routine
> > that sent a count (presumably always 1) instead of the query string, and
> > then just add the count to a counter on receiving.
>
> You wouldn't have to change the backends at all, just modify the
> collector to count the number of report_activity messages received.
> Might have to play some games with ignoring "<IDLE>" messages, but
> otherwise simple (and simplistic...)

The OP wanted statements/sec rather than just a total.

Having stats logged by time would be very useful, but I wouldn't limit
that just to numbers of statements in each time period.

stats_logging_interval = 60 by default, 0 to disable, range 5-3600

--
  Simon Riggs
  EnterpriseDB          http://www.enterprisedb.com

Alvaro Herrera wrote:
> Yeah, the problem seems underspecified.

So, Dan, the question is, what are you trying to measure?
This might be a statistic that management has always been given,
for Oracle, and you need to produce the "same" number for PostgreSQL.

If not, it's hard to figure out what a statement counter actually can measure,
to the extent that you can say, "If that number does THIS, I should do THAT."

--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.

On 5/23/06, Dan Gorman <dgorman@hi5.com> wrote:
>
> In any other DB (oracle, mysql) I know how many queries (selects) per second
> the database is executing. How do I get this
> number out of postgres?

Mysql does AFAIR only count the number of queries and then uses the
"seconds since startup" to estimate the number of queries per second.
If your server is hammered with queries 1 hour a day it's not giving
you a fair result.


--
        regards,
        Robin

On Wed, May 24, 2006 at 12:27:41PM +0200, Robin Ericsson wrote:
> On 5/23/06, Dan Gorman <dgorman@hi5.com> wrote:
> >
> >In any other DB (oracle, mysql) I know how many queries (selects) per
> >second
> >the database is executing. How do I get this
> >number out of postgres?
>
> Mysql does AFAIR only count the number of queries and then uses the
> "seconds since startup" to estimate the number of queries per second.
> If your server is hammered with queries 1 hour a day it's not giving
> you a fair result.

Somehow that doesn't surprise me...

In any case, if we at least provide a raw counter, it's not that hard to
turn that into selects per second over some period of time.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

I am not sure if this is what the original poster was refering to, but I
have used an application called mtop that shows how many queries per second
mysql is doing.

In my case this is helpfull because we have a number of machines running
postfix and each incoming mail generates about 7 queries. Queries are all
very simmilar to each other in that scenario.

Having access to that query/second stat allowed me to improve the
settings in MysQL. Ultimately once we migrated to a new server I could see
how moving to the new machine increased the speed at which we could accept
emails.

I am, little by little, getting PostgreSQL to be used, but for now the
postfix queries are tied to MySQL. By the time we hopefully do move to
PostgreSQL for the Postfix backend it will be very helpfull to have some
sort of way to measure queries/time period.

try pgtop. It is mytop clone for postgresql.

Regards,
alvis

Francisco Reyes wrote:
> I am not sure if this is what the original poster was refering to, but I
> have used an application called mtop that shows how many queries per
> second mysql is doing.
>
> In my case this is helpfull because we have a number of machines running
> postfix and each incoming mail generates about 7 queries. Queries are
> all very simmilar to each other in that scenario.
>
> Having access to that query/second stat allowed me to improve the
> settings in MysQL. Ultimately once we migrated to a new server I could
> see how moving to the new machine increased the speed at which we could
> accept emails.
>
> I am, little by little, getting PostgreSQL to be used, but for now the
> postfix queries are tied to MySQL. By the time we hopefully do move to
> PostgreSQL for the Postfix backend it will be very helpfull to have some
> sort of way to measure queries/time period.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings