Skip navigation links
MySQL Performance Schema
1 Performance Schema Quick Start »
Section Navigation      [Toggle]

MySQL Performance Schema

Copyright © 2009, 2010, Oracle and/or its affiliates. All rights reserved.

This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the following terms: You may create a printed copy of this documentation solely for your own personal use. Conversion to other formats is allowed as long as the actual content is not altered or edited in any way. You shall not publish or distribute this documentation in any form or on any media, except if you distribute the documentation in a manner similar to how Sun disseminates it (that is, electronically for download on a Web site with the software) or on a CD-ROM or similar medium, provided however that the documentation is disseminated together with the software on the same medium. Any other use, such as any dissemination of printed copies or use of this documentation, in whole or in part, in another publication, requires the prior written consent from an authorized representative of Sun Microsystems, Inc. Sun Microsystems, Inc. reserves any and all rights to this documentation not expressly granted above.

For more information on the terms of this license, for details on how the MySQL documentation is built and produced, or if you are interested in doing a translation, please contact the http://www.mysql.com/company/contact/.

If you want help with using MySQL, please visit either the MySQL Forums or MySQL Mailing Lists where you can discuss your issues with other MySQL users.

For additional documentation on MySQL products, including translations of the documentation into other languages, and downloadable versions in variety of formats, including HTML, CHM, and PDF formats, see MySQL Documentation Library.

Abstract

MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level.

This document is preliminary.

Document generated on: 2010-03-11 (revision: 1679)

Table of Contents     [+/-]

1. Performance Schema Quick Start
2. Performance Schema Configuration     [+/-]
3. Performance Schema Status Monitoring
4. Performance Schema Event Timing
5. Performance Schema Event Instrument Naming Conventions
6. Performance Schema Table General Characteristics
7. Performance Schema Table Descriptions     [+/-]
8. Performance Schema and Plugins
9. Performance Schema System Variables
10. Performance Schema Status Variables
11. Using Performance Schema to Diagnose Problems
12. Performance Schema Restrictions
13. Other Changes to Manual

MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. This is a new feature. MySQL is working on enhancements and may add, change, rename, or remove parts of this feature at any time with no guarantee of backward compatibility.

Performance Schema has these characteristics:

  • Performance Schema provides a way to inspect internal execution of the server at runtime. It is implemented via the PERFORMANCE_SCHEMA storage engine and the performance_schema database. Performance Schema focuses primarily on performance data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata.

  • Performance Schema monitors server events. An “event” is anything the server does that takes time and has been instrumented so that timing information can be collected. In general, an event could be a function call, a wait for the operating system, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of statements. Currently, event collection provides access to information about synchronization calls (such as for mutexes) and disk I/O calls for the server and for several storage engines.

  • Performance Schema events are distinct from events written to the server's binary log (which describe data modifications) and Event Scheduler events (which are a type of stored program).

  • Current events are available, as well as event histories and summaries. This enables you to determine how many times instrumented activities were performed and how much time they took. Event information is available to show the activities of specific threads, or activity associated with particular objects such as a mutex or file.

  • The PERFORMANCE_SCHEMA storage engine collects event data using “instrumentation points” in server source code.

  • Collected events are stored in tables in the performance_schema database. These tables can be queried using SELECT statements like other tables.

  • Performance Schema configuration can be modified dynamically by updating tables in the performance_schema database via SQL statements. Configuration changes affect data collection immediately.

  • Tables in the performance_schema database are views or temporary tables that use no persistent on-disk storage.

  • Monitoring is available on all platforms supported by MySQL.

    Some limitations might apply: The types of timers might vary per platform. Instruments that apply to storage engines might not be implemented for all storage engines. Instrumentation of each third-party engine is the responsibility of the engine maintainer.

  • Data collection is implemented by modifying the server source code to add instrumentation. There are no separate threads associated with Performance Schema, unlike other features such as replication or the Event Scheduler.

Performance Schema is intended to provide access to useful information about server execution while having minimal impact on server performance. The implementation follows these design goals:

  • Activating Performance Schema causes no changes in server behavior. For example, it does not cause thread scheduling to change, and it does not cause query execution plans (as shown by EXPLAIN) to change.

  • No memory allocation is done beyond that which occurs during server startup. By using early allocation of structures with a fixed size, it is never necessary to resize or reallocate them, which is critical for achieving good runtime performance.

  • Server monitoring occurs continuously and unobtrusively with very little overhead. Activating Performance Schema does not make the server unusable.

  • The parser is unchanged. There are no new keywords or statements.

  • Execution of server code proceeds normally even if Performance Schema fails internally.

  • When there is a choice between performing processing during event collection initially or during event retrieval later, priority is given to making collection faster. This is because collection is ongoing whereas retrieval is on demand and might never happen at all.

  • It is easy to add new instrumentation points.

  • Instrumentation is versioned. If the instrumentation implementation changes, previously instrumented code will continue to work. This benefits developers of third-party plugins because it is not necessary to upgrade each plugin to stay synchronized with the latest Performance Schema changes.