Introduction to InnoDB monitoring System

Agenda

•     Overview

•     Current Monitoring System in InnoDB

•     Performance Schema in InnoDB

•     Metrics Counter System in InnoDB

•     Summary

Overview

•     Monitor System is a generic term that refers to system that collects and displays the system state information

•     System Monitoring is important for server resource configuration as well as server performance tuning

•     Monitoring system could target different audience, developers, DBAs or common users

•     Ideally it should be light weight with minimum performance impact

•     The system should have easy interfaces for further data process and presentation

Current Monitoring System in InnoDB

•     InnoDB provide monitor information through following interfaces:

•     SHOW ENGINE INNODB STATUS (or create table innodb_monitor)

•     create specific innodb monitor tables for periodically output

•     innodb_lock_monitor for extensive lock information

•     innodb_tablespace_monitor provides list of file segments in the shared tablespace (Space Allocation)

•     innodb_table_monitor prints metadata information about InnoDB internal data dictionary (Metadata)

•     Status Variables

What’s coming for Monitoring System in InnoDB

•     More Monitoring:  Support a variety of server status/state Monitoring through additional Monitoring System features

•     Centralize: Centralize the monitor information interfaces, provide one-stop shop for server monitoring information. Move away from “create monitor table” interfaces.

•     Extensible: Additional monitors can be added into different modules over the time, or on demand.

•     Easy Query: Monitoring Data displayed through “fake” tables/views, and be able to query with SQL

•     Both resource monitoring and performance monitoring are important to support.

•     InnoDB Metrics Table(s) can achieve this

•     For event monitoring, performance schema is a good choice

What is Performance Schema

•     New feature in MySQL 5.5 (Peter Gulutzan, Marc Alff)

•     Monitors low-level server events (mutex, rwlock and IO etc.)

•     Instrumentations give you both information about “Elapsed time” and “counter” for the events

•     Expose as SQL tables for easy query, can be aggregated, averaged, ordered etc.

Performance Schema in InnoDB

•     Performance Schema in InnoDB 1.1

•     42 mutexes

•     10 rwlocks

•     7 types of threads

•     3 types of I/O (data, log, tmpfile)

•     What do you get from Performance Schema

•     Mutexes / rwlock usage statistics and current status

•     IO statistics

•     Active running threads

Performance Schema Instance table

•     Find out instrumented mutexes / rwlocks through instance table:

Performance Schema Instance Table

•     There could be multiple instances of a mutex in the server

•     Buffer block mutex/rwlock instrumentations are disabled by default.

•     One mutex/rwlock per 16k buffer block, server with large buffer pool configuration could easily create thousands of instances

•     Exceed the default value of max mutex/rwlock instances (1000) allowed

•     If the mutex is not yet created, it will not be listed in the instance table, so you might see fewer events/instances than you might expected.

Performance Schema PROCESSLIST Table

•     Check out running InnoDB Threads with PROCESSLIST table

Performance Schema WAITS Table

•     Find out what is the last event happened to a thread with EVENTS_WAITS_CURRENT table

Performance Schema HISTORY Table

•     Check “Limited history” with HISTORY tables

•     Two “HISTORY” tables that record each instrumented events. The EVENTS_WAITS_HISTORY table contains the most recent 10 events per thread.And EVENTS_WAITS_HISTORY_LONG contains the most recent 10,000 events by

default

•     For example, following query gives you exact mutex instances that has been on the top list as shown in the history table:

• But “History” is short, even if we extend EVENTS_WAITS_HISTORY_LONG to 1 million events, you might record just a few seconds data on a busy system

Performance Schema HISTORY Table

Performance Schema SUMMARY Table

•     Find out aggregated information from SUMMARY Tables

Performance Schema in InnoDB – a few notes

•     Individual Mutexes / rwlocks can be excluded from instrumentation in InnoDB using SETUP tables, although by default all mutexes / rwlock are enabled.

•     The number of mutex / rwlock instance could be very large. Need to adjust new system variables to accommodate: performance_schema_max_mutex_instances

•     Remember the block mutex/rwlock example, for a server with 2G buffer pool size configured, you would have 125,000 mutexes and rwlocks respectively.

•     Additional defines in InnoDB so that each of 4 instrumented modules can be disabled from instrumentation

•     Now the study on mutex/rwlock can be quantified

Performance Impact from Performance Schema

•     DBT2 test

•     32 connections

•     50 warehouses

•     2G innodb_buffer_pool_size

•     500 MB innodb_log_file_size

Performance Impact from Performance Schema

•     Performance Impact from Performance Schema(Data from Peter Gulutzan)

Performance Schema in InnoDB

•     Performance Schema helps us study and quantify the low level server events

•     Good tool for event monitoring and development tuning, especially for mutex optimization

•     It comes a cost, but working to minimize the cost

Introducing InnoDB Metrics Table

•     What is InnoDB Metrics Table

•     Infrastructure for Monitor counter based monitoring system

•     Display through information schema tables

•     Light weight counters, rely on caller for synchronization protection

•     Control system to turn on/off and reset the monitors. Counter start to counting only if the counter is turned on

•     Used for resource usage (capacity) as performance counters

•     Important for “knobs” – configurable options

•     Easy to add additional counters

•     No measurable performance impact

Column Defines for Monitor Table

InnoDB Metrics Monitor Table

•     What does the metrics table look like:Control System for Monitor Counters

•     Control System:

1) Turn on the monitor:

•     set global innodb_monitor_counter_on

•     Ex. set global innodb_monitor_counter_on = server_table_open;

2) After the sampling period, stop the monitor counting:

•     set global innodb_monitor_counter_off

3) Reset the counter:

•     set global innodb_monitor_counter_reset

4) Reset all of the counter

•     set global innodb_monitor_counter_reset_all

Monitor Counters Grouped into Modules

•     Counters are grouped into modules:

• Counters in the module can be turned on/off/reset together by applying the module name to the control system.

Monitor Counter Examples

•     An example: obtain DML stats in a fixed period of time

Monitor Counter Examples

•     Getting more information:

•     SET global innodb_monitor_counter_off = module_dml;

•     SET global innodb_monitor_counter_reset_all =  all;

•     SET global innodb_monitor_counter_on = all;

•     SET global innodb_monitor_counter_off = all;

Why Metrics Monitor Table

•     Easy to query and aggregates with table format

•     Minimum performance impact.

•     Easy to extend. With this infrastructure, add monitor counter is easy

•     Generic. Can be used for a variety of monitoring purpose as well as statistics collection

•     One stop provider for all monitor info. 31 Existing system status variables are now supported through Metrics Counter (but will still support status variables)

•     All status variable will have entry in the metrics table

Performance Schema vs. Metrics Counters

•     Performance Schema vs. Metrics Counters

•     They are complimentary. Performance schema gives more information on events, mutex / rwlock. Metrics counter is a more generic infrastructure for resource and performance counting.

•     Both tried to avoid involving mutexes. And counter value could be approximate.

•     For most metrics counters, upper level synchronization protection gives it a relatively reliable value.

•     No measurable performance impact from metrics counter, designed to be generic, simple and cheap.

Summary

•     Metrics System/Tables are likely to be our main interfaces for obtaining performance statistics and resource usage information. More performance and resource counters can be added with ease. And it will be one-stop shop for most performance/resource information

•     Performance Schema in InnoDB gives us powerful tools to study events such as mutex, rwlock and I/O related performance bottlenecks. Its value will soon be proved.

References and More Information

•     http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html

•     Blog on Performance Schema in InnoDB

•     http://blogs.innodb.com/wp/2010/04/innodb-performance-schema

•    The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.

The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.