MySQL Bugs: #57981: UNIV_DEBUG causes big performance degradation for some operations on Solaris

Bug #57981	UNIV_DEBUG causes big performance degradation for some operations on Solaris
Submitted:	4 Nov 2010 14:57	Modified:	13 Feb 2012 3:42
Reporter:	John Embretsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5.7-m3	OS:	Solaris
Assigned to:	Sunny Bains	CPU Architecture:	Any
Tags:	rqg_pb2

Description:
With UNIV_DEBUG enabled the performance of some server operations degrade noticeably, up to 400% just for booting the server for the first time, compared to similar builds without UNIV_DEBUG, on Solaris platforms.

The issue is most prominent on SPARC CPUs with relatively low clock frequency.

Apparently, UNIV_DEBUG is enabled by default in debug builds of 5.5 and up since revid vasil.dimov@oracle.com-20100909100643-cocooto4dcnixxmh, see http://lists.mysql.com/commits/117827)

It is generally understood and acceptable that UNIV_DEBUG comes with some performance impact, but 400% seems to be out of the acceptable range.

Example measurements of starting the server and running a very small test case with MTR on a Solaris 10 SPARC (CMT) machine:

Without UNIV_DEBUG:

real	0m21.146s
user	0m6.579s
sys	0m2.223s

With UNIV_DEBUG:

real	1m27.407s
user	1m22.956s
sys	0m1.765s

This does not seem to be an issue on Linux, example:

Without UNIV_DEBUG:

real	0m15.126s
user	0m1.379s
sys	0m0.947s

With UNIV_DEBUG:

real	0m18.209s
user	0m5.262s
sys	0m1.410s

This issue manifests itself as various timeouts in automated testing on Solaris SPARC machines, and is especially visible during server start-up and data-intensive load, see e.g. Bug#57773.

How to repeat:
Build two set of binaries, one with and one without UNIV_DEBUG enabled. You can enable/disable UNIV_DEBUG for example by editing the file storage/innobase/CMakeLists.txt where it mentions ADD_DEFINITIONS("-DUNIV_DEBUG").

You can verify that UNIV_DEBUG is enabled by examining the server error log after starting the server and looking for the text 
  "InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!!".

With each set of binaries, do for example:

cd mysql-test
time perl mysql-test-run.pl 1st

and compare the output from "time".

on linux, FC13 x64, it takes 2.5 minutes to create the doublewrite buffer in innodb on the first startup in valgrind.  annoying.  no idea what it is really doing all that time...

yes, i confirm on FC13 it is just the doublewrite creation that takes ages, but only when starting mysqld in valgrind.  so my workaround is start it up without valgrind, shut it down, then start in valgrind.

Part of the UNIV_DEBUG slowness has been reported as Bug #58226.