- Montréal 2010
This tutorial teaches you how to build a High Performance, Scalable Application around MySQL. While it can be critical of stupid decisions some ORMs make, it does not relate to any specific programming language. You'll learn about MySQL concepts, what it's good (or bad at), and how to design your schema and queries to solve a large array of theoretical problems. The examples I use come from various web/social networking challenges, the lessons that come out of them have been designed to be as generic as possible.
Introduction (45 minutes)
- What application metrics you need for performance.
- How to interpret results.
- Common MySQL Myths dispelled.
Application Architecture Concepts (1 hour)
- Replication Topologies
- How to work with Replication from your Application
- What you need to know about sharding
- Connection Pooling, Persistent Connections and connection policies
- Make the Application able to deal with different versions of schema.
Improving Query Performance (1 hours)
- Using the EXPLAIN command in MySQL.
- Optimizing example queries from a sample schema.
"Textbook Problems" (45 Minutes)
- Over Querying
- "Jack of all trades" tables.
- The XML/Serialized Data problem.
- Goliath tables.
- Very wide tables.
- The over promise of ORMs.
- DoS by deep search.
- Unlimited freedom to sort.
- Single hot column on wide table.
- Delayed Join
- Explicit Locking
- Over Indexed Tables
- Under Indexed Tables
- Complex Queries
- Too much concurrency
- Updating too much data at once.
- "We must have foreign keys"
- Long Open Transaction
- Blind Inserts
In Depth Analysis by Example (60 mins)
- Finding people near me / Solving the "6 degrees of separation" problem.
- Solving Category / Hierarchal Data Problems.
Things to change you want to get right (1 hour)
- Case Study #1 - Hardcoding database names, hostnames, etc.
- Case Study #2 - Sharding too early before solving other problems.
- Case Study #3 - Small tweaks to user requirements.
How and where you can leverage new features (30 minutes)
- Stored Procedures - Usage and Limitations
- Triggers - Usage and Limitations
- Views - Usage and Limitations
Conclusion (15 minutes)
Web developers who are familiar with basic SQL queries and who wish to sharpen their skills and improve performance of their applications.
Duration: 1 day (Monday)
Maximum capacity: 15 persons
Requirements: A laptop with a text editor and MySQL installed