Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Postgres session database leaking connections #9827

Closed
JorritSalverda opened this Issue Nov 8, 2017 · 17 comments

Comments

Projects
None yet
6 participants
@JorritSalverda
Copy link

commented Nov 8, 2017

The postgres session database connection seems to have problems to keep the number of connections to the database under control.

  • What Grafana version are you using?
    4.6.1

  • What datasource are you using?
    Postgres

  • What OS are you running grafana on?
    docker container grafana/grafana

  • What did you do?

My dashboards kept showing errors in the graphs. Looking at the error logs it turned out the Google Cloud SQL postgres ran out of connections. I then set up a cockroachdb cluster for the sessions database.

  • What was the expected result?

The number of database connections to stay at a reasonable level. Connection pooling should probably keep it at 10 connections per grafana instance?

  • What happened instead?

After running this configuration for half an hour the connection count has gone up to 800 connections, which seems a bit overzealous for a 3 pod grafana setup.

grafana-sessions-db-connections

@JorritSalverda

This comment has been minimized.

Copy link
Author

commented Nov 8, 2017

It only gets worse, grafana is now failing to render any graphs. The connection count has surpassed 1500! The page itself can be refreshed, so it seems the database connection for the regular database works fine (I still point that one to the Cloud SQL postgres instance).

1500plus

@JorritSalverda

This comment has been minimized.

Copy link
Author

commented Nov 9, 2017

Could it be that the init code for sessions using postgres at https://github.com/grafana/grafana/blob/v4.6.1/vendor/github.com/go-macaron/session/postgres/postgres.go#L108-L116 is getting called for each macaron session?

@daniellee

This comment has been minimized.

Copy link
Member

commented Nov 10, 2017

Wonder if this is related to: #9784

We are all out of office this week (at a conference). Will investigate on Monday.

@JorritSalverda

This comment has been minimized.

Copy link
Author

commented Dec 14, 2017

Did you find anything regarding a possible connection leak? This is still an issue every now and then. We currently use Google Cloud SQL postgres, which has a 100 connection limit which it frequently runs out of.

@remeika

This comment has been minimized.

Copy link

commented Dec 19, 2017

Have you set [database] max_idle_conn / [database] max_open_conn? If so, what are the values?

@JorritSalverda

This comment has been minimized.

Copy link
Author

commented Dec 19, 2017

Ah, I didn't test those. Setting them to max_idle_conn = 2 and max_open_conn = 10 to verify whether that solves the issue. If this resolves the issue perhaps the default value for max_open_conn is better set to something else than unlimited :)

@JorritSalverda

This comment has been minimized.

Copy link
Author

commented Dec 19, 2017

Do these settings actually apply to the session database connection?

@JorritSalverda

This comment has been minimized.

Copy link
Author

commented Dec 19, 2017

Unfortunately the settings don't seem to apply to the session database connection created by macaron. I still get these kind of errors:

t=2017-12-19T08:41:59+0000 
lvl=eror 
msg="Request error" 
error="session/postgres: error checking existence: pq: remaining connection slots are reserved for non-replication superuser connections"
stack="
/usr/local/go/src/runtime/panic.go:491 (0x431e62)\n
/go/src/github.com/grafana/grafana/vendor/github.com/go-macaron/session/postgres/postgres.go:148 (0xb495c4)\n
/go/src/github.com/grafana/grafana/vendor/github.com/go-macaron/session/session.go:257 (0x832d21)\n
/go/src/github.com/grafana/grafana/pkg/middleware/session.go:117 (0xb9490f)\n
/go/src/github.com/grafana/grafana/pkg/middleware/middleware.go:99 (0xb8fd6d)\n
/go/src/github.com/grafana/grafana/pkg/middleware/middleware.go:57 (0xb969d6)\n
/go/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:79 (0x7f19c0)\n
/go/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:157 (0x7d4863)\n
/go/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:135 (0x7d459a)\n
/go/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:121 (0x7f1bdd)\n
/go/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:112 (0x7f1b15)\n
/go/src/github.com/grafana/grafana/pkg/middleware/recovery.go:146 (0xb979e0)\n
/go/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:79 (0x7f19c0)\n
/go/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:157 (0x7d4863)\n
/go/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:135 (0x7d459a)\n
/go/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:121 (0x7f1bdd)\n
/go/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:112 (0x7f1b15)\n
/go/src/github.com/grafana/grafana/pkg/middleware/logger.go:33 (0xb95571)\n
/usr/local/go/src/runtime/asm_amd64.s:509 (0x46107a)\n
/usr/local/go/src/reflect/value.go:434 (0x4bf934)\n
/usr/local/go/src/reflect/value.go:302 (0x4bef13)\n
/go/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:177 (0x7d4ba8)\n
/go/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:137 (0x7d44a9)\n
/go/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:121 (0x7f1bdd)\n
/go/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/router.go:187 (0x803412)\n
/go/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/router.go:296 (0x7fd98b)\n
/go/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/macaron.go:220 (0x7f6387)\n
/usr/local/go/src/net/http/server.go:2619 (0x692de3)\n
/usr/local/go/src/net/http/server.go:1801 (0x68f09c)\n
/usr/local/go/src/runtime/asm_amd64.s:2337 (0x4638c0)\n
"

Like the lvl by the way ;)

@daniellee

This comment has been minimized.

Copy link
Member

commented Dec 19, 2017

@JorritSalverda Maybe you could do a temporay fix and try set it via the connection string:

http://www.npgsql.org/doc/connection-string-parameters.html

or changing the max_connections setting in the postgres config file might be another option.

To properly solve this, it looks like we need to create a PR for Macaron so that we can send in the max open and max idle connections as session options.

@remeika

This comment has been minimized.

Copy link

commented Feb 12, 2018

max_connections attempts to change the value on your postgres server, not in the Macaron client. Adding it to the session connection string causes grafana to panic:

panic: pq: parameter "max_connections" cannot be changed without restarting the server

@lastblindpilot

This comment has been minimized.

Copy link

commented Jul 4, 2018

Is this still an open issue? We have Grafana v5.1.3 and a lot of panels with postgres datasource.
grafana_postgres_issue

@torkelo

This comment has been minimized.

Copy link
Member

commented Jul 4, 2018

Have you set [database] max_idle_conn / [database] max_open_conn , what to?

@lastblindpilot

This comment has been minimized.

Copy link

commented Jul 5, 2018

Seems it solved the problem. I've set the max_open_conn to 300 and max_idle_conn to 2. Thnks!

@lastblindpilot

This comment has been minimized.

Copy link

commented Jul 18, 2018

The issue reappeared.
max_idle_conn = 2, max_open_conn = 300, Grafana v5.1.3, Postgres max_connections = 1000
image

@torkelo

This comment has been minimized.

Copy link
Member

commented Jul 18, 2018

@lastblindpilot are you using the postgres data source or just as Grafana main db?

@lastblindpilot

This comment has been minimized.

Copy link

commented Jul 18, 2018

@torkelo, postgres as data source

@torkelo

This comment has been minimized.

Copy link
Member

commented Jul 18, 2018

dupe of (and fixed today) #12636

@torkelo torkelo closed this Jul 18, 2018

@torkelo torkelo added this to the 5.2.2 milestone Jul 18, 2018

marefr added a commit that referenced this issue Jul 24, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.