MySQL Bugs: #39630: AIX 5.3 64-bit binaries are too slow

Bug #39630	AIX 5.3 64-bit binaries are too slow
Submitted:	24 Sep 2008 15:21	Modified:	30 Jun 2011 15:04
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Compiling	Severity:	S5 (Performance)
Version:	5.0.68	OS:	IBM AIX (5.3)
Assigned to:	Kent Boortz	CPU Architecture:	Any
Tags:	regression

Description:
Official AIX 5.3 Enterprise binaries (mysql-enterprise-gpl-5.0.68-aix5.3-powerpc-64bit.tar.gz) demonstrates bad performance with the following select:

select benchmark(500000000,2*2);

It runs for more than a minute:

bash-3.00# bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.68-enterprise-gpl MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select benchmark(500000000,2*2);
+--------------------------+
| benchmark(500000000,2*2) |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (1 min 0.78 sec)

The box has the following processor:

bash-3.00# lsattr -El proc0
frequency 2101896000 Processor Speed False
smt_enabled true Processor SMT enabled False
smt_threads 2 Processor SMT threads False
state enable Processor state False
type PowerPC_POWER5 Processor type False

On 64-bit Windows binaries of 5.0.67 on 3GHz Xeon, for example, it takes 16.11 seconds. So, theoretically, on AIX 5.3 this select should run 25 seconds or so, but not 50.

Moreover, previous 5.0.60 enterprise binaries for AIX 5.2 on this same platforms run faster:

-bash-3.00$ bin/mysql -uroot --socket=/tmp/vk.sock test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.60-enterprise-gpl MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select benchmark(500000000,2*2);
+--------------------------+
| benchmark(500000000,2*2) |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (58.30 sec)

So, we have a small performance regression after adding official support for AIX 5.2.

Compilation of 5.0.68 from source with optimization options for Power5 architecture (-O3, -qarch=auto) using current VAC 10.1.0.0 compiler allows to get faster execution:

-bash-3.00$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.68 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select benchmark(500000000,2*2);
+--------------------------+
| benchmark(500000000,2*2) |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (38.40 sec)

The fastest results with 5.0.60 64-bit binaries built with VAC 9.0.0.2 on the same box is:

-bash-3.00$ Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.60 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select benchmark(500000000,2*2);
+--------------------------+
| benchmark(500000000,2*2) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (22.20 sec)

How to repeat:
Install 5.0.68 binaries on AIX 5.3 box and run 

select benchmark(500000000,2*2);

compare result to any other platform/with any other OS on PowerPC taking CPU frequency into account.

Suggested fix:
1. Find proper combination of compiler version and optimization options to provide adequate performance on AIX 5.3/Power5 platform, as soon as it is officially supported.

2. Run benchmarks on AIX 5.3 before publishing binaries.

A gcc build was built with ./configure and then "make bin-dist" on an AIX 5.3 machine. No non-default optimisation flags were used.

Version 5.0.70-enterprise-gpl is the enterprise AIX-5.3 64bit release.  The version "5.0.70" is the gcc build.  This shows about a 2x performance increase in this test:

mysql> select version();
+-----------------------+
| version() |
+-----------------------+
| 5.0.70-enterprise-gpl |
+-----------------------+

mysql> select benchmark(500000000, 2*2);
+---------------------------+
| benchmark(500000000, 2*2) |
+---------------------------+
| 0 |
+---------------------------+
1 row in set (53.02 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.70 |
+-----------+
1 row in set (0.10 sec)

mysql> select benchmark(500000000, 2*2);
+---------------------------+
| benchmark(500000000, 2*2) |
+---------------------------+
| 0 |
+---------------------------+
1 row in set (24.94 sec)

For the results above gcc version used was 4.2.0.

Build team can change compilers, but I am hesitant to modifying platforms mid flight, and this could break linkability on client side and compatibility.

Such a decision will need to be made by product management and owner.

In addition to above comment:

One option could be adding new builds for a new compiler while keeping the builds from the old compiler.  This removes risk on customer's side, buts adds cost to MySQL.

As a first step, I have built from the same source using the same compiler flags and run some benchmarking. The compilers are (reported with -qversion)

C for AIX version 6.0.0.0
IBM XL C/C++ Enterprise Edition for AIX, V9.0, Version: 09.00.0000.0011
IBM XL C/C++ for AIX, V10.1, Version: 10.01.0000.0000

Machine used for the testing is "IBM,9110-51A", a Power5 machine. The compiler
flags given were

CC=xlc_r
CXX=xlC_r
CFLAGS="-g -O2 -q64 -ma -qstrict -qoptimize=2 -qmaxmem=8192"
CXXFLAGS="-g -O2 -q64 -ma -qstrict -qoptimize=2 -qmaxmem=8192"
LDFLAGS="-Wl,-brtl"

The very simplistic benchmark "select benchmark(500000000,2*2)" does show
a significant speed improvement using 9.0 or 10.1, from about 49 seconds using
6.0 to about 17 seconds in 9.0 and 10.1.

However running "run-all-tests" from the "sql-bench" benchmark directory shows
no significant changes in performance (10.1 tests where run when host a bit on load)

Operation                          seconds     usr     sys     cpu   tests
TOTALS                              2220.00   71.79    9.76   81.55  897029  (VisualAge 6.0)
TOTALS                              2190.00   71.76    9.75   81.51  897029  (IBM XL 9.0)
TOTALS                              2280.00   70.94    9.50   80.44  897029  (IBM XL 10.1)

Interestingly Bug#46895 and Bug#47017 seems to disappear if using IBM XL 10.1.

Tried some more aggressive optimization with IBM XL 10.1

-O3 -qinline -qipa=level=1 -qhot=level=1 -qhot=arraypad=8 -q64 -ma -qstrict

Using "-qipa=level=2" gave out of memory problems, so set down to "-qipa=level=1".
But the resulting binary ran slower than the one with less aggressive optimization flags.

Even worse, the resulting server showed several new test failures in the regression suite.
Could still be bugs in the MySQL source (incorrect aliasing or similar), but could also
be compiler bugs. The more aggressive optimizations gets less coverage by users, and
then tend to hit more compiler bugs. But can't say for sure.

Anyway, the resulting binary was less than half in size, and the simplistic test
"select benchmark(500000000,2*2)" was taking about 35 seconds to complete.

Can't really run the "sql-bench" suite, as SQL code is failing. Well, even with the
failing tests, the speedup is not significant

Operation   seconds     usr     sys     cpu   tests
TOTALS     2241.00   58.30    9.09   67.39  882905

I was able to build 5.5.12 binaries on AIX 6.1 that perform very well.  Here's the info on my machine and build:

root@armani:/usr/local> env
LDFLAGS=-L/usr/vac/bin -Wl,-brtl
AUTHSTATE=compat
WSM_WS_CMD="startsrc -s http4websm"
TERM=vt100
SHELL=/usr/bin/ksh
OBJECT_MODE=64
OLDPWD=/home/matt
WSM_DOC_DIR="/usr/websm/http/com.ibm.websm.http.server_1.0.0"
LOCPATH=/usr/lib/nls/loc
USER=root
ODMDIR=/etc/objrepos
CXXFLAGS=-O3 -ma -q64 -qcache=auto -qtune=auto -qstrict -qprefetch -qoptimize=3 -qmaxmem=-1
A__z=! LOGNAME
MAIL=/usr/spool/mail/root
PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/java5/jre/bin:/usr/java5/bin:/usr/vac/bin:/usr/vacpp/bin:/usr/local/bin:/usr/local/mysql/bin:/usr/vac/bin:/usr/vacpp/bin:/usr/local/bin:/usr/local/mysql/bin:/usr/vac/bin:/usr/vacpp/bin:/usr/local/bin:/usr/local/mysql/bin:/usr/vac/bin:/usr/vacpp/bin:/usr/local/bin:/usr/local/mysql/bin
LOGIN=root
PWD=/usr/local
LANG=en_US
TZ=CST6CDT
PS1=\u@\h:\w> 
WSM_CGI_DIR=
CXX=xlC_r
SHLVL=1
HOME=/root
LC__FASTMSG=true
CFLAGS=-O3 -ma -q64 -qcache=auto -qtune=auto -qstrict -qprefetch -qoptimize=3 -qmaxmem=-1
MAILMSG=[YOU HAVE NEW MAIL]
LOGNAME=root
CC=xlc_r
_=/usr/bin/env
NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat
LD_LIBRARY_PATH=/opt/freeware/lib/gcc/powerpc-ibm-aix6.1.0.0/4.2.0

root@armani:/usr/local> uname -a
AIX armani 1 6 00C7DDD04C00
root@armani:/usr/local> oslevel -s
6100-06-01-1043

This was done using the new xlc 11.1 compiler package.

They've also proven very stable so far.

Unfortunately I wasn't able to build 5.5.8 binaries on AIX 6.1 (the same problem with 5.5.12 binaries). Too many errors on linking steps and unsuccessful result. 
  
config: 
  
CC="xlc_r -O3 -q64 -ma -qstrict -qcache=auto -qtune=auto -qoptimize=3 -qprefetch -qmaxmem=-1 -qarch=auto" 
CXX="xlC_r -O3 -q64 -ma -qstrict -qcache=auto -qtune=auto -qoptimize=3 -qprefetch -qmaxmem=-1 -qarch=auto" 
CFLAGS="-O3 -ma -q64 -qcache=auto -qtune=auto -qstrict -qprefetch -qoptimize=3 -qmaxmem=-1" 

>uname -a 
AIX Blade8_AIXPS2_1 1 6 0006A74AD400 
>oslevel -s 
6100-06-04-1112 

xlc 11.1 compiler package 

Any problem with config or anything else? Can you tell me what's wrong and how to correct this?

example of errors: "ld: 0711-317 ERROR: Undefined symbol: .MDL_request..." 
If needed I can send a file with errors by mail.

For reference, on a Power7 based AIX LPAR with:

$ xlc_r -qversion
IBM XL C/C++ for AIX, V10.1
Version: 10.01.0000.0005

CC="xlc_r"
CXX="xlC_r"
CFLAGS="-ma -O3 -qstrict -qoptimize=3 -qmaxmem=16384 -DSYSV -D_AIX -D_AIX32 -D_AIX41 -D_AIX43 -D_AIX51 -D_AIX52 -D_AIX53 -D_AIX61 -D_ALL_SOURCE -DFUNCPROTO=15 -I/opt/freeware/include"; export CFLAGS

$ oslevel -s
6100-07-01-1141

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.62    |
+-----------+
1 row in set (0.00 sec)

mysql> select benchmark(500000000,2*2);
+--------------------------+
| benchmark(500000000,2*2) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (10.70 sec)

Best regards,

Frank Fegert