Measuring Performance (Benchmarking)
To measure performance, consider the following factors:
- Whether you are measuring the speed of a single operation on a quiet system, or how a set of operations (a “workload”) works over a period of time. With simple tests, you usually test how changing one aspect (a configuration setting, the set of indexes on a table, the SQL clauses in a query) affects performance. Benchmarks are typically long-running and elaborate performance tests, where the results could dictate high-level choices such as hardware and storage configuration, or how soon to upgrade to a new MySQL version.
- For benchmarking, sometimes you must simulate a heavy database workload to get an accurate picture.
- Performance can vary depending on so many different factors that a difference of a few percentage points might not be a decisive victory. The results might shift the opposite way when you test in a different environment.
- Certain MySQL features help or do not help performance depending on the workload. For completeness, always test performance with those features turned on and turned off. The most important feature to try with each workload is the adaptive hash index for
InnoDBtables.
Benchmark Tools
mysqlslap
https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html
SysBench
http://swsmile.info/post/mysql-benchmark-sysbench/
DBT2
Measuring the Speed of Expressions and Functions
To measure the speed of a specific MySQL expression or function, invoke the BENCHMARK() function using the mysql client program. Its syntax is BENCHMARK(*loop_count*,*expr*). The return value is always zero, but mysql prints a line displaying approximately how long the statement took to execute. For example:
mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.32 sec)
This result was obtained on a Pentium II 400MHz system. It shows that MySQL can execute 1,000,000 simple addition expressions in 0.32 seconds on that system.
The built-in MySQL functions are typically highly optimized, but there may be some exceptions. BENCHMARK() is an excellent tool for finding out if some function is a problem for your queries.
Reference
- https://dev.mysql.com/doc/refman/8.0/en/optimize-benchmarking.html
- http://osdldbt.sourceforge.net/#dbt2