Chapter 2. Performance Analysis

In this chapter, you'll get recommendations for how to achieve good performance, what tools to use, and MariaDB internal presentations for analyses purposes. The goal of this chapter is to help you find where a performance issue comes from.

The performance goal takes time and requires a lot of tests to make things as performant as possible. There are many situations, many possibilities, and different architectures, and all these complex things need to be answered with many tools. These tools will help you diagnose performance issues as fast as possible to find complex issues.

Tools are not the only solutions. You can do many other things to optimize your databases:

• Use good index types when it's necessary. Too many indexes will slow down your databases.
• Set the best column data type. For example, do not use a char column data type if it stores only integers.
• Avoid duplicated keys.
• Optimize your SQL queries as much as possible.

If these points are correctly taken into account, the number of performance issues can be minimized.

For this chapter, some features are only available from MariaDB 10. That's why you need to change your repository information. In addition, we're going to use Percona tools and the Percona repositories need to be set up. So, here is the Vagrantfile for it:

# -*- mode: ruby -*-
# vi: set ft=ruby :
# Vagrantfile API/syntax version. Don't touch unless you know what you're doing!
#
VAGRANTFILE_API_VERSION = "2"

# Insert all your Vms with configs
boxes = [
    { :name => :mariadb },
]

$install = <<INSTALL
aptitude update
DEBIAN_FRONTEND=noninteractive aptitude -y -o Dpkg::Options::="--force-confdef" -o Dpkg::Options::="--force-confold" install python-software-properties
apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
add-apt-repository 'deb http://ftp.igh.cnrs.fr/pub/mariadb/repo/10.0/debian wheezy main'
add-apt-repository 'deb http://repo.percona.com/apt wheezy main'
echo 'Package: *
Pin: release o=Percona Development Team
Pin-Priority: 100' > /etc/apt/preferences.d/00percona.pref
aptitude update
DEBIAN_FRONTEND=noninteractive aptitude -y -o Dpkg::Options::="--force-confdef" -o Dpkg::Options::="--force-confold" install mariadb-server percona-toolkit bzr
INSTALL

Vagrant::Config.run do |config|
  # Default box OS
  vm_default = proc do |boxcnf|
    boxcnf.vm.box       = "deimosfr/debian-wheezy"
  end

  # For each VM, add a public and private card. Then install Ceph
  boxes.each do |opts|
    vm_default.call(config)
    config.vm.define opts[:name] do |config|
        config.vm.customize ["modifyvm", :id, "--cpus", 2]
        config.vm.host_name = "%s.vm" % opts[:name].to_s
        config.vm.provision "shell", inline: $install
    end
  end
end

In addition, in some of this chapter's examples, free and open source software will be taken for their database analysis, such as MediaWiki (Tiny Tiny RSS (http://tt-rss.org).