Metricbeat postgresql connection leak under Windows

[adriansr]

For confirmed bugs, please report:

• Version: 6.6.2
• Operating System: Windows
• Discuss Forum URL: https://discuss.elastic.co/t/enabling-postgresql-module-in-metricbeat-causes-crash/173467
• Steps to Reproduce:

# Run under Windows
$ metricbeat modules enable postgresql
$ vim modules.d/postgresql.yml
 > set period to 3s

This causes an inordinate amount of open connections to PostgreSQL server, all of them in the ESTABLISHED state. Windows becomes unusable / SQL server crashes due to OOM.

Under macOS, the metricbeat process peaks around 1200 open sockets, most of them in the CLOSE_WAIT state. Under Windows, those are shown as ESTABLISHED and don't seem to clean up after Metricbeat is killed (Sysinternals tcpview becomes sluggish).

This bug may be related:
golang/go#6593

Tried SetMaxOpenConns / sharing a single DB object between filesets, but made no difference.

[leopucci]

@adriansr Let me play with this one :)

[leopucci]

@adriansr - Can you help me testing it? I am not building on windows yet. Will start installing the local build enviroment now!

[adriansr]

Thanks a lot @leopucci !

I will try your patch and report back. I guess you have to apply this patch to all the other metricsets, as sql.Open is called for each of them.

[leopucci]

@adriansr I have just tested on windows, did not solved the issue.
Connections kept growing. I need to debug libe by line and don't know yet how to do it.

There is a side solution. As this happens only on windows that does not have ssl connection by default, the user can specify on the host URI to disable the ssl like this: ( ?sslmode=disable )
hosts: ["postgres://localhost:5432?sslmode=disable"]

After that connections flows nicely without overflowing.

This seems to happen only when connection errors happens. Maybe not only on postgresql but all sql connections that throws an error inside sql, after successfully connecting to server.

[leopucci]

Debug shows that close is being called properly on defered return.
So seems to be a race condition on the libpq or libsql. Not in metricbeat code!
I have also tried to limit conections with db.SetMaxOpenConns(10) db.SetMaxIdleConns(5)
None of them worked.

[leopucci]

Opened an issue on the lib: lib/pq#840 @adriansr

To solve the client problem, tell him to add the ?sslmode=disable for now

hosts: ["postgres://localhost:5432?sslmode=disable"]

[adriansr]

Hi @leopucci

I have a patch to fix this issue. The problem is with creating too many sql.DB that don't get cleaned up.

[leopucci]

Cool! @adriansr Share the PR so I could understand more about beats =)