MaxDB performance tuning primer

Introduction

A history of more than three decades has made MaxDB by MySQL a very mature database with a rich tool set for performance monitoring and analysis. Aside from an activity overview screen and the EXPLAIN SQL statement a SQL logger sophisticated monitoring tools are shipped with MaxDB for free. In a small series of articles we'll try to put light on the miracle of performance tuning. Advanced readers will be disappointed by the first introductionary article: skip it and come back at the second article.

It starts with confusion

Performance analysis is a complicated matter. Performance problems always raise a long list of question: was something wrong with the SQL command, was it a high-load situation that caused delays, is the system I/O bound, did I set a database parameter wrong? There's no simple answer to all these questions. The truth is, that databases are large and complicated software products. MaxDB is no exception to this rule. But don't worry, armed with some basic understanding of the implementation of databases you'll be able to get the big picture and learn how individual factors are related. This understanding is a key for a successful database performance tuning. Let's start with a confusing excursus into database implementations in order to proof the said.

In 1973 Michael E. Senko published an article on “Data Structures and Data Accessing in Data Base Systems – Past, Present, Future” in the IBM Systems Journal which sketched the cornerstones of a five layer based database system. The article described the system architecture used for the IBM System R prototype. It has gained some popularity and it has influenced database implementations until today. Although the five layers found by Senko do no map directly to the MaxDB layers, the article is still an excellent resource to identify starting points in performance analysis.

Senko has identified the following layers.

1. Logical Data Schema
   Components: SQL parsing, Access path selection, Access control, Constraints
2. Data Access Management
   Components: Data Dictionary, Transaction handling, Sorting
3. Internal Storage Management
   Components: Record Manager, Lock management, Log/Backup/Recovery
4. Buffer management
   Components: Page Caching
5. Operating system
   Components: external (permanent) storage management

Read On

The whole article is long, so we thought it better to make it a PDF. To download, click here (no registration required!) You could even print out the PDF so you can peruse at your leisure.