Brought to you by your Belgian waffle man! 2 Dimitri - - PowerPoint PPT Presentation

Brought to you by your Belgian waffle man!

1

Dimitri "Dim0" Vanoverbeke Solutions Engineer

2

What is Percona Toolkit? pt-stalk pt-table-checksum pt-table-sync pt-online-schema-change pt-query-digest Other interesting tools Getting Percona-Toolkit! Conclusion

Table of contents

3

What is Percona Toolkit?

A collection of advanced command-line tools Self Contained ("well written") Perl scripts. Based on Maatkit and Aspersa Works with MySQL 5.0+, MariaDB, Percona Server, Percona XtraDB Cluster

4

Sysadmin 101 - Use case 1

Pffffrt, this morning one of our machines is acting strangely. It's 4 in the morning, I just had a heavy evening off, however, the oncall was escalated to me. I know nothing about the cause. I just logged in and the environment is running fine...

5

Sysadmin 101 - pt-stalk

pt-stalk will verify the state of the server and will create an information stream whenever specific conditions are met. Features: Very flexible Ready information for your support departement or internal DBAs. Trigger based, define your triggers eg. Active connections on server Server load Deadlocks

6

Sysadmin 101 - No pt-stalk

7

Sysadmin 101 - No pt-stalk

8

Sysadmin 101 - Having pt-stalk

9

Sysadmin 101 - Having pt-stalk

10

Sysadmin 101 - Having pt-stalk

11

Example

Just collecting information

$ pt-stalk --no-stalk -- --defaults-file=./my.default.cnf

When a host has more than 10 connections, fetch in formation.

$ pt-stalk --function processlist --variable Host\

• -match 10.0.0.1 --threshold 10 -- --defaults-file=./my.default.cnf

Make your own function

$ pt-stalk --function ./pt-stalk-function \

• -threshold 100 -- --defaults-file=./my.default.cnf

12

Conclusion - pt-stalk

pt-stalk is a tool removing distress during dire moments on your MySQL infrastructure. The information you capture can be used later for support and understanding the cause of issues.

13

DBA 101 - Use case 2

Replication is a lovely solution, one day you notice that some information seem to be missing on your slaves.

14

DBA 101 - pt-table-checksum

Reasons for inconsistencies: Possible consistency problems thanks to statement based replication. Mixed transactional and non-transactional tables Stored procedures and functions creating havoc Update with LIMIT clause (non deterministic queries) USER ERROR!

15

DBA 101 - pt-table checksum

What does it do? Mathematical checksums of chunks of data. Automated script Runs checksum queries against master Waits for replication to the slaves Shows the differences in the results

16

DBA 101 - How does it work?

17

DBA 101 - How does it work? (2)

18

DBA 101 - How does it work? (3)

19

DBA 101 - How does it work? (4)

20

Example pt-table-checksum

[root@master]# pt-table-checksum --replicate=percona.checksums \--ignore-databases mysql h=localhost,u=checksum_user,p=checksum_password TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 09-28T18:21:12 0 1 1992 1 0 1.044 moehaha.perfectissimo 09-28T18:21:20 0 0 351 1 0 1.044 moehaha.meh

21

Conclusion - pt-table-checksum

This tool will help you: Compare a source and a slave database Conclusions: If there is a difference, the slave is out of sync Understand how inconsistencies could happen Replication requires consistency checks

22

DBA 101 - pt-table-sync

So now we understand how we can actually see slave inconsistencies with the master SYNC them NOW pt-table-sync synchronizes data efficiently between MySQL tables. Watch out in a master-master situation. Can run seperately (will run it's own comparison) or based on initial pt-table-checksum

23

DBA 101 - pt-table-sync | Keep in mind

WARNING: (This will change data) Before please do the following: Read the tool's documentation Test the tool on a non production server Make sure you have backups Test it using --dry-run and --print

24

Example of pt-table-sync

[root@slave]# pt-table-sync --print --replicate=percona.checksums \--sync-to-master h=localhost,u=checksum_user,p=checksum_password REPLACE INTO test.dummy(id, name) VALUES ('6', 'f') /*percona-toolkit \ src_db:moehahaha src_tbl:perfectissimo src_dsn:P=3306,h=192.168.0.20,p=...,=\ u=checksum_user dst_db:test dst_tbl:dummy dst_dsn:h=localhost,\ p=...,u=checksum_user lock:1 transaction:1 changing_src:percona.checksums\ replicate:percona.checksums bidirectional:0 pid:24683 user:root host:slave*/;

25

Conclusion - pt-table-sync

This tool exists for syncing information in between a master and a slave environment. Keep in mind that this tool will initially compare the information mitigating pt-table-checksum.

26

DBA 101 - Use case 3

You just performed a complete release on your application and have performed modifications on your database schema. In the afternoon you notice that significant slowdowns occur due to mistakes made in the schema format.

27

DBA 101 - pt-online-schema-change

MySQL is a special piece of work sometimes, whenever a DDL query is performed on a schema it might require a lock on the table involved. You might require to, Do it on the slave, promote the slave, do it

• n the master.

pt-online-schema-change: Runs DDL queries (ALTER) on line so that the table is not locked for reads and writes. Removes the need of having the original table locked during the process.

28

DBA 101 - pt-online-schema-change | drawing

29

DBA 101 - pt-online-schema-change | command

30

DBA 101 - pt-online-schema-change | chunks

31

DBA 101 - pt-online-schema-changed | copyback

32

DBA 101 - pt-online-schema-change | DONE

33

Example

dim0@littlebasterd:~$ pt-online-schema-change --user root --ask-pass \--alter "ADD COLUMN ratings INT" D=tutorials,t=tutorials_tbl --execute Operation, tries, wait: copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering tutorials.tutorials_tbl... Creating new table... Created new table tutorials._tutorials_tbl_new OK. Altering new table... Altered tutorials._tutorials_tbl_new OK. 2016-10-01T08:50:55 Creating triggers... 2016-10-01T08:50:55 Created triggers OK. 2016-10-01T08:50:55 Copying approximately 2 rows... 2016-10-01T08:50:55 Copied rows OK. 2016-10-01T08:50:55 Swapping tables... 2016-10-01T08:50:55 Swapped original and new tables OK. 2016-10-01T08:50:55 Dropping old table... 2016-10-01T08:50:55 Dropped old table tutorials._tutorials_tbl_old OK. 2016-10-01T08:50:55 Dropping triggers... 2016-10-01T08:50:55 Dropped triggers OK. Successfully altered tutorials.tutorials_tbl.

34

Conclusion - pt-online-schema-change

Interesting solution for online DDL modifications Less situations in which locking is required than in MySQL 5.6

• r 5.7

Always first do a --dry-run

35

Developer/DBA 101 - use case 4

Our system engineer is complaining about long running queries. He is wondering if we could review some of our queries. Where do we start?

36

Developer 101 - pt-query-digest

pt-query-digest, this tool brings you: a command line tool to analyse: logs, SHOW PROCESSLIST and tcpdumps. easy identification of problematic, most system time consuming queries. History functionality to compare!

37

Developer 101 - Why should you use it

Query analysis is a best-practice that should be done frequently. Do you know that most performance problems originate from: Badly written queries. Awful table schema's If your system engineer wakes you up for a query problem, there must be something rotten in the state of ...

38

Developer 101 - Easy readable

# Current date: Sat Oct 1 09:06:06 2016 # Hostname: ip-172-31-20-145 # Files: ip-172-31-20-145-slow.log # Overall: 28.68k total, 79 unique, 87.99 QPS, 0.00x concurrency _________ # Time range: 2016-10-01 09:00:15 to 09:05:41 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 640ms 1us 15ms 22us 40us 155us 9us # Lock time 11ms 0 487us 0 0 5us 0 # Rows sent 237.43k 0 112 8.48 107.34 26.48 0.99 # Rows examine 1.77k 0 154 0.06 0 2.61 0 # Query size 2.43M 17 34.15k 88.90 299.03 214.80 69.19 # Boolean: # QC hit 62% yes, 37% no # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============= ===== ====== ===== =============== # 1 0x92F3B1B361FB0E5B 0.0958 15.0% 1891 0.0001 0.00 SELECT wp_options # 2 0xF0265D81808CE240 0.0708 11.1% 1891 0.0000 0.00 SELECT # 3 0xF1B6F473642E5770 0.0687 10.7% 1868 0.0000 0.00 SELECT # 4 0x7AEDF19FDD3A33F1 0.0465 7.3% 3003 0.0000 0.00 SELECT wp_options # 5 0x28BF5A31C1125B0B 0.0398 6.2% 15 0.0027 0.00 UPDATE wp_options # 6 0x746D96521AE5B82C 0.0366 5.7% 1891 0.0000 0.00 SET # 7 0x6498E5CBE1B003AB 0.0334 5.2% 1891 0.0000 0.00 SET # 8 0x8AE5000CAF43D53F 0.0275 4.3% 8 0.0034 0.01 INSERT UPDATE wp_options # 9 0x7D18C5C503757896 0.0261 4.1% 2808 0.0000 0.00 SELECT wp_postmeta

39

Developer 101 - Per query easy metrics

# Query 1: 6.00 QPS, 0.00x concurrency, ID 0x92F3B1B361FB0E5B at byte 258935 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2016-10-01 09:00:26 to 09:05:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 6 1891 # Exec time 14 96ms 18us 8ms 50us 33us 279us 28us # Lock time 4 494us 0 57us 0 0 3us 0 # Rows sent 85 203.15k 109 112 110.01 107.34 0 107.34 # Rows examine 75 1.33k 0 154 0.72 0 10.07 0 # Query size 5 131.11k 71 71 71 71 0 71 # Boolean: # QC hit 99% yes, 0% no # String: # Databases njieh # Hosts localhost # Users oldfart # Query_time distribution # 1us # 10us ################################################################ # 100us # # 1ms # # 10ms # 100ms # 1s

40

# 10s+

Conclusion - pt-query-digest

Most problems on DBA environments are related to problematic query’s Dev problems (why don’t you help your dev) One query could be locking them all Overload of some of the specific queries Identify a wrong execution path Use it often, use it wise!

41

Mention worthy tools

pt-archiver pt-kill pt-heartbeat pt-upgrade pt-ioprofile

42

What is Percona Toolkit? pt-stalk pt-table-checksum pt-table-sync pt-online-schema-change pt-query-digest Other interesting tools Getting Percona-Toolkit! Conclusion

Table of contents

43

Getting Percona Toolkit

Rpm and deb packages are available

http://www.percona.com/downloads/percona-toolkit /LATEST/''

Or you can use a tarball

wget percona.com/get/percona-toolkit.tgz''

Extract, then make, make install Or, if you only need a specific tool

wget percona.com/get/TOOL ''

44

Conclusions

So you feel stressed out due to some MySQL issue? Use Percona- Toolkit and have a waffle... , The Waffle boy!

45