Webpulp.tv

Deep dive into MySQL with the experts at Percona

I interviewed Baron Schwartz from Percona. We talked mostly about their field of expertise, which is MySQL.

Show Notes

Percona provides various types of MySQL and LAMP related services and consultancy for companies.

It was founded by Peter Zaitsev and Vadim Tkachenko.

Baron joined Percona in April 2008 as a consultant. He subsequently was the ‘Direction of Consulting’ and the ‘VP of Consulting’. He is now the ‘Chief Performance Architect’ in Percona.

The current VP of Consulting is Espen Braekken

Percona has its own version of MySQL.

Percona covers numerous ‘big-name’ clients from all over USA, Canada and a vast region of Europe.

Percona server started out as a patch - a performance enhancing tool, written by their Polish consultant Maciek Dobrzanski.

The patch later on got developed into a much more complete product - XtraDB.

There is no compiling or plug-in hassle with XtraDB as Percona server comes with XtraDB complied into it.

Choosing XtraDB over InnoDB and Percona server over MySQL can give you - operational enhancements and performance & scalability.

It also provides additional instrumentations and monitorability of the server.

By default, Percona server with XtraDB is faster than MySQL.

When web companies need to upgrade their DB management system, they go to Percona.

Percona is also asked for help when a company doesn’t have an in-house DBA.

As database hardwares got more powerful and systems grew bigger, people started to notice various types of new problems.

Baron has written a toolkit called Aspersa which helps by gather diagnostic data to solve unorthodox problems like server lock-ups.

Baron says whenever solving a problem, the simple operating system tools can provide precious diagnostic informations.

But a common mistake that many DBAs make is the temptation of drawing conclusion from only the data that is available to you.

That’s why the Percona team has ended up building many efficient diagnostic tools of their own (like Aspersa and Maatkit) which serves their particular purposes.

Aspersa is basically a collection of scripts.

Baron names two of them - summary and mysql-summary.

These tools summarize the system status, what’s installed in them, how it’s configured, etc.

Aspersa even understands most of the popular Raid-controllers.

Baron then talks about server memory, memory related tuner tools and Cache-Hit-Ratio.

Server performance is mostly about time - It’s not about the number of operations to a cache; It’s about how expensive it is to miss that cache.

Most of the tuner tools are based on simple rules of thumb - telling you to add more memory or increase cache size; where there could be other, more efficient logical solutions to optimize performance.

Writing better index and rebuilding the query execution plan can also help optimize performance dramatically. His book High Performance MySQL, Second Edition goes deep into this topic.

The book also explores topics like replication, high availability, scaling, backup-recovery, operations, tools to help you with your tasks, etc.

For backup and restore purposes, the percona server comes with a tool called Xtrabackup. Being designed for XtraDB, it works fine with InnoDB as well.

Xtrabackup is basically an open-source reimplementation of InnoDB’s Hotbackup tool. As its not proprietary, it’s directly linked to standard MySQL command-line client libraries.

Baron then explains why you should go for Xtrabackup over Mysqldump.

With Mysqldump, when the dump size surpasses a couple hundred gigabytes, the restore process could take days if not months. On the other hand, Xtrabackup works fine with such large chunks of data.

For people who want to learn more about high performance MySQL, Baron recommends to visit Mysqlperformanceblog.com.