Opened 4 years ago

Closed 3 years ago

#16961 closed Cleanup/optimization (fixed)

Unit tests 10 times slower on Linux if MySQL is used

Reported by: aigarius@… 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)

ticket_16961.diff (5.7 KB) - added by akaariai 3 years ago.
ticket_16961.2.diff (15.3 KB) - added by akaariai 3 years ago.
Resolved problems found in comment:8
ticket_16961.diff.3 (5.7 KB) - added by akaariai 3 years ago.
3rd try. Hopefully this diff now contains only relevant changes
ticket_16961.3.diff (5.7 KB) - added by akaariai 3 years ago.
4th time must work.

Download all attachments as: .zip

Change History (18)

comment:1 Changed 4 years ago by lukeplant

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

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

comment:2 Changed 4 years ago by anonymous

  • Type changed from Bug to Cleanup/optimization

comment:3 Changed 4 years ago by nate_b

  • Has patch unset

comment:4 Changed 3 years ago by ramiro

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

comment:5 Changed 3 years ago by akaariai

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 Changed 3 years ago by akaariai

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 Changed 3 years ago by akaariai

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

Changed 3 years ago by akaariai

comment:8 Changed 3 years ago by claudep

  • 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 Changed 3 years ago by akaariai

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

Last edited 3 years ago by akaariai (previous) (diff)

Changed 3 years ago by akaariai

Resolved problems found in comment:8

Changed 3 years ago by akaariai

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

Changed 3 years ago by akaariai

4th time must work.

comment:10 Changed 3 years ago by 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>

comment:11 Changed 3 years ago by ramiro

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 Changed 3 years ago by claudep

  • Triage Stage changed from Accepted to Ready for checkin

comment:13 Changed 3 years ago by ramiro

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

comment:14 Changed 3 years ago by akaariai

  • Resolution set to fixed
  • Status changed from new to closed

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.

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