Unit tests 10 times slower on Linux if MySQL is used

[aigarius@…]

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

[Luke Plant]

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

[Ramiro Morales]

See also #16992 for a related MySQL particularity regarding AUTO_INCREMENT.

[Anssi Kääriäinen]

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.

[Anssi Kääriäinen]

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.

[Anssi Kääriäinen]

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

[Claude Paroz]

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!

[Anssi Kääriäinen]

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

[Anssi Kääriäinen]

Resolved problems found in comment:8

[Anssi Kääriäinen]

3rd try. Hopefully this diff now contains only relevant changes

[Anssi Kääriäinen]

4th time must work.

[anonymous]

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>

[Ramiro Morales]

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.

[Ramiro Morales]

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

[Anssi Kääriäinen]

In [17932]:

Fixed #16961 -- Skipped resetting AUTO_INCREMENT fields for MySQL if the server version is greater than 5.0.12. This allows for much faster testing.

Thanks to aigarius for the report and claudep and ramiro for review.