Обсуждение: Queries Per Second (QPS)
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.
>
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.
> >
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.
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