Re: Get the statistics based on the application name and IP address

On Mon, Aug 8, 2022 at 10:11 PM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:

> Hi,
>
> On Mon, Aug 08, 2022 at 08:21:06PM +0500, Ibrar Ahmed wrote:
> > While working on pg_stat_stements, I got some questions from customers to
> > have statistics by application and IP address.
> > [...]
> > name. I did some POC and had a patch. But before sharing the patch.
> >
> > I need to know if there has been any previous discussion about this
> topic;
> > by the way,
>
> Thanks for the input.

> I don't think there was any discussion on this exactly, but there have been
> some related discussions.
>
> This would likely bring 2 problems.

> First, for now each entry contains its own
> query text in the query file. There can already be some duplication, which
> isn't great, but adding the application_name and/or IP address will make
> things
> way worse, so you would probably need to fix that first.

I doubt that makes it worst because these (IP and Application) will be part
of
the key, not the query text. But yes, I agree that it will increase the
footprint of rows,
excluding query text.

I am not 100% sure about the query text duplication but will look at that
in detail,
if you have more insight, then it will help to solve that.

> There has been some
> discussion about it recently (1) but more work and benchmarking are needed.
>
> The other problem is the multiplication of entries. It's a well known
> limitation that pg_stat_statements eviction are so costly that it makes it
> unusable. The last numbers I saw about it was ~55% overhead (2). Adding
> application_name or ip address to the key would probably make
> pg_stat_statements unusable for anyone who would actually need those
> metrics.
>

I am sure adding a new item in the key does not affect the performance of
evictions of the row,
as it will not be part of that area. I am doing some benchmarking and
hacking to reduce that and will
send results with the patch.

> [1]:
> https://www.postgresql.org/message-id/flat/604E3199-2DD2-47DD-AC47-774A6F97DCA9%40amazon.com
> [2]: https://twitter.com/AndresFreundTec/status/1105585237772263424
>

--

Ibrar Ahmed.
Senior Software Engineer, PostgreSQL Consultant.