Opened 13 years ago

Closed 11 years ago

#15119 closed Cleanup/optimization (fixed)

MySQL backend generates unnecessary commands

Reported by: Luke Sneeringer Owned by: Aymeric Augustin
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

Description

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.

Attachments (2)

15119-1.diff (8.7 KB ) - added by Ramiro Morales 12 years ago.
15119-2.diff (8.5 KB ) - added by Ramiro Morales 12 years ago.

Download all attachments as: .zip

Change History (12)

comment:1 by Ramiro Morales, 13 years ago

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 by Ramiro Morales, 13 years ago

Component: UncategorizedDatabase layer (models, ORM)
Triage Stage: UnreviewedDesign decision needed

comment:3 by Łukasz Rekucki, 13 years ago

Severity: Normal
Type: Cleanup/optimization

comment:4 by anonymous, 13 years ago

Easy pickings: unset

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.

comment:5 by Aymeric Augustin, 12 years ago

Triage Stage: Design decision neededAccepted
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.

by Ramiro Morales, 12 years ago

Attachment: 15119-1.diff added

comment:6 by Ramiro Morales, 12 years ago

Has patch: set

by Ramiro Morales, 12 years ago

Attachment: 15119-2.diff added

comment:7 by Ramiro Morales, 12 years ago

I've teste 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
Version 0, edited 12 years ago by Ramiro Morales (next)

comment:8 by Aymeric Augustin, 11 years ago

Owner: changed from nobody to Aymeric Augustin
Status: newassigned

comment:9 by Aymeric Augustin, 11 years ago

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 by Aymeric Augustin <aymeric.augustin@…>, 11 years ago

Resolution: fixed
Status: assignedclosed

In 282b2f40cd0aa09815001e178f60c9e71667d847:

Fixed #15119 -- Stopped pinging the MySQL server.

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