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
Triage: Needs Triage: D1 (Critical)

[8 Sep 2009 7:45] Susanne Ebrecht
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
[8 Sep 2009 7:46] Susanne Ebrecht
I have to test the workaround
[8 Sep 2009 8:51] Giuseppe Maxia
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
[8 Sep 2009 9:14] Susanne Ebrecht
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;
[8 Sep 2009 11:30] Giuseppe Maxia
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)
[8 Sep 2009 13:44] Peter Laursen
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.
[8 Sep 2009 16:13] Calvin Sun
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
[8 Sep 2009 19:04] Peter Laursen
With hindsight I should probably have told that I am not using the new InnoDB plugin!
[8 Oct 2009 23:00] Bugs System
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".
[20 Jul 2010 10:01] Susanne Ebrecht
I will close this because I am not able anymore to follow my own thoughts.