MySQL Bugs: #22268: MySQL/InnoDB crashes in high concurrency test

Bug #22268	MySQL/InnoDB crashes in high concurrency test
Submitted:	12 Sep 2006 16:47	Modified:	8 Nov 2006 16:06
Reporter:	Alexey Stroganov
Status:	Closed
Category:	Server: InnoDB	Severity:	S1 (Critical)
Version:	5.0.25pre/64bit	OS:	Microsoft Windows (Windows 2003/64bit)
Assigned to:	Sunny Bains	Target Version:

Description:
I observed crash of MySQL/InnoDB on Windows 2003 64bit during high concurrecy test with
following symptoms:

Slot 991: thread id 10080, type 0, in use 1, susp 0, time 4
Slot 992: thread id 5668, type 0, in use 1, susp 0, time 4
Slot 993: thread id 10212, type 0, in use 1, susp 0, time 4
Slot 994: thread id 9504, type 0, in use 1, susp 0, time 4
Slot 995: thread id 2216, type 0, in use 1, susp 0, time 4
Slot 996: thread id 8672, type 0, in use 1, susp 0, time 5
Slot 997: thread id 5152, type 0, in use 1, susp 0, time 5
Slot 998: thread id 7628, type 0, in use 1, susp 0, time 5
Slot 999: thread id 8712, type 0, in use 1, susp 0, time 5
060912 14:35:13InnoDB: Assertion failure in thread 8740 in file .\srv\srv0srv.c
line 1357
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.

How to repeat:
1. Start mysql server on windows box
2. Grant permisson for remote host/user where client(sysbench) will be run
3. Get and compile sysbench on any linux box(sysbench.sf.net)

4. Run it as following for prepare stage:
./sysbench --test=oltp --mysql-host=<your target host> --mysql-port=3306
--mysql-user=dbuser --mysql-db=test --mysql-table-engine=innodb --oltp-table-size=5000
prepare

5. Run it as following to run of test itself:
./sysbench --test=oltp --mysql-host=<your target host> --mysql-port=3306
--mysql-user=dbuser --mysql-db=test --mysql-table-engine=innodb --max-requests=0
--max-time=900 --num-threads=1500

Alexey,

please attach the COMPLETE .err log to the bug report.

Regards,

Heikki

Heikki,

It looks like I missed somehow the following error message in the beginning of error
log:

InnoDB: There appear to be 1000 MySQL threads currently waiting
InnoDB: inside InnoDB, which is the upper limit. Cannot continue operation.
InnoDB: We intentionally generate a seg fault to print a stack trace
InnoDB: on Linux. But first we print a list of waiting threads.
Slot 0: thread id 1344, type 0, in use 1, susp 0, time 1
Slot 1: thread id 5396, type 0, in use 1, susp 0, time 1
Slot 2: thread id 3424, type 0, in use 1, susp 0, time 1
Slot 3: thread id 3872, type 0, in use 1, susp 0, time 1
Slot 4: thread id 3316, type 0, in use 1, susp 0, time 1
...

Then I looked for source of this limitation and found the following in srv/srv0start.c

#if defined(__WIN__) || defined(__NETWARE__)

/* Create less event semaphores because Win 98/ME had difficulty creating
40000 event semaphores.
Comment from Novell, Inc.: also, these just take a lot of memory on
NetWare. */
        srv_max_n_threads = 1000;
#else
        if (srv_pool_size >= 1000 * 1024) {
                                  /* Here we still have srv_pool_size counted
                                  in kilobytes (in 4.0 this was in bytes)
                                  srv_boot() converts the value to
                                  pages; if buffer pool is less than 1000 MB,
                                  assume fewer threads. */
                srv_max_n_threads = 50000;

        } else if (srv_pool_size >= 8 * 1024) {

                srv_max_n_threads = 10000;
        } else {
                srv_max_n_threads = 1000;       /* saves several MB of memory,
                                                especially in 64-bit
                                                computers */
        }
#endif

So there is no chance for the recent windows OSes and especially on boxes with a lot of
RAM to raise this limit? May be it has sense to differentiate Win98/Me and recent Windows
2003/Vista. 

Also above explain why I didn't observe such issue on Linux boxes.

Alexey,

we could raise the limit on Windows to 10 000 in 5.0. I believe no one tries to run 5.0
on Windows 95/ME any more.

On Linux, the limit is 10 000 with a buffer pool of 8 MB. That is probably why you did
not observe this on Linux.

Regards,

Heikki

Heikki,

Please do raise the limit to 10 000 on Windows.  After discussion with Elliot, we agree
this is desirable.

Thank you,

Timothy

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12663

ChangeSet@1.2272, 2006-09-27 17:13:58-04:00, elliot@mysql.com +1 -0
  Temporary fix for bug#22268 (official patch will come soon)
  Don't cap threads at 1000 on Windows any more.

Available in 5.0.26.

Available in 5.1.12-beta.

Patch pending?  Not setting to Documented.

Docs team:  this bug has been fixed in 5.0.26 & 5.1.12 with a temporary patch.

Please set this back to "In progress" after marking it fixed.  I expect the official
patch from the InnoDB team will be very similar to the patch made in 5.0.26 and 5.1.12. 
However, if it differs significantly, I will notify you when the official patch is
available.

The temporary patch removes the 1,000-concurrent-connection limitation for InnoDB on
Windows servers.  Novell Netware servers are still limited to 1,000 concurrent InnoDB
connections.

Sunny,

Elliot's patch is ok. Please merge this to the official Innobase Oy source tree and
correct the comment just after the changed line.

Thank you,

Heikki

Noted in 5.0.26, 5.1.12 changelogs.

The number of InnoDB threads is no longer limited to 1,000 on
Windows.

Setting report back to In Progress per earlier note.

This is now fixed also in the official Innobase Oy svn repositories of 5.0 and 5.1.