Percona Live New York 2012
!
!
- Marcos Albe, Support Engineer @ Percona
- Fernando Ipar, Senior Consultant @ Percona
!
- We'll review all the tools in the kit
- Won't be in depth
- Focused on most frequently used tools
- As hands on as time permits
- More specific examples in our 'recipes' talk tomorrow
!
https://github.com/fipar/vagrant_pt_demos
!
!
| Utility | pt-find |
| pt-kill | |
| pt-tcp-model | |
| Unix-related | pt-fifo-split |
| pt-ioprofile | |
| pt-pmp | |
| pt-align | |
| Do things | pt-archiver |
| pt-log-player | |
| pt-online-schema-change | |
| Learn things | pt-summary |
| pt-mysql-summary | |
| pt-config-diff | |
| pt-variable-advisor | |
| pt-duplicate-key-checker | |
| pt-mext | |
!
| Record things | pt-deadlock-logger |
| pt-fk-error-logger | |
| pt-show-grants | |
| pt-diskstats | |
| Replication related | pt-heartbeat |
| pt-slave-delay | |
| pt-slave-find | |
| pt-slave-restart | |
| pt-table-checksum | |
| pt-table-sync | |
| Query related | pt-query-advisor |
| pt-index-usage | |
| pt-query-digest | |
| pt-visual-explain | |
| pt-upgrade | |
| pt-trend | |
| pt-fingerprint | |
| pt-table-usage | |
!
| Troubleshooting | pt-stalk |
| pt-sift |
!
!
- find tables and take actions
- Like the unix 'find' command
- Works from SHOW TABLES and if needed SHOW TABLE STATUS
!
pt-find --mtime +30 --engine Innodb \
--exec "ALTER TABLE %D.%N ENGINE=MyISAM"
!
- Watch SHOW PROCESSLIST
- Select processes, or groups of processes
- Apply filters
- Take a specified action
!
pt-kill --busy-time 60 --kill --interval 30
!
- Analyses tcpdump data
- Desgined to help scalability and performance analysis
!
!
- Reads lines from a file
- Prints them to a FIFO
- EOF every N lines
- Useful for loading large files into MySQL
!
- Dangerous tool!
- Attaches strace to a process
- Captures I/O-related system calls
- Generates a profile of I/O activity
!
- Dangerous tool!
- Attaches gdb to a process
- Prints out stack traces
- Aggregates them
- Useful for bottleneck analysis
- Inspired by http://poormansprofiler.org/
!
- Aligns sloppy output
- For example: vmstat, iostat
!
!
- Retrieve rows from a table
- Write them to another table, or file
- Delete them from the first table
- Many safety checks and features
- Extensible via plugins
!
pt-archiver --primary-key-only \
--source
h=localhost,D=source-database,t=source-table,u=archiver,p=user-password \
--purge --where 'ts < date_sub(now(), interval 7 day)' --txn-size 10000
!
- For load simulation
- Splits a query log into several
- Replays them multi-threadedly
- Probably obsoleted by Percona Playback
- pt-query-digest --execute is another alternative
!
- Perform ALTER without locking
- Creates a new table
- Copies rows to it
- Uses triggers to track updates to original table
- Swaps original/new table
- Many safety/sanity features
!
pt-online-schema-change --execute --alter "add column c int" \
D=sbtest,t=sbtest1,h=localhost,u=percona
!
!
- Report on system config
- Goals: diff-able, email-able
- Easily extensible
!
- Like pt-summary, but for MySQL
!
- Compare two servers
- Compare a server to its my.cnf
!
- “Best practices” analysis of configuration
- Works from SHOW VARIABLES
!
- Analyze indexes and foreign keys
- Find duplicate/redundant
- Print SQL that can remove them
!
- Format SHOW STATUS samples
- Make it easier to see what's changing
!
!
- Keep track of how often deadlocks happen
- Analyze which tables and indexes are involved
!
- Ditto, but for foreign keys instead
!
- Print GRANT statements for all users
!
- A replacement for iostat -dx
!
!
- Measure replication delay accurately
- Place “quasi-global transaction IDs” into binlogs
- Ease recovery on large and complex topologies
!
- Intentionally delay replication
- Works by starting and stopping slave SQL thread
!
- Automatically discover all servers in a replication relationship
- Print a summary of their status and configuration
!
- Restart replication when it fails
- Please use with care!
- Skipping errors is not good practice
- Useful in dire circumstances
!
- Determine whether replicas have logically the same data as master
- Come to our presentation tomorrow
!
- Repair (online!) an out-of-sync replica
- Use with care! It does change data
- Also possible to do multi-server sync with confict detection, etc
!
!
- Analyze a query and point out problems with the SQL
- Analyze an entire log file
- See also tools.percona.com GUI interface
!
- Make a catalog of tables and indexes
- Analyze a log of queries
- Report on indexes that are unused, queries that have several execution plans, etc
!
- Retrieve queries from some source
- Pass them through a “pipeline” of filters/transforms
- Aggregate them and generate a report
- Do lots of other nifty/crazy things
!
- Analyze EXPLAIN and generate a tree-formatted view
- “Reverse engineer” the execution plan
!
- Execute a log of queries on two servers and compare
- Highlight resultset changes, execution time changes, errors, etc
!
- Obsolete tool, likely to be removed
- Slices a query log and reports aggregate stats from segments of it
!
- Converts queries into fingerprints
- Useful to work on lists of queries using multiple tools
!
- Analyze how queries use tables
!
!
- Essential tool for diagnosing things
- Many customers run it 24x7 to catch unforeseen problems
- Watches the server and collects information when there is a problem
!
pt-stalk --function processlist --variable State \
--match statistics --threshold 10
!
- Important for helping analyze pt-stalk's collected data
- Not complete; just a helper script!
- Useful for analyzing a lot of collected samples
- See https://github.com/box/RainGauge too!
!
- marcos.albe/at/percona.com
- fernando.ipar/at/percona.com
!