Wednesday Jun 11, 2008

MySQL Performance Comparison

We run an internally developed AMP benchmark to understand the performance characteristics of the AMP stack on various platforms.


We did some comparisons of the AMP stack in Cool Stack 1.2 with the one in Cool Stack 1.3. We didn't see any signficant difference in performance in the Apache/PHP tier between the two versions. However, there is a dramatic improvement in performance in the MySQL tier using Cool Stack 1.3. Cool Stack 1.2 shipped with MySQL 5.0.45 and 1.3 now has MySQL 5.1.24.


The graph below shows the CPU utilization on the MySQL system at different load levels :



MySQL 5.1 is 4x more efficient than 5.0.45 ! There are improvements in the Query Cache and query optimization in MySQL 5.1, but at this point we are doing more analysis to understand the reasons for the dramatic performance increase. Here is another graph that shows the network throughput at different load levels :



It appears that some of that efficiency comes from driving much less load on the network as well. We know that the client API has changed in 5.1, but it is amazing how much of a difference it makes in terms of performance.


 So, the bottom-line is : Upgrade to MySQL 5.1 in  Cool Stack 1.3 and share your experiences. I'm very interested in finding out what others see in terms of performance.






Monday Apr 16, 2007

SMF support for MySQL in Cool Stack



Here are instructions on how to use SMF to manage MySQL. These instructions assume no knowledge of SMF. All commands should be executed as root.

Create the manifest

 The manifest defines the service and controls the privileges with which the service will execute. We use the service name csk-mysql to distinguish this as a Cool Stack service.

Create a file named /var/svc/manifest/network/cskmysql.xml with the following contents :


<?xml version='1.0'?>
 <!DOCTYPE service_bundle SYSTEM '/usr/share/lib/xml/dtd/service_bundle.dtd.1'>
<!--
    Copyright 2006,2007 Sun Microsystems, Inc.  All rights reserved.
    Manifest for CSKmysql - should reside in /var/svc/manifest/network
-->

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

<service
        name='network/csk-mysql'
        type='service'
        version='1'>
        <create_default_instance enabled='false' />
        <single_instance />

       <!--
         Wait for network interfaces to be initialized.
        -->
       <dependency name='network'
           grouping='require_all'
           restart_on='error'
           type='service'>
           <service_fmri 

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

        <!--
          Wait for all local filesystems to be mounted.
        -->
        <dependency name='filesystem-local'
            grouping='require_all'
            restart_on='none'
            type='service'>
            <service_fmri
               value='svc:/system/filesystem/local:default'/>
         </dependency>

         <exec_method
             type='method'
             name='start'
             exec='/opt/coolstack/lib/svc/method/svc-cskmysql start'
             timeout_seconds='60'>
             <method_context
working_directory='/opt/coolstack'>
                 <method_credential
                   user='mysql' group='mysql'
                   privileges='basic,!proc_session,!proc_info,!file_link_any,net_privaddr' />
             </method_context>
         </exec_method>

         <exec_method
             type='method'
             name='stop'
             exec='/opt/coolstack/lib/svc/method/svc-cskmysql stop'
             timeout_seconds='60'>
             <method_context />
         </exec_method>

         <exec_method
             type='method'
             name='refresh'
             exec='/opt/coolstack/lib/svc/method/svc-cskmysql restart'
             timeout_seconds='60'>
             <method_context working_directory='/opt/coolstack'>
                 <method_credential
                   user='mysql' group='mysql'
                   privileges='basic,!proc_session,!proc_info,!file_link_any,net_privaddr' />
             </method_context>
         </exec_method>

 </service>
 </service_bundle>

Create the method

Create the file /opt/coolstack/lib/svc/method/svc-cskmysql referenced in the manifest with the following contents and make it executable. You may have to create the directories below /opt/coolstack/lib first. This file needs to be edited to set DB_DIR to the path of your data directory (where data files reside), and MYSQL_DIR if you are using the 64-bit MySQL version.

#!/usr/bin/sh
#
#   Copyright 2007 Sun Microsystems, Inc.  All rights reserved.
#        Method file for CSKMySQL
#
# This uses the MySQL packages from CoolStack 1.1 (CSKmysql)
# If you're using the 32bit mysql from CSKamp, change MYSQL_DIR below to mysql_32bit.
# This file should reside in /opt/coolstack/lib/svc/method
#
# NOTE: Make sure DB_DIR is owned BY the mysql user and group and chmod 700
#

. /lib/svc/share/smf_include.sh

DB_DIR=/data
PIDFILE=${DB_DIR}/`/usr/bin/uname -n`.pid
MYSQL_DIR=/opt/coolstack/mysql_32bit

mysql_stop () {
        if [ -f ${PIDFILE} ]; then
            /usr/bin/pkill mysqld_safe >/dev/null 2>&1
            /usr/bin/kill `cat ${PIDFILE}` > /dev/null 2>&1 && echo -n ' mysqld'
        fi
}

mysql_start () {
        $MYSQL_DIR/bin/mysqld_safe --user=mysql --datadir=${DB_DIR} --pid-file=${PIDFILE} > /dev/null &
}

##
# Start of script
#
case "$1" in
    start)
        mysql_start
        ;;
    stop)
        mysql_stop
        ;;
    restart)
        mysql_stop
        while pgrep mysqld > /dev/null
        do
            sleep 1
        done
        mysql_start
        ;;
    \*)
        echo ""
        echo "Usage: `basename $0` { start | stop | restart }"
        echo ""
        exit 64
        ;;
esac


Change file ownership

Ensure that the mysql user and group exist and this user owns $DB_DIR. It's also a good idea to chmod 0700 all files in $DB_DIR.

Start the csk-mysql service

Import the new mysql config :

# svccfg -v import /var/svc/manifest/network/cskmysql.xml

We are now ready to start our service. Start it as follows :

# svcadm -v enable csk-mysql

If the service starts successfully, you should see mysqld running. A log of the
service startup will be in /var/svc/log/network-csk-mysql:CSKmysql.log file. You can also get more detailed information for troubleshooting startup failures from the command svcs -x. More information on SMF can be found at  http://www.sun.com/bigadmin/content/selfheal/smf-quickstart.html

 


About

I'm a Senior Staff Engineer in the Performance & Applications Engineering Group (PAE). This blog focuses on tips to build, configure, tune and measure performance of popular open source web applications on Solaris.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today