MariaDB High Performance

By Pierre MAVRO

Designing and maintaining a huge amount of data in production is not an easy task. Understanding how solutions work, what kind of differences exist between them, and how to get them working is necessary before designing a solution for production. MariaDB High Performance will help you avoid mistakes, bad designs, and wrong strategic choices.

This book will throw light on important and crucial subjects such as which hardware should be used in which situation and what the bottlenecks generally are. You will get acquainted with the latest addition to the MariaDB family, known as Spider, and you will get to know how data sharding is carried out across several MariaDB servers using Spider.

• Language: English
• Publisher: Packt Publishing
• Release date: Sep 23, 2014
• ISBN: 9781783981618

Book preview

Table of Contents

MariaDB High Performance

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Performance Introduction

MariaDB history

Choosing the appropriate hardware

Disks

SATA magnetic drives

SAS magnetic drives

Hybrid drives

SSDs

RAID and acceleration cards

RAID cards and levels

Fusion-io direct acceleration cards

Disk arrays

RAM

CPU

Architecture types and performances

BIOS power management optimization

C-States

P-States

Constructor name options

Power management optimization

cpufreq

cpuidle

Disk and filesystem optimization

Kernel disks' I/O schedulers

Partition alignment

SSD optimization

Filesystem options

SWAP

Dedicating hardware with cgroups

Manual solution

Automatic solution using the cgconfig daemon

Dedicating hardware optimization with NUMA

Migrating from MySQL to MariaDB

Introduction to MariaDB engines

Summary

2. Performance Analysis

Slow queries

The explain command

Slow query logs

The show explain command

Profiling

Performance schema

User statistics

Sysbench

Percona Toolkits

pt-query-digest

pt-stalk

pt-summary

pt-mysql-summary

pt-duplicate-key-checker

pt-index-usage

Process list progression

mytop

innotop

mysqlsla

Summary

3. Performance Optimizations

Resetting statistics

Global statistics

DNS connections

The DNS cache server

Maximum connections

The binlogs cache

Binlogs for transactional caches

Binlogs for nontransactional caches

Temporary tables

Open tables

The query cache

Understanding the query cache

Modifying the query cache

Optimizing storage engines

Summarizing your databases

InnoDB/XtraDB

Pool size and statistics

Redo logs

Transaction commits and logs

Buffer pool instances

The flush method

TokuDB

Installation

The flush method

Cache size

Transaction commits and logs

Temporary directory

Compression

MyISAM

Key buffer

Index

Engines

Types

mysqltuner

Summary

4. MariaDB Replication

How replication works

Configuring the master node

Preparing the master node

Configuring the slave node

Creating a slave

Using mysqldump

Using Xtrabackup

Checking the slave status

GTID replication

What is GTID

Configuring the master node

Preparing the master node

Configuring a GTID slave node

Creating a slave

Starting the slave

Checking the slave status

Migrating from classical to GTID replication

Parallel replication

Load balancing read transactions

Installing HAProxy

Configuring HAProxy

Checking health

Testing the configuration

Use cases and troubleshooting

SQL errors

Analyzing binlogs

GTID – switching a slave to master and recovering

Summary

5. WAN Slave Architectures

Cascade slaves

Speeding up replication performance for middle slaves

Restricting replications

Designing slave in multiple continents

SSL replication

Generating certificates

Building your own CA

Building your server certificate

Building your client certificates

Checking your certificates

Configuring MariaDB for SSL

Master SSL

Client SSL

Compression options

Summary

6. Building a Dual Master Replication

Dual master replication and risks

Installing and configuring a dual master

Automatic management

HAProxy

Learning about the maintenance mode

Using Unix Socket

Using HATop

Using the configuration file

Keepalived

Pacemaker or Percona Replication Manager

DRBD

How to repair a dual master replication

Summary

7. MariaDB Multimaster Slaves

Multimaster slave replication

Setting up a multisource replication

Other options

Summary

8. Galera Cluster – Multimaster Replication

How Galera Cluster works

Galera Cluster limitations

The basics of installation and configuration

Installation

Configuration files

MariaDB configuration

Galera configuration

First boot

Usages and understandings

Transfer methods

Using mysqldump

Using Xtrabackup

Using rsync

Dedicating a donor node

Starting after a complete blackout

Consensus clustering and maintenance

Garb – the quorum solution

Performance tuning

Parallel slave threads

Gcache size

Designing redundant architectures

Read and write nodes

Load balanced architecture

WAN replication

Disaster recovery

Tests and issues

Paused replication

Break Galera

Split-brain

Summary

9. Spider – Sharding Your Data

Configuring Spider

Creating your first shard

Sharding replication

Creating replicated shards

Spider HA monitoring

Recovering data after server failure

Performance tuning

Spider parameters

The bgs mode

The connection recycle mode

Statistics tables

Remote SQL logs

Number of shards

Summary

10. Monitoring

Single instance

Replication

Galera Cluster

Other monitoring solutions

Graphs

Logs

Summary

11. Backups

Using mysqldump

Compression

Using mysqlhotcopy

LVM

Snapshot

Removing snapshots

Rollback

Backup

Xtrabackup

Full backup

Incremental backup

Restoring from a full backup

Restoring from an incremental backup

Galera backup

Summary

Index

MariaDB High Performance

---

MariaDB High Performance

Copyright © 2014 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: September 2014

Production reference: 1190914

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-78398-160-1

www.packtpub.com

Credits

Author

Pierre MAVRO

Reviewers

David DaviXX CHANIAL

P. R. Karthik

Emilien Kenler

Joffrey MICHAÏE

Daniel Parnell

Dimitri Savineau

Commissioning Editor

Kunal Parikh

Acquisition Editor

Subho Gupta

Content Development Editor

Mohammed Fahad

Technical Editors

Dennis John

Sebastian Rodrigues

Copy Editors

Roshni Banerjee

Sarang Chari

Project Coordinator

Danuta Jones

Proofreaders

Maria Gould

Ameesha Green

Paul Hindle

Kevin McGowan

Elinor Perry-Smith

Indexers

Hemangini Bari

Priya Sane

Tejal Soni

Graphics

Sheetal Aute

Ronak Dhruv

Valentina D'silva

Disha Haria

Production Coordinators

Aparna Bhagat

Manu Joseph

Nitesh Thakur

Cover Work

Aparna Bhagat

About the Author

Pierre MAVRO lives in Joinville-le-Pont (a suburb of Paris). He's an open source software lover and has been working with Linux for more than 10 years now. Today, he works as a Senior DevOps Engineer at Red Hat / eNovance, where he designs and implements solutions for the Web and personal clouds (OpenStack). During the last few years, he has been designing high-availability infrastructures with performance tuning for a high-frequency trading company. He has also built geoclusters and developed tools to fit high-availability requirements for financial companies. He has worked on resolving issues on open source software for the French government. He has also provided training to several IT professionals on subjects such as Linux and MySQL/MariaDB.

I would really like to thank my wife and daughter, who encouraged and helped me to find the time to write this book. I would also like to thank my friend Joffrey and my colleague Dimitri for reviewing the technical part of the book. A big thanks goes to Packt Publishing for approaching me to write this book. And to finish, thanks to the employees of Packt Publishing I worked with (Subho, Neha, and Mohammed); thanks for showing patience. I would also like to thank the technical reviewers Daniel Parnell, P. R. Karthik, Emilien Kenler, and David DaviXX CHANIAL, who provided their valuable support and helped me enhance the quality of the content.

Writing this book was my first experience in editing; it was not an easy task, and therefore, thanks a lot to all who helped me in different ways to do so!

About the Reviewers

David DaviXX CHANIAL is a French autodidact system administrator and programmer. He has been setting up high-availability hosting solutions for years, especially using Gentoo Linux, Apache/Nginx, PHP, MySQL/MariaDB, and Python/Perl/C.

David sold the French company EuroWeb in 2011, which he had cofounded and managed on a technical level since 2003 (EuroWeb was into hosting, dedicated servers, managed services, and consulting). He spent some time working as a consultant for the company Magic Online that had acquired his old company.

Currently, in addition to working independently with his company DaviXX on projects using Ansible, MariaDB, Django, embedded systems, and some electronic systems, David holds the position of Director of System and Network at Believe Digital Group.

P. R. Karthik has a Bachelor's degree in Engineering in Electronics and Communications. He is an experienced MySQL database administrator and works for one of the Fortune 500 companies providing planning, architecture, and resource management solutions for mission-critical database applications, such as online advertising and e-commerce. He manages one of the biggest MySQL farms. He is a tech enthusiast and is socially connected with MySQL and open source communities, sharing his expertise and learning from other technologists in this field. He is a regular blogger at www.remotemysqldba.blogspot.in.

He has also worked on reviewing the book Getting Started with MariaDB, Packt Publishing.

I would like to thank my parents for their support and my colleagues for helping me in reviewing this book.

Emilien Kenler, after working on small web projects, began focusing on game development in 2008 while he was in high school. Until 2011, he worked for different groups and specialized in system administration.

In 2011, he founded a company that sold Minecraft servers while he was completing his engineering in Computer Science. He created a lightweight IaaS based on new technologies such as Node.js and RabbitMQ.

Thereafter, he worked at TaDaweb as a system administrator, building its infrastructure and creating tools to manage deployments and monitoring.

In 2014, he began a new adventure at Wizcorp, Tokyo. He will graduate in 2014 from the University of Technology of Compiègne.

He has also contributed as a reviewer on another book Learning Nagios 4, Packt Publishing (http://www.packtpub.com/learning-nagios-4/book).

Joffrey MICHAÏE joined MySQL AB / Sun as a consultant in 2009 and quickly became one of the most prominent consultants. He has since joined SkySQL and continues to spread the word as a principal consultant. His common duties include designing architectures, tuning the performance, and troubleshooting or migrating database installations using MariaDB and MySQL. When not in an airplane, Joffrey enjoys the nightlife in Barcelona.

Daniel Parnell has been messing around with computers from a very early age. Starting out with an AIM-65, he has used Commodore VIC-20, Commodore 64, Apple IIe, Commodore Amiga, an ICL Concurrent CP/M-86 machine, Apple Macintosh Plus, and various other PCs and Macs so far.

Recently, Daniel has been working on web applications for the healthcare industry using Ruby on Rails and is building a rich web application using JavaScript as the frontend and Erlang as the backend.

When Daniel is not coding or tinkering with electronic gadgets, he can be found spending time with his family. He lost his 6-year-old son last year to an untreatable neurodegenerative disorder called Batten disease.

www.PacktPub.com

Support files, eBooks, discount offers, and more

For support files and downloads related to your book, please visit www.PacktPub.com.

Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at for more details.

At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.

http://PacktLib.PacktPub.com

Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can search, access, and read Packt's entire library of books.

Why subscribe?

Fully searchable across every book published by Packt

Copy and paste, print, and bookmark content

On demand and accessible via a web browser

Free access for Packt account holders

If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view 9 entirely free books. Simply use your login credentials for immediate access.

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:

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

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

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

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

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

vagrant up

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 <[e-mail]>, 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 <[e-mail]> with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to