MySQL Bugs: #58151: Default value of innodb_buffer_pool_size is now insufficient for stress test

Bug #58151	Default value of innodb_buffer_pool_size is now insufficient for stress test
Submitted:	12 Nov 2010 2:01	Modified:	10 Jan 2013 11:33
Reporter:	Elena Stepanova	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.1-m5	OS:	Any
Assigned to:	Sunny Bains	CPU Architecture:	Any

Description:
Starting from a push of 2010-10-26 11:32:36 on mysql-next-mr-bugfixing, we observe a change in behavior of server under high concurrency test.

The test executes Sysbench workload in 2,000 connections for long enough time (over an hour). It is run with the default value of innodb_buffer_pool_size=8MB. 

It might be not quite reasonable configuration for such a test; however, before the push we'd never had the problem, while now the test always causes the 67-percent complaints starting a few minutes after the test start, and ending with the 95-percent complaint and the forced assertion. 

It is observed on Solaris Sparc, I tried to run the test with the same configuration on Linux and on Solaris x86_64 and did not have the problem. 

On Windows (Server 2003 x86_64) error log looks somewhat different, it shows numerous semaphore wait issues. 

How to repeat:
Run high concurrency test with 2000 connections (instructions will be provided separately).

This might be related to the the same root cause as Bug#57981 (UNIV_DEBUG causes big performance degradation for some operations on Solaris), for the following reasons:
  1) Issue is most prominent on Solaris SPARC.
  2) Issue is possibly only visible with debug builds (though not confirmed).
  3) Issue started occurring upon a merge which included the revision that introduced InnoDB's UNIV_DEBUG instrumentation by default for debug builds.

The revision introducing UNIV_DEBUG by default was vasil.dimov@oracle.com-20100909100643-cocooto4dcnixxmh. This was merged to mysql-next-mr-bugfixing on 2010-10-26. It might be worth checking out.

After collecting some statistics, I'm not convinced that it is an entirely debug issue, and hence that it's caused by UNIV_DEBUG affecting performance.

I do observe performance degradation described in bug#57981; but in regard to the problem described in the current bug, even on non-debug builds, I'm getting noticeably more 67-percent warnings and 95-percent errors with the recent builds than with the older ones.

Below is a summary for the last 6+6 short tests I ran on builds of oct 11 and nov 15.
Each test starts server (with default innodb_buffer_pool_size=8Mb) and runs 4000-thread sysbench test for 5 min. The numbers below are counts of 67-percent warnings or 95-percent errors produced during the test run.

Oct 11: 
3 warnings
2 warnings
0 warnings 
0 warnings 
3 warnings 
1 warning

Nov 15:
1 warning + 1 error (95-percent, assertion)
3 warnings
16 warnings
1 warning
16 warnings
3 warnings + 1 error (95-percent, assertion)

I also tried running tests on 
  Windows Server 2008
  Solaris x86_64
  Linux RHEL (2 different HW configurations)
but did not get a definite trend like the one on Solaris Sparc.

For the time being, I'm switching the bug to Verified and assigning to Sunny as discussed before.

I have more various data and can collect even more on demand, please just let me know what is relevant and which tests you would want me to run, if any.

Not repeatable on recent 5.6 sources.