All Tutorials

MySQL High Performance Tuning Guide

MySQL High Performance Tuning Guide Tips for Tuning MySQL like a Pro | Learn how to prevent MySQL performance problems
MySQL High Performance Tuning Guide Tips for Tuning MySQL like a Pro | Learn how to prevent MySQL performance problems

MySQL High Performance Tuning Guide

Tips for Tuning MySQL like a Pro | Learn how to prevent MySQL performance problems

What you’ll learn

MySQL High Performance Tuning Guide

  • Understand how MySQL optimizes and executes queries.
  • Find where MySQL spends most time on your query, and Why?
  • Finding those few queries that are responsible for most of the load on the system.
  • How to find the relevant information out of the plethora of performance views, and metrics available in MySQL?
  • How queries are automatically planned before execution, based on statistics and cost decisions?
  • Understand what a Clustered Index is?
  • How to choose the optimal Primary Key?
  • Find which tables are in need for an index, and which tables are better without one.
  • How we can help the MySQL Optimizer make better decisions?
  • Which are the common pitfalls, when using indexes on Multiple Columns?
  • Which are the use-cases, that make Redundant Indexes useful, and when to avoid them?
  • Cover the Best Practices for making Server Configurations
  • Which are the three options that MySQL is most likely to benefit, from non-default values, and also how to set them?
  • Data lifecycle in InnoDB, so that we have background knowledge when making configuration changes.
  • Get an overview of why Transactions and Locks are needed and some ways to reduce their impact.
  • Introduction to MySQL Scaling Options
  • Fundamental Concepts for Scaling Reads with Replication
  • How to Partition Data in MySQL
  • Fundamental Concepts for Scaling Writes with Queueing and Sharding

Requirements

  • You need access to a Windows/Mac/Linux PC with 10GB of free disk space
  • Basic familiarity with database objects such as tables and indexes is expected
  • Some familiarity with Linux will be helpful

Description

MySQL Architecture

  • View the steps involved in Query Execution
  • How the Cient/Server Protocol affects MySQL
  • Understand how the Query Optimizer does its job
  • How the Storage Engine Layer makes MySQL unique

Finding Candidates for Query Optimizations

  • Making use of The Performance Schema, which is a gold mine for spotting querying for optimization because it allows us to measure where the time goes
  • Finding the few queries that are responsible for most of the load on the system. Checking to the top 1000 queries it’s usually not worth your time to optimize.
  • How to find the relevant information out of the plethora of performance views and metrics available in MySQL
  • Understand why UPDATE and DELETE statements are also reads even though they are primarily write statements. This will have an impact on I/O, and therefore to the response time.

Analyzing the Queries

  • Understand why MySQL creates a query plan, before each query request
  • How to read query plans, and understand how each of the underlying node types works on MySQL
  • A series of examples, to show how the EXPLAIN output can be used, to identify where MySQL spends time on your query, and why
  • Which are the important pieces of information, to look for in order to spot performance issues

The Clustered Index and Choosing the Primary Key

  • When we work with index-organized tables, as the ones in MySQL, the choice of the primary index, is very important.
  • We’ll see how the primary key, can impact the amount of random, or sequential I/O, the size of secondary indexes, and how many pages need to be read into the buffer pool.
  • We’re going to understand, why an optimal primary key, with respect to the clustered index, is as small (in bytes) as possible, keeps increasing monotonically
    , and groups the rows we query frequently, and within a short distance of each other.

Indexing for performance

  • Which are the main function of an index?
  • We’ll see exactly which tables are in need for an index, and which tables are better without one.
  • We’ll check how MySQL relies on statistics to choose the best index, and how we can help it to make better decisions.

Composite Indexes

  • Common mistakes of composite indexes
  • How to decide the order of columns for multicolumn indexes
  • Be able to reason through how indexes work, and to choose them based on that understanding, not on rules of thumb
  • Which are the use-cases, that make Redundant Indexes useful, and when to avoid them.
  • Work on some benchmarks, to check the actual performance in numbers.

MySQL Server Configurations

  • Check how we should approach MySQL configuration changes by considering some best practices, so that we can successfully tune MySQL.
  • Which are the three options that MySQL is most likely to benefit, from non-default values, and also how to set them.
  • Go through an overview of the data lifecycle in InnoDB, so that we have background knowledge when making configuration changes.
  • Adjust parameters that are important for certain use-cases, for example, buffer pool instances for high concurrent workloads, and how to make use of parallel execution for certain queries.

MySQL Transactions and Locks

  • Transactions are very important to ensure data integrity but when used wrong they can cause severe performance issues.
  • Get an overview of why locks are needed and some ways to reduce their impact.
  • How indexes, splitting the transaction and the isolation levels can help to reduce the number of locks
  • Monitoring options and what reporting tables to check to spot lock problems.

Introduction to Scaling MySQL

  • What it means to scale MySQL and, we’ll walk through the different axes where we may need to scale.
  • Understand how to scale different kinds of loads
  • Replication and why we need to make use of Read-Pools, Health Checks, and Service Discovery
  • Which are the main concerns and pitfalls when deciding to Shard the database
  • How to partition data in MySQL
  • Which are the use-cases and advantages for using a Queue
  • Fundamental Concepts for Scaling Writes with Sharding

Who this course is for:

  • Developers interested in advanced database internals that impact application design and performance
  • Database administrators
  • Everyone interested in building better MySQL applications
  • Last updated 8/2021

Tuning Apache Spark: Powerful Big Data Processing Recipes Course

Download Now Content From: https://www.udemy.com/course/mysql-high-performance-tuning-guide/

Advertisement

Categories