This course will present detailed practical information on MySQL how to measure performance and how to optimize it and resolve bottlenecks.
Cost: Price on application
Duration: 2 Days
This course is not available as part of our public schedule but can be provided on a customised client specific basis.
Database experience, proficient at SQL
How to measure performance.
A word about premature optimization.
Measure performance parameters for a selection of queries on small, medium and large datasets using a selection of tools.
This module will present each of the components in the database with a discussion on how each can affect performance.
The various components will be presented and where appropriate their performance will be profiled.
Query Execution Plan.
The EXPLAIN statement.
- Cartesian Product.
- Match Elimination.
- Using joins in aggregation.
- Optimizing joins.
- Joins and sub-queries.
- Driving tables.
The slow query log.
MySql performance schema.
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:
MySQL server configuration options.
MySQL Table Types (Storage Engines).
Setting / Changing Engine - MYISAM, InnoDB.
HEAP or MEMORY Tables.
Review how to view / set MySQL server parameters.
Changing the engine.
Compare some queries between engines.
Preparing for an upgrade
Making a backup (configuration, data directory, data dump)
Get list of all drivers and verify compatibility with new version.
Verify upgrade was successful.
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.
What is Replication?
Monitoring and administering a replicated server.
Using Virtual Box a mysql will be configured to run across two separate VM’s. Some configuration, monitoring and administration tasks will be demonstrated.
Best Practices / Common Anti-Patterns:
A selection of best practices.
A selection of anti-patterns.
These will be accompanied by a practical example and where appropriate the profiling techniques will be used to show / measure the problem.
Spatial data types.
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.