Percona Toolkit : Most command uses with MySQL

by Mark Nielsen
Copyright Dec 2021


This is just about using the toolkit with MySQL and what I have used. This will not cover all the tools. Another article will cover the use with MongoDB. Also, this toolkit can be used in the cloud, and sometimes DaaS with AWS RDS. Also, the toolkit may have changed after this article was made. I might update this article once a year.
  1. Links
  2. The tools

Links

  • https://www.percona.com/software/database-tools/percona-toolkit
  • https://www.percona.com/doc/percona-toolkit/LATEST/index.html
  • https://severalnines.com/database-blog/a-look-at-the-percona-toolkit
  • https://computingforgeeks.com/how-to-install-and-use-percona-toolkit-on-ubuntu/
  • https://www.youtube.com/watch?v=dLBOabteIPo
  • https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
  • https://www.percona.com/doc/percona-xtrabackup/2.3/xtrabackup_bin/xbk_option_reference.html

    Tools

    Most used tools. A few of them I wrote python programs for to achieve the same desire before they existed, but usually they have many more options.
    • pt-online-schema-change
      If you have never used this a DBA, practice it. Even though you have inline schema changes which don't lock tables, not everything is lock free in MySQL. This tool is vital for schema changes in prod without downtime and being safe. I highly recommend this tool.
    • percona ExtraBackup
      Not part of the toolset but I am including because it is not useful. This tool is VERY VERY useful for making backups. It doesn't block the server. It will slow down the IO. This tools also makes it easy to restore a server. It is saved in binary format, so copy it to the new server in the data directory, and making a few changes like accounts and replication you can restore the server quickly (as soon as all the files are transferred). There is also lots of other nice features to many to mention here (like encryption).
      NOTE: You should get the BINLOG position on the master AND slave if you are backing up the slave. If you use a slave, add "--slave-info". Even though it says slave info, you are recording the binlog info of the master (because this is a slave). Thus you can restore this as a slave of the original master, or as a slave to the server you ran this program on (effectively a slave of a slave). This can be very useful with all the options. BIDIRECTIONAL SYNCING seems new and I am not sure how useful it is.
      NOTE: If there is valid data on the Slave, this will destroy it. Get data off the slave before running this.
      How can data get on the slave? Replication errors and the person who started replication let the last command fly. Someone logged in as root and made changes. There are multiple masters for this server --- this can be dangerous if so. An application wrote to the slave when it should not of -- did you turn read the global read-only variable?
      I wrote a python script back in 2008 which tells you the exact rows by primary key or unique index which is not the same -- quickly with low impact. It had other capabilities. I made it VERY VERY easy to sync the servers. Better than pt-sync in my opinion. From the keys you can determine if the Slave or Master is correct. Sometimes you have to go by key to key to see which one is correct.

    Useful tools you will probably need to use. This is ONLY my preference. You may find some of these tools useful for yourself that I don't.

    • pt-index-usage
      This can be useful to run queries from a log to analyze how queries use the indexes. This can be used for debugging.
    • pt-diskstats
      NOTE: For debugging why a query isn't working, I look at disk IO and space, memory and memory config for MySQL, cpu usage, load, network speed and latency and hitting a bottleneck, account permissions, and others things. A complete tool or set of tools is desired.
    • pt-variable-advisor
      I find this tool useful and use it every once in a while. Other tools can do a similar thing.
    • pt-stalk
      I find this tool useful, but I usually write a Lambda or Python script to fine tune the detection of problems and how it should be displayed. Still, to a non programmer this is a useful tool.

    Less Used tools

    • https://www.percona.com/doc/percona-toolkit/LATEST/pt-visual-explain.html
      You might find this tool very useful if you are not used to "explain QUERY " in MySQL. I find it useful sometimes.
    • pt-archiver
      You can do this manually with a cronjob. You don't need this. But its nice.
    • pt-config-diff
      Less useful if you have automated installs. But it can be used to check servers for alarms. If someone changes things, you want to know about it.
    • pt-duplicate-key-checker
      When trying to optimize tables, removing useless indexes might be nice. Run this as an alarm every in a while and not at night.
    • pt-heartbeat
      Look at pacemaker.
    • pt-kill
      Very nice program. For me I generally don't need it. What I do is writes a python program which shows the highest time on queries first, or use Solar Winds or Relic or another tool, or use this bash command just to quickly scan the processlist
           echo PASSWORD="bad_password"  
     mysql -u root -p$PASSWORD -e "show full processlist" | grep -i "sleep" | less
    • pt-mysql-summary
      Nice and useful for reports, but doesn't have everything I want.