10. Table Partitioning | Mastering MariaDB

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

Mastering MariaDB

By : Razzoli

3.8 (10)

Mastering MariaDB

3.8 (10)

By: Razzoli

Overview of this book

This book is intended for intermediate users who want to learn how to administrate a MariaDB server or a set of servers. It is aimed at MariaDB users, and hence working knowledge of MariaDB is a prerequisite.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Free Chapter

1. Understanding the Essentials of MariaDB

1. Understanding the Essentials of MariaDB

The MariaDB architecture

The command-line client

Storage engines

Logs

MariaDB caches

InnoDB data structures

Authentication and security

The information_schema database

The performance_schema database

Compatibility with MySQL and other DBMS

MariaDB resources

Summary

2. Debugging

2. Debugging

Understanding error conditions in MariaDB

The diagnostics area

The GET DIAGNOSTICS statement

The error log

System logs

The general query log

Maintenance of the server logs

The SQL_ERROR_LOG plugin

Tips on debugging stored programs

Debugging stored programs using the SQL_ERROR_LOG plugin

Summary

3. Optimizing Queries

3. Optimizing Queries

The slow query log

Explaining the pt-query-digest command from Percona Toolkit

Introducing indexes

Working with the EXPLAIN statement

Summary

4. Transactions and Locks

4. Transactions and Locks

The InnoDB locks

Reads consistency

Deadlocks

Transactions

Metadata locks

Summary

5. Users and Connections

5. Users and Connections

User accounts

Setting permissions using roles

Connecting MariaDB through Secure Socket Layer

Authentication plugins

Monitoring connections

Summary

6. Caches

6. Caches

InnoDB caches

MyISAM key cache

Aria page cache

The query cache explained

The table open cache

Per-session buffers

Summary

7. InnoDB Compressed Tables

7. InnoDB Compressed Tables

An overview of the InnoDB compression

InnoDB compression requirements

Explaining the file-per-table mode

Creating InnoDB compressed tables

Explaining the implementation of the InnoDB compression

Monitoring the InnoDB compression performance

Other compression solutions

Summary

8. Backup and Disaster Recovery

8. Backup and Disaster Recovery

Types of backups

Complete and incremental backups

Creating a dump file with mysqldump

Delimited text backups

Physical backups

Using the binary log for incremental backups

Percona XtraBackup

Securing backups

Repairing tables

Summary

9. Replication

9. Replication

An overview of replication

Choosing a binary log format

Configuring replication

Multisource replication

Replication logs

Checking the replication for errors

Troubleshooting

Summary

10. Table Partitioning

10. Table Partitioning

Support for partitioning

Partitioning types and expressions

Administering partitioned tables

Partitions' physical files

Query optimizations

Summary

11. Data Sharding

11. Data Sharding

Distributing files between multiple disks

The FEDERATEDX and CONNECT storage engines

The SPIDER storage engine

Summary

12. MariaDB Galera Cluster

12. MariaDB Galera Cluster

MariaDB Galera Cluster key concepts

Setting up a cluster

Starting the nodes

Configuring the cluster

Monitoring and troubleshooting

Load balancing

Listing the limitations of Galera Cluster

Galera Load Balancer

Summary

Index

Index

Administering partitioned tables

Partitions support the same maintenance operations that are supported for regular tables, such as repairing and defragmenting, plus other specific operations. MariaDB provides a set of SQL extensions that allow us to perform all necessary maintenance tasks. It also provides some system tables that store metadata about the partitions. This section covers these topics.

Obtaining information about partitions

MariaDB provides several ways to obtain information about partitions.

The output of SHOW TABLE STATUS and the Create_options column contains the partitioned string for partitioned tables.

The easiest way to get human-readable information about partitions' definitions is running SHOW CREATE TABLE.

The PARTITIONS table in the INFORMATION_SCHEMA database contains information about the partitions. Subpartitioned tables have a row for each subpartition; other tables have a row for each partition. Some columns are identical to the columns in the TABLES table. The most...

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Mastering MariaDB

Search

Your notes and bookmarks