| Bug #26590 | MySQL does not allow more than 1023 open transactions | ||
|---|---|---|---|
| Submitted: | 23 Feb 2007 12:30 | Modified: | 16 Apr 2007 14:10 |
| Reporter: | Domas Mituzas | ||
| Status: | Patch pending | ||
| Category: | Server: InnoDB | Severity: | S2 (Serious) |
| Version: | 5.0, 5.1, etc. | OS: | Any |
| Assigned to: | Sunny Bains | Target Version: | |
| Tags: | v6 | ||
| Triage: | Triaged: D2 (Serious) / R4 (High) / E2 (Low) | ||
[23 Feb 2007 12:44]
Domas Mituzas
Raising severity to S1, in many cases this may mean indefinite hang without workaround.
[23 Feb 2007 16:02]
Heikki Tuuri
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.
[23 Feb 2007 23:16]
Domas Mituzas
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.
[24 Feb 2007 16:41]
James Day
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.
[26 Feb 2007 14:44]
Heikki Tuuri
http://bugs.mysql.com/bug.php?id=18828 requests us to add a descriptive error code if we cannot allocate enough active transactions.
[26 Feb 2007 14:44]
Heikki Tuuri
http://bugs.mysql.com/bug.php?id=26608 is associated with this.
[26 Feb 2007 16:25]
Heikki Tuuri
Assigning this to Sunny.
[16 Apr 2007 14:10]
Heikki Tuuri
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.
[23 Apr 2007 21:08]
Mark Leith
Hi Heikki, What would this effectively raise the limit on number of transactions to? Any ideas? Cheers, Mark
[24 Apr 2007 3:32]
Sunny Bains
128K open transactions.
[9 Nov 2007 18:21]
Heikki Tuuri
Maybe along the zip code. No fix in 5.0 or 5.1.
[19 Feb 2008 17:15]
Heikki Tuuri
A complex patch. Delayed until 6.0 or later.
[16 Mar 2008 21:51]
Peter Zaitsev
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 ?
[18 Mar 2008 5:15]
Sunny Bains
If you are affected by this bug and the problem persists on restart then see Bug#35352 for a possible solution.
[2 Jul 0:57]
Mark Callaghan
It would be really nice to have this in the official InnoDB release.
[2 Jul 3:29]
Ken Jacobs
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.
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.