MySQL backend generates unnecessary commands

[Luke Sneeringer]

When looking at our MySQL (InnoDB) instance that powers our Django application, our DBA reports that the com_admin_commands variable is extremely high: MySQL is running an administrative command (such as a ping) roughly three times for every query that goes off.

There's a roughly 2.5 year old discussion about this on django-users where the behavior is explained: ​http://www.mail-archive.com/django-users@googlegroups.com/msg55094.html
This is probably a pretty small amount of overhead, but it definitely seems like a waste. It seems like Django should not pummel the system with a ton of extraneous queries, even if they're extremely small.

[Ramiro Morales]

IMHO, if it is in Django hands to optimize this (i.e. there is some MySQLdb knob or DP-API complaint change of workflow in query generation that would help in reducing that com_admin_command counter) then it would be worth considering. But to even start thinking about this we need more concrete information, real numbers about the overhead introduced compared to real data in-band traffic. Probably a pretty small amount of overhead, but it definitely seems like a waste is going to get the same traction as the thread you pointed got. Karen asked for this same information to the OP there and there was no answer.

Fortunately, it seem you have a dedicated MySQL DBA fellow that can give you and us that information. Can you coordinate collecting some valuable data?

[anonymous]

Please look at Baron's article here: ​http://www.mysqlperformanceblog.com/2010/05/05/checking-for-a-live-database-connection-considered-harmful/

I am not sure that is what you need to completely convince you, but I hope it is a good start.

[Aymeric Augustin]

Django implements the pattern described in the blog post linked above in DatabaseWrapper._valid_connection().

This check is performed every time Django creates a cursor.

We could optimize this at some point, but it's a pretty low priority item, given the lack of data demonstrating an actual performance hit.

[Ramiro Morales]

I've tested the attached patch by running the Django test suite with a two-system setup, The MySQL server (5.0.32) running in a separate system from the one running the tests (with Python 2.5). Both of them very old PC workstations (a Pentium III 500MHz and an AMD Sempron 1.4GHz respectively) with IDE disks. Network is 100Mbit

Without the patch:

  Ran 4592 tests in 11766.392s

  FAILED (failures=2, errors=1, skipped=88, expected failures=2)

  real    201m49.926s
  user    28m50.192s
  sys     1m40.106s

With the patch:

  Ran 4593 tests in 11670.732s

  FAILED (failures=2, skipped=88, expected failures=2)

  real    199m50.378s
  user    29m14.410s
  sys     1m41.838s

[Aymeric Augustin]

Also discussed here: ​https://groups.google.com/d/msg/django-developers/oIkjJbdWVEo/xOiZF3gP0t4J

[Aymeric Augustin]

Currently the behavior for MySQL is inconsistent with all the other database backends. After 29628e0b6e5b1c6324e0c06cc56a49a5aa0747e0, fixing this ticket is as simple as removing the definition of _valid_connection in the MySQL database backend.

Then, if we want to introduce resilience to database connection failures, we should do it consistently on all backends.

Hardcoding retries doesn't sound like a good idea. For instance, if the connection breaks between two queries in a transaction, the database will roll back the first query; at this point, if Django automatically reopens a connection, retries the second query and commits the transaction, only the changes from the second query will be saved!

[Aymeric Augustin <aymeric.augustin@…>]

In 282b2f40cd0aa09815001e178f60c9e71667d847:

Fixed #15119 -- Stopped pinging the MySQL server.