Opened 5 years ago

Closed 4 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 Anssi Kääriäinen 4 years ago.
ticket_16961.2.diff (15.3 KB) - added by Anssi Kääriäinen 4 years ago.
Resolved problems found in comment:8
ticket_16961.diff.3 (5.7 KB) - added by Anssi Kääriäinen 4 years ago.
3rd try. Hopefully this diff now contains only relevant changes
ticket_16961.3.diff (5.7 KB) - added by Anssi Kääriäinen 4 years ago.
4th time must work.

Download all attachments as: .zip

Change History (18)

comment:1 Changed 5 years ago by Luke Plant

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Triage Stage: UnreviewedAccepted

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 5 years ago by anonymous

Type: BugCleanup/optimization

comment:3 Changed 5 years ago by Nate Bragg

Has patch: unset

comment:4 Changed 4 years ago by Ramiro Morales

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

comment:5 Changed 4 years ago by 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.

comment:6 Changed 4 years ago by 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.

comment:7 Changed 4 years ago by 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%.

Changed 4 years ago by Anssi Kääriäinen

Attachment: ticket_16961.diff added

comment:8 Changed 4 years ago by Claude Paroz

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 4 years ago by Anssi Kääriäinen

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 4 years ago by Anssi Kääriäinen (previous) (diff)

Changed 4 years ago by Anssi Kääriäinen

Attachment: ticket_16961.2.diff added

Resolved problems found in comment:8

Changed 4 years ago by Anssi Kääriäinen

Attachment: ticket_16961.diff.3 added

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

Changed 4 years ago by Anssi Kääriäinen

Attachment: ticket_16961.3.diff added

4th time must work.

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

comment:12 Changed 4 years ago by Claude Paroz

Triage Stage: AcceptedReady for checkin

comment:13 Changed 4 years ago by 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

comment:14 Changed 4 years ago by Anssi Kääriäinen

Resolution: fixed
Status: newclosed

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