HPM Note3, Benchmarking and Profiling

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?"

Why Benchmark?
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

Benchmarking Strategies
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

Benchmarking Tactics
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

Benchmarking Tools
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

Benchmarking Examples

$ 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

Profiling
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

log = <file_name>

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

log-slow-queries = <file_name>
long_query_time = 2
log-queries-not-using-indexex
log-slow-admin-statements

Log analysis tools:
mysqldumpslow
mysql_slow_log_filter
mysql_slow_log_parser
mysqlsla
SHOW STATUS/SHOW PROFILE

Operating System Profiling
netstat
vmstat
iostat
mpstat
strace
OProfile
pgrof
Intel VTune, Sun Performace Analyzer, DTrace

评论

1 楼 hideto 2009-07-02  

FW也是在Rails程序里有Profiling相关的代码的，每天发一个TOP 20 slow urls和TOP 20 slow queries的report出来。其实这个可以做成一个通用的插件的，不知道有没有现成的