Note3, Finding Bottlenecks: Benchmarking and Profiling
Benchmarking and profiling are two essential practices for finding bottlenecks
A benchmark measures your system's performance. This can help determine a system's capacity, show you which changes matter and which don't, or show how your application performs with different data
Profiling helps you find where your application spends the most time or consumes the most resources
Benchmarking answers the question "How well does this perform?" and profiling answers the question "Why does it perform the way it does?"
[b]Why Benchmark?[/b]
Benchmarks can help you do:
Measure how your application currently performs
Validate your system's scalability
Plan for growth
Test your appplication's ability to tolerate a changing environment
Test different hardware, software, and operating system ocnfigurations
[b]Benchmarking Strategies[/b]
There are two primary benchmarking strategies: you can benchmark the appplication as a whole, or isolation MySQL
These two strategies are known as full-stack and single-component benchmarking respectively
What to measure:
Transactions per time unit, Response time or latency, Scalability, Concurrency
[b]Benchmarking Tactics[/b]
The first step in planning a benchmark is to identify the problem and the goal
Try to record as much additional information as you can during the benchmarks, such as CPU usage, disk I/O, and network traffic statistics, counters from SHOW GLOBAL STATUS
The best way to get accurate results is to design your benchmark to answer the question you want to answer
It's usually a good idea to automate the benchmark runs
[b]Benchmarking Tools[/b]
Full-Stack Tools:
ab
ab is a well-known Apache HTTP server benchmarking tool, it shows how many requests per second your HTTP server is capable of serving
http_load
Similar tool in concept to ab, it's also designed to load a web server, but more flexible
JMeter
A Java application that can load another application and measure its performance, much more complex
Single-Component Tools:
mysqlslap
mysqlslap simulates load on the server and reports timing information
sysbench
sysbench is a multithreaded system benchmarking tool
You can measure the performance of file I/O, the OS scheduler, memory allocation and transfer speed, POSIX threds, and the database server itself
sysben supports scription in the Lua language, which makes it very flexible for testing a variety of scenarios
Database Test Suite
a test kit for running benchmarks similar to some industry-standard benchmarks, such as those published by TPC
MySQL Benchmark Suite
MySQL's own benchmark suite, single-threaded and measure how quickly the server executes queries
Super Smack
a benchmarking, stress-testing, and load-generating tool for MySQL and PostgreSQL
It's a complex, powerful tool that lets you simulate multiple users, load test data into the database, and populate tables with randomly generated data
[b]Benchmarking Examples[/b]
[code]
$ http_load -parallel 1 -seconds 10 urls.txt
$ http_load -paraleel 5 -seconds 10 urls.txt
$ http_load -rate 5 -seconds 10 urls.txt
$ http_load -rate 20 -seconds 10 urls.txt
$ sysbench --test=cpu --cpu-max-prime=20000 run
$ sysbench --test=fileio --file-total-size=150G prepare
$ sysbench --test=fileio --file-total-size=150G --file-test-mode=rndrw --init-rnd=on --max-time=300 --max-requests=0 run
$ sysbench --test=fileio --file-total-size=150G cleanup
$ sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root prepare
$ sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
sql-bench$ ./run-all-tests --server=mysql --user=root --log --fast
sql-bench$ ./test-insert
[/code]
[b]Profiling[/b]
Database access if often the bottleneck in application
Bottlenecks can also be caused by any of the following:
External resources, such as calls to web services or search engines
Operations that require processing large amounts of data in the application, such as parsing big XML files
Expensive operations in tight loops, such as abusing regular expressiosn
Badly optimized algorithms, such as naive search algorithms to find items in lists
Application profiling can help you find the bottlenecks, and it's an important step in monitoring and improving overall performance
We recommend that you include profiling code in every new project you start
Your profiling code should gather and log at least the following:
Total execution time, or "wall-clock time"(in web applications, this is the total page render time)
Each query executed, and its execution time
Each connection opened to the MySQL server
Every call to an external resource, such as web services, memcached, and externally invoked scripts
Potentially expensive function calls, such as XML parsing
User and system CPU time
MySQL has two kinds of query logs: the general log and the slow log
The general log writes out every query as the server receives it, even some events such as connecting and disconnecting and queries may not even be executed due to errors
[code]
log = <file_name>
[/code]
The slow log contains only queries that have executed
In particlular, slow log logs queries that take more than a specified amount of time to execute
[code]
log-slow-queries = <file_name>
long_query_time = 2
log-queries-not-using-indexex
log-slow-admin-statements
[/code]
Log analysis tools:
mysqldumpslow
mysql_slow_log_filter
mysql_slow_log_parser
mysqlsla
SHOW STATUS/SHOW PROFILE
[b]Operating System Profiling[/b]
netstat
vmstat
iostat
mpstat
strace
OProfile
pgrof
Intel VTune, Sun Performace Analyzer, DTrace
Benchmarking and profiling are two essential practices for finding bottlenecks
A benchmark measures your system's performance. This can help determine a system's capacity, show you which changes matter and which don't, or show how your application performs with different data
Profiling helps you find where your application spends the most time or consumes the most resources
Benchmarking answers the question "How well does this perform?" and profiling answers the question "Why does it perform the way it does?"
[b]Why Benchmark?[/b]
Benchmarks can help you do:
Measure how your application currently performs
Validate your system's scalability
Plan for growth
Test your appplication's ability to tolerate a changing environment
Test different hardware, software, and operating system ocnfigurations
[b]Benchmarking Strategies[/b]
There are two primary benchmarking strategies: you can benchmark the appplication as a whole, or isolation MySQL
These two strategies are known as full-stack and single-component benchmarking respectively
What to measure:
Transactions per time unit, Response time or latency, Scalability, Concurrency
[b]Benchmarking Tactics[/b]
The first step in planning a benchmark is to identify the problem and the goal
Try to record as much additional information as you can during the benchmarks, such as CPU usage, disk I/O, and network traffic statistics, counters from SHOW GLOBAL STATUS
The best way to get accurate results is to design your benchmark to answer the question you want to answer
It's usually a good idea to automate the benchmark runs
[b]Benchmarking Tools[/b]
Full-Stack Tools:
ab
ab is a well-known Apache HTTP server benchmarking tool, it shows how many requests per second your HTTP server is capable of serving
http_load
Similar tool in concept to ab, it's also designed to load a web server, but more flexible
JMeter
A Java application that can load another application and measure its performance, much more complex
Single-Component Tools:
mysqlslap
mysqlslap simulates load on the server and reports timing information
sysbench
sysbench is a multithreaded system benchmarking tool
You can measure the performance of file I/O, the OS scheduler, memory allocation and transfer speed, POSIX threds, and the database server itself
sysben supports scription in the Lua language, which makes it very flexible for testing a variety of scenarios
Database Test Suite
a test kit for running benchmarks similar to some industry-standard benchmarks, such as those published by TPC
MySQL Benchmark Suite
MySQL's own benchmark suite, single-threaded and measure how quickly the server executes queries
Super Smack
a benchmarking, stress-testing, and load-generating tool for MySQL and PostgreSQL
It's a complex, powerful tool that lets you simulate multiple users, load test data into the database, and populate tables with randomly generated data
[b]Benchmarking Examples[/b]
[code]
$ http_load -parallel 1 -seconds 10 urls.txt
$ http_load -paraleel 5 -seconds 10 urls.txt
$ http_load -rate 5 -seconds 10 urls.txt
$ http_load -rate 20 -seconds 10 urls.txt
$ sysbench --test=cpu --cpu-max-prime=20000 run
$ sysbench --test=fileio --file-total-size=150G prepare
$ sysbench --test=fileio --file-total-size=150G --file-test-mode=rndrw --init-rnd=on --max-time=300 --max-requests=0 run
$ sysbench --test=fileio --file-total-size=150G cleanup
$ sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root prepare
$ sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
sql-bench$ ./run-all-tests --server=mysql --user=root --log --fast
sql-bench$ ./test-insert
[/code]
[b]Profiling[/b]
Database access if often the bottleneck in application
Bottlenecks can also be caused by any of the following:
External resources, such as calls to web services or search engines
Operations that require processing large amounts of data in the application, such as parsing big XML files
Expensive operations in tight loops, such as abusing regular expressiosn
Badly optimized algorithms, such as naive search algorithms to find items in lists
Application profiling can help you find the bottlenecks, and it's an important step in monitoring and improving overall performance
We recommend that you include profiling code in every new project you start
Your profiling code should gather and log at least the following:
Total execution time, or "wall-clock time"(in web applications, this is the total page render time)
Each query executed, and its execution time
Each connection opened to the MySQL server
Every call to an external resource, such as web services, memcached, and externally invoked scripts
Potentially expensive function calls, such as XML parsing
User and system CPU time
MySQL has two kinds of query logs: the general log and the slow log
The general log writes out every query as the server receives it, even some events such as connecting and disconnecting and queries may not even be executed due to errors
[code]
log = <file_name>
[/code]
The slow log contains only queries that have executed
In particlular, slow log logs queries that take more than a specified amount of time to execute
[code]
log-slow-queries = <file_name>
long_query_time = 2
log-queries-not-using-indexex
log-slow-admin-statements
[/code]
Log analysis tools:
mysqldumpslow
mysql_slow_log_filter
mysql_slow_log_parser
mysqlsla
SHOW STATUS/SHOW PROFILE
[b]Operating System Profiling[/b]
netstat
vmstat
iostat
mpstat
strace
OProfile
pgrof
Intel VTune, Sun Performace Analyzer, DTrace