pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQueries Per Second (QPS)
5+ messages / 4 participants
[nested] [flat]
* Queries Per Second (QPS)
@ 2015-09-26 16:24 Adam Scott <adam.c.scott@gmail.com>
2015-09-27 06:02 ` Re: Queries Per Second (QPS) Guillaume Lelarge <guillaume@lelarge.info>
2015-09-30 17:06 ` Re: Queries Per Second (QPS) Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 5+ messages in thread
From: Adam Scott @ 2015-09-26 16:24 UTC (permalink / raw)
To: pgsql-performance
How do we measure queries per second (QPS), not transactions per second, in
PostgreSQL without turning on full logging which has a performance penalty
and can soak up lots of disk space?
We are using 8.4, but I'm interested in any version as well.
Thank you,
Adam C. Scott
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Queries Per Second (QPS)
2015-09-26 16:24 Queries Per Second (QPS) Adam Scott <adam.c.scott@gmail.com>
@ 2015-09-27 06:02 ` Guillaume Lelarge <guillaume@lelarge.info>
2015-09-27 06:06 ` Re: Queries Per Second (QPS) Guillaume Lelarge <guillaume@lelarge.info>
1 sibling, 1 reply; 5+ messages in thread
From: Guillaume Lelarge @ 2015-09-27 06:02 UTC (permalink / raw)
To: Adam Scott <adam.c.scott@gmail.com>; +Cc: pgsql-performance
Le 26 sept. 2015 6:26 PM, "Adam Scott" <adam.c.scott@gmail.com> a écrit :
>
> How do we measure queries per second (QPS), not transactions per second,
in PostgreSQL without turning on full logging which has a performance
penalty and can soak up lots of disk space?
>
The only way I can think of is to write an extension that will execute some
code at the end of the execution of a query.
Note that this might get tricky. Do you want to count any query? Such as
those in explicit transactions and those in plpgsql functions? People might
not see this your way, which may explain why I don't know of any such
extension.
> We are using 8.4, but I'm interested in any version as well.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Queries Per Second (QPS)
2015-09-26 16:24 Queries Per Second (QPS) Adam Scott <adam.c.scott@gmail.com>
2015-09-27 06:02 ` Re: Queries Per Second (QPS) Guillaume Lelarge <guillaume@lelarge.info>
@ 2015-09-27 06:06 ` Guillaume Lelarge <guillaume@lelarge.info>
2015-09-27 17:34 ` Re: Queries Per Second (QPS) Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Guillaume Lelarge @ 2015-09-27 06:06 UTC (permalink / raw)
To: Adam Scott <adam.c.scott@gmail.com>; +Cc: pgsql-performance
Le 27 sept. 2015 8:02 AM, "Guillaume Lelarge" <guillaume@lelarge.info> a
écrit :
>
> Le 26 sept. 2015 6:26 PM, "Adam Scott" <adam.c.scott@gmail.com> a écrit :
> >
> > How do we measure queries per second (QPS), not transactions per
second, in PostgreSQL without turning on full logging which has a
performance penalty and can soak up lots of disk space?
> >
>
> The only way I can think of is to write an extension that will execute
some code at the end of the execution of a query.
>
> Note that this might get tricky. Do you want to count any query? Such as
those in explicit transactions and those in plpgsql functions? People might
not see this your way, which may explain why I don't know of any such
extension.
>
Thinking about this, such an extension already exists. It's
pg_stat_statements. You need to sum the count column of the
pg_stat_statements from time to time. The difference between two sums will
be your number of queries.
> > We are using 8.4, but I'm interested in any version as well.
> >
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Queries Per Second (QPS)
2015-09-26 16:24 Queries Per Second (QPS) Adam Scott <adam.c.scott@gmail.com>
2015-09-27 06:02 ` Re: Queries Per Second (QPS) Guillaume Lelarge <guillaume@lelarge.info>
2015-09-27 06:06 ` Re: Queries Per Second (QPS) Guillaume Lelarge <guillaume@lelarge.info>
@ 2015-09-27 17:34 ` Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jeff Janes @ 2015-09-27 17:34 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Adam Scott <adam.c.scott@gmail.com>; pgsql-performance
On Sat, Sep 26, 2015 at 11:06 PM, Guillaume Lelarge <guillaume@lelarge.info>
wrote:
> Le 27 sept. 2015 8:02 AM, "Guillaume Lelarge" <guillaume@lelarge.info> a
> écrit :
> >
> > Le 26 sept. 2015 6:26 PM, "Adam Scott" <adam.c.scott@gmail.com> a
> écrit :
> > >
> > > How do we measure queries per second (QPS), not transactions per
> second, in PostgreSQL without turning on full logging which has a
> performance penalty and can soak up lots of disk space?
> > >
> >
> > The only way I can think of is to write an extension that will execute
> some code at the end of the execution of a query.
> >
> > Note that this might get tricky. Do you want to count any query? Such as
> those in explicit transactions and those in plpgsql functions? People might
> not see this your way, which may explain why I don't know of any such
> extension.
> >
>
> Thinking about this, such an extension already exists. It's
> pg_stat_statements. You need to sum the count column of the
> pg_stat_statements from time to time. The difference between two sums will
> be your number of queries.
>
That is what I was thinking, but the pg_stat_statement does discard
statements sometimes, discarding the counts with them. You would have set
pg_stat_statements.max to a higher value than you ever expect to get
reached.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Queries Per Second (QPS)
2015-09-26 16:24 Queries Per Second (QPS) Adam Scott <adam.c.scott@gmail.com>
@ 2015-09-30 17:06 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2015-09-30 17:06 UTC (permalink / raw)
To: Adam Scott <adam.c.scott@gmail.com>; pgsql-performance
On 09/26/2015 09:24 AM, Adam Scott wrote:
> How do we measure queries per second (QPS), not transactions per second,
> in PostgreSQL without turning on full logging which has a performance
> penalty and can soak up lots of disk space?
Measure it from the client side. pgBench does this.
If you mean on your production workload, then I recommend using a
connection proxy which counts statements. A few exist for Postgres, for
example:
VividCortex:
https://www.vividcortex.com/blog/2015/05/13/announcing-vividcortex-network-analyzer-mysql-postgresql...
WireShark: https://github.com/dalibo/pgshark
You'd need to measure how much one of these tools affects your QPS, of
course, but that should be easily measurable on a test system.
Also, if the PostgresQL activity log is moved to a seperate SSD from the
database storage, I've found overhead in writing to it to be less than
3% ... depending on the nature of your query traffic. Pathological
situations are mainly databases which have a high volume of very long
queries or failed connection attempts.
>
> We are using 8.4, but I'm interested in any version as well.
You are aware that 8.4 is EOL, yes? Not to mention missing 5 years of
performance improvements ...
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2015-09-30 17:06 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-09-26 16:24 Queries Per Second (QPS) Adam Scott <adam.c.scott@gmail.com>
2015-09-27 06:02 ` Guillaume Lelarge <guillaume@lelarge.info>
2015-09-27 06:06 ` Guillaume Lelarge <guillaume@lelarge.info>
2015-09-27 17:34 ` Jeff Janes <jeff.janes@gmail.com>
2015-09-30 17:06 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox