You are previewing MariaDB High Performance.
O'Reilly logo
MariaDB High Performance

Book Description

Familiarize yourself with the MariaDB system and build high-performance applications

In Detail

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.

What You Will Learn

  • Set up master/slave classical replications and make them scale easily, even over WAN
  • Create a dual master replication with load balancer and cluster software
  • Shard your data using the Spider engine
  • Grow your write infrastructure by setting up a Galera Cluster
  • Make your Galera Cluster and replication work together to build complex solutions
  • Optimize your engine and identify bottlenecks
  • Compare the Galera and MySQL Cluster
  • Graph your data and tools solution
  • Build a Galera disaster recovery solution
  • Downloading the example code for this book. 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.

    Table of Contents

    1. MariaDB High Performance
      1. Table of Contents
      2. MariaDB High Performance
      3. Credits
      4. About the Author
      5. About the Reviewers
      6. www.PacktPub.com
        1. Support files, eBooks, discount offers, and more
          1. Why subscribe?
          2. Free access for Packt account holders
      7. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Conventions
        5. Reader feedback
        6. Customer support
          1. Downloading the example code
          2. Errata
          3. Piracy
          4. Questions
      8. 1. Performance Introduction
        1. MariaDB history
        2. Choosing the appropriate hardware
          1. Disks
            1. SATA magnetic drives
            2. SAS magnetic drives
            3. Hybrid drives
            4. SSDs
          2. RAID and acceleration cards
          3. RAID cards and levels
          4. Fusion-io direct acceleration cards
          5. Disk arrays
          6. RAM
          7. CPU
        3. Architecture types and performances
        4. BIOS power management optimization
          1. C-States
          2. P-States
          3. Constructor name options
          4. Power management optimization
            1. cpufreq
            2. cpuidle
          5. Disk and filesystem optimization
            1. Kernel disks' I/O schedulers
            2. Partition alignment
            3. SSD optimization
            4. Filesystem options
            5. SWAP
          6. Dedicating hardware with cgroups
            1. Manual solution
            2. Automatic solution using the cgconfig daemon
          7. Dedicating hardware optimization with NUMA
        5. Migrating from MySQL to MariaDB
        6. Introduction to MariaDB engines
        7. Summary
      9. 2. Performance Analysis
        1. Slow queries
        2. The explain command
        3. Slow query logs
        4. The show explain command
        5. Profiling
        6. Performance schema
        7. User statistics
        8. Sysbench
        9. Percona Toolkits
          1. pt-query-digest
          2. pt-stalk
          3. pt-summary
          4. pt-mysql-summary
          5. pt-duplicate-key-checker
          6. pt-index-usage
        10. Process list progression
        11. mytop
        12. innotop
        13. mysqlsla
        14. Summary
      10. 3. Performance Optimizations
        1. Resetting statistics
        2. Global statistics
        3. DNS connections
          1. The DNS cache server
        4. Maximum connections
        5. The binlogs cache
          1. Binlogs for transactional caches
          2. Binlogs for nontransactional caches
        6. Temporary tables
        7. Open tables
        8. The query cache
          1. Understanding the query cache
          2. Modifying the query cache
        9. Optimizing storage engines
          1. Summarizing your databases
          2. InnoDB/XtraDB
            1. Pool size and statistics
            2. Redo logs
            3. Transaction commits and logs
            4. Buffer pool instances
            5. The flush method
          3. TokuDB
            1. Installation
            2. The flush method
            3. Cache size
            4. Transaction commits and logs
            5. Temporary directory
            6. Compression
          4. MyISAM
            1. Key buffer
        10. Index
          1. Engines
          2. Types
        11. mysqltuner
        12. Summary
      11. 4. MariaDB Replication
        1. How replication works
          1. Configuring the master node
          2. Preparing the master node
          3. Configuring the slave node
          4. Creating a slave
            1. Using mysqldump
            2. Using Xtrabackup
          5. Checking the slave status
        2. GTID replication
          1. What is GTID
          2. Configuring the master node
          3. Preparing the master node
          4. Configuring a GTID slave node
          5. Creating a slave
          6. Starting the slave
          7. Checking the slave status
        3. Migrating from classical to GTID replication
        4. Parallel replication
        5. Load balancing read transactions
          1. Installing HAProxy
          2. Configuring HAProxy
          3. Checking health
          4. Testing the configuration
        6. Use cases and troubleshooting
          1. SQL errors
          2. Analyzing binlogs
          3. GTID – switching a slave to master and recovering
        7. Summary
      12. 5. WAN Slave Architectures
        1. Cascade slaves
          1. Speeding up replication performance for middle slaves
        2. Restricting replications
        3. Designing slave in multiple continents
        4. SSL replication
          1. Generating certificates
            1. Building your own CA
            2. Building your server certificate
            3. Building your client certificates
            4. Checking your certificates
          2. Configuring MariaDB for SSL
            1. Master SSL
            2. Client SSL
        5. Compression options
        6. Summary
      13. 6. Building a Dual Master Replication
        1. Dual master replication and risks
        2. Installing and configuring a dual master
        3. Automatic management
          1. HAProxy
            1. Learning about the maintenance mode
              1. Using Unix Socket
              2. Using HATop
              3. Using the configuration file
            2. Keepalived
          2. Pacemaker or Percona Replication Manager
          3. DRBD
        4. How to repair a dual master replication
        5. Summary
      14. 7. MariaDB Multimaster Slaves
        1. Multimaster slave replication
          1. Setting up a multisource replication
          2. Other options
        2. Summary
      15. 8. Galera Cluster – Multimaster Replication
        1. How Galera Cluster works
        2. Galera Cluster limitations
        3. The basics of installation and configuration
          1. Installation
          2. Configuration files
            1. MariaDB configuration
            2. Galera configuration
          3. First boot
        4. Usages and understandings
          1. Transfer methods
            1. Using mysqldump
            2. Using Xtrabackup
            3. Using rsync
          2. Dedicating a donor node
          3. Starting after a complete blackout
          4. Consensus clustering and maintenance
          5. Garb – the quorum solution
          6. Performance tuning
            1. Parallel slave threads
            2. Gcache size
        5. Designing redundant architectures
          1. Read and write nodes
          2. Load balanced architecture
          3. WAN replication
          4. Disaster recovery
        6. Tests and issues
          1. Paused replication
          2. Break Galera
          3. Split-brain
        7. Summary
      16. 9. Spider – Sharding Your Data
        1. Configuring Spider
        2. Creating your first shard
        3. Sharding replication
          1. Creating replicated shards
          2. Spider HA monitoring
          3. Recovering data after server failure
        4. Performance tuning
          1. Spider parameters
            1. The bgs mode
            2. The connection recycle mode
            3. Statistics tables
            4. Remote SQL logs
          2. Number of shards
        5. Summary
      17. 10. Monitoring
        1. Single instance
        2. Replication
        3. Galera Cluster
        4. Other monitoring solutions
          1. Graphs
          2. Logs
        5. Summary
      18. 11. Backups
        1. Using mysqldump
          1. Compression
        2. Using mysqlhotcopy
        3. LVM
          1. Snapshot
            1. Removing snapshots
            2. Rollback
          2. Backup
        4. Xtrabackup
          1. Full backup
          2. Incremental backup
          3. Restoring from a full backup
          4. Restoring from an incremental backup
        5. Galera backup
        6. Summary
      19. Index