High-performance mysql Reading Notes (1)

1. Benchmark Test and Performance Analysis

Benchmark Testing and performance analysis are two basic methods for identifying system bottlenecks:

The benchmark test is used to measure the overall performance of the system. This helps to determine the processing capacity of the system and prompt factors that affect or do not affect the system performance, it can also be used to prompt the performance of applications when processing different data.

The analysis helps to find out where the application spends the most time or consumes the most resources.

 

What Benchmark Testing can help you achieve:

• Test the running status of the current application.
• Verify the scalability of the system. Such as simulating high load status and checking processing capability.
• Plans for future business growth.
• Test the application's ability to adapt to a variable environment.
• It detects the performance of configurations on different hardware, software, and operating systems.

The following are common test indicators:

• Transaction processing volume per time unit
• Response time or latency
• Scalability
• Concurrency

Some errors that should be avoided during testing:

• Use a subset of real data instead of a complete set. For example, the application requires hundreds of GB of data, but the test only uses 1 GB.
• Use the wrong distributed data.
• Use non-real distribution parameters.
• In a multi-user application, you can choose to perform a test.
• Test A distributed application on a single server.
• Does not match real user behavior.
• Run the same query cyclically. Real queries are different.
• Ignore the error check.
• Use the default server settings.

Integrated Test tool:

• AB
• Http_load
• Jmeter

Single Component test tool:

• Mysqlslap
• Sysbench
• Database test suite
• MySQL benchmark suite (SQL-benchmark)
• Super smack

MySQL has a very convenient benchmark () function that can test the execution efficiency of a specific database operation,

You can specify the number of test cycles and the expression to be tested for the function. An expression can be any scalar expression, for example, a title may be a good fortune or a function. This function is convenient for comparing the processing performance of some operations.

For example, you can use it to evaluate whether the MD5 () function is faster than the sha1 () function:

Mysql> set @ input: = 'Hello world ';

Mysql> select benchmark (1000000, MD5 (@ input ));

+ --------------------------------------- +

| Benchmark (1000000, MD5 (@ input) |

+ --------------------------------------- +

| 0 |

+ --------------------------------------- +

1 row in set (2.78 sec)

Mysql> select benchmark (1000000, sha1 (@ input ));

+ --------------------------------------- +

| Benchmark (1000000, sha1 (@ input) |

+ --------------------------------------- +

| 0 |

+ --------------------------------------- +

1 row in set (3.50 sec)

 

The Return Value of the function is always 0. You can use it to obtain different timing results of the customer's application when performing the query.

Although benchmark () is easy to use, it is generally not used to complete a benchmark test. Because it is difficult

Total results to be tested. It can also be used in a small part of the overall test process very narrowly.

.. Write it first.