,

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)