Professional Training

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.