Description
This training will show you how to efficiently optimize your MySQL database servers and applications. You will learn to improve performance by configuring servers, organizing data and better writing SQL queries.
Who is this training for ?
For whom ?
System administrators and database administrators.
Prerequisites
Training objectives
Training program
- Introduction
- MySQL architecture.
- Choice of storage engine.
- Choice of data types.
- Normalization/denormalization.
- Processing an SQL statement.
- The INFORMATION_SCHEMA database.
- Collection of statistics.
- Practical work Collection of statistics, choice data types.
- Measurement and diagnostic tools
- Tracing and profiling.
- EXPLAIN command.
- Using the BLACKHOLE engine.
- Using mysqlslap.
- Logging and slow query log.
- Practical work Profiling, using the EXPLAIN command, using the mysqlslap tool, slow query log.
- Data organization and indexing
- MyISAM tables.
- Internal architecture.
- Indexing MyISAM tables.
- Acceleration of searches with "Fulltext" indexes.
- Maintenance of MyISAM tables, problem solving.
- InnoDB transactional tables.
- Internal architecture of InnoDB tables.
- Table structure and InnoDB indexes.
- Maintenance.
- Management of tablespace(s).
- Use of MEMORY type tables.
- Partitioning.
- Practical work Using Fulltext indexes and MEMORY tables.
- Compressing MyISAM tables.
- Optimisation des applications
- How the MySQL optimizer works.
- EXPLAIN tool and execution plans.
- Using prepared statements.
- Locking tables .
- Transactional operation and isolation levels (InnoDB).
- Using views.
- Optimize load operations.
- Practical work Execution plans.
- Using prepared queries, data loading optimization.
- Transactions and improving concurrency.
- Server Optimization
- Observing server activity.
- Dynamic variables.
- Memory usage in MySQL.
- Query cache.
- Index cache.
- Optimization of the InnoDB and MyISAM engine.
- Practical work Using the query cache and the index cache.
