Share My Idea from life

Benchmark MySQL database

ipstor -

Installing Windows Server 2008 R2 Enterprise Edition
We used Microsoft Windows Server 2008 R2 Enterprise Edition for the operating system on the server.
To install Windows Server 2008 R2, complete the following steps:

  1. Boot the server, and insert the Windows Server 2008 R2 installation DVD in the DVD-ROM drive.
  2. At the Language Selection screen, click Next.
  3. Click Install Now.
  4. Select Windows Server 2008 Enterprise (Full Installation), and click Next.
  5. Click the I accept the license terms check box, and click Next.
  6. Click Custom.
  7. Click Drive options (advanced).
  8. Delete any existing partitions.
  9. Ensure the first drive is selected, and click New.
  10. Click Apply.
  11. Click OK.
  12. Click Next.
  13. At the User’s password must be changed before logging on warning screen, click OK.
  14. Type your new password into both fields, and click the arrow to continue.
  15. At the Your password has been changed screen, click OK.
    Windows Server 2008 R2 settings
    We installed all recommended and critical Windows updates through 2/03/2011. In addition, we
    downloaded and installed the latest drivers.

Installing and setting up MySQL Server 5.1.54
We used MySQL Server 5.1.54 to create our test database. To install and set up MySQL Server 5.1.54, complete the following steps:

  1. Download mysql-essential-5.1.54-winx64.msi from http://dev.mysql.com/downloads/.
  2. Double-click the mysql-essential-5.1.54-winx64.msi file to launch the MySQL Server 5.1 Setup Wizard.
    Click Next.
  3. Select Custom, and click Next.
  4. Highlight MySQL Server Datafiles, and click Change…
  5. Set the MySQL Server Datafiles directory as the RAID volume F:\, and click OK
  6. Ensure that the Destination Folder is C:\Program Files\MySQL\MySQL Server 5.1 and the Date Folder is
    F:\
  7. and click Install.
  8. Click Next.
  9. Click Next.
  10. At the Wizard Completed screen, click Finish. The MySQL Server Instance Configuration Wizard automatically launches.

Configuring MySQL Server 5.1.54
After you have installed and set up MySQL Server 5.1.54, configure it by completing the following
steps:

  1. At the Welcome to MySQL Server Instance Configuration Wizard 1.0.16.0 screen, click Next.
  2. At the MySQL Server Instance Configuration screen, select Reconfigure Instance, and click Next.
  3. Select Detailed Configuration, and click Next.
  4. Select Dedicated MySQL Server Machine, and click Next.
  5. Select Transactional Database Only, and click Next.
  6. To change the InnoDB Tablespace Settings RAID volume, click Modify, and in at the Are you sure you
    want to change the InnoDB datafile path? pop-up window, click Yes.
  7. Click the … button, select the RAID volume target, and click Open.
  8. Note that the drive drop-down menu under InnoDB Tablespace Settings changed to F:\, and click Next.
  9. Select Online Transaction Processing (OLTP), and click Next.
  10. Select Enable TCP/IP Networking, type 3306 for the Port Number, select Enable Strict Mode, and click
    Next.
  11. Leave the default Standard Character Set, and click Next.
  12. Select Install As Windows Service and Include Bin Directory in Windows PATH, and click Next.
  13. Select Modify Security Setting, type password1 in the New root password and Confirm boxes, and
    check Enable root access from remote machines. Click Next.
  14. To complete the configuration, click Execute.
  15. When the wizard finishes, click Finish.
  16. Open MySQL Command Line Client.
  17. At the Enter password prompt, type password1 and press Enter.
  18. Type show databases and press Enter.
  19. Type create database sbtest and press Enter.
  20. Type show databases and press Enter.
  21. Type use sbtest to switch MySQL to use the SysBench (sbtest) database, and press Enter.
  22. Type \q to close the MySQL Command Line Client.

Building the SysBench test database
We used SysBench to create a 400,000,000-row (90GB) MySQL database and executed a batch of
online transaction processing (OLTP) transactions against that data. Each run lasted one hour, performing 74
percent reads and 26 percent writes to simulate a standard database workload. To build the SysBench
database, complete the following steps:

  1. Run the SysBench “prepare” phase to build a 90GB database using the following parameters:
    sysbench –debug=off –mysql-host=localhost –mysql-user=root -mysql-password=Password1 –test=oltp –oltp-table-size=400000000 prepare
  2. Make a backup copy of the database on an external storage volume before running SysBench.

Increasing database performance using the Dell PERC H700 with CacheCade technology

Running SysBench

  1. Ensure you have a fresh copy of the database copied over to the target volume.
  2. To run the benchmark, open a command prompt, and type the following command:
    sysbench –debug=off –mysql-host=localhost –mysql-user=root -mysql-password=Password1 –test=oltp –oltp-table-size=400000000 -num-threads=10 –max-time=3600 –max-requests=10000000 run
  3. After the benchmark completes, refer to the command prompt window to review the results.
  4. Repeat steps 2 and 3 to conduct three more runs without restoring the database or rebooting the
    server. The first run allows for data caching as would occur in a real-world environment. The following
    runs provide the stable performance results of the testing.
  5. Report the median score.
  6. Repeat steps 1 through 5 for each drive configuration.

Clear test data
sysbench –debug=off –mysql-host=localhost –mysql-user=root -mysql-password=Password1 –test=oltp –oltp-table-size=400000000 -num-threads=10 –max-time=3600 –max-requests=10000000 cleanup