MySQL Benchmark test

First, Benchmark test

The role of benchmark testing:

• Understand the performance of the current system and establish a MySQL server performance baseline (provides a super-start line for later performance optimizations)

• Simulate higher load than current system, find out the expansion bottleneck of system, provide reference for system expansion and optimization.

• Test different hardware, software, and operating system configurations

• Prove that the new hardware device is properly configured and is optimally configured

Benchmark tests can be divided into integrated and single-component tests.

• Integrated testing is the entire application system testing, such as a Web application system, the entire site system testing.

• A single-component test is a test of a component in the system, such as testing a database in a Web site, or testing a Web server for that component.

(i), Integrated testing tools

There are http_load,ab,jmeter,wrk, etc.

1, the use of AB

AB, Apache Bench, is an HTTP pressure measurement tool included with Apache HTTPD

(1) AB installation

Install Apache httpd (detailed installation tutorial refer to: Installing Apache httpd and httpd common usage in Linux

(2) General usage of AB

AB [options] url

• Options indicates parameters

-N: Number of requests

-C: Concurrency (number of requests)

-S: Timeout period

• URL indicates the address of the stress test

Such as:

1	ab -n 2000 -c 10000 http://localhost/hello.php

The results are as follows:

2, the use of wrk

WRK is a simple HTTP pressure measurement tool that is easy to install and use (recommended)

(1) Installation

: http://github.com/wg/wrk

123	cdwrk make cpwrk /usr/local/bin/wrk

(2) Use

wrk <options> URL

• -Options: Parameters

  -C: Number of connections

-D: Measurement time (can use m (minutes), s (seconds) such units)

-T: Number of threads used

• -URL: Pressure-measured address

1	wrk -c20000 -t10 -d1m http://localhost/index.php

The results are as follows:

3, the use of JMeter

JMeter is relatively complex relative to other tools, then there will be a special article to introduce the use of JMeter

(b), single-component test tools

There are mysqlslap,sysbench, etc.

1, Mysqlslap

It's a test tool that comes with MySQL.

Use: Mysqlslap <options>

General Options:

• --auto-generate-sql (or use-a) means that the SQL scripts generated by the Mysqlslap tool are used to test the concurrency pressure

• --concurency (or using-C), which indicates how many clients the simulation performs simultaneously

• --engine (or use-e), which represents the storage engine to be tested

• --iterations (or use-i), which indicates how many times each test is tested in different concurrent environments

• --number-of-queries=n, indicating the total number of test queries

Such as: Testing a single user

1	./mysqlslap -a

Or: Simulate multiple users (100)

1	./mysqlslap -a -c200

2, Sysbench

For more information, please refer to: MySQL optimization tuning one: Discovering problems-using sysbench of benchmark tests

Second, performance analysis

As with testing, performance analysis also includes performance analysis of the entire application and performance analysis of individual components, and there are different solutions for the entire application, for different development platforms. such as openresty can be analyzed by the flame diagram, PHP can be used to analyze the xhprof, or the use of third-party performance analysis tools (usually charged, search APM can be)

1. Application-level performance analysis

For the entire application-level performance analysis, not the focus of this article, you can choose according to the characteristics of the project or develop the corresponding tools to analyze.

2. mysql Performance analysis

(1) Analyze slow query log

You can use Mysqldumpslow or pt-query-digest to analyze, refer to the use of MySQL slow query log analysis tool

(2) Through show status and show engine InnoDB status, etc.

Show status can view the state of the current server:

To view the commands that the server is executing

1	show status like‘Com_%‘;

To view traffic between and between servers:

1	show status like‘Bytes_%‘;

To view the temporary tables and files created during query execution:

1	show status like‘Created_%‘;

To view server uptime:

1	show status like‘uptime‘;

Show Engine InnoDB Status: View InnoDB the status of this storage engine

(3) Through show profile

Show profile allows you to see where the performance loss of MySQL statements

First step: To open the profiling (you can see if it is open first)

12	show variables like‘profiling‘;setprofiling = 1;

Step two: Execute SQL

Step three: View performance loss analysis report for executed SQL

To view a recent execution result:

1	show profile;

See all results after opening profling: But only show the execution time for each bar:

1	show profiles;

For example:

12345

mysql>setprofling =1;mysql>select* frompopulation wherecountry="cn";mysql>select* frompopulation wherecity = "beijing";mysql>select * frompopulation wherepopulation>=1000000;mysql>show profile;

View results:

You can see how long each stage of the entire execution process consumes.

View by Show Profiles:

You can find the time each execution statement consumes

(4) through show processlist

With show Processlist you can see which threads are currently running, or you can think of this as a way to find the current number of MySQL connections, a good way to troubleshoot too many connections errors.

1	show processlist

The results are as follows:

• ID: Number of the connection

• User: Connected Users

• Host: Connected hosts

• DB: Database name of the connection

• Command: Commands to execute

• Time: Connection duration

• State: Connection Status

• Info: SQL statement for specific execution

Main state states: (This state value has a lot of, here only a few commonly used, you can easily know its meaning according to the name)

• Locked: Locked by another query

• Sending data: The record of the select query is being processed and the results are being sent to the client

• Connect out: Replication from the server is connecting to the primary server

• Updating: Searching for matching records and modifying the data

MySQL Benchmark test