Opened 13 years ago
Closed 13 years ago
#16961 closed Cleanup/optimization (fixed)
Unit tests 10 times slower on Linux if MySQL is used
Reported by: | Owned by: | nobody | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.3 |
Severity: | Normal | Keywords: | mysql |
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Unit tests call the command 'ALTER TABLE tbl_name AUTO_INCREMENT=1' for all tables between test sets ( in db/backends/mysql/base.py:sql_flush ), in MySQL this command can take up to a second even for an empty table due to very old wishlist bug in MySQL. http://bugs.mysql.com/bug.php?id=37927
This bug causes a set of unittests to take 30 minutes to run (instead of 3 minutes, if the ALTER TABLE statements are removed).
Attachments (4)
Change History (18)
comment:1 by , 13 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 13 years ago
Type: | Bug → Cleanup/optimization |
---|
comment:3 by , 13 years ago
Has patch: | unset |
---|
comment:4 by , 13 years ago
See also #16992 for a related MySQL particularity regarding AUTO_INCREMENT
.
comment:5 by , 13 years ago
In MySQL's sql_flush() we could first check what the value for the table's auto-increment is (using information_schema.tables). If it is already 1, skip the ALTER TABLE. This would get rid of most of the ALTER TABLE commands.
Otherwise I don't see anything we can do here. BTW I did not spot any dramatic speedup when disabling the auto_increment resetting. There wasn't any errors in the test suite, either.
comment:6 by , 13 years ago
A minor correction to the above: there is a big speed difference. With auto-increment reset Django's test suite takes 6500 seconds on my machine, without it 2700.
Also, there is no need for the sequence reset on my version (5.5.22): the truncate already resets the sequence to 1. So, it seems we are wasting around 3000 seconds doing exactly nothing.
mysql> truncate foof; mysql> select auto_increment from information_schema.tables where table_name = 'foof'; +----------------+ | auto_increment | +----------------+ | 1 | +----------------+ mysql> insert into foof values (default); mysql> select auto_increment from information_schema.tables where table_name = 'foof'; +----------------+ | auto_increment | +----------------+ | 2 | +----------------+ mysql> truncate foof; mysql> select auto_increment from information_schema.tables where table_name = 'foof'; +----------------+ | auto_increment | +----------------+ | 1 | +----------------+
I don't know which MySQL versions this applies to. It would be very interesting to test this on 5.0.3 which is the now-minimum supported version. I don't have time to do that check, so if any MySQL user wants to see this ticket fixed, verify which versions do auto_increment reset on truncate.
comment:7 by , 13 years ago
Answering myself about the MySQL version needed. From mysql's TRUNCATE TABLE docs:
From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset to zero by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.
So, if server version > 5.0.12 we can just skip the auto_increment reset and speed up tests by about 60%.
by , 13 years ago
Attachment: | ticket_16961.diff added |
---|
comment:8 by , 13 years ago
Has patch: | set |
---|---|
Patch needs improvement: | set |
connection.get_server_version() should be connection.mysql_version (renamed in r17921).
Just the backends tests, without the patch:
Ran 33 tests in 25.951s
With the patch:
Ran 34 tests in 9.406s
Nice!
comment:9 by , 13 years ago
Patch needs improvement: | unset |
---|
Rebased the patch on current trunk head, the above get_server_version() error should now be fixed.
There is also a github version of the patch here: https://github.com/akaariai/django/tree/fast_autoincrement
EDIT: the below patch seems to be a bit messed up. Will fix.
by , 13 years ago
Attachment: | ticket_16961.diff.3 added |
---|
3rd try. Hopefully this diff now contains only relevant changes
comment:10 by , 13 years ago
MySQL 5.1.61, with both MyISAM and InnoDB storage engines show the same behavior:
InnoDB:
... Server version: 5.1.61-3-log (Debian) ... mysql> SET storage_engine=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> USE ramiro; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> CREATE TABLE t16961_test (ai INTEGER AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.09 sec) mysql> insert into t16961_test values (default); Query OK, 1 row affected (0.00 sec) mysql> insert into t16961_test values (default); Query OK, 1 row affected (0.00 sec) mysql> select auto_increment from information_schema.tables where table_name = 't16961_test'; +----------------+ | auto_increment | +----------------+ | 3 | +----------------+ 1 row in set (0.00 sec) mysql> truncate t16961_test; Query OK, 0 rows affected (0.00 sec) mysql> select auto_increment from information_schema.tables where table_name = 't16961_test'; +----------------+ | auto_increment | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) mysql>
MyISAM:
mysql> SET storage_engine=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> USE ramiro; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> CREATE TABLE t16961_test_myisam (ai INTEGER AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.00 sec) mysql> insert into t16961_test_myisam values (default); Query OK, 1 row affected (0.00 sec) mysql> insert into t16961_test_myisam values (default); Query OK, 1 row affected (0.00 sec) mysql> insert into t16961_test_myisam values (default); Query OK, 1 row affected (0.00 sec) mysql> select auto_increment from information_schema.tables where table_name = 't16961_test_myisam'; +----------------+ | auto_increment | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec) mysql> truncate t16961_test_myisam; Query OK, 0 rows affected (0.00 sec) mysql> select auto_increment from information_schema.tables where table_name = 't16961_test_myisam'; +----------------+ | auto_increment | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) mysql>
comment:11 by , 13 years ago
Last anonymous commenter was me. Ignore it as I hadn't seen comment by Anssi about the fact that the sane behavior is available from 5.0.13 onwards. Perhaps only useful part the testing/confirmation with both InnoDB and MyISAM.
comment:12 by , 13 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:13 by , 13 years ago
Oh my, this reduces the time to run Django trunk test suite from 3h28m34s to 51min6s (a reduction of 75%!).
MySQL version is 5.1.61, InnoDB storage engine.
Patch looks ok.
Without the patch:
Tue Apr 24 09:51:55 ART 2012 Python version: 2.7.3rc2 (default, Apr 5 2012, 18:58:12) [GCC 4.6.3] ... ---------------------------------------------------------------------- Ran 4718 tests in 12132.824s OK (skipped=91, expected failures=2) Tue Apr 24 13:20:29 ART 2012
With the patch:
$ date ; ~/django_test/mysql50 -v0 ; date Tue Apr 24 08:52:00 ART 2012 Python version: 2.7.3rc2 (default, Apr 5 2012, 18:58:12) [GCC 4.6.3] ... ---------------------------------------------------------------------- Ran 4721 tests in 2787.808s OK (skipped=91, expected failures=2) Tue Apr 24 09:43:06 ART 2012
I'm marking 'Accepted' on the basis that if a reasonably easy and correct workaround can be found, we will include it (rather than saying this is a bug in Django).