MySQL Optimization Training

Request more details:

submit request
Description:
This course will present detailed practical information on MySQL how to measure performance and how to optimize it and resolve bottlenecks.
Prerequisites:
Database experience, proficient at SQL
Profiling :
Topics:
How to measure performance.
A word about premature optimization.
Identifying bottlenecks.
Practical:
Measure performance parameters for a selection of queries on small, medium and large datasets using a selection of tools.
Database Components:
Topics:
This module will present each of the components in the database with a discussion on how each can affect performance.
Tables.
Views.
Transactions.
Indexes.
Constraints.
Stored Procedures.
Precompiled queries.
Materialized views.
Parameterised queries.
Triggers.
Functions.
Practical:
The various components will be presented and where appropriate their performance will be profiled.
Query Optimization:
Topics:
Query Execution Plan.
The EXPLAIN statement.
Join Optimization
- Cartesian Product.
- Match Elimination.
- Using joins in aggregation.
- Optimizing joins.
- Joins and sub-queries.
- Driving tables.
Indexes.
The slow query log.
Expensive operations.
MySql performance schema.
Practical:
A variety of queries and datasets will be presented.
These will be profiled and bottlenecks will be identified
Solutions to these bottlenecks will be implemented
The queries will be profiled again to verify that the change actually worked.
MySQL Server Optimization:
Topics:
MySQL server configuration options.
MySQL Table Types (Storage Engines).
Setting / Changing Engine - MYISAM, InnoDB.
MyISAM Tables.
InnoDB Tables.
HEAP or MEMORY Tables.
Practical:
Review how to view / set MySQL server parameters.
Changing the engine.
Compare some queries between engines.
Upgrading MYSQL:
Preparing for an upgrade
Making a backup (configuration, data directory, data dump)
Get list of all drivers and verify compatibility with new version.
Perform upgrade.
Verify upgrade was successful.
Practical:
We will start with a vm that has an older version of MYSQL installed and is running an application that uses an old driver. We will do a backup of everything, perform the upgrade, upgrade the driver and verify that the upgrade was successful and that the application still works.
Replication:
Topics:
What is Replication?
Configuring Replication.
Monitoring and administering a replicated server.
Practical:
Using Virtual Box a mysql will be configured to run across two separate VMs. Some configuration, monitoring and administration tasks will be demonstrated.
Best Practices / Common Anti-Patterns:
Topics:
A selection of best practices.
A selection of anti-patterns.
Practical:
These will be accompanied by a practical example and where appropriate the profiling techniques will be used to show / measure the problem.
Spatial Data:
Topics:
Spatial data types.
Storing locations.
Spatial functions.
Calculating distance.
Examples:
Storing locations.
Finding all records within a certain distance and sort them in order of closeness from a position.
Find all records in a polygon and sort them in order of distance from a position.