Menu:
monitoring-performance-schema:: Measuring Performance with performance_schema
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 two most important features to try with each workload are the note MySQL query cache: query-cache, and the note adaptive hash index: innodb-adaptive-hash. for 'InnoDB' tables.
This section progresses from simple and direct measurement techniques that a single developer can do, to more complicated ones that require additional expertise to perform and interpret the results.
File: manual.info.tmp, Node: select-benchmarking, Next: custom-benchmarks, Prev: optimize-benchmarking, Up: optimize-benchmarking
To measure the speed of a specific MySQL expression or function, invoke the 'BENCHMARK()' function using the note 'mysql': mysql. client program. Its syntax is 'BENCHMARK(LOOP_COUNT,EXPR)'. The return value is always zero, but note 'mysql': 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.
File: manual.info.tmp, Node: custom-benchmarks, Next: monitoring-performance-schema, Prev: select-benchmarking, Up: optimize-benchmarking
Benchmark your application and database to find out where the bottlenecks are. After fixing one bottleneck (or by replacing it with a 'dummy' module), you can proceed to identify the next bottleneck. Even if the overall performance for your application currently is acceptable, you should at least make a plan for each bottleneck and decide how to solve it if someday you really need the extra performance.
A free benchmark suite is the Open Source Database Benchmark, available at http://osdb.sourceforge.net/.
It is very common for a problem to occur only when the system is very heavily loaded. We have had many customers who contact us when they have a (tested) system in production and have encountered load problems. In most cases, performance problems turn out to be due to issues of basic database design (for example, table scans are not good under high load) or problems with the operating system or libraries. Most of the time, these problems would be much easier to fix if the systems were not already in production.
To avoid problems like this, benchmark your whole application under the worst possible load:
The note 'mysqlslap': mysqlslap. program can be helpful for simulating a high load produced by multiple clients issuing queries simultaneously. See note mysqlslap::.
You can also try benchmarking packages such as SysBench and DBT2, available at https://launchpad.net/sysbench, and http://osdldbt.sourceforge.net/#dbt2.
These programs or packages can bring a system to its knees, so be sure to use them only on your development systems.
File: manual.info.tmp, Node: monitoring-performance-schema, Prev: custom-benchmarks, Up: optimize-benchmarking
You can query the tables in the 'performance_schema' database to see real-time information about the performance characteristics of your server and the applications it is running. See *note performance-schema:: for details.
File: manual.info.tmp, Node: thread-information, Next: optimizer-tracing, Prev: optimize-benchmarking, Up: optimization