Book Image

MariaDB High Performance

By : Pierre Mavro
Book Image

MariaDB High Performance

By: Pierre Mavro

Overview of this book

Table of Contents (18 chapters)
MariaDB High Performance
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface
Index

Preface

What is MariaDB? If you bought this book, it is assumed that you already know a bit; anyways, a quick reminder and a short introduction will help us understand certain things.

MariaDB is a fork (drop-in replacement) of MySQL. MySQL was acquired by Sun Microsystems in 2008. Then, Oracle acquired Sun Microsystems in 2009 with MySQL included.

For several reasons, Michael "Monty" Widenius (founder of MySQL) decided to fork MySQL and to create a company for it called Monty Program AB; that's how MariaDB was born (Maria is the name of the second daughter of Michael Widenius).

In December 2012, the MariaDB foundation was brought into existence to avoid any company acquisition like what had happened in the past for MySQL.

SkySQL is a company formed of ex-MySQL executives and investors who deliver services around MySQL/MariaDB. In April 2013, SkySQL and Monty Program AB were merged, because for a company to switch to MariaDB without support was problematic. But since the merge, it's been possible.

MariaDB has new interesting features, better testing, performance improvements, and bug fixes that unfortunately are not available in MySQL. For example, some optimizations come from Google, Facebook, Twitter, and so on.

Please remember that MariaDB is a full open source project and you're welcome to contribute.

What this book covers

Chapter 1, Performance Introduction, describes common hardware solutions to help you choose the best solution for your needs; furthermore, it introduces system optimization and describes how to migrate from MySQL to MariaDB.

Chapter 2, Performance Analysis, introduces tools to find performance issues and shares basic best practices.

Chapter 3, Performance Optimizations, talks about how to find bottlenecks, how to tune caches, and also introduces some engines.

Chapter 4, MariaDB Replication, explains how to set up MariaDB replications, how to scale with HAProxy, and the benefits of replication.

Chapter 5, WAN Slave Architectures, helps us understand the problems that arise in WAN replications and how to work with them.

Chapter 6, Building a Dual Master Replication, describes what the benefits are of this kind of architecture and how to set it up using DRBD, Pacemaker, PRM, and so on.

Chapter 7, MariaDB Multimaster Slaves, introduces the benefits of using the replication features of MariaDB 10.

Chapter 8, Galera Cluster – Multimaster Replication, describes the benefits and the way to deploy a Galera Cluster.

Chapter 9, Spider – Sharding Your Data, explains how to achieve better performance in sharding your data.

Chapter 10, Monitoring, describes what kind of elements are important to monitor on a single instance, replication, or Galera Cluster.

Chapter 11, Backups, introduces several ways to create backups and helps you choose the best method for your needs.

What you need for this book

As you proceed with this book, you will see a lot of features, solutions, and practical exercises that require technical tests. It's not often easy to test everything in the correct environment.

Many feel that preparing an environment is a waste of time, and they are right! To avoid it and concentrate on the content of the book, we'll use virtual machines. To make it fast and simple, we're going to use VirtualBox and Vagrant. If you are not acquainted with these tools, don't worry, we will show you how to use them here.

These tools will help you test everything very quickly (a few seconds/minutes). The advantages of both tools are:

  • They are free

  • They can run on Linux, Mac OS X, and Windows

  • Fast instance provisioning

To install them, go to the official websites and download and install them on your current infrastructure:

  • VirtualBox: https://www.virtualbox.org/

  • Vagrant: http://www.vagrantup.com/

In this book, every exercise will run on Debian GNU/Linux Wheezy amd64 version on a VirtualBox. That's why, after installing both the latest versions of those tools, I suggest you work in a separate folder/box per exercise:

  1. Create a folder named MariaDB that will contain all the exercises of this book.

  2. Inside that folder, create a subfolder named Chapter X, where X is the chapter number.

  3. Inside the Chapter X subfolder, create another subfolder named Exercise X, where X is the name of the section.

  4. Place the appropriate content of the Vagrantfile in the Exercise X folder.

  5. Inside that folder, power up machines (you absolutely need to be in to perform actions on the virtual machines):

    vagrant up
    
  6. And access them in the following manner:

    vagrant ssh (for a single machine)
    vagrant ssh machine-name (for multiple machines)
    

You're now ready for the exercises. When the exercises finish and you want to get your disk space back, you can stop and remove them with the following command:

vagrant halt
vagrant destroy

Then, you can remove the current folder.

Who this book is for

This book is for anyone who is already familiar with MariaDB, has good system knowledge, and wants to scale or set up a high availability MariaDB infrastructure. It will be especially useful for system architects, senior system administrators, or DBAs.

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "This engine is a drop-in replacement for the FEDERATED engine. It uses libmysql to talk to an RDBMS."

A block of code is set as follows:

# -*- mode: ruby -*-
# vi: set ft=ruby :
ENV['LANG'] = 'C'

# 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 => :mysqlserver },

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
               Master_SSL_Crl: /etc/mysql/ssl/cacert.pem
           Master_SSL_Crlpath:
                   Using_Gtid: No

Any command-line input or output is written as follows:

MariaDB [(none)]> show global variables like 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 33554432 |
+----------------+----------+
1 row in set (0.00 sec)

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "With HP hardware, a Ctrl + A in the BIOS shows an additional Services Options menu."

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to , and mention the book title via the subject of your message.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

Errata

Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

Questions

You can contact us at if you are having a problem with any aspect of the book, and we will do our best to address it.