pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLog size in bytes of query result
5+ messages / 4 participants
[nested] [flat]
* Log size in bytes of query result
@ 2019-05-10 14:10 Franklin Haut <franklin.haut@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Franklin Haut @ 2019-05-10 14:10 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hello
How can we generate in the log of executed querys (directory pg_log) the
amount of bytes transferred between the server and the client of the result
of a query?
Example:
a) select now (); - few bytes transferred
b) select * from large_table; - 20,000,000 bytes transferred
I understand that this parameter can reduce the performance of the database
in general. I intend to use this information to measure the impact of each
query on the total volume of bytes transferred by the network interface by
IP address in a log analysis tool such as pgBadger
--
regards,
Franklin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Log size in bytes of query result
@ 2019-05-10 16:09 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Franklin Haut <franklin.haut@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2019-05-10 16:09 UTC (permalink / raw)
To: Franklin Haut <franklin.haut@gmail.com>; pgsql-performance@lists.postgresql.org
Franklin Haut wrote:
> How can we generate in the log of executed querys (directory pg_log)
> the amount of bytes transferred between the server and the client
> of the result of a query?
As far as I know, there is no parameter to do that.
You'd have to write an extension that hooks into PostgreSQL, but I
have no idea how hard that would be.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Log size in bytes of query result
@ 2019-05-10 16:42 Sergei Kornilov <sk@zsrv.org>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Sergei Kornilov @ 2019-05-10 16:42 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Franklin Haut <franklin.haut@gmail.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi
> extension that hooks into PostgreSQL
We have any hooks that can be used for such purposes?
Sometimes I think how to implement counters "bytes sent to client"/"bytes recv from client" in pg_stat_statements but did not found good place. Where we can accumulate such counters and how they can be accessible from extension? Extend DestReceiver or add counter directly in src/backend/libpq/pqcomm.c ?
PS: some returned with feedback old patch: https://www.postgresql.org/message-id/flat/CAHhq2wJXRqTMJXZwMAOdtQOkxSKxg_aMxxofhvCo%3DRGXvh0AUg%40m...
regards, Sergei
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Log size in bytes of query result
@ 2019-05-22 12:50 Franklin Haut <franklin.haut@gmail.com>
parent: Sergei Kornilov <sk@zsrv.org>
0 siblings, 1 reply; 5+ messages in thread
From: Franklin Haut @ 2019-05-22 12:50 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
@Sergei and @Laurenz Thank you for reply.
I think it is important to have other resource indicators consumed by the
query besides the execution time as the amount of Bytes sent / received by
each query, how many blocks / bytes were read / written from the cache or
had to be loaded from the disk or even CPU cycles .
With this information we can make more efficient adjustments to the queries
and improve the management of the resources, especially in virtualized
environments, where we often can not identify the real reason for poor
performance delivery, whether it is in the structure modeling or if there
is a dispute over resources by other virtual machines.
By my analysis, I see that the most efficient way to perform this control
would be in the existing medium in postgresql that is the log file (pg_log)
adding a few more variables for each query executed.
* Bytes Received (ethernet)
* Bytes Sent (ethernet)
* Bytes Written (disk)
* Bytes read only cache (cache hit)
* Bytes Read from disk (cache miss)
* CPU Time
Unfortunately I do not have the necessary knowledge to assist in the
implementation of these features and I want to leave it as a suggestion for
a new version.
regards,
Em sex, 10 de mai de 2019 às 13:42, Sergei Kornilov <sk@zsrv.org> escreveu:
> Hi
>
> > extension that hooks into PostgreSQL
>
> We have any hooks that can be used for such purposes?
> Sometimes I think how to implement counters "bytes sent to client"/"bytes
> recv from client" in pg_stat_statements but did not found good place. Where
> we can accumulate such counters and how they can be accessible from
> extension? Extend DestReceiver or add counter directly in
> src/backend/libpq/pqcomm.c ?
>
> PS: some returned with feedback old patch:
> https://www.postgresql.org/message-id/flat/CAHhq2wJXRqTMJXZwMAOdtQOkxSKxg_aMxxofhvCo%3DRGXvh0AUg%40m...
>
> regards, Sergei
>
--
Atenciosamente,
Franklin Haut
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Log size in bytes of query result
@ 2019-05-22 13:15 Dmitry Dolgov <9erthalion6@gmail.com>
parent: Franklin Haut <franklin.haut@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dmitry Dolgov @ 2019-05-22 13:15 UTC (permalink / raw)
To: Franklin Haut <franklin.haut@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>; Sergei Kornilov <sk@zsrv.org>; Laurenz Albe <laurenz.albe@cybertec.at>
> On Wed, May 22, 2019 at 2:51 PM Franklin Haut <franklin.haut@gmail.com> wrote:
>
> By my analysis, I see that the most efficient way to perform this control
> would be in the existing medium in postgresql that is the log file (pg_log)
> adding a few more variables for each query executed.
> On Fri, May 10, 2019 at 6:42 PM Sergei Kornilov <sk@zsrv.org> wrote:
>
> We have any hooks that can be used for such purposes? Sometimes I think how
> to implement counters "bytes sent to client"/"bytes recv from client" in
> pg_stat_statements but did not found good place. Where we can accumulate such
> counters and how they can be accessible from extension? Extend DestReceiver
> or add counter directly in src/backend/libpq/pqcomm.c ?
For the records, I guess on Linux you can gather such kind of information via
ebpf, even without hooks in Postgres (if you avoid too frequent context
switches between kernel/user space via e.g. relying on send/recv, it should be
also efficient). I have a POC in my postgres-bcc repo, it looks like this:
$ net_per_query.py bin/postgres -c $container_id
attaching...
listening...
detaching...
sent
[16397:4026532567] copy pgbench_accounts from stdin: 16b
[16397:4026532567] alter table pgbench_accounts add primary key (aid): 96b
[16428:4026532567] postgres: backend 16428: 2k
received
[16397:4026532567] copy pgbench_accounts from stdin: 16m
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-05-22 13:15 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-05-10 14:10 Log size in bytes of query result Franklin Haut <franklin.haut@gmail.com>
2019-05-10 16:09 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-05-10 16:42 ` Sergei Kornilov <sk@zsrv.org>
2019-05-22 12:50 ` Franklin Haut <franklin.haut@gmail.com>
2019-05-22 13:15 ` Dmitry Dolgov <9erthalion6@gmail.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