Bug #39715 performance regression of auto_increment

• Submitted: 29 Sep 2008 6:47
• Modified: 20 Oct 2008 14:23
• Reporter: Yoshiaki Tajika (Basic Quality Contributor)
• Status: Can't repeat
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0
• OS: Linux
• CPU Architecture: Any
• Tags: qc

[29 Sep 2008 6:47] Yoshiaki Tajika

Description:
I found performance regression of auto_increment through 5.0 series.
Inserting rows to tables with auto_increment column takes longer time.

The elapsed time for inserting to such a table at my PC is:

5.0.15 to 5.0.58:  21 sec
5.0.60 to 5.0.66a: 38 sec
5.0.67 to 5.0.68:  44 sec

There might be some additional imprementation around auto_increment,
but I think the regression above is too large to miss.

How to repeat:
drop table if exists t1, t2;
/* create dummy records */
create table t1(c1 int);
insert t1 values(1);
insert t1 select 1 from t1; 
  : /* repeat many times, so that you have enough rows. */
  : /* At my PC, I had 1,000,000 rows. */

/* Insert records into the table with auto_increment column. */
/* It takes longer through 5.0.x revisions. */
create table t2(c1 int primary key auto_increment);
insert t2 select null from t1;

My my.cnf is:
[mysqld]
default-character-set=latin1
server-id = 1
user=mysql
datadir = /usr/local/mysql/var
innodb_data_home_dir = /usr/local/mysql/var
innodb_log_group_home_dir = /usr/local/mysql/var
innodb_data_file_path = ibdata1:10M:autoextend
innodb_lock_wait_timeout=10
innodb_file_per_table

That is, I don't use logging.
And, the tables t1 and t2 are both MyISAM.

Suggested fix:
I have no idea.

[16 Oct 2008 16:17] MySQL Verification Team

I couldn't repeat this issue on Windows. I will test on Linux.

[20 Oct 2008 14:23] MySQL Verification Team

Thank you for the bug report. I couldn't repeat this issue the difference I found is < 1sc between 5.0.15 and 5.0.67:

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.00 sec)

mysql> create table t2(c1 int primary key auto_increment);
Query OK, 0 rows affected (0.04 sec)

mysql> insert t2 select null from t1;
Query OK, 1048576 rows affected (5.60 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql> 
mysql> show variables like "%version%";
+-------------------------+------------------------------------------+
| Variable_name           | Value                                    |
+-------------------------+------------------------------------------+
| protocol_version        | 10                                       |
| version                 | 5.0.15-standard                          |
| version_comment         | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686                                     |
| version_compile_os      | pc-linux-gnu                             |
+-------------------------+------------------------------------------+
5 rows in set (0.00 sec)

*********************************************************************
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  1048576 | 
+----------+
1 row in set (0.00 sec)

mysql> create table t2(c1 int primary key auto_increment);
Query OK, 0 rows affected (0.00 sec)

mysql> insert t2 select null from t1;
Query OK, 1048576 rows affected (6.19 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| protocol_version        | 10                           | 
| version                 | 5.0.67                       | 
| version_comment         | MySQL Community Server (GPL) | 
| version_compile_machine | i686                         | 
| version_compile_os      | pc-linux-gnu                 | 
+-------------------------+------------------------------+
5 rows in set (0.00 sec)