MySQL Bugs: #26590: MySQL does not allow more than 1023 open transactions

Bug #26590	MySQL does not allow more than 1023 open transactions
Submitted:	23 Feb 2007 11:30	Modified:	15 Jun 2010 15:35
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0, 5.1, etc.	OS:	Any
Assigned to:	Sunny Bains	CPU Architecture:	Any
Tags:	v6

Description:
at 1024 transactions, that have undo records (as in, edited any data), InnoDB will fail to work:

mysql> insert into yt values (66666);
ERROR 1114 (HY000): The table 'yt' is full
mysql> insert into t2 values (66666);
ERROR 1114 (HY000): The table 't2' is full
mysql> create table xxxxxxxx (a int) engine=InnoDB;
ERROR 1005 (HY000): Can't create table 'test.xxxxxxxx' (errno: 135)

Error log will have:

070223 13:26:01InnoDB: Warning: cannot find a free slot for an undo log. Do you have too
InnoDB: many active transactions running concurrently?
070223 13:26:22InnoDB: Warning: cannot find a free slot for an undo log. Do you have too
InnoDB: many active transactions running concurrently?
070223 13:26:22  InnoDB: Warning: cannot create table `test`.`xxxxxxx` because tablespace full

How to repeat:
<?
        $dbs=array();
        $i=1024;
        while($i--) {
                $db=$dbs[$i]=mysql_connect("localhost","root","",1);
                mysql_select_db("test",$db);
                mysql_query("BEGIN",$db) or die(mysql_error());
                mysql_query("INSERT INTO yt VALUES ($i)",$db) or die(mysql_error());
        }
        sleep(600);
?>

Suggested fix:
allow more. unlimited, or run-time settable value.

Raising severity to S1, in many cases this may mean indefinite hang without workaround.

I am marking this as a feature request. An application should try to keep the number of concurrently open transactions less than 1000. Having lots of open transactions can cause bottlenecks in locking, for example.

This either has to be portrayed as InnoDB limitation in documentation, or bumped up. 

It is no longer 1996, where "oh noes, 1000 transactions gonna crash my 486, rewrite everything in ASM".
Databases nowadays are served by  many-gigabytes memory / many cores boxes, that can handle far more than 1000 concurrent connections. 

Additionally, in scaled out environments single database isn't the only point-of-waiting. Blocking on other resources may cause idle transactions, so even if they are short-lived (100ms, for example, what is quite good metric for web application execution), the throughput of such a system is limited to 10000 TPS in theory, and actual number is much lower.

Opinions stating that 1000 concurrent transactions is bad application design need serious reality check. 
Even if such load is exceptional, database should be last element to crash or refuse service. 
Especially, if fix for such issue is few constants away. 

So, it is a bug, either InnoDB's, or in documentation.

Heikki, feature request is sort of OK, but it's happened in normal service to high load customers even on fairly ordinary hardware (couple of cores, 16GB of RAM) so it seems like a problem that will grow.

It seems that bug #18828 should be a duplicate of this or this a duplicate of it.

http://bugs.mysql.com/bug.php?id=18828 requests us to add a descriptive error code if we cannot allocate enough active transactions.

http://bugs.mysql.com/bug.php?id=26608 is associated with this.

Assigning this to Sunny.

Sunny has a patch for this. We may apply it to 5.2. It is a bit risky to put it to 5.1 or 5.0, since it involves creation of 128 rollback segment pages at the database creation, and thus is quite a big change to InnoDB.

Hi Heikki,

What would this effectively raise the limit on number of transactions to? Any ideas?

Cheers,

Mark

128K open transactions.

Maybe along the zip code. No fix in 5.0 or 5.1.

A complex patch. Delayed until 6.0 or later.

Heikki,

Can you clarify one thing - does it corresponds to concurrently actively writing transactions only does this limit applies to unpurged transactions as well.

Ie if transaction was committed but not  yet purged will it still use the slot ?

If you are affected by this bug and the problem persists on restart then see Bug#35352 for a possible solution.

It would be really nice to have this in the official InnoDB release.

Because this would change on disk structures, it has to be done 
under the control of innodb_file_format management.   That really
can't be done during the life of a release.   Will consider for a
future release.

Bug #43655, running out of space in tablespace and bug #49238, DDL crashes server when it's at the open transaction limit, are possibly related to this one. The two might be the same bug or might have different resolution possibilities.

Correction: bug #43665 [corrected], running out of space in tablespace and bug #49238, DDL crashes server when it's at the open transaction limit, are possibly related to this one. The two might be the same bug or might have different resolution possibilities.

See http://blogs.innodb.com/wp/2010/04/innodb-multiple-rollback-segments/ for the planned improvement for this that is expected to raise the limit to 128,000 concurrent transactions that have modified data. Release date is not yet known.

The fix is in the latest 5.5.4 release.

Fix is in the 5.5.4 release.

Regards,
-sunny

Noted in 5.5.4 changelog:

The limit of 1023 concurrent data-modifying transactions has been
raised. The limit is now 128 * 1023 concurrent transactions that
generate undo records. You can remove any workarounds that require
changing the proper structure of your transactions, such as
committing more frequently or delaying DML operations to the end of a
transaction.

See http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-rollback-segments.html

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

Already fixed in 5.5.