Bug #22268 InnoDB consumes too much events/kernel objects on Windows
Submitted: 12 Sep 2006 14:47 Modified: 10 Dec 2010 22:50
Reporter: Alexey Stroganov Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0,5.1 OS:Microsoft Windows
Assigned to: Calvin Sun
Triage: Needs Triage: D1 (Critical) / R3 (Medium) / E3 (Medium)

[12 Sep 2006 14:47] Alexey Stroganov
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
[13 Sep 2006 11:18] Heikki Tuuri
Alexey,

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

Regards,

Heikki
[14 Sep 2006 14:36] Alexey Stroganov
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.
[14 Sep 2006 14:45] Heikki Tuuri
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
[15 Sep 2006 1:57] Timothy Smith
Heikki,

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

Thank you,

Timothy
[27 Sep 2006 21:14] Bugs System
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.
[3 Oct 2006 20:01] Chad MILLER
Available in 5.0.26.
[3 Oct 2006 20:14] Chad MILLER
Available in 5.1.12-beta.

Patch pending?  Not setting to Documented.
[4 Oct 2006 5:58] Timothy Smith
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.
[4 Oct 2006 14:00] Heikki Tuuri
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
[5 Oct 2006 17:01] Paul Dubois
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.
[8 Nov 2006 15:06] Heikki Tuuri
This is now fixed also in the official Innobase Oy svn repositories of 5.0 and 5.1.
[4 Aug 2010 9:58] Roel Van de Paar
This is a duplicate of bug #52102. 

The underlying issue is the same ("excessive event handles") and that bug has a patch, which when applied, fixes the  issue listed here. Tested in 5.5.6m3:

With a 4Gb innodb_buffer_pool:

5.1/5.5.5m3:
---------
C:\mysql555m3\bin>C:\Users\Roel\Downloads\Handle\handle -p mysqld.exe -s

Handle v3.42
Copyright (C) 1997-2008 Mark Russinovich
Sysinternals - www.sysinternals.com

Handle type summary:
  ALPC Port       : 4
  Desktop         : 1
  Directory       : 2
  EtwRegistration : 25
  Event           : 1199518  <---- Excessive
  File            : 18
  IoCompletion    : 2
  Key             : 5
  KeyedEvent      : 1
  Mutant          : 25
  Thread          : 20
  Timer           : 3
  TpWorkerFactory : 4
  WindowStation   : 2
Total handles: 1199630
---------

5.5.6m3:
---------
C:\mysql555m3\bin>C:\Users\Roel\Downloads\Handle\handle -p mysqld.exe -s

Handle v3.42
Copyright (C) 1997-2008 Mark Russinovich
Sysinternals - www.sysinternals.com

Handle type summary:
  ALPC Port       : 4
  Desktop         : 1
  Directory       : 2
  EtwRegistration : 25
  Event           : 642
  File            : 18
  IoCompletion    : 2
  Key             : 5
  KeyedEvent      : 1
  Mutant          : 1
  Thread          : 20
  Timer           : 3
  TpWorkerFactory : 4
  WindowStation   : 2
Total handles: 730
---------
[10 Dec 2010 23:03] Omer Barnir
Issue is fixed in 5.5 and will not be back ported to 5.1