Opened 5 years ago

Closed 3 years ago

#15119 closed Cleanup/optimization (fixed)

MySQL backend generates unnecessary commands

Reported by: lukesneeringer Owned by: aaugustin
Component: Database layer (models, ORM) Version: 1.2
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


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:
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.

Attachments (2)

15119-1.diff (8.7 KB) - added by ramiro 4 years ago.
15119-2.diff (8.5 KB) - added by ramiro 4 years ago.

Download all attachments as: .zip

Change History (12)

comment:1 Changed 5 years ago by ramiro

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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?

comment:2 Changed 5 years ago by ramiro

  • Component changed from Uncategorized to Database layer (models, ORM)
  • Triage Stage changed from Unreviewed to Design decision needed

comment:3 Changed 5 years ago by lrekucki

  • Severity set to Normal
  • Type set to Cleanup/optimization

comment:4 Changed 5 years ago by anonymous

  • Easy pickings unset

Please look at Baron's article here:

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

comment:5 Changed 4 years ago by aaugustin

  • Triage Stage changed from Design decision needed to Accepted
  • UI/UX unset

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.

Changed 4 years ago by ramiro

comment:6 Changed 4 years ago by ramiro

  • Has patch set

Changed 4 years ago by ramiro

comment:7 Changed 4 years ago by ramiro

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
Last edited 4 years ago by ramiro (previous) (diff)

comment:8 Changed 3 years ago by aaugustin

  • Owner changed from nobody to aaugustin
  • Status changed from new to assigned

comment:9 Changed 3 years ago by aaugustin

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!

comment:10 Changed 3 years ago by Aymeric Augustin <aymeric.augustin@…>

  • Resolution set to fixed
  • Status changed from assigned to closed

In 282b2f40cd0aa09815001e178f60c9e71667d847:

Fixed #15119 -- Stopped pinging the MySQL server.

Note: See TracTickets for help on using tickets.
Back to Top