0

On a production PostgreSQL server I am seeing intermittent enormous spikes in data traffic. Up to 400Mbit/s which is causing NETWORK IO waits which in turn cause query timeouts. How can I find out what queries are returning big result sets?

1
  • You could try to identify slow statements using the auto_explain module, assuming those that have a high network traffic are also slow Nov 24 at 12:46
1

Postgresql lacks metrics for network traffic usage.

There was some patches for such metrics, but nothing related has been merged at the moment.


pg_stat_statements extension could track total number of rows retrieved or affected by the statement (rows metric). Doesn't help if you are sending large text/json/bytea/etc in one row. But it will tell you the places in which many rows are transmitted.

PS: are you sure the bottleneck is the network IO on postgresql side? 400 Mbps is not too much, I have several gigabytes of upstream traffic on some of the database servers.

1
  • Agreed 400 Mbps seems low. I believe most modern networks have at least 2.5x (1 Gbps) bandwidths as a minimum.
    – J.D.
    Nov 24 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.