MySQL performance test (i)--rhel 7.1, MySQL 5.6.25, sql-bench

Source: Internet
Author: User
Tags check character

Since the previous time to test the performance of MySQL running on all-flash arrays, a period of time was studied, and two tools were tested: One is MySQL's own sql-bench, and the other is sysbench.

The environment used in this procedure is Redhat Enterprise Linux 7.1, MySQL 5.6.25, and the test tool is the Sql-bench tool that comes with MySQL. Since the Sql-bench directory is not included in the MySQL RPM package, I chose to install it using the MySQL source code.

Since MySQL 5.6 is compiled with CMake, you also need to install CMake.

1. Install Redhat 7.1 Server, the installation process is omitted. Select the server with GUI during installation and select the add-ons you can select

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/71/A7/ Wkiom1xvydmbgj4iaafdumx0eli590.jpg "" 435 "height=" 327 "/>

2. After the installation is complete, log in as root and create the MySQL directory in the root directory for copying the installation media. Use WINSCP to copy the mysql-5.6.25.tar.gz, cmake-2.8.4.tar.gz, and the installation source files to the MySQL directory.

Since MySQL relies on Perl packages, PERL-DBD and PERL-DBI are also copied to the directory from the installation disk. When installing the perl-package, it is necessary to rely on libmysqlclient.so.18, which is included in the RPM installation package of MySQL, so I have copied the RPM installation package for MySQL.

In addition, there is a MySQL-dependent ncurse package.

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/71/A7/ Wkiom1xvydvy2labaaihz7f7l3c899.jpg "" 739 "height=" 313 "/>

3. First install CMake: Unzip TAR–ZXVF cmake-2.8.4.tar.gz, use make to install


[Email protected] cmake-2.8.4]# TAR-ZXVF cmake-2.8.4.tar.gz

[Email protected] cmake-2.8.4]# CD cmake-2.8.4/
[Email protected] cmake-2.8.4]#./configure
[[email protected] cmake-2.8.4]# make

[[email protected] cmake-2.8.4]# make install

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/71/A7/ Wkiom1xvydzdzx-iaahjmonclxk614.jpg "" 471 "height=" 298 "/>650) this.width=650;" title= "image" Style= "Border-top: 0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/71/A7/ Wkiom1xvyd3trcwzaaiujnbxmfc711.jpg "" 472 "height=" 298 "/>

4. Unload your own database mariadb:

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/71/A3/ Wkiol1xvyk3yblvmaagsoxdiy9i342.jpg "" 487 "height=" 308 "/>650) this.width=650;" title= "image" Style= "Border-top: 0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/71/A3/ Wkiol1xvyk3sadxkaagvxoswcww326.jpg "" 483 "height=" 305 "/>

5. Unzip the MySQL installation package, install the Shared-compat and shared compatibility packs, and then install the PERL-DBD and PERL-DBI packages:

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/71/A3/ Wkiol1xvyk7c7sogaadd7srwidq907.jpg "" 486 "height=" 307 "/>650) this.width=650;" title= "image" Style= "Border-top: 0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/71/A3/wKioL1XVyk_ Tti7haaggaf36wpa472.jpg "482" height= "305"/>650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/71/A3/ Wkiol1xvylkz2gbcaag92ynnt3u749.jpg "" 392 "height=" 303 "/>

6. Install the ncurses package:

[Email protected] ncurses-5.9]#./configure

[[email protected] ncurses-5.9]# make

[[email protected] ncurses-5.9]# make install

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/71/A4/ Wkiol1xvylpsxsrjaaiv2amxxna618.jpg "" 448 "height=" 346 "/>650) this.width=650;" title= "image" Style= "Border-top: 0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/71/A4/ Wkiol1xvylobanioaajw8gsimk4930.jpg "" 447 "height=" 345 "/>

7. Then start installing MySQL, unzip MySQL:

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/71/A4/ Wkiol1xvylprlg7raafxu3azh80290.jpg "" 497 "height=" 314 "/>

8. Create a MySQL installation directory and database storage directory

[[email protected] mysql-5.6.25]# mkdir-p/usr/local/mysql//install MySQL
[[email protected] mysql-5.6.25]# mkdir-p/usr/local/mysql/data//Store database directory, if you want to test the performance of the storage, you can mount this LUN to This directory

9. Create MySQL user and user group (already exists in this system)

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/71/A7/wKiom1XVyEWCwiTHAABseAQ_ N-i894.jpg "" 372 "height=" 288 "/>

10. Start compiling the MySQL source file and compile with CMake:
[Email protected] mysql-5.6.25]# CMake. -dcmake_install_prefix=/usr/local/mysql-dmysql_datadir=/usr/local/mysql/data-ddefault_charset=utf8-ddefault_ Collation=utf8_general_ci-dextra_charsets=all-denabled_local_infile=1

[[email protected] mysql-5.6.25]# make

[[email protected] mysql-5.6.25]# make install

Parameter description:

-dcmake_install_prefix=/usr/local/mysql//installation directory

-dinstall_datadir=/usr/local/mysql/data//Database storage directory

-ddefault_charset=utf8//using UTF8 characters

-DDEFAULT_COLLATION=UTF8_GENERAL_CI//Check character

-dextra_charsets=all//Install all extended character sets

-denabled_local_infile=1//Allow import of data from local

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/71/A4/ Wkiol1xvyltgxzkxaaju18oebs4770.jpg "" 468 "height=" 362 "/>650) this.width=650;" title= "image" Style= "Border-top: 0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/71/A7/ Wkiom1xvyebyyvz5aajbmcro1fo308.jpg "466" height= "/>650" this.width=650; "title=" image "Style=" Border-top: 0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/71/A4/ Wkiol1xvylwyj0braaj2wyliiwq713.jpg "" 462 "height=" 357 "/>

To delete a compiled temporary file:

[[email protected] mysql-5.6.25]# make clean

[Email protected] mysql-5.6.25]# rm-f CMakeCache.txt

11. Configuration:

(1) Set directory permissions

[Email protected] mysql-5.6.25]# cd/usr/local/mysql/
[Email protected] mysql]# chown-r root:mysql. Set the owner owner of all files in the current directory to root, and the owning group is MySQL
[Email protected] mysql]# chown-r mysql:mysql data

(2) Create a table for the system database
[Email protected] mysql]# scripts/mysql_install_db--user=mysql

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/71/A4/ Wkiol1xvylxbfg3raagr9xgkzxs794.jpg "" 472 "height=" 365 "/>

(3) Setting environment variables, editing

[Email protected] mysql]# Vi/root/.bash_profile

Add the MySQL path to the path

[Email protected] mysql]# Source/root/.bash_profile

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/71/A4/ Wkiol1xvylbw0pxpaaczcjv7afm579.jpg "" "height=" 348 "/>

(4) Add MySQL to the system service

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/71/A7/ Wkiom1xvyerc1vutaabsprcb6p4260.jpg "" 453 "height="/>

(5) Start MySQL success

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/71/A4/wKioL1XVylry_ Weiaab56ik02au536.jpg "" 454 "height=" 351 "/>

(6) Set a password for the root user of MySQL

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/71/A7/ Wkiom1xvyeua6kuuaackcqcrkj0754.jpg "" 452 "height="/>

(7). Running Sql-bench is another step, because the script is looking for sock from another place, so a connection is needed:

[Email protected] sql-bench]# ln-s/tmp/mysql.sock/var/lib/mysql/mysql.sock

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/71/A7/ Wkiom1xvyevtzobaaadfv3fblwm026.jpg "" 474 "height=" 366 "/>

12. Run the test script

[Email protected] sql-bench]#/run-all-tests--user=root--password=password

The output can be viewed in the outputs directory

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/71/A7/ Wkiom1xvyeyazr1maah-wzdlpku186.jpg "" 456 "height=" 445 "/>650) this.width=650;" title= "image" Style= "Border-top: 0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/71/A7/ Wkiom1xvye3wpdtxaaf4di3cbj0517.jpg "" 573 "height=" 443 "/>

MySQL performance test (i)--rhel 7.1, MySQL 5.6.25, sql-bench

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.