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

1 Brought to you by your Belgian waffle man!

2 Dimitri "Dim0" Vanoverbeke Solutions Engineer

3 Table of contents 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

4 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

5 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...

6 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

7 Sysadmin 101 - No pt-stalk

8 Sysadmin 101 - No pt-stalk

9 Sysadmin 101 - Having pt-stalk

10 Sysadmin 101 - Having pt-stalk

11 Sysadmin 101 - Having pt-stalk

12 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

13 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.

14 DBA 101 - Use case 2 Replication is a lovely solution, one day you notice that some information seem to be missing on your slaves.

15 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!

16 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

17 DBA 101 - How does it work?

18 DBA 101 - How does it work? (2)

19 DBA 101 - How does it work? (3)

20 DBA 101 - How does it work? (4)

21 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

22 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

23 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

24 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

25 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*/;

26 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.

27 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.

28 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 on 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.

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

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

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

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

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

34 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.

35 Conclusion - pt-online-schema-change Interesting solution for online DDL modifications Less situations in which locking is required than in MySQL 5.6 or 5.7 Always first do a --dry-run

36 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?

37 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!

38 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 ...

39 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