What is the fate of closed database connections?
137 views
Skip to first unread message
Michael Grundler
Jan 17, 2017, 2:26:37 PM1/17/17
to Django users
When Django closes a database connection at the end of a request/response cycle does that connection still maintain its hold on the database?
I ask because I am running an app on a spatialite (v4.3.0a) database, which, due to a limitation in the GEOS library (v3.6.1-CAPI-1.10.1 r0), can only accept a maximum of 64 connections. When I configure Django so that CONN_MAX_AGE = 0, the server log will eventually begin filling up with messages that read "ERROR: Too many connections: max 64". If I enable persistent connections by setting CONN_MAX_AGE = None, these messages disappear. To me this suggests that when Django closes a connection at the end of a request/response cycle that connection persists from spatialite's perspective.
Is this expected behavior?
The error message is easily reproduced by starting a project configured to use spatialite, logging into the admin page, and then refreshing your browser repeatedly until 64 requests have been made.
Thanks for any insights,
Mike
Fred Stluka
Jan 17, 2017, 2:59:21 PM1/17/17
to django...@googlegroups.com
Mike,
As of version 1.6, Django supports persistent DB connections
(connection pooling). CONN_MAX_AGE specifies the number of
seconds that a connection will remain open and be reused.
You can set it to 0 to cause each DB interaction to open, use,
and close its own connection. You can set it to None to allow
an unlimited number of connections. See:
- https://docs.djangoproject.com/en/dev/ref/databases/
However, the behavior you're seeing doesn't seem to match this
documented behavior. I'd have expected CONN_MAX_AGE = 0
to prevent connections from being reused, so they should be
closed at the end of the request that used them. So, unless you
have 65 or more concurrent requests, you should not be hitting
your limit.
I'm not surprised that CONN_MAX_AGE = None has an effect, but
I wouldn't have been able to predict whether it makes things
better or worse. It allows Django to reuse connections so fewer
of them should be created, but it also allows Django to pool them
for as long as it likes, so a large number of them may accumulate.
Have you tried any other values for CONN_MAX_AGE, like 1 or 2?
Also, are you using the Django development server? The docs
linked above say:
"The development server creates a new thread for each request
it handles, negating the effect of persistent connections.
Don’t enable them during development."
As of version 1.6, Django supports persistent DB connections
(connection pooling). CONN_MAX_AGE specifies the number of
seconds that a connection will remain open and be reused.
You can set it to 0 to cause each DB interaction to open, use,
and close its own connection. You can set it to None to allow
an unlimited number of connections. See:
- https://docs.djangoproject.com/en/dev/ref/databases/
However, the behavior you're seeing doesn't seem to match this
documented behavior. I'd have expected CONN_MAX_AGE = 0
to prevent connections from being reused, so they should be
closed at the end of the request that used them. So, unless you
have 65 or more concurrent requests, you should not be hitting
your limit.
I'm not surprised that CONN_MAX_AGE = None has an effect, but
I wouldn't have been able to predict whether it makes things
better or worse. It allows Django to reuse connections so fewer
of them should be created, but it also allows Django to pool them
for as long as it likes, so a large number of them may accumulate.
Have you tried any other values for CONN_MAX_AGE, like 1 or 2?
Also, are you using the Django development server? The docs
linked above say:
"The development server creates a new thread for each request
it handles, negating the effect of persistent connections.
Don’t enable them during development."
--Fred
Fred Stluka -- mailto:fr...@bristle.com -- http://bristle.com/~fred/
Bristle Software, Inc -- http://bristle.com -- Glad to be of service!
Open Source: Without walls and fences, we need no Windows or Gates.
Fred Stluka -- mailto:fr...@bristle.com -- http://bristle.com/~fred/
Bristle Software, Inc -- http://bristle.com -- Glad to be of service!
Open Source: Without walls and fences, we need no Windows or Gates.
--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/8b2d88c4-056c-4eeb-9dc9-7295f7ff0961%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Michael Grundler
Jan 17, 2017, 3:59:48 PM1/17/17
to Django users
Fred,
I don't understand why a CONN_MAX_AGE = 0 would yield qualitatively different behavior than CONN_MAX_AGE = 2.
Many thanks,
Mike
Bristle Software, Inc -- http://bristle.com -- Glad to be of service!
Open Source: Without walls and fences, we need no Windows or Gates.
Fred Stluka
Jan 17, 2017, 4:18:01 PM1/17/17
to django...@googlegroups.com
Mike,
I agree. It seems that 0 and 2 should have similar effects in your
case. Have you tried 1?
So, 2 behaves like None, eh? Perhaps that's because they are both
allowing connections to be reused. 0 explicitly prevents connection
pooling, so that each request opens/uses/closes its own connection.
Maybe there's a bug related to closing connections as you originally
suggested?
Have you tried a different driver for the spatialite DB? Also, can you
experiment with a different DB to shed some light in the problem?
Personally, I've found it VERY easy to flip between MySQL and SQLite.
On my project, we use MySQL in PROD, but we run all of our
regression tests against SQLite. To make this happen, all I had to
do was add this to our settings file:
RUNNING_UNIT_TESTS = 'test' in sys.argv
if RUNNING_UNIT_TESTS:
DATABASES['default'] = {
'ENGINE': 'django.db.backends.sqlite3',
}
Any other ideas, anyone?
--Fred
I agree. It seems that 0 and 2 should have similar effects in your
case. Have you tried 1?
So, 2 behaves like None, eh? Perhaps that's because they are both
allowing connections to be reused. 0 explicitly prevents connection
pooling, so that each request opens/uses/closes its own connection.
Maybe there's a bug related to closing connections as you originally
suggested?
Have you tried a different driver for the spatialite DB? Also, can you
experiment with a different DB to shed some light in the problem?
Personally, I've found it VERY easy to flip between MySQL and SQLite.
On my project, we use MySQL in PROD, but we run all of our
regression tests against SQLite. To make this happen, all I had to
do was add this to our settings file:
RUNNING_UNIT_TESTS = 'test' in sys.argv
if RUNNING_UNIT_TESTS:
DATABASES['default'] = {
'ENGINE': 'django.db.backends.sqlite3',
}
Any other ideas, anyone?
--Fred
Bristle Software, Inc -- http://bristle.com -- Glad to be of
service!
Open Source: Without walls and fences, we need no Windows or Gates.
Open Source: Without walls and fences, we need no Windows or Gates.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/5e49730b-8e58-4d26-aedc-0228717eefa6%40googlegroups.com.
Michael Grundler
Jan 17, 2017, 4:52:13 PM1/17/17
to Django users
CONN_MAX_AGE = 1 behaves similarly to 2 and None. I will try and do some testing with additional spatialite DB drivers. Perhaps its worth noting that I have not seen any of these problems when using postgresql as the DB backend.
Many thanks,
Mike
On Tuesday, January 17, 2017 at 4:18:01 PM UTC-5, Fred Stluka wrote:
Mike,
I agree. It seems that 0 and 2 should have similar effects in your
case. Have you tried 1?
So, 2 behaves like None, eh? Perhaps that's because they are both
allowing connections to be reused. 0 explicitly prevents connection
pooling, so that each request opens/uses/closes its own connection.
Maybe there's a bug related to closing connections as you originally
suggested?
Have you tried a different driver for the spatialite DB? Also, can you
experiment with a different DB to shed some light in the problem?
Personally, I've found it VERY easy to flip between MySQL and SQLite.
On my project, we use MySQL in PROD, but we run all of our
regression tests against SQLite. To make this happen, all I had to
do was add this to our settings file:
RUNNING_UNIT_TESTS = 'test' in sys.argv
if RUNNING_UNIT_TESTS:
DATABASES['default'] = {
'ENGINE': 'django.db.backends.sqlite3',
}
Any other ideas, anyone?
--Fred
Fred Stluka
Jan 17, 2017, 5:16:28 PM1/17/17
to django...@googlegroups.com
Mike,
Good info. Yeah, keep experimenting and report what you find
here. Hopefully someone will jump in with a definitive answer
for you.
Good info. Yeah, keep experimenting and report what you find
here. Hopefully someone will jump in with a definitive answer
for you.
Bristle Software, Inc -- http://bristle.com -- Glad to be of
service!
Open Source: Without walls and fences, we need no Windows or Gates.
Open Source: Without walls and fences, we need no Windows or Gates.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/8110b66a-00db-477c-9591-8b79db398a75%40googlegroups.com.
Tom Evans
Jan 18, 2017, 9:39:53 AM1/18/17
to django...@googlegroups.com
On Tue, Jan 17, 2017 at 7:58 PM, Fred Stluka <fr...@bristle.com> wrote:
> Mike,
>
> As of version 1.6, Django supports persistent DB connections
> (connection pooling).
Pedantry: Persistent connections are not the same as connection
> Mike,
>
> As of version 1.6, Django supports persistent DB connections
> (connection pooling).
pooling. With persistent connections, if a worker does not have a
currently open connection to the database, it will open a new one.
With a connection pool, it will retrieve an unused open connection
from the pool, which is shared amongst many processes.
Connection pools are not built in to django, but you can use external
software like pgbouncer to provide them.
Cheers
Tom
Michael Grundler
Jan 19, 2017, 7:37:41 PM1/19/17
to Django users
It looks like the source of this error is related to a spatialite internal connection leak (discussed in this post: https://groups.google.com/forum/#!topic/spatialite-users/xrV7CA_GlwM)
Each time Django requests a connection to the database sqlite loads the spatialite extension module, which creates an internal database connection that persists after Django closes its connection. This seems to explain why enabling persistent connections fixes the issue on my local machine.
Apart from this workaround I don't know if there is another solution. The post linked above discusses one strategy but I'm not sure it is possible with python's sqlite bindings.
Reply all
Reply to author
Forward
0 new messages