HP 5992-4755 Sizing Manual
HP O SM S: M yS QL Si zing Gui de f or HP Pr oL i ant c -C las s Blade Se r v er s HP P art Number: 5 99 2 -4 7 5 5 P ublished: Mar ch 2008 E dition: 1 .0
© Copyright 2008 Hewlett-P ackard Development Company , L.P . Legal Notice Confidential computer software. V alid license from HP required for possession, use or copying. Consistent with F AR 12.211 and 12.212, Commercial Computer Softw are, Computer Software Documentation, and T echnical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice. The only w arranties for HP products and services are set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty . HP shall not be liable for technical or editorial errors or omissions contained herein. Acknowledgments Intel and Itanium are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries. RED HA T READY Logo and RED HA T CERTIFIED P ARTNER Logo are trademarks of Red Hat, Inc.
T able of C ont ents Introduction .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. 9 Executive Summary. .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . 9 Intended Audience.. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 9 Scope and Purpose.. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. 9 HP Services.. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .9 T ypographic Conventions. .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. 11 Publishing History. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 11 HP Encourages Y our Comments. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . 11 Benchmark Software.. .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .12 Hardw are T est Environment. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. 12 SysBench Configuration.. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. 13 MySQL Installation. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .14 MySQL Configuration.. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . 14 Benchmark Results. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. 15 Interpreting the Data.. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. 16 HP Proliant BL460c Server.. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 16 HP Proliant BL465c Server.. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 17 HP Proliant BL480c Server.. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 18 HP Proliant BL685c Server.. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 19 Summary .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 20 Resources .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .20 T able of Contents 3
4
L ist of F igur es 1 SysBench Results for the HP ProLiant BL460c Server.. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . 17 2 SysBench Results for the HP ProLiant BL465c Server.. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . 18 3 SysBench Results for the HP ProLiant BL480c Server .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .19 4 SysBench Results for the HP ProLiant BL685c Server.. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . 20 5 Benchmark T est Results. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .20 5
6
L ist of T ables 1 T est Softw are Environment.. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. 12 2 T est Hardware Environment.. .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . 12 3 SQL Queries. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. 13 4 SysBench Options.. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . 14 5 MySQL Installation P ackages. .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . 14 6 MySQL T est Configuration . . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 14 7 SysBench Results for the HP ProLiant BL460c Server.. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . 16 8 SysBench Results for the HP ProLiant BL465c Server.. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . 17 9 SysBench Results for the HP ProLiant BL480c Server .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .18 10 SysBench Results for the HP ProLiant BL685c Server.. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . 19 7
8
Intr oducti on HP Open Source Middleware Stacks (OSMS) offer building block applications, such as the W eb Server; technical blueprints, and documents such as this MySQL Sizing Guide which describes the maximum MySQL workload that HP ProLiant c-Class Blade Servers are capable of supporting; and consulting services to speed the successful implementation of an open source strategy . Ex ec uti ve Summar y The HP Open Source Integrated Portfolio (HP OSIP) comprises a range of products and services designed to verify that customers can successfully realize the cost and feature benefits of adopting open source softw are in their IT environments. HP Open Source foundation components include the base components of an open source-based ecosystem. HP serv ers and storage are v alidated to run the Linux operating system together with the supported commercial Linux distribution. This results published in this document w ere achiev ed using the SysBench benchmark for each of the HP ProLiant c-Class Blade Servers. V arious test configurations and test environments were used and documented to provide readers an opportunity to understand the testing methodology . Inte nded Audi ence The intended audience for this document is anyone who is interested in determining the number of database workload that can be supported on a given HP ProLiant c-Class Blade Server and details for deploying a hardw are environment based on the database requirements of certain applications. Scope and P urpos e This document presents the results of a series of benchmark tests performed using the SysBench softw are test suite. The tests were conducted running a MySQL database serv er to evaluate system performance when running a database under a heavy load. The following HP ProLiant servers were used in this test: BL460c, BL480c, BL465c, and BL685c. The benchmark data provided in this sizing guide can assist customers in determining which HP Proliant c-Class Blade Server meets their MySQL database requirements along with how-to information for configuring the HP Proliant c-Class Blade Serv er and storage in a specific production environment. HP provides quality assurance from extensiv e integration testing with open source software and HP hardw are so that you can confidently deploy the complete stack. Once y ou hav e completed a successful evaluation, you have the flexibility to âdo it yourself â or get assistance from HP to incorporate open source stacks into your existing IT infrastructure. HP Serv ice s HP Open Source Consulting Services can help you build and integrate open source and commercial softw are across multiple operating system (OS) environments. Additionally , HP Open Source Support Services provide industry leading technical support for all the products HP sells, including hardw are, operating systems, and open source middleware. T o learn more about HP Open Source Consulting and Support Services, contact your local HP sales representativ e or visit the HP Business and IT Services w ebsite at: http://www .hp.com/hps For the location of the nearest sales office, call: ⢠In the United States: 1 800 637 7740 ⢠In Canada: 1 905 206 4725 ⢠In Japan: 81 3 3331 6111 ⢠In Latin America: 1 305 267 4220 ⢠In Australia/New Zealand: 61 3 9272 2895 Introduction 9
⢠In Asia Pacific: 8522 599 7777 ⢠In Europe/Africa/Middle East: 41 22 780 81 11 10
T y pogr aphi c Con ve ntio ns This document uses the following typographic conv entions. Command A command name or qualified command phrase. ComputerOut T ext displayed by the computer . Ctrl-x A key sequence. A sequence such as Ctrl-x indicates that you must hold down the key labeled Ctrl while y ou press another key or button. ENVIRONVAR The name of an environment v ariable, for example, PATH. [ERRORNAME] The name of an error , usually returned in the errno v ariable. Ke y The name of a keyboard key . Return and Enter both refer to the same key . T erm The defined use of an important word or phrase. UserInput Commands and other text that you type. VARIABLE The name of a placeholder in a command, function, or other syntax display that you replace with an actual value. \ (continuation character) A backslash (\) at the end of a line of code (such as a command) indicates that the following line of code is contiguous, and you must not insert a line break. This convention facilitates the typesetting of long lines of code examples on a printed page. If you cut and paste sample code from this publication, ensure that you remove backslash characters at line endings. ... The preceding element can be repeated an arbitrary number of times. | Separates items in a list of choices. P ublishing Hist ory The document publishing date and part number indicate the current edition of the document. The publishing date changes when a new edition is printed. Minor changes might be made without changing the publishing date. The document part number changes only when extensive changes are made. Document updates might be issued between editions to correct errors or document product changes. For the latest version of this document online, see the HP T echnical Documentation website at: http://www .docs.hp.com Publication Date Edition Number Supported OS Manufacturing P art Number March 2008 1 Red Hat Enterprise Linux 5 AS Update 1 (RHEL5ASu1) 5992-4755 HP Encour ages Y our Co mments HP encourages your comments concerning this document. W e are committed to providing documentation that meets your needs. Send any errors found, suggestions for improv ement, or compliments to: feedback@fc.hp.com Include the document title, manufacturing part number , and any comment, error found, or suggestion for improvement you hav e concerning this document. Introduction 11
Benc hmar k So ft w ar e The SysBench test suite is an open source, cross-platform, multi-threaded benchmark tool for ev aluating system performance when running a database under a heavy load. Depending on the options used, SysBench can be used to test the following system components: ⢠File I/O performance ⢠Scheduler performance ⢠Memory allocation and transfer speed ⢠POSIX threads implementation performance ⢠Database serv er performance (OL TP benchmark) The results documented in this document w ere obtained using the SysBench test suite to generate results in an environment using the MySQL database serv er on Proliant systems (x86_64) running RHEL. T able 1 lists the softw are versions used for the benchmark tests. T able 1 T est Software Envir onment W ebsite Software http://sysbench.sourceforge.net/ SysBench version 0.4.8 N/A Red Hat Enterprise Linux 5 AS Update 1 (RHEL5 AS u1) MySQL database server version 5.0.46 (for an x86_64 system) This paper describes the results of ov erall database server performance based on the OL TP benchmark. Because this represents a transactional w orkload, the InnoDB storage engine was used for the MySQL database server . Four different serv ers from the HP ProLiant family were used for each of the OL TP tests. Each test was performed using read-only queries and then with read/write queries and a v aried number of connections. Additionally , each of the tests were performed when the MySQL data files were located on the local attached storage (SAS), as w ell as Fibre Channel attached Storage Area Network (SAN) on an HP StorageW orks 1500 Modular Smart Array (MSA1500). Har d w ar e T es t En v ir onment This benchmark was performed within an HP BladeSystem c-Class blade enclosure with enhanced backplane using C-GbE2 Network interconnect switches. T able 2 lists the specifications of the hardw are test environment. T able 2 T est Hardw are Envir onm ent BL68 5c BL480c BL46 5c BL46 0c Hardw are (2) Dual-Core AMD Opteron processor Model 8218 (2.6 GHz/1MB L2 cache per core) (2) Quad-Core Intel® Xeon® processor X5365 (3.0 GHz/4MB L2 cache per core) (2) Dual-Core AMD Opteron⢠processor 2218 (2.6 GHz/1MB L2 cache per core) (2) Quad-Core Intel® Xeon® processor X5365 (3.0 GHz/4MB L2 cache per core) CPU 12GB (6x2GB PC2-5300 DDR2) 24GB (12x2GB PC2-5300 DDR2) 14GB (6x2GB 2x1GB PC2-5300 DDR2) 16GB (8x2GB PC2-5300 DDR2) Memory 2 x 73GB SAS Smart Array E200i controller with Battery Backed W rite Cache (BBWC) and Fibre Channel attached MSA1500 4 x 73GB SAS Smart Array P400i controller with Battery Backed W rite Cache (BBWC) and Fibre Channel attached MSA1500 2 x 73GB SAS Smart Array E200i controller with Battery Backed W rite Cache (BBWC) and Fibre Channel attached MSA1500 2 x 73GB SAS Smart Array E200i controller with Battery Backed W rite Cache (BBWC) and Fibre Channel attached MSA1500 Disk Storage 12
T able 2 T est Hardw are Envir onm ent (contin ued) BL68 5c BL480c BL46 5c BL46 0c Hardw are (ext3 â no L VM): Disk0: /, /boot, sw ap Disk1: FC attach â 7 36GB disks 10k RPM, RAID 0 (ext3 â no L VM): Disk0: /, /boot, sw ap Disk1: FC attach â 7 36GB disks 10k RPM, RAID 0 (ext3 â no L VM): Disk0: /, /boot, sw ap Disk1: FC attach â 7 36GB disks 10k RPM, RAID 0 (ext3 â no L VM): Disk0: /, /boot, sw ap Disk1: FC attach â 7 36GB disks 10k RPM, RAID 0 File System Partitioning RHEL5 AS u1 SMP kernel (2.6.18-53.el5) with errata updates RHEL5 AS u1 SMP kernel (2.6.18-53.el5) with errata updates RHEL5 AS u1 SMP kernel (2.6.18-53.el5) with errata updates RHEL5 AS u1 SMP kernel (2.6.18-53.el5) with errata updates Operating System S y sBenc h Conf igur ation The OL TP test mode emulates real database usage by issuing common SQL queries on typical table structures. SysBench populates the following table with oltp-table-size rows at the beginning of the test. CREATE TABLE `sbtest` ( `id` int(10) unsigned NOT NULL auto_increment, `k` int(10) unsigned NOT NULL default '0', `c` char(120) NOT NULL default '', `pad` char(60) NOT NULL default '', PRIMARY KEY (`id`), KEY `k` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 T o test general purpose database performance, the complex test mode w as used. The InnoDB storage engine supports ACID compliant transactions; therefore, the SysBench uses BEGIN/COMMIT statements around each of the transactions. T able 3 lists the SQL queries used in testing. T able 3 SQL Queries SQL Query S yntax T ype of Quer y SELECT c FROM sbtest WHERE id=N P oint queries SELECT c FROM sbtest WHERE id BETWEEN N and M Range queries SELECT SUM(c) FROM sbtest WHERE id BETWEEN N AND M Range SUM() queries SELECT c FROM sbtest WHERE id BETWEEN N AND M ORDER BY c Ordered range queries SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N AND M ORDER BY c Distinct range queries UPDATE sbtest SET k=k 1 WHERE id=N Updates on indexed column UPDATE sbtest SET c=N WHERE id=N Updates on non-index column DELETE FROM sbtest WHERE id=N Delete queries INSERT INTO sbtest VALUES (...) Insert queries T able 4 lists the configuration options that were used for SysBench. SysBench Configuration 13
T able 4 S ysBench Options Description V alue S ysBench option OL TP emulates operations that a read database would perform. Other options are CPU, threads, mutex, memory , and fileio. oltp --test Sets the number of rows in the test table. 1000000 --oltp-table-size Sets the value for whether the storage engine used is transactional. yes --mysql-engine-trx Determines how transactions are performed. Options are simple (very simple SELECT s), complex (transactional), and nontrx (non-transactional). complex --oltp-test-mode Sets whether the UPDA TE/DELETE/INSERT queries are used. Both cases were run for these tests. on/off --oltp-read-only . Allows for the ditribution of random numbers special --oltp-dist-type Sets the total number of requests allowed. The v alue â0â indicates unlimited requests are allow ed. 0 --max-requests Sets the number of worker threads. varied from 1 to 128 --num-threads Initializes the random number generator . on --init-rng Sets the limit for total execution time in seconds. 180 --max-time M yS QL Installati on The MySQL Certified Softw are, downloaded from the MySQL Network, provides the mysql-enterprise-gpl-5.0.46-0.rhel5.x86_64.tar file for RHEL5 on x86_64 servers. T able 5 lists the components contained in the tar file. T able 5 MyS QL Installation P ac k ages Description RPM P ackage MySQL database server (mysqld, etc) MySQL-server-enterprise-gpl-5.0.46-0.rhel5.x86_64.rpm MySQL client programs (mysql, mysqladmin, etc) MySQL-client-enterprise-gpl-5.0.46-0.rhel5.x86_64.rpm MySQL header files and libraries (needed to build SysBench ) MySQL-devel-enterprise-gpl-5.0.46-0.rhel5.x86_64.rpm M yS QL Conf igur ation The example configuration file ( my-large.cnf) that is provided with the MySQL database server , w as used as the basis of the my.cnf test file. T able 6 lists the changes that were made to the mysqld configuration. T able 6 MyS QL T est Configur ation Description V alue my sqld Option Maximum number of connections. The default is 100. 1600 max_connections Number of interrupted connections from a host after which connections are blocked. 10 max_connect_errors Number of open tables for all threads. The default is 512. 2048 table_cache Buffer size for each thread that needs to sort results. Increase for faster ORDER BY and GROUP BY operations. 512K sort_buffer_size Size of cache to hold SQL statements for the binary log during a transaction. Increase for large, multiple-statement transactions. 1M binlog_cache_size 14
T able 6 MyS QL T est Configur ation (contin ued) Description V alue my sqld Option Buffer size for joins that do not use indices and require a full table scan. 1M join_buffer_size Number of server threads to cache for reuse. 16 thread_cache_size P er-thread stack size. The default is 192K. 192K thread_stack Amount of memory allocated for caching query results. The value â0â disables query cache. 0 query_cache_size Minimum length of the word to be included in a full-text index. 4 ft_min_word_len Maximum size of in-memory temp tables. Increase for GROUP BY . 64M tmp_table_size P ath to innodb data files and sizes. Increase size from 10MB to 100MB. ibdata1:100M\ :autoextend innodb_data_file_path Number of bytes of the memory buffer to cache data and indices. 1900M innodb_buffer_pool_size Number of bytes of memory pool used to store the data dictionary information and data structures. The default is 1M. 20M innodb_additional_mem_pool_size Size of each log file in log group. The default is 5MB. 900M innodb_log_file_size Determines how the log buffer is written to the disk. The value â2â means data is flushed on every commit, but no disk flush occurs. The default is 1. 2 innodb_flush_log_at_trx_commit Controls next-key locking in search and index scans. The default is 0 (disabled). 1 innodb_locks_unsafe_for_binlog Number of threads currently inside the innodb engine. The default varies with the MySQL version. 1000 innodb_thread_concurrency Number of 'free tickets' to allow re-entry into the innodb engine. 500 innodb_concurrency_tickets The size of buffer InnoDB used in the memory to write the log files. Ranges from 1MB to 8MB. Large log buffer saves disk I/O operations. 8M Innodb_log_buffer_size Specifies whether InnoDB locks a table internally . The default is 1 (enabled). 0 innodb_table_locks Specifies whether InnoDB supports XA transactions. The default is 1 (supported). 0 innodb_support_xa InnoDB stores all data twice by default. Disable it by using the --skip-innodb-doublewrite parameter when starting the MySQL server . skip-innodb-doublewrite innodb_doublewrite Checksum validation is used on all data pages by default. Disable it by using the --skip-innodb-checksums parameter when starting the MySQL server . skip-innodb-checksums innodb_checksums Benc hmar k R esults For each read-only or read/write test, SysBench was run with a varying number of thread connections to determine the maximum transactions per second (tps). The tests w ere conducted on both the internal storage with the battery backed write cache option (BBWC) and the Fibre Channel attached Storage Area Netw ork. Benchmark Results 15
Depending on whether the application requires many read/write queries, it may help to set the database connection pool to the number of processing cores. For read-only testing, all HP Proliant c-Class Blade Servers in the test can still handle almost the same number of transactions even though the number of concurrent threads is double the number of processing cores. Therefore, more database connections than the number of processing cores for read-only applications can be used. NO TE: For read/write testing, the maximum performance (shown in bold in the following result tables) was achieved when the number of concurrent threads was the same as the total number of processing cores in the system. Inte rpr eting the Data The results of the testing are presented in the following sections and show the maximum number of tps that each of the systems can handle, depending on the number of threads. The precise database query load and mix of queries that an application generates vary greatly between applications. Another important consideration in the benchmark testing w as how many concurrent connections the application had with the database server . The results in the data tables show that having more connections can negatively impact the throughput. If an application requires 1200 read/write tps from 16 database connections, all of the HP Proliant c-Class Blade Servers used in these tests are suitable because all of the test servers can handle at least 1245 tps. For read-only applications that require 2000 tps using eight database connections, both the HP ProLiant BL465c and BL685c servers, configured with 2 Dual-Core Opteron processors, are suitable having a capacity of 2500 tps. The ProLiant BL460c serv er and BL480c server , configured with 2 Quad-Core Xeon processors, can handle a maximum of 4500 read-only tps and 3000 read/write tps. For an application that requires more than 4500 read-only or more than 3000 read/write tps, consideration should be given to using MySQL Replication and spreading the load across multiple ProLiant servers. HP Pr oli ant BL4 60c Server The HP ProLiant BL460c serv er has features that are equal to standard 1U rack-mount servers. The tw o-processor , dual-core or quad-core ProLiant BL460c combines pow er-efficient compute power , and high density with expanded memory and I/O for maximum performance. The ProLiant c7000 enclosure supports up to 16 BL460c server blades, two more serv ers than the IBM BladeCenter , and each BL460c supports double the memory capacity of the HS21 server without an expansion blade. The Proliant BL460c now comes with dual-core and quad-core Intel® Xeon® processors, DDR2 fully buffered DIMMs, Serial Attached SCSI (SAS) or Serial A T A (SA T A), hard drives, and support of multi-function NICs and multiple I/O cards. The BL460c provides a performance system ideal for the full range of scale-out applications. In this small form factor , the BL460c includes more features to enable high-availability , such as hot plug hard drives, mirrored memory , online spare memory , memory interleaving, embedded RAID capability , and enhanced remote Lights-Out management. T able 7 provides the benchmark results that w ere obtained using a ProLiant BL460c server configured with two Quad-Core Intel® Xeon® processors running at 3.0GHz. NO TE: Maximum performance appears in bold. T able 7 S ysBench Results for t he HP ProL iant BL4 60c Ser ver Read Only (tps) Fibre Channel SAN Read/W rite (tps) Fibre Channel SAN Read Only (tps) Internal SAS Read/W rite (tps) Internal SAS Connections 809 610 794 624 1 1330 1029 1454 1057 2 16
T able 7 S ysBench Results for t he HP ProL iant BL4 60c Ser ver (con tinued) Read Only (tps) Fibre Channel SAN Read/W rite (tps) Fibre Channel SAN Read Only (tps) Internal SAS Read/W rite (tps) Internal SAS Connections 2593 1910 2615 1893 4 4297 3145 4301 3088 8 4353 2690 4374 2604 16 4271 2423 4148 2382 32 3990 2271 4006 2220 64 3722 1626 3641 1478 128 Figur e 1 Sy sBench Results for the HP ProLiant BL46 0c Server HP Pr oli ant BL4 6 5c Server The HP ProLiant BL465c serv er is a two-w ay server blade designed for maximum compute density and power efficiencies. The small form factor accommodates two blades in each slot allowing for 16 serv ers in a standard 6u enclosure. Each CPU is a 95 W att v ersion of the Dual-Core AMD Opteron 2000 series processor , which gives the equivalent of four processing units per server . Up to 32GB of PC2-5300 DIMMs running at 667MHz is supported. Internal storage consists of two small form factor disk drives, either SAS or SA T A attached to the integrated Smart Array E200i RAID controller with 64MB cache supporting RAID 0, 1. An optional StorageW orks SB40c storage blade provides connectivity to additional storage, supporting RAID 5, and ADG. The benchmark results in T able 8 were obtained with a ProLiant BL465c server configured with two Dual-Core Opteron processors running at 2.6 GHz. T able 8 S ysBench Results for t he HP ProL iant BL4 6 5c Server Read Only (tps) , Fibre Channel SAN Read/W rite (tps) , Fibr e Channel SAN Read Only (tps) , Internal SA S Read/W rite (tps) , Internal SAS Connections 746 514 664 497 1 1415 973 1340 931 2 2568 1692 2341 1665 4 2640 1523 2455 1467 8 2621 1298 2437 1245 16 2445 1048 2302 1014 32 Benchmark Results 17
T able 8 S ysBench Results for t he HP ProL iant BL4 6 5c Server (continued) Read Only (tps) , Fibre Channel SAN Read/W rite (tps) , Fibr e Channel SAN Read Only (tps) , Internal SA S Read/W rite (tps) , Internal SAS Connections 2148 911 2097 885 64 2049 630 1962 621 128 Figur e 2 Sy sBench Results for the HP ProLiant BL46 5c Ser v er HP Pr oli ant BL4 80c Server The HP ProLiant BL480c serv er is a 2-way server blade designed for maximum performance and scalability . The ProLiant BL480c server can support two Quad-Core Inter Xeon processors, which gives the equiv alent of eight processing units per serv er . Up to 48GB (PC2-5300 DDR2, 667 MHz) of memory is supported. Each full-height blade has a Smart Array P400i Controller that supports four hot-sw ap SCSI disks in a RAID configuration. An optional dual-port Fiber Channel adapter provides connectivity to HP and third-party storage area networks (SANs). The benchmark results in T able 9 were obtained with a BL480c serv er configured with two Quad-Core Inter Xeon processors running at 3.0 GHz. T able 9 S ysBench Results for t he HP ProL iant BL480c Ser v er Read Only (tps) , Fibre Channel SAN Read/W rite (tps) , Fibr e Channel SAN Read Only (tps) , Internal SA S Read/W rite (tps) , Internal SAS Connections 944 650 817 628 1 1573 1070 1494 1060 2 2987 1951 2939 1922 4 4588 3177 4544 3125 8 4682 2655 4635 2663 16 4551 2435 4533 2411 32 4333 2251 4260 2261 64 3986 1634 3926 1604 128 18
Figur e 3 Sy sBench Results for the HP ProLiant BL480c Server HP Pr oli ant BL6 8 5c Server The HP ProLiant BL685c serv er blade delivers no-compromise performance and expansion in the densest four processor server blade form factor av ailable. With up to four AMD Opteron⢠8000 Series processors, 64GB of DDR2 memory , two hot-plug Serial Attached SCSI (SAS) or Serial A T A (SA T A) hard-drives, four embedded Gigabit NICs, and three I/O expansion slots, the HP ProLiant BL685c delivers the density y ou w ant with the performance you need to handle the most demanding enterprise class applications. Each blade server has a Smart Array E200i Controller that supports two hot-sw ap SCSI disks in a RAID configuration. Optional dual-port Fiber Channel adapter provides connectivity to HP and third-party storage area networks (SANs). The benchmark results are listed in T able 10 , and were obtained using a ProLiant BL685c server configured with two Dual-Core Opteron processors running at 2.6 GHz. T able 1 0 Sy sBench Results for the HP ProLiant BL68 5c Server Read Only (tps) , Fibre Channel SAN Read/W rite (tps) , Fibr e Channel SAN Read Only (tps) , Internal SA S Read/W rite (tps) , Internal SAS Connections 664 497 670 470 1 1323 984 1307 951 2 2426 1770 2327 1650 4 2427 1655 2419 1540 8 2413 1548 2400 1463 16 2347 1468 2312 1385 32 2139 1319 2094 1247 64 1975 1050 1971 1034 128 Benchmark Results 19
Figur e 4 Sy sBench Results for the HP ProLiant BL68 5c Server Summary The results of the SysBench testing indicated that the HP Proliant c-Class Blade Servers provide a solid platform for the MySQL database server . Along with the integrated Smart Array controller these serv ers provide the security of RAID storage to the host operating system and database files, while also offering excellent performance for many applications. The results of the benchmarks tests are provided in Figure 5 and indicate that the HP ProLiant BL465c serv er is a good choice for an entry-level database server with scalability to SAN, as the size of the database grows. When higher performance levels are required from a single serv er , both the HP ProLiant BL460c and HP ProLiant BL480c serv ers (when configured with tw o Quad-Core processors) provided a good option for scalability . Figur e 5 B enchmark T est Results The test results indicated that optimum price and performance could be achiev ed using a MySQL Replication configuration with a pair of HP ProLiant BL460c or BL480c servers with 8 cores in a high-av ailability cluster where HP Proliant c-Class Blade Serv ers acted as the MySQL master database serv er . This, combined with a number of ProLiant BL465c serv ers acting as the MySQL slave database serv ers, provided a robust, scalable database environment within a single HP BladeSystem enclosure. R es our ces For additional information on the hardware and softw are used in the MySQL sizing tests, see the following websites: HP and MySQL http://h71028.www7.hp.com/enterprise/cache/77721-0-0-0-121.html 20
MySQL http://www .mysql.com HP BladeSystem http://www .hp.com/go/bladesystem/ http://h18004.www1.hp.com/products/blades/components/bladeserv ers.html HP Activ eAnswers for MySQL Database Server http://h71019.www7.hp.com/Activ eAnswers/cache/81882-0-0-0-121.html SysBench Benchmark Software http://sysbench.sourceforge.net/ Resources 21
© Copyright 2008 Hewlett-P ackard Development Company , L.P . Legal Notice Confidential computer software. V alid license from HP required for possession, use or copying. Consistent with F AR 12.211 and 12.212, Commercial Computer Softw are, Computer Software Documentation, and T echnical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice. The only w arranties for HP products and services are set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty . HP shall not be liable for technical or editorial errors or omissions contained herein. Acknowledgments Intel and Itanium are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries. RED HA T READY Logo and RED HA T CERTIFIED P ARTNER Logo are trademarks of Red Hat, Inc.
T able of C ont ents Introduction .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. 9 Executive Summary. .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . 9 Intended Audience.. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 9 Scope and Purpose.. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. 9 HP Services.. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .9 T ypographic Conventions. .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. 11 Publishing History. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 11 HP Encourages Y our Comments. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . 11 Benchmark Software.. .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .12 Hardw are T est Environment. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. 12 SysBench Configuration.. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. 13 MySQL Installation. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .14 MySQL Configuration.. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . 14 Benchmark Results. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. 15 Interpreting the Data.. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. 16 HP Proliant BL460c Server.. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 16 HP Proliant BL465c Server.. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 17 HP Proliant BL480c Server.. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 18 HP Proliant BL685c Server.. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 19 Summary .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 20 Resources .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .20 T able of Contents 3
4
L ist of F igur es 1 SysBench Results for the HP ProLiant BL460c Server.. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . 17 2 SysBench Results for the HP ProLiant BL465c Server.. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . 18 3 SysBench Results for the HP ProLiant BL480c Server .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .19 4 SysBench Results for the HP ProLiant BL685c Server.. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . 20 5 Benchmark T est Results. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .20 5
6
L ist of T ables 1 T est Softw are Environment.. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. 12 2 T est Hardware Environment.. .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . 12 3 SQL Queries. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. 13 4 SysBench Options.. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . 14 5 MySQL Installation P ackages. .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . 14 6 MySQL T est Configuration . . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. 14 7 SysBench Results for the HP ProLiant BL460c Server.. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . 16 8 SysBench Results for the HP ProLiant BL465c Server.. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . 17 9 SysBench Results for the HP ProLiant BL480c Server .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .18 10 SysBench Results for the HP ProLiant BL685c Server.. .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . .. .. . .. . .. . .. .. . .. . 19 7
8
Intr oducti on HP Open Source Middleware Stacks (OSMS) offer building block applications, such as the W eb Server; technical blueprints, and documents such as this MySQL Sizing Guide which describes the maximum MySQL workload that HP ProLiant c-Class Blade Servers are capable of supporting; and consulting services to speed the successful implementation of an open source strategy . Ex ec uti ve Summar y The HP Open Source Integrated Portfolio (HP OSIP) comprises a range of products and services designed to verify that customers can successfully realize the cost and feature benefits of adopting open source softw are in their IT environments. HP Open Source foundation components include the base components of an open source-based ecosystem. HP serv ers and storage are v alidated to run the Linux operating system together with the supported commercial Linux distribution. This results published in this document w ere achiev ed using the SysBench benchmark for each of the HP ProLiant c-Class Blade Servers. V arious test configurations and test environments were used and documented to provide readers an opportunity to understand the testing methodology . Inte nded Audi ence The intended audience for this document is anyone who is interested in determining the number of database workload that can be supported on a given HP ProLiant c-Class Blade Server and details for deploying a hardw are environment based on the database requirements of certain applications. Scope and P urpos e This document presents the results of a series of benchmark tests performed using the SysBench softw are test suite. The tests were conducted running a MySQL database serv er to evaluate system performance when running a database under a heavy load. The following HP ProLiant servers were used in this test: BL460c, BL480c, BL465c, and BL685c. The benchmark data provided in this sizing guide can assist customers in determining which HP Proliant c-Class Blade Server meets their MySQL database requirements along with how-to information for configuring the HP Proliant c-Class Blade Serv er and storage in a specific production environment. HP provides quality assurance from extensiv e integration testing with open source software and HP hardw are so that you can confidently deploy the complete stack. Once y ou hav e completed a successful evaluation, you have the flexibility to âdo it yourself â or get assistance from HP to incorporate open source stacks into your existing IT infrastructure. HP Serv ice s HP Open Source Consulting Services can help you build and integrate open source and commercial softw are across multiple operating system (OS) environments. Additionally , HP Open Source Support Services provide industry leading technical support for all the products HP sells, including hardw are, operating systems, and open source middleware. T o learn more about HP Open Source Consulting and Support Services, contact your local HP sales representativ e or visit the HP Business and IT Services w ebsite at: http://www .hp.com/hps For the location of the nearest sales office, call: ⢠In the United States: 1 800 637 7740 ⢠In Canada: 1 905 206 4725 ⢠In Japan: 81 3 3331 6111 ⢠In Latin America: 1 305 267 4220 ⢠In Australia/New Zealand: 61 3 9272 2895 Introduction 9
⢠In Asia Pacific: 8522 599 7777 ⢠In Europe/Africa/Middle East: 41 22 780 81 11 10
T y pogr aphi c Con ve ntio ns This document uses the following typographic conv entions. Command A command name or qualified command phrase. ComputerOut T ext displayed by the computer . Ctrl-x A key sequence. A sequence such as Ctrl-x indicates that you must hold down the key labeled Ctrl while y ou press another key or button. ENVIRONVAR The name of an environment v ariable, for example, PATH. [ERRORNAME] The name of an error , usually returned in the errno v ariable. Ke y The name of a keyboard key . Return and Enter both refer to the same key . T erm The defined use of an important word or phrase. UserInput Commands and other text that you type. VARIABLE The name of a placeholder in a command, function, or other syntax display that you replace with an actual value. \ (continuation character) A backslash (\) at the end of a line of code (such as a command) indicates that the following line of code is contiguous, and you must not insert a line break. This convention facilitates the typesetting of long lines of code examples on a printed page. If you cut and paste sample code from this publication, ensure that you remove backslash characters at line endings. ... The preceding element can be repeated an arbitrary number of times. | Separates items in a list of choices. P ublishing Hist ory The document publishing date and part number indicate the current edition of the document. The publishing date changes when a new edition is printed. Minor changes might be made without changing the publishing date. The document part number changes only when extensive changes are made. Document updates might be issued between editions to correct errors or document product changes. For the latest version of this document online, see the HP T echnical Documentation website at: http://www .docs.hp.com Publication Date Edition Number Supported OS Manufacturing P art Number March 2008 1 Red Hat Enterprise Linux 5 AS Update 1 (RHEL5ASu1) 5992-4755 HP Encour ages Y our Co mments HP encourages your comments concerning this document. W e are committed to providing documentation that meets your needs. Send any errors found, suggestions for improv ement, or compliments to: feedback@fc.hp.com Include the document title, manufacturing part number , and any comment, error found, or suggestion for improvement you hav e concerning this document. Introduction 11
Benc hmar k So ft w ar e The SysBench test suite is an open source, cross-platform, multi-threaded benchmark tool for ev aluating system performance when running a database under a heavy load. Depending on the options used, SysBench can be used to test the following system components: ⢠File I/O performance ⢠Scheduler performance ⢠Memory allocation and transfer speed ⢠POSIX threads implementation performance ⢠Database serv er performance (OL TP benchmark) The results documented in this document w ere obtained using the SysBench test suite to generate results in an environment using the MySQL database serv er on Proliant systems (x86_64) running RHEL. T able 1 lists the softw are versions used for the benchmark tests. T able 1 T est Software Envir onment W ebsite Software http://sysbench.sourceforge.net/ SysBench version 0.4.8 N/A Red Hat Enterprise Linux 5 AS Update 1 (RHEL5 AS u1) MySQL database server version 5.0.46 (for an x86_64 system) This paper describes the results of ov erall database server performance based on the OL TP benchmark. Because this represents a transactional w orkload, the InnoDB storage engine was used for the MySQL database server . Four different serv ers from the HP ProLiant family were used for each of the OL TP tests. Each test was performed using read-only queries and then with read/write queries and a v aried number of connections. Additionally , each of the tests were performed when the MySQL data files were located on the local attached storage (SAS), as w ell as Fibre Channel attached Storage Area Network (SAN) on an HP StorageW orks 1500 Modular Smart Array (MSA1500). Har d w ar e T es t En v ir onment This benchmark was performed within an HP BladeSystem c-Class blade enclosure with enhanced backplane using C-GbE2 Network interconnect switches. T able 2 lists the specifications of the hardw are test environment. T able 2 T est Hardw are Envir onm ent BL68 5c BL480c BL46 5c BL46 0c Hardw are (2) Dual-Core AMD Opteron processor Model 8218 (2.6 GHz/1MB L2 cache per core) (2) Quad-Core Intel® Xeon® processor X5365 (3.0 GHz/4MB L2 cache per core) (2) Dual-Core AMD Opteron⢠processor 2218 (2.6 GHz/1MB L2 cache per core) (2) Quad-Core Intel® Xeon® processor X5365 (3.0 GHz/4MB L2 cache per core) CPU 12GB (6x2GB PC2-5300 DDR2) 24GB (12x2GB PC2-5300 DDR2) 14GB (6x2GB 2x1GB PC2-5300 DDR2) 16GB (8x2GB PC2-5300 DDR2) Memory 2 x 73GB SAS Smart Array E200i controller with Battery Backed W rite Cache (BBWC) and Fibre Channel attached MSA1500 4 x 73GB SAS Smart Array P400i controller with Battery Backed W rite Cache (BBWC) and Fibre Channel attached MSA1500 2 x 73GB SAS Smart Array E200i controller with Battery Backed W rite Cache (BBWC) and Fibre Channel attached MSA1500 2 x 73GB SAS Smart Array E200i controller with Battery Backed W rite Cache (BBWC) and Fibre Channel attached MSA1500 Disk Storage 12
T able 2 T est Hardw are Envir onm ent (contin ued) BL68 5c BL480c BL46 5c BL46 0c Hardw are (ext3 â no L VM): Disk0: /, /boot, sw ap Disk1: FC attach â 7 36GB disks 10k RPM, RAID 0 (ext3 â no L VM): Disk0: /, /boot, sw ap Disk1: FC attach â 7 36GB disks 10k RPM, RAID 0 (ext3 â no L VM): Disk0: /, /boot, sw ap Disk1: FC attach â 7 36GB disks 10k RPM, RAID 0 (ext3 â no L VM): Disk0: /, /boot, sw ap Disk1: FC attach â 7 36GB disks 10k RPM, RAID 0 File System Partitioning RHEL5 AS u1 SMP kernel (2.6.18-53.el5) with errata updates RHEL5 AS u1 SMP kernel (2.6.18-53.el5) with errata updates RHEL5 AS u1 SMP kernel (2.6.18-53.el5) with errata updates RHEL5 AS u1 SMP kernel (2.6.18-53.el5) with errata updates Operating System S y sBenc h Conf igur ation The OL TP test mode emulates real database usage by issuing common SQL queries on typical table structures. SysBench populates the following table with oltp-table-size rows at the beginning of the test. CREATE TABLE `sbtest` ( `id` int(10) unsigned NOT NULL auto_increment, `k` int(10) unsigned NOT NULL default '0', `c` char(120) NOT NULL default '', `pad` char(60) NOT NULL default '', PRIMARY KEY (`id`), KEY `k` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 T o test general purpose database performance, the complex test mode w as used. The InnoDB storage engine supports ACID compliant transactions; therefore, the SysBench uses BEGIN/COMMIT statements around each of the transactions. T able 3 lists the SQL queries used in testing. T able 3 SQL Queries SQL Query S yntax T ype of Quer y SELECT c FROM sbtest WHERE id=N P oint queries SELECT c FROM sbtest WHERE id BETWEEN N and M Range queries SELECT SUM(c) FROM sbtest WHERE id BETWEEN N AND M Range SUM() queries SELECT c FROM sbtest WHERE id BETWEEN N AND M ORDER BY c Ordered range queries SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N AND M ORDER BY c Distinct range queries UPDATE sbtest SET k=k 1 WHERE id=N Updates on indexed column UPDATE sbtest SET c=N WHERE id=N Updates on non-index column DELETE FROM sbtest WHERE id=N Delete queries INSERT INTO sbtest VALUES (...) Insert queries T able 4 lists the configuration options that were used for SysBench. SysBench Configuration 13
T able 4 S ysBench Options Description V alue S ysBench option OL TP emulates operations that a read database would perform. Other options are CPU, threads, mutex, memory , and fileio. oltp --test Sets the number of rows in the test table. 1000000 --oltp-table-size Sets the value for whether the storage engine used is transactional. yes --mysql-engine-trx Determines how transactions are performed. Options are simple (very simple SELECT s), complex (transactional), and nontrx (non-transactional). complex --oltp-test-mode Sets whether the UPDA TE/DELETE/INSERT queries are used. Both cases were run for these tests. on/off --oltp-read-only . Allows for the ditribution of random numbers special --oltp-dist-type Sets the total number of requests allowed. The v alue â0â indicates unlimited requests are allow ed. 0 --max-requests Sets the number of worker threads. varied from 1 to 128 --num-threads Initializes the random number generator . on --init-rng Sets the limit for total execution time in seconds. 180 --max-time M yS QL Installati on The MySQL Certified Softw are, downloaded from the MySQL Network, provides the mysql-enterprise-gpl-5.0.46-0.rhel5.x86_64.tar file for RHEL5 on x86_64 servers. T able 5 lists the components contained in the tar file. T able 5 MyS QL Installation P ac k ages Description RPM P ackage MySQL database server (mysqld, etc) MySQL-server-enterprise-gpl-5.0.46-0.rhel5.x86_64.rpm MySQL client programs (mysql, mysqladmin, etc) MySQL-client-enterprise-gpl-5.0.46-0.rhel5.x86_64.rpm MySQL header files and libraries (needed to build SysBench ) MySQL-devel-enterprise-gpl-5.0.46-0.rhel5.x86_64.rpm M yS QL Conf igur ation The example configuration file ( my-large.cnf) that is provided with the MySQL database server , w as used as the basis of the my.cnf test file. T able 6 lists the changes that were made to the mysqld configuration. T able 6 MyS QL T est Configur ation Description V alue my sqld Option Maximum number of connections. The default is 100. 1600 max_connections Number of interrupted connections from a host after which connections are blocked. 10 max_connect_errors Number of open tables for all threads. The default is 512. 2048 table_cache Buffer size for each thread that needs to sort results. Increase for faster ORDER BY and GROUP BY operations. 512K sort_buffer_size Size of cache to hold SQL statements for the binary log during a transaction. Increase for large, multiple-statement transactions. 1M binlog_cache_size 14
T able 6 MyS QL T est Configur ation (contin ued) Description V alue my sqld Option Buffer size for joins that do not use indices and require a full table scan. 1M join_buffer_size Number of server threads to cache for reuse. 16 thread_cache_size P er-thread stack size. The default is 192K. 192K thread_stack Amount of memory allocated for caching query results. The value â0â disables query cache. 0 query_cache_size Minimum length of the word to be included in a full-text index. 4 ft_min_word_len Maximum size of in-memory temp tables. Increase for GROUP BY . 64M tmp_table_size P ath to innodb data files and sizes. Increase size from 10MB to 100MB. ibdata1:100M\ :autoextend innodb_data_file_path Number of bytes of the memory buffer to cache data and indices. 1900M innodb_buffer_pool_size Number of bytes of memory pool used to store the data dictionary information and data structures. The default is 1M. 20M innodb_additional_mem_pool_size Size of each log file in log group. The default is 5MB. 900M innodb_log_file_size Determines how the log buffer is written to the disk. The value â2â means data is flushed on every commit, but no disk flush occurs. The default is 1. 2 innodb_flush_log_at_trx_commit Controls next-key locking in search and index scans. The default is 0 (disabled). 1 innodb_locks_unsafe_for_binlog Number of threads currently inside the innodb engine. The default varies with the MySQL version. 1000 innodb_thread_concurrency Number of 'free tickets' to allow re-entry into the innodb engine. 500 innodb_concurrency_tickets The size of buffer InnoDB used in the memory to write the log files. Ranges from 1MB to 8MB. Large log buffer saves disk I/O operations. 8M Innodb_log_buffer_size Specifies whether InnoDB locks a table internally . The default is 1 (enabled). 0 innodb_table_locks Specifies whether InnoDB supports XA transactions. The default is 1 (supported). 0 innodb_support_xa InnoDB stores all data twice by default. Disable it by using the --skip-innodb-doublewrite parameter when starting the MySQL server . skip-innodb-doublewrite innodb_doublewrite Checksum validation is used on all data pages by default. Disable it by using the --skip-innodb-checksums parameter when starting the MySQL server . skip-innodb-checksums innodb_checksums Benc hmar k R esults For each read-only or read/write test, SysBench was run with a varying number of thread connections to determine the maximum transactions per second (tps). The tests w ere conducted on both the internal storage with the battery backed write cache option (BBWC) and the Fibre Channel attached Storage Area Netw ork. Benchmark Results 15
Depending on whether the application requires many read/write queries, it may help to set the database connection pool to the number of processing cores. For read-only testing, all HP Proliant c-Class Blade Servers in the test can still handle almost the same number of transactions even though the number of concurrent threads is double the number of processing cores. Therefore, more database connections than the number of processing cores for read-only applications can be used. NO TE: For read/write testing, the maximum performance (shown in bold in the following result tables) was achieved when the number of concurrent threads was the same as the total number of processing cores in the system. Inte rpr eting the Data The results of the testing are presented in the following sections and show the maximum number of tps that each of the systems can handle, depending on the number of threads. The precise database query load and mix of queries that an application generates vary greatly between applications. Another important consideration in the benchmark testing w as how many concurrent connections the application had with the database server . The results in the data tables show that having more connections can negatively impact the throughput. If an application requires 1200 read/write tps from 16 database connections, all of the HP Proliant c-Class Blade Servers used in these tests are suitable because all of the test servers can handle at least 1245 tps. For read-only applications that require 2000 tps using eight database connections, both the HP ProLiant BL465c and BL685c servers, configured with 2 Dual-Core Opteron processors, are suitable having a capacity of 2500 tps. The ProLiant BL460c serv er and BL480c server , configured with 2 Quad-Core Xeon processors, can handle a maximum of 4500 read-only tps and 3000 read/write tps. For an application that requires more than 4500 read-only or more than 3000 read/write tps, consideration should be given to using MySQL Replication and spreading the load across multiple ProLiant servers. HP Pr oli ant BL4 60c Server The HP ProLiant BL460c serv er has features that are equal to standard 1U rack-mount servers. The tw o-processor , dual-core or quad-core ProLiant BL460c combines pow er-efficient compute power , and high density with expanded memory and I/O for maximum performance. The ProLiant c7000 enclosure supports up to 16 BL460c server blades, two more serv ers than the IBM BladeCenter , and each BL460c supports double the memory capacity of the HS21 server without an expansion blade. The Proliant BL460c now comes with dual-core and quad-core Intel® Xeon® processors, DDR2 fully buffered DIMMs, Serial Attached SCSI (SAS) or Serial A T A (SA T A), hard drives, and support of multi-function NICs and multiple I/O cards. The BL460c provides a performance system ideal for the full range of scale-out applications. In this small form factor , the BL460c includes more features to enable high-availability , such as hot plug hard drives, mirrored memory , online spare memory , memory interleaving, embedded RAID capability , and enhanced remote Lights-Out management. T able 7 provides the benchmark results that w ere obtained using a ProLiant BL460c server configured with two Quad-Core Intel® Xeon® processors running at 3.0GHz. NO TE: Maximum performance appears in bold. T able 7 S ysBench Results for t he HP ProL iant BL4 60c Ser ver Read Only (tps) Fibre Channel SAN Read/W rite (tps) Fibre Channel SAN Read Only (tps) Internal SAS Read/W rite (tps) Internal SAS Connections 809 610 794 624 1 1330 1029 1454 1057 2 16
T able 7 S ysBench Results for t he HP ProL iant BL4 60c Ser ver (con tinued) Read Only (tps) Fibre Channel SAN Read/W rite (tps) Fibre Channel SAN Read Only (tps) Internal SAS Read/W rite (tps) Internal SAS Connections 2593 1910 2615 1893 4 4297 3145 4301 3088 8 4353 2690 4374 2604 16 4271 2423 4148 2382 32 3990 2271 4006 2220 64 3722 1626 3641 1478 128 Figur e 1 Sy sBench Results for the HP ProLiant BL46 0c Server HP Pr oli ant BL4 6 5c Server The HP ProLiant BL465c serv er is a two-w ay server blade designed for maximum compute density and power efficiencies. The small form factor accommodates two blades in each slot allowing for 16 serv ers in a standard 6u enclosure. Each CPU is a 95 W att v ersion of the Dual-Core AMD Opteron 2000 series processor , which gives the equivalent of four processing units per server . Up to 32GB of PC2-5300 DIMMs running at 667MHz is supported. Internal storage consists of two small form factor disk drives, either SAS or SA T A attached to the integrated Smart Array E200i RAID controller with 64MB cache supporting RAID 0, 1. An optional StorageW orks SB40c storage blade provides connectivity to additional storage, supporting RAID 5, and ADG. The benchmark results in T able 8 were obtained with a ProLiant BL465c server configured with two Dual-Core Opteron processors running at 2.6 GHz. T able 8 S ysBench Results for t he HP ProL iant BL4 6 5c Server Read Only (tps) , Fibre Channel SAN Read/W rite (tps) , Fibr e Channel SAN Read Only (tps) , Internal SA S Read/W rite (tps) , Internal SAS Connections 746 514 664 497 1 1415 973 1340 931 2 2568 1692 2341 1665 4 2640 1523 2455 1467 8 2621 1298 2437 1245 16 2445 1048 2302 1014 32 Benchmark Results 17
T able 8 S ysBench Results for t he HP ProL iant BL4 6 5c Server (continued) Read Only (tps) , Fibre Channel SAN Read/W rite (tps) , Fibr e Channel SAN Read Only (tps) , Internal SA S Read/W rite (tps) , Internal SAS Connections 2148 911 2097 885 64 2049 630 1962 621 128 Figur e 2 Sy sBench Results for the HP ProLiant BL46 5c Ser v er HP Pr oli ant BL4 80c Server The HP ProLiant BL480c serv er is a 2-way server blade designed for maximum performance and scalability . The ProLiant BL480c server can support two Quad-Core Inter Xeon processors, which gives the equiv alent of eight processing units per serv er . Up to 48GB (PC2-5300 DDR2, 667 MHz) of memory is supported. Each full-height blade has a Smart Array P400i Controller that supports four hot-sw ap SCSI disks in a RAID configuration. An optional dual-port Fiber Channel adapter provides connectivity to HP and third-party storage area networks (SANs). The benchmark results in T able 9 were obtained with a BL480c serv er configured with two Quad-Core Inter Xeon processors running at 3.0 GHz. T able 9 S ysBench Results for t he HP ProL iant BL480c Ser v er Read Only (tps) , Fibre Channel SAN Read/W rite (tps) , Fibr e Channel SAN Read Only (tps) , Internal SA S Read/W rite (tps) , Internal SAS Connections 944 650 817 628 1 1573 1070 1494 1060 2 2987 1951 2939 1922 4 4588 3177 4544 3125 8 4682 2655 4635 2663 16 4551 2435 4533 2411 32 4333 2251 4260 2261 64 3986 1634 3926 1604 128 18
Figur e 3 Sy sBench Results for the HP ProLiant BL480c Server HP Pr oli ant BL6 8 5c Server The HP ProLiant BL685c serv er blade delivers no-compromise performance and expansion in the densest four processor server blade form factor av ailable. With up to four AMD Opteron⢠8000 Series processors, 64GB of DDR2 memory , two hot-plug Serial Attached SCSI (SAS) or Serial A T A (SA T A) hard-drives, four embedded Gigabit NICs, and three I/O expansion slots, the HP ProLiant BL685c delivers the density y ou w ant with the performance you need to handle the most demanding enterprise class applications. Each blade server has a Smart Array E200i Controller that supports two hot-sw ap SCSI disks in a RAID configuration. Optional dual-port Fiber Channel adapter provides connectivity to HP and third-party storage area networks (SANs). The benchmark results are listed in T able 10 , and were obtained using a ProLiant BL685c server configured with two Dual-Core Opteron processors running at 2.6 GHz. T able 1 0 Sy sBench Results for the HP ProLiant BL68 5c Server Read Only (tps) , Fibre Channel SAN Read/W rite (tps) , Fibr e Channel SAN Read Only (tps) , Internal SA S Read/W rite (tps) , Internal SAS Connections 664 497 670 470 1 1323 984 1307 951 2 2426 1770 2327 1650 4 2427 1655 2419 1540 8 2413 1548 2400 1463 16 2347 1468 2312 1385 32 2139 1319 2094 1247 64 1975 1050 1971 1034 128 Benchmark Results 19
Figur e 4 Sy sBench Results for the HP ProLiant BL68 5c Server Summary The results of the SysBench testing indicated that the HP Proliant c-Class Blade Servers provide a solid platform for the MySQL database server . Along with the integrated Smart Array controller these serv ers provide the security of RAID storage to the host operating system and database files, while also offering excellent performance for many applications. The results of the benchmarks tests are provided in Figure 5 and indicate that the HP ProLiant BL465c serv er is a good choice for an entry-level database server with scalability to SAN, as the size of the database grows. When higher performance levels are required from a single serv er , both the HP ProLiant BL460c and HP ProLiant BL480c serv ers (when configured with tw o Quad-Core processors) provided a good option for scalability . Figur e 5 B enchmark T est Results The test results indicated that optimum price and performance could be achiev ed using a MySQL Replication configuration with a pair of HP ProLiant BL460c or BL480c servers with 8 cores in a high-av ailability cluster where HP Proliant c-Class Blade Serv ers acted as the MySQL master database serv er . This, combined with a number of ProLiant BL465c serv ers acting as the MySQL slave database serv ers, provided a robust, scalable database environment within a single HP BladeSystem enclosure. R es our ces For additional information on the hardware and softw are used in the MySQL sizing tests, see the following websites: HP and MySQL http://h71028.www7.hp.com/enterprise/cache/77721-0-0-0-121.html 20
MySQL http://www .mysql.com HP BladeSystem http://www .hp.com/go/bladesystem/ http://h18004.www1.hp.com/products/blades/components/bladeserv ers.html HP Activ eAnswers for MySQL Database Server http://h71019.www7.hp.com/Activ eAnswers/cache/81882-0-0-0-121.html SysBench Benchmark Software http://sysbench.sourceforge.net/ Resources 21