Permalink
Find file
Fetching contributors…
Cannot retrieve contributors at this time
359 lines (230 sloc) 5.07 KB

Percona toolkit recipes

Percona Live New York 2012

!

Introduction

!

About us

  • Marcus Albe - Support Engineer @Percona
  • Fernando Ipar - Senior Consultant @Percona
  • Ryan Lowe - Production Engineer @Square

!

Presentation overview

!

  • What is Percona Toolkit & Why should you be here?
  • Classes of problems solved by Percona Toolkit
  • Targeted recipes for specific problems
  • Further resources & Recommended reading
  • Q&A

!

What is Percona Toolkit?

Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL and system tasks that are too tedious and/or complex to perform manually.

!

Why should you be here?

Don't reinvent the wheel

!

Problems

  • Replication
  • Performance
  • Upgrading
  • Troubleshooting
  • Schema Changes

!

Recipes

!

Replication

!

Problem 1: Master and slave MAY have different data

!

  • Statements unsafe for replication (CURRENT_USER(), etc)
  • Incorrect failover procedures
  • Developers writing directly to the slave
  • Rolling upgrades
  • Storage Engines
  • Temporary Tables
  • Replication Filters

!

Solution 1 : pt-table-checksum

pt-table-checksum --tables dbname.tbname \
  h=master,u=user,p=password

!

Problem 2: Master and slave DO have different data

Same reasons as Problem 1, but validated via Solution 1

!

Solution 2: pt-table-sync

Demo: resyncing two instances

!

Problem 3: SQL Injection + long restore times

(http://xkcd.com/327/ is one reason why a slave is **not** a backup)

!

Solution 3: pt-slave-delay

Demo: restoring from a delayed slave

!

Performance

!

Problem 4: Slow queries

Amdahl's Law

!

Solution 4: pt-query-digest

Demo: Analyzing a query log looking for candidates for performance optimization

!

Problem 5: Rapid Development & Constantly Changing Workload

!

Solution 5: pt-query-digest

!

Solution 5: pt-query-digest

!

Upgrading

!

Problem 6: Upgrading MySQL can break my app

!

Solution 5: pt-upgrade

2 ways to upgrade MySQL:

!

pt-upgrade h=host1 h=host2 --query \
    "select a from b" --temp-database test

!

pt-upgrade h=host1 h=host2 \ 
    --temp-database test queries.txt

!

!

!

!

Troubleshooting

!

Problem 7: Intermittent non-repeatable problems

!

Solution 7: pt-stalk & pt-sift

Why?

"Everything freezes at random times"

!

pt-stalk: triggered captures

!

Triggers based on

  • show global status
  • show full processlist
  • user defined function

!

Some examples

!

pt-stalk --function processlist --variable Command --match Sleep
  --threshold 155 --cycles 0

!

pt-stalk --threshold 40 --cycles 6

!

pt-stalk --function check_loadavg.sh --threshold 12

!

function trg_plugin()   
{   
uptime |sed 's/.*load average: //g;s/\..*//g'   
}

!

pt-stalk : making sense of a capture

Now ... Where do we start?

pt-sift!

!

Sample pt-sift summary

!

Schema changes

!

Problem 9: blocking alter statements

!

Solution 9: pt-online-schema-change

!

Resources

http://bit.ly/pt-recipes

!

Questions?

!