Benchmarking and profiling
Contents |
Benchmarking
The idea here is to find bottlenecks in the application and answer these questions:
- Which data MysQL access most?
- Which kind of queries MySQL executes most?
- Which states MySQL threads spend the most time in?
- What subsystems MySQL uses most to execute a query?
- What kinds of data accesses MySQL does during a query?
- How much of various kinds of activities, such as index, scans, MySQL does?
Tools
- Full-stack or hole app. test (ab, http_load, JMeter)
- Single-component or single queries test (MySQLSlap, Sysbench, Database Test Suite, MySQL Benchmark Suite, Super Smack)
benchmark() function
Specify the number of times to execute the expression
SET @input := 'hello world'; SELECT BENCHMARK(1000000,MD5(@input));
Profiling
Profiling is a way of seeing what consumes the server the most
How to do this:
SHOW STATUS
- The best practice is to perform it from time to time to compare changes
- for a complete description of cars [1]
- It brings the server's variables
Server variables
- Aborted_connects >0
- Number of times connections failed
- If number is to high, MySQL blocks the host
- Aborted_clients
- When program forgets to close connections
- It's not a big problem
- Threads_created/Uptime
- If value not close to 0 means thread cache is too small and new connections aren't able to fund free threads on the cache
- Connections
- Max_used_connections
- Threads_connected
- Bytes_received/sent
- Slow_launch_threads
- Threads_cached
- Threads_running
- Binlog_cache_use/Binlog_cache_disk_use
- Show how many transactions have been stored in the binary log cahce, and the transactions to big for the cache stored in a temp. file
- Com_select
- Counts number of selects
- Com_change_db
- Questions
- Counts the total num of queries the server has received
- Com_admin_command
- Counts the num of pings the server received. That means that if user connect to DB before every query, this counter will load up. It's best to eliminate connections.
- Created_tmp\_%
- Counts how many times MySQL has created temporary tables and files
- Handler\_*
- Counts requests between server and storage engine
- These vars give insights into what kinds of work your server does most
- Performance of non-select queries
- Key\_*
- Metrics of MyISAM key buffer
- Qcache\_*
- Query cache
- Select\_*
- Select_range: num of joins that scanned an index range on the first
- Select_scan: num of joins scanned on the entire first tbl
- Select_full_range_join:
- Select_range_check: These should not increase rapidly on a well-tuned server
- Select_full_join: If either of them are more than a few percent more than Com_select, queries need improvements
- Sort\_*
- When an index can't sort, a filesort is used and these vars are increased
- Sort_merge_passes: Can only be solved using indexes
- Table_locks\_*
- If you see to many threads in the lock state in 'SHOW FULL PROCESSLIST', check Table_locks_immediate and Table_locks_waited
SHOW PROCESSLIST
- It shows a list of connections currently available
- Show master status (need master status)
- Show binlog events (need master status)
