Re: stats for network traffic WIP

On Sat, Dec 7, 2013 at 1:17 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>
> Could you share the performance numbers? I'm really concerned about
> the performance overhead caused by this patch.
>

I've tried pgbench in select mode with small data sets to avoid disk
io and didn't see any difference. That was on my old core2duo laptop
though .. I'll have to retry it on some server class multi core
hardware.

I could create a new GUC to turn on/off this feature. Currently, it
uses "track_counts".

> Here are the comments from me:
>
> All the restrictions of this feature should be documented. For example,
> this feature doesn't track the bytes of the data transferred by FDW.
> It's worth documenting that kind of information.
>

OK. It also doesn't account for DNS resolution, Bonjour traffic and
any traffic generated from PL functions that create their own sockets.

> ISTM that this feature doesn't support SSL case. Why not?

It does support SSL, see my_sock_read() and my_sock_write() in
backend/libpq/be-secure.c

> The amount of data transferred by walreceiver also should be tracked,
> I think.

I'll have to take another look at it. I might be able to create SSL
BIO functions in libpqwalreceiver.c and change some other functions
(eg. libpqrcv_send) to return byte counts instead of void to get it
working.

> I just wonder how conn_received, conn_backend and conn_walsender
> are useful.

I thought of it mostly for monitoring software usage (eg. cacti,
nagios) to track connections/sec which might be used for capacity
planning, confirm connection pooler settings, monitoring abuse, etc.
Eg. If your conn_walsender is increasing and you have a fixed set of
slaves it could show a network issue.
The information is available in the logs if "log_connections" GUC is
on but it requires parsing and access to log files to extract. With
the increasing popularity of hosted postgres services without OS or
log access, I think more metrics should be available through system
views.

-nigel.