{"id":2298,"date":"2012-02-05T10:58:15","date_gmt":"2012-02-05T15:58:15","guid":{"rendered":"http:\/\/www.bytebot.net\/blog\/?p=2298"},"modified":"2012-02-05T11:00:53","modified_gmt":"2012-02-05T16:00:53","slug":"managing-mysql-with-percona-toolkit-by-frederic-descamps","status":"publish","type":"post","link":"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/managing-mysql-with-percona-toolkit-by-frederic-descamps","title":{"rendered":"Managing MySQL with Percona Toolkit by Fr\u00c3\u00a9d\u00c3\u00a9ric Descamps"},"content":{"rendered":"

Fr\u00e9d\u00e9ric Descamps of Percona<\/a>.<\/p>\n

Percona Toolkit is Maatkit & Aspersa combined. Opensource and the tools are very useful for a DBA.<\/p>\n

You need Perl, DBI, DBD::mysql, Term::ReadKey. Most tools are written in Perl, and whatever is in Bash is being re-written in Perl. There is also a tarball or RPM or DEB packages.<\/p>\n

Know your environment. The hardware & OS are crucial for you to know. How much memory\/CPU do you use? Do you use swap? Is this a physical\/virtual machine? Do you have free space? What kind of RAID controller? Volumes? Disk? What about the network interfaces? What IO schedulers are used? Which filesystem is the data stored on?\u00a0To answer all that, just use pt-summary<\/a><\/tt>.<\/p>\n

Know your MySQL environment. Version? Build? How many databases? Where is the data directory? What about replication? What are key InnoDB settings? Storage engine in use? Index type? Foreign keys? Full text indexes? To answer all this and more use pt-mysql-summary<\/tt><\/a>.<\/p>\n

pt-slave-find shows you the topology and replication hierarchy of your MySQL replication instances. An inventory of replicas!<\/p>\n

Where is my disk I\/O going? Use pt-diskstats<\/a><\/tt> which is an improved iostat. There is pt-ioprofile<\/a><\/tt> but it can be dangerous in production.<\/p>\n

Now its time to get more intimate with your database. Let’s try to find the answer to these questions: how are the indexes used? Are there duplicate keys? Which queries are eating most of the resources? You can use pt-duplicate-key-checker<\/tt><\/a> to check for duplicate\/redundant indexes or foreign keys. pt-index-usage<\/tt><\/a> can tell you which indexes are unused. If you think you have bad SQL, check out pt-query-advisor<\/tt><\/a>.<\/p>\n

You can use pt-query-digest<\/tt><\/a> to analyze the slow query log and show a profile of the workload. You mostly use this with slow query logs & tcpdump’s. Be careful when you have dropped packets — results may tend to be fake then!<\/p>\n

After all this, its time to maintain your environment.<\/p>\n

pt-deadlock-logger<\/tt><\/a> checks InnoDB status to log MySQL deadlock information. It needs to run continually to capture things.<\/p>\n

pt-fk-error-logger<\/tt><\/a> extracts and logs MySQL foreign key errors.<\/p>\n

pt-online-schema-change<\/a><\/tt> to alter tables. It makes a “shadow copy” and swaps them. Extremely useful for large, long-running ALTER. Facebook uses the same technique.<\/p>\n

Validate your upgrades as upgrades are the leading cause of downtime. Are queries using different indexes? Is query execution plan different? New errors? See pt-upgrade<\/a><\/tt> for this. Best to run this on a third machine (i.e. the old machine and a new machine to see how it goes).<\/p>\n

Verify replication integrity – pt-table-checksum<\/a><\/tt>. Perform an online replication consistency check or checksum MySQL tables efficiently on one or many servers. Use it routinely (mandatory for 95% of MySQL users). Put it in a weekly crontab. Repair differences with pt-table-sync<\/tt>.<\/p>\n

Repair out-of-sync replicas – pt-table-sync<\/a><\/tt><\/p>\n

Measure delay acfurately – pt-heartbeat<\/a><\/tt><\/p>\n

Deliberately delay replication – pt-slave-delay<\/a><\/tt><\/p>\n

Watch & restart MySQL replication after errors – pt-slave-restart<\/tt><\/a><\/p>\n

When there are problems, get the symptoms when it hurts. Look at pt-stalk<\/a><\/tt> (wait for a condition to occur them begin collecting data – eg. everytime the threads go over 2,000 you have a problem, so it collects stuff – it calls pt-collect<\/tt>), pt-collect<\/a><\/tt> (collect information from a server for some period of time), and pt-sift<\/a><\/tt>.<\/p>\n

pt-mext<\/a><\/tt> looks at many samples of MySQL SHOW GLOBAL STATUS<\/tt> side-by-side. Default STATUS shows counter since the MySQL instances started. It is very helpful to see a delta of recent activity.<\/p>\n

The future: pt-query-digest<\/tt><\/a> will do query reviews; pt-stalk<\/tt> will do “magical fault detection algorithm”. Its all opensource and its all on Launchpad at lp:percona-toolkit<\/a>.<\/p>\n

Share this:<\/h3>