Benchmarking and profiling
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?
- 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)
Specify the number of times to execute the expression
SET @input := 'hello world'; SELECT BENCHMARK(1000000,MD5(@input));
Profiling is a way of seeing what consumes the server the most
How to do this:
- The best practice is to perform it from time to time to compare changes
- for a complete description of cars 
- It brings the server's variables
- Aborted_connects >0
- Number of times connections failed
- If number is to high, MySQL blocks the host
- When program forgets to close connections
- It's not a big problem
- 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
- 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
- Counts number of selects
- Counts the total num of queries the server has received
- 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.
- Counts how many times MySQL has created temporary tables and files
- 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
- Metrics of MyISAM key buffer
- Query cache
- 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_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
- When an index can't sort, a filesort is used and these vars are increased
- Sort_merge_passes: Can only be solved using indexes
- If you see to many threads in the lock state in 'SHOW FULL PROCESSLIST', check Table_locks_immediate and Table_locks_waited
- It shows a list of connections currently available
- Show master status (need master status)
- Show binlog events (need master status)