MySQL Bugs: #47187: Inserting 4 million rows not possible InnoDB

Bug #47187	Inserting 4 million rows not possible InnoDB
Submitted:	8 Sep 2009 7:45	Modified:	20 Jul 2010 10:01
Reporter:	Susanne Ebrecht	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.1	OS:	Linux (Ubuntu amd64)
Assigned to:	Susanne Ebrecht	CPU Architecture:	Any
Tags:	regression

Description:
Tested with actual bzr tree more often:

CREATE TABLE t(i SERIAL, j INTEGER) ENGINE=InnoDB;

INSERT INTO t(j) VALUES(1);

INSERT INTO t(j) SELECT j+1 FROM t;
INSERT INTO t(j) SELECT j+1 FROM t;
INSERT INTO t(j) SELECT j+1 FROM t;
...

mysql> insert into t(j) select j+1 from t;
Query OK, 2097152 rows affected (1 min 32.75 sec)
Records: 2097152  Duplicates: 0  Warnings: 0

mysql> insert into t(j) select j+1 from t;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

Always when I try to insert 4194304 rows I get this crash.

I/O gives out in an endless loop:

090908  9:43:04 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 854, signal count 854
Mutex spin waits 0, rounds 18918, OS waits 655
RW-shared spins 197, OS waits 98; RW-excl spins 84, OS waits 82
------------
TRANSACTIONS
------------
Trx id counter 0 1817
Purge done for trx's n:o < 0 1322 undo n:o < 0 0
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 1816, not started, process no 11263, OS thread id 140174144792912
MySQL thread id 1, query id 60 localhost miracee
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
5631 OS file reads, 5001 OS file writes, 2046 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 17393, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 954601046
Log flushed up to   0 954601046
Last checkpoint at  0 954601046
0 pending log writes, 0 pending chkp writes
1922 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 22452144; in additional pool allocated 693248
Dictionary memory allocated 37816
Buffer pool size   512
Free buffers       437
Database pages     74
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 28190, created 20455, written 32253
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 11263, id 140174114281808, state: waiting for server activity
Number of rows inserted 6952060, updated 0, deleted 0, read 8388607
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

How to repeat:
See above

I have to test the workaround

It probably depends on how much memory is allocated to innodb_buffer_pool_size
### On Linux, using 5.1.33 (innodb_buffer_pool_size=5G)

mysql [localhost] {msandbox} (test) > insert into t(j) select j+1 from t;
Query OK, 2097152 rows affected (14.99 sec)
Records: 2097152  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t(j) select j+1 from t;
Query OK, 4194304 rows affected (30.10 sec)
Records: 4194304  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t(j) select j+1 from t;
Query OK, 8388608 rows affected (1 min 0.60 sec)
Records: 8388608  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t(j) select j+1 from t;
Query OK, 16777216 rows affected (2 min 1.28 sec)
Records: 16777216  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t(j) select j+1 from t;
Query OK, 33554432 rows affected (3 min 55.71 sec)
Records: 33554432  Duplicates: 0  Warnings: 0

### On Mac OSX, using 5.1.37 (innodb_buffer_pool_size=1G)
mysql [localhost] {msandbox} (test) > insert into t(j) select j+1 from t;
Query OK, 2097152 rows affected (31.47 sec)
Records: 2097152  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t(j) select j+1 from t;
Query OK, 4194304 rows affected (1 min 3.73 sec)
Records: 4194304  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t(j) select j+1 from t;
Query OK, 8388608 rows affected (2 min 37.00 sec)
Records: 8388608  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t(j) select j+1 from t;
Query OK, 16777216 rows affected (4 min 49.50 sec)
Records: 16777216  Duplicates: 0  Warnings: 0

It seems to be a regression because it works fine with older versions.

The workaround here is to:

ALTER TABLE tab ENGINE=MyISAM;
Insert the data
ALTER TABLE tab ENGINE=INNODB;

What earlier versions behave differently?
I did this test: out of the box, I tried the above instructions on 5.0.84 and 5.1.39, without any buffer increase.
They behave the same way. On my Mac OSX laptop, I can insert 4 million records, and I get the message about too many locks when I try to insert 8 million. But the result is the same on both versions, 5.0 and 5.1.
On Linux, I can only insert 2 million records without errors 0n 5.1.39, but the same is true for 5.0.84.
Moreover, the workaround is simple: add memory to innodb_buffer_pool_size, and you can insert up to 67 million records (It could be more, but 67 million is where I stopped looking)

With 64 bit Windows server (5.1.38) running on Windows7 with those InnoDB options:

innodb_additional_mem_pool_size=3M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=107M
innodb_log_file_size=54M
(all this was configured by the configuration instance wizard) 

.. I get for same

SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; -- 112197632

CREATE TABLE t(i SERIAL, j INTEGER) ENGINE=INNODB;

INSERT INTO t(j) VALUES(1);
INSERT INTO t(j) SELECT j+1 FROM t;
..
..
INSERT INTO t(j) SELECT j+1 FROM t; -- success (2097152 row(s) affected)
INSERT INTO t(j) SELECT j+1 FROM t; -- success (4194304 row(s) affected)
INSERT INTO t(j) SELECT j+1 FROM t; -- success (8388608 row(s) affected)
INSERT INTO t(j) SELECT j+1 FROM t; -- success (16777216 row(s) affected)
INSERT INTO t(j) SELECT j+1 FROM t; -- success (33554432 row(s) affected)

.. and note this was a ~100M Innodb_buffer_pool_size only. So I do not find the correlation to innodb_buffer_pool_size as assumed by Guiseppe. Seems not the only thing at least.

Susanne,

Could you please provide the old version you tested? Will you be able to insert more rows after increasing the buffer pool size? There is a possibility that sizes of internal data structures increase over time, but should not change too much.

Thanks,
Calvin

With hindsight I should probably have told that I am not using the new InnoDB plugin!

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I will close this because I am not able anymore to follow my own thoughts.