Mysql Ping and performance

[bo]

Not sure if this is a Django issue or the supporting Mysqldb (1.2.2)
python package .. (i'll stop this here if its not, but it seems worthy
of at least letting other know this)

however while profiling a page i came across this seemingly
performance hole.

--------
Ordered by: internal time

ncalls tottime percall cumtime percall
filename:lineno(function)
230 0.343 0.001 0.343 0.001 {method 'query' of
'_mysql.connection' objects}
228 0.116 0.001 0.116 0.001 {method 'ping' of
'_mysql.connection' objects}
234 0.029 0.000 0.047 0.000 query.py:
473(get_default_columns)
972 0.021 0.000 0.048 0.000 __init__.py:
487(__init__)
1303 0.019 0.000 0.022 0.000 __init__.py:
633(__init__)
2068 0.017 0.000 0.216 0.000 __init__.py:
690(_resolve_lookup)
-------

#1 time sink is the queries themselves (i figured that would be the
case) .. but #2 is "ping" and it seems to ping on every query. This
issue is probably not so bad on Localhost or Socket based connections,
but on remote Mysql server, as you can see, it is not so good.

again not sure if django can solve (or even wants to solve this) ..

bo

[Jesse Young]

Yeah, we found the ping() during performance profiling too. It's a
significant performance hit -- it basically doubles the number of
requests to the DB server. It seems that the reason is to support use
cases where the DB connection is long-lived, since sometimes DB
connections die if you leave them open for a really long time. In the
normal Django HTTP request use case this never happens because Django
closes the DB connection at the end of each request. But it could
potentially be useful for people who use django.db in some other
custom process.

We ended up commenting out the ping() stuff so that any opened
connection was always treated as valid.

It seems to me that the performance overhead in the 99% use case would
suggest that it would be beneficial for Django to let users configure
whether or not their DB connection constantly pings or not. Or maybe
just keep track of the time and don't ping unless some minimum time
has elapsed since the last one. Or do what we did, and just don't ping
at all and let the app deal with the case where the DB drops the
connection.

-Jesse

[Jesse Young]

Oh, I forgot to mention that this is certainly a Django issue and not
a mysqldb issue. The relevant code is in django/db/backends/mysql/
base.py:232:

def _valid_connection(self):
if self.connection is not None:
try:
self.connection.ping()
return True
except DatabaseError:
self.connection.close()
self.connection = None
return False

I replaced that in my local version of Django with :

def _valid_connection(self):
return self.connection is not None

-Jesse

[bo]

Would it be wise to suggest that it be removed from the django core
given the connection drops every request? It's not done for Postgres
at all and the Oracle base.py is doing what you suggest.

I guess this question would be posed to whomever controls that slice
of the django pie.

bo