#31275: Optimize MariaDB/MySQL sql_flush
-------------------------------------+-------------------------------------
Reporter: Adam (Chainz) | Owner: Masashi
Johnson | SHIBATA
Type: | Status: assigned
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* cc: Simon Charette (added)
Comment:
> If that's the case we'd want to `ALTER TABLE tablename AUTO_INCREMENT =
1` for those tables with auto-increment keys.
I decided to guide the patch submitter towards avoiding these queries and
suggesting the use of `TransactionTestCase.reset_sequences` instead while
documenting the behaviour changed in Django 3.1 and that this feature
should be explicitly enabled to achieve this previously implicit reset.
It made more sense to me since the goal of the ticket is to make this
operation faster and these sequence alteration queries took longer than
plain `TRUNCATE` calls from my local testing which completely defeats the
purpose of this ticket. The fact we have an explicit feature to achieve
this behaviour only makes it slightly backward incompatible for users
relying on this undefined behaviour.
Based on the fact `ALTER TABLE tablename AUTO_INCREMENT = 1` is slower
than `TRUNCATE tablename` I guess MySQL's `sql_flush` could be optimized
further to only issue the latter when asked to flush both a table and its
sequence. Given `TransactionTestCase`
[https://github.com/django/django/blob/b9336b78cf2a9a29f4934041c9e221bc68daec80/django/test/testcases.py#L1037-L1040
calls the flush command] with `reset_sequences=False` that would allow us
to favour `DELETE` over `TRUNCATE` when no sequences are meant to be
flushed and grasp the benefit in the test suite without relying on the
1000 rows heuristics which is a bit arbitrary and require a schema
introspection query which isn't cheap either.
I'll add that the benefits for Django's test suite and any suite that
[https://docs.djangoproject.com/en/3.0/topics/testing/advanced/#django.test.TransactionTestCase.available_apps
heavily uses] `TransactionTestCase.available_apps` will likely be minimal
as this ms difference between `DELETE` and `TRUNCATE` is only apparent
when the number flushed tables after every test is large and the
`available_apps` feature greatly reduces it this number in large Django
project. In summary projects that will benefits from this patch would
benefit way more by defining `available_apps` on their tests.
--
Ticket URL: <https://code.djangoproject.com/ticket/31275#comment:10>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/068.b0c0d47def17bc30b6a9e0729119d871%40djangoproject.com.
