Обсуждение: Log size in bytes of query result
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
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
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 didnot found good place. Where we can accumulate such counters and how they can be accessible from extension? Extend DestReceiveror 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%40mail.gmail.com regards, Sergei
@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%40mail.gmail.com
regards, Sergei
Atenciosamente,
Franklin Haut
> 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