Monday Feb 25, 2008

MySQL Benchmark UltraSPARC T2 beats Xeon on Consolidation of OLTP & Web

Recently we put together a consolidation benchmark to see how an open-source stack performs against the proprietary stack from Microsoft. Solaris, MySQL, and Sun Web Server running on the open-source UltraSPARC T2 processor were pitted against a Microsoft SW stack running on a 4-socket QC Xeon server. This benchmark highlights the continued trend to incorporate MySQL open-source databases and how it works under virtualization (Solaris Zones).

The Sun SPARC Enterprise T5220 (1.4 Ghz UltraSPARC T2 processor) and Solaris Containers managing a consolidation of Open-Source Software components (MySQL Database and Sun Java System Web Server) provided 2.4 times better performance than the HP DL580 system (four Xeon quad-core processors) and a major virtualization software, Microsoft Windows 2003 Server EE, Microsoft SQLserver database and Microsoft IIS webserver.

The Sun SPARC Enterprise T5220 using the MySQL database in Solaris zones is over 3.2 times faster performance than HP DL580 using Microsoft SQLserver database running with the leading virtualization software.

The Sun SPARC Enterprise T5220 using the Sun Java System Web Server with Solaris Zones is 53% faster than than HP DL580 using Microsoft IIS webserver running with the leading virtualization software.

The database performance and the web performance were normalized and equally weighted for an overall performance metric.

The Sun SPARC Enterprise T5220 (1.4 Ghz UltraSPARC T2 processor) including hardware and open-source software costs has 3.7 times better price/performance than the HP DL580 system (four Xeon quad-core processors) with the Microsoft software stack. The Sun solution has 56% less price than the HP/Microsoft/virtualization configuration.

The Sun SPARC Enterprise T5220 (1.4 Ghz UltraSPARC T2 processor) had a 4.1 times better watt/performance than the HP DL580(four Xeon quad-core processors). The Sun system used 73% fewer watts total.

Sun's solution requires half of the rack space of the HP DL580. Sun has an 8.1 SWaP advantage over the HP DL580 (SWaP = Perf /[ Space (RU) x Watts ] )

The Sun SPARC Enterprise T5220 used open-source MySQL 5.0 database software and Sun Java System Web Server 7 which are both free for download to obtain these results.

Performance Comparison:

System Configuration (both 64GB Memory)
server SW
Sun SPARC Enterprise T5220 1.4GHz 1 chip
8 cores
2 RU Solaris 10 8/07 MySQL 5.0.51a Sun Java System Web
Server 7
HP DL580 G5 2.93GHz 4 QC Xeon
16 cores
4 RU MS Server 2003 EE MS SQL-
server 2005
MS IIS 6.0

System Performance & Cost (server, 64GB, disks, SW)
Web ops/s OLTP txns/m Watts HW+
SW $
Sun SPARC Enterprise T5220 1.4GHz 9546 224K 480W $131k 2.36 203 55
HP DL580 G5 2.93GHz 6250 70K 830W $204k 1.0 830 204
Sun's Advantage 53% 3.2x 73% 56% 2.4x 4.1x 3.7x
Note: Watts are measured server watts during the run.

Benchmark Description

The Full-Stack Consolidated workload consists of 2 primary components: 1) a Web workload consisting of static HTTP requests, and 2) an OLTP database workload using a mix of common SQL transactions executed on a mix of tables in an RDBMS.

The Web workload generates static requests for web pages using a mix of file sizes that range from 100 bytes to 900K bytes with an average file size of 16K bytes. The metrics from this workload includes the number of web operations per second. In this Web workload, a set of 6 client systems emulating thousands of users, generated HTTP requests to all 3 webservers in parallel running on the server.

This performance comparison used an Ad-Hoc OLTP workload, called iGEN OLTP 1.6, which was developed from a realistic customer workload. iGen OLTP avoids problems that plague other OTLP benchmarks like TPC-C. In particular, it is completely random in table row selections and thus is difficult to use artificial optimizations. iGen OLTP stresses process and thread creation, process scheduling, and database commit processing. The database has 10 million customers residing in it, and is approximately 15 GB in size. at time of creation. The transactions are comprised of various SQL transactions: read-only selects, joins, inserts and update operations. For this OLTP workload, 1 client system was used to emulate hundreds of users, generating SQL transactions to all 3 database instances in parallel running on the server.

On the Sun SPARC Enterprise T5220, the 3x Webserver instances and 3x MySQL database instances where isolated on 6 Containers. Each webserver zone shared a single storage array, but separate Gbit network interfaces. The 3x MySQL database zones shared a single network interface but used separate storage arrays for the database files and transaction logs. On the HP DL580, the 3x Webserver instances and 3x SQLserver database instances where isolated on 6 virtual machines. Each webserver VM shared a single storage array, but separate Gbit network interfaces. The 3x SQLserver database VMs shared a single network interface but used separate storage arrays for the database files and transaction logs.

When results listed above were obtained when running both the Web and OLTP workloads concurrently. The performance results for each workload were normalized using the results obtained from the HP DL580 as follows:

Sun SPARC Enterprise T5220 factor = Web + OLTP = 2.36

  1. Web Factor = 9546 ops/sec / (2x 6250 ops/sec) = 0.76
  2. OLTP Factor = 224K txns/min / (2x 70K txns/min) = 1.6

HL DL580 performance factor = Web + OLTP = 1.0

  1. Web Factor = 6250 ops/sec / (2x 6250 ops/sec) = 0.50
  2. OLTP Factor = 70K txns/min / (2x 70K txns/min) = 0.50

The Sun Enterprise T5220 used 3 Solaris Containers to host separate MySQL database instances and 3 Containers to host separate Sun Web Server instances.

Each MySQL and Webserver instance ran in an isolated zone not visible o any processes or users in the other containers.

The combination of OLTP and Web workloads consumed an average of 95% of cpu utilization when running concurrently.

System Configuration & Results Summary

Sun SPARC Enterprise T5220 (1 chip, 8 cores)9546 Web operations/sec, 224K OLTP Txns/min.
HP DL580 (4 chips, 16 cores)6250 Web operations/sec, 70K OLTP Txns/min. Results as of 2/22/2008.

Sun SPARC Enterprise T5220 9546 Web ops/sec, 224K OLTP Txns/min
HP DL580 G5 6250 Web ops/sec, 70K OLTP Txns/min
Reference Date: Feb 22, 2008
Systems: Sun SPARC Enterprise T5220
HP DL580 G5
Processor/GHz of Server: 1x UltraSPARC T2 1.4 GHz,
4x Intel Xeon X7350 2.93 GHz
Operating System/Virtualization: Solaris 10 8/07 & Solaris Containers
Microsoft Windows Server2003 EE & a leading virtualization platform

Sun SPARC Enterprise T5220 with:

  • 1x UltraSPARC T2, 1.4  GHz 8-core processor
  • 64 GB of memory
  • 4x Internal 146GB SAS disks
  • 2x 750W Power Supplies
  • 4x Sun StorageTek ST2540 arrays (12x 146GB disks per array)
  • 2x Sun StorageTek 4Gb Dual-Port Fibre Channel PCI-E HBA (from Emulex)
  • 1x Sun PCI-E Dual Giga Ethernet Adapter UTP
  • Solaris 10 8/07
  • Solaris Containers
  • MySQL 5.0.51a
  • Sun Java System Web Server 7 u1

HP DL580 G5 with:

  • 4x Intel Xeon X7350, 2.93  GHz 4-core processors
  • 64 GB of memory
  • 5x Internal 146GB SAS disks & Smart Array P400i SAS Ctlr
  • 4x 800/1200W Power Supplies
  • 4x Sun StorageTek ST2540 arrays (12x 146GB disks per array)
  • 2x QLogic SANblade QLE2462 PCI-E Dual Port FC adapter
  • 3x HP NC360T PCI Express Dual Port Gigabit Server Adapter
  • Leading Virtualization Platform
  • Microsoft Windows Server 2003 EE 64-bit
  • Microsoft SQLserver 2005
  • Microsoft IIS 6.0 Webserver

Monday Feb 11, 2008

Optimal Sun Studio compiler options for MySQL


We recently integrated MySQL 5.0.45 version with Open Solaris. MySQL 5.0.45 is available in SXDE 01/08. Sun Studio was used to compile MySQL on both Solaris SPARC and Solaris AMD64 platform.

This blog entry describes the changes that were made to improve MySQL performance using Sun Studio:

  • Enable function inlining when Sun Studio is used.
    • The header file univ.i has the following code:
      • #if !defined(GNUC) && !defined(WIN)
      • #undef UNIV_MUST_NOT_INLINE /\* Remove compiler warning \*/
      • #define UNIV_MUST_NOT_INLINE
      • #endif
    •  Modifying it to add Sun Studio compiler as under:
      • #if !defined(GNUC) && !defined(WIN) && !defined(__SUNPRO_C)
      • #undef UNIV_MUST_NOT_INLINE /\* Remove compiler warning \*/
      • #define UNIV_MUST_NOT_INLINE
      • #endif

  •   The following compiler options boost performance on both SPARC and AMD64:
    • -xbuiltin=%all' and ' -xprefetch=auto -xprefetch_level=3
  •   On SPARC setting the optimization level to 4 helps performance. (-x04).
  •  On AMD64 platform, the compilation of NDB fails with Sun Studio due to the following snippet in    

case $SYSTEM_TYPE-$MACHINE_TYPE-$ac_cv_prog_gcc-$have_ndbcluster in
  # ndb fail for whatever strange reason to link Sun Forte/x86
  # unless using incremental linker

           The -xildon option is added for Solaris AMD64 platform ...which is no longer available.In previous releases, this option forced the compiler to use the incremental linker (ild) by default instead of the linker (ld) for link-only invocations of the compiler. That is, with -g, the compiler's default behavior was to automatically invoke ild in place of ld whenever you used the compiler to link object files, unless you specified -G or source files on the command line. This is no longer the case. The incremental linker is no longer available. Refer to the following for more details :

                After removing this option, the compilation of ndb works fine.

  • On 32bit built , the release binary is using the following option: -features=no%except It is recommended not to use this option. Removing this option results in following error:
    • Undefined                       first referenced
       symbol                             in file
      __1cG__CrunMex_rethrow_q6F_v_       ../libmysql/.libs/libmysqlclient.a(ssl.o) 
            This error can be resolved by adding the -lCrun library at run-time

How to configure MySQL to run with Solaris Management Facility (SMF)

MySQL 5.0.45 is integrated with Open Solaris build 79. It is available in Solaris Express Developer Edition (SXDE) 01/08. MySQL 5.0.45 is integrated with Solaris Service Management Facility (SMF).

This blog entry describes the steps that were taken to integrate MySQL with SMF.

First a quick recap of what is SMF:

SMF is the core component of the predictive self-healing technology available in Solaris 10, which provides automatic recovery from software and hardware failures as well as adminstrative errors.
Some of the advantages of using SMF are as under:

  • Failed services are automatically restarted in dependency order, whether they failed as the result of administrator error, software bug, or were affected by an uncorrectable hardware error.

  • More information is available about misconfigured or misbehaving services, including an explanation of why a service isn't running , as well as individual, persistent log files for each service.

  • Problems during the boot process are easier to debug, as boot verbosity can be controlled, service startup messages are logged, and console access is provided more reliably during startup failures.

  • Administrators can securely delegate tasks to non-root users more easily, including the ability to configure, start, stop, or restart services .

  • Large systems boot faster by starting services in parallel according to their dependencies.

Below are the SMF service manifest and accompanying shell script needed to integrate MySQL with Solaris SMF.

Perform the following steps to import the manifest into the SMF repository.

  1. Save the following XML code to a file called "mysql.xml" in /var/svc/manifest/application/database. You need to create the directory if it doesn't exist and have the appropriate privileges to perform this action.

<service_bundle type='manifest' name='mysql'>


Wait for network interfaces to be initialized.

        <service_fmri value='svc:/milestone/network:default' />


Wait for all local filesystems to be mounted.

    <service_fmri value='svc:/system/filesystem/local:default' />

    exec='/lib/svc/method/mysql start'
    timeout_seconds='60' />

exec='/lib/svc/method/mysql stop'
timeout_seconds='60' />

<instance name='version_50' enabled='false'>

    <method_credential user='mysql' group='mysql' />

    <property_group name='mysql' type='application'>
        <propval name='bin' type='astring'      
            value='/usr/mysql/5.0/bin' />
        <propval name='data' type='astring'
            value='/var/mysql/5.0/data' />


<stability value='Evolving' />

        <loctext xml:lang='C'>
            MySQL RDBMS

        <manpage title='MySQL 5.0.45' section='1' />
        <doc_link name='' uri='' />



The default instance of the manifest assumes that the database user is mysql and the database directory is /var/mysql/5.0/datasql/data . If any of them is different, update the above XML accordingly.
  1. Save the following shell script to a file called "mysql".

getproparg() {
    val=`svcprop -p $1 $SMF_FMRI`
    [ -n "$val" ] && echo $val

MYSQLBIN=`getproparg mysql/bin`
MYSQLDATA=`getproparg mysql/data`
PIDFILE=${MYSQLDATA}/`/usr/bin/uname -n`.pid

if [ -z $SMF_FMRI ]; then
    echo "SMF framework variables are not initialized."
    exit $SMF_EXIT_ERR

if [ -z ${MYSQLDATA} ]; then
    echo "mysql/data property not set"

                    if [ ! -d ${MYSQLDATA} ]; then
echo "mysql/data directory ${MYSQLDATA} is not a valid MySQL data directory"

if [ ! -d ${MYSQLDATA}/mysql ]; then
    ${MYSQLBIN}/mysql_install_db --user=mysql --datadir=${MYSQLDATA}

mysql_start() {

    echo ${MYSQLBIN}/mysqld --user=mysql --datadir=${MYSQLDATA} --pid-file=${PIDFILE}
    ${MYSQLBIN}/mysqld --user=mysql --datadir=${MYSQLDATA} --pid-file=${PIDFILE} > /dev/null &


mysql_stop() {

    if [ -f ${PIDFILE} ]; then
        pkill mysqld

case "$1" in





        echo "Usage: $0 {start|stop}"
        exit 1



  1. Place the shell script "mysql" in /lib/svc/method.

  2. Change the permission to 555. You need to have the appropriate write privileges to copy files into this directory.

  3. Import the SMF manifest by executing the following commands:

    # cd /var/svc/manifest/application/database 
    # /usr/sbin/svccfg import mysql.xml
  4. Initially the service instance is disabled. Use the following command to see the state.
    # svcs mysql

  5. Start the service for the default instance by executing the following command:

    # /usr/sbin/svcadm enable mysql

    From this point on the MySQL process is controlled by the Solaris SMF.

    For more details on how to use SMF, refer to the following BigAdmin site:


Ritu Kamboj


« February 2008 »