Optimize MariaDB/MySQL sql_flush

[Adam Johnson]

This was suggested as an extra feature for Django-MySQL by @jonatron in ​https://github.com/adamchainz/django-mysql/pull/611/files , but it would make more sense to make it in Django core.

Currently MySQL's sql_flush emits TRUNCATE TABLE statements. These are relatively slow on small tables since they force recreation of the table on disk. The alternative, DELETE FROM, is faster. I can see a diference even for a single empty table locally (MariaDB 10.4):

chainz@localhost [21]> truncate t2;
Query OK, 0 rows affected (0.005 sec)

chainz@localhost [22]> delete from t2;
Query OK, 0 rows affected (0.000 sec)

TransactionTestCase uses sql_flush, via the flush management command, to reset the database. Most test suites don't use very large tables, so using DELETE FROM should normally be faster in this case. Optimizing it MySQL to use DELETE FROM for flushing, at least for tables up to a certain size (1000 rows or similar heuristic) could save lot of time when using TransactionTestCase.

[Claude Paroz]

I confirm that TransactionTestCase is horrrrribly slow on MySQL/MariaDB! So a big +1 from me.

[jonatron]

If I recall correctly, TRUNCATE will reset AUTO_INCREMENT but DELETE FROM won't.

[Adam Johnson]

If that's the case we'd want to ALTER TABLE tablename AUTO_INCREMENT = 1 for those tables with auto-increment keys.

[Masashi SHIBATA]

Hi! Can I work on this ticket?

[Claude Paroz]

No need to ask when there is no owner. Please do!

[Masashi SHIBATA]

Thank you!

[Masashi SHIBATA]

I created a pull request.
​https://github.com/django/django/pull/12634

[Simon Charette]

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

[Adam Johnson]

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.

Very sensible. I forget about this option!

In summary projects that will benefits from this patch would benefit way more by defining available_apps on their tests.

Yes indeed, although it can be tedious to define and keep correct.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 89032876:

Fixed #31275 -- Optimized sql_flush() without resetting sequences on MySQL.

Co-Authored-By: Simon Charette <charettes@…>