Exploring MariaDB Performance Schema

MariaDB Performance Schema is a powerful tool designed to monitor and analyze the performance of your MariaDB server. It provides a detailed insight into the internal workings of the database, allowing database administrators and developers to identify and optimize performance bottlenecks. In this blog post, we’ll delve into the intricacies of MariaDB Performance Schema, its components, and how to effectively utilize it for performance tuning.

Understanding Performance Schema

Performance Schema is a feature-rich instrument embedded within MariaDB Server that captures and exposes detailed information about server activity. It operates at a low level, offering insights into events such as SQL statements execution, locking, resource utilization, and much more. Enabling and configuring Performance Schema can be crucial for diagnosing and resolving performance issues in your MariaDB environment. Unlike traditional storage engines, Performance Schema operates as a separate in-memory database, providing detailed insights into various aspects of server performance.

At its core, Performance Schema is designed to answer some of the most important questions related to database performance:

Where was the time spent?
Performance Schema records detailed information about the execution of various events within the database, allowing administrators to pinpoint where time is being consumed during query execution.

Who has used most of the resources/time?
By analyzing Performance Schema data, administrators can identify users, connections, or processes that are consuming the most resources or time, enabling them to optimize resource allocation and query execution.

What was executed to consume those resources?
Performance Schema captures details about executed statements, queries, and operations, providing insights into which specific actions are consuming database resources.

When was it executed?
With Performance Schema, administrators can analyze the timing of various events and operations within the database, facilitating performance tuning and optimization based on workload patterns.

Considerations

While Performance Schema does incur some overhead due to its data collection mechanisms, it’s crucial to understand that this impact is typically minimal and outweighed by the benefits it offers in terms of performance analysis and optimization. Here are several points to consider when addressing these concerns:

1. Selective Instrumentation
Performance Schema allows for selective instrumentation, meaning users can choose which events and activities to monitor. By enabling only the necessary instruments, users can minimize overhead while still gaining valuable insights into performance metrics.

2. Low Overhead
With advancements in MariaDB versions, the overhead associated with Performance Schema has been significantly reduced. Modern implementations are designed to minimize performance impact, ensuring that the benefits of performance analysis outweigh any potential drawbacks.

3. Dynamic Configuration
Performance Schema offers dynamic configuration options, allowing users to adjust settings based on their specific monitoring requirements. By fine-tuning parameters such as the size of data collection, users can strike a balance between performance analysis and system resources.

4. Isolated Testing
Before enabling Performance Schema in a production environment, users can conduct isolated testing to evaluate its impact on performance. By monitoring system metrics before and after enabling Performance Schema, users can quantify any performance impact and make informed decisions.

5. Benefits Outweigh Drawbacks
Despite the minimal overhead, the benefits of Performance Schema in terms of performance analysis, query optimization, and troubleshooting far outweigh any potential drawbacks. By leveraging Performance Schema, users gain valuable insights into database performance, enabling them to identify and resolve bottlenecks more effectively.

While concerns about Performance Schema impacting performance are valid, it’s important to recognize that the benefits it offers outweigh any potential drawbacks. By selectively enabling instrumentation, fine-tuning configuration settings, and conducting thorough testing, users can harness the power of Performance Schema to optimize MariaDB performance without significant performance degradation.

Enabling Performance Schema

Before diving into the usage, let’s ensure that Performance Schema is enabled in your MariaDB installation. It can be enabled by adding the following line to your MariaDB configuration file (my.cnf):

[mysqld]
performance_schema=ON

Once enabled, you can verify its status using:

SHOW VARIABLES LIKE 'performance_schema';

Using the Tables

When utilizing Performance Schema in MariaDB, it’s essential to understand how to interact with its tables effectively. These tables are characterized by lowercase names and lengthy identifiers, while column names are in uppercase, though this doesn’t impact functionality. It’s crucial to note that these tables reside in memory without any indexes, meaning data is lost upon database restart. To persist data, manual scripts or SQL commands are necessary as there’s no automatic disk persistence. Typically, queries involve SELECT statements to retrieve data, updating selected columns in setup_* tables, or truncating events_* and *_summary_* tables to free up space.

It’s important to highlight that disabling Performance Schema isn’t as simple as toggling a global variable; instead, tables must be manually truncated. To utilize Performance Schema effectively, users require SELECT, UPDATE, and DROP privileges on PERFORMANCE_SCHEMA.* objects. By mastering these practices, users can harness the power of Performance Schema for comprehensive database performance analysis and optimization.

Performance_Schema Tables

accountsClient account connection statistics.
cond_instancesSynchronization object instances.
events_stages_currentCurrent stage events.
events_stages_historyTen most recent stage events per thread.
events_stages_history_longTen thousand most recent stage events.
events_stages_summary_by_account_by_event_nameSummarized stage events per account and event name.
events_stages_summary_by_host_by_event_nameSummarized stage events per host and event name.
events_stages_summary_by_thread_by_event_nameSummarized stage events per thread and event name.
events_stages_summary_by_user_by_event_nameSummarized stage events per user name and event name.
events_stages_summary_global_by_event_nameSummarized stage events per event name.
events_statements_currentCurrent statement events.
events_statements_historyTen most recent events per thread.
events_statements_history_longTen thousand most recent stage events.
events_statements_summary_by_account_by_event_nameSummarized statement events per account and event name.
events_statements_summary_by_digestSummarized statement events by scheme and digest.
events_statements_summary_by_host_by_event_nameSummarized statement events by host and event name.
events_statements_summary_by_thread_by_event_nameSummarized statement events by thread and event name.
events_statements_summary_by_user_by_event_nameSummarized statement events by user and event name.
events_statements_summary_global_by_event_nameSummarized statement events by event name.
events_waits_currentCurrent wait events.
events_waits_historyTen most recent wait events per thread.
events_waits_history_longTen thousand most recent wait events per thread.
events_waits_summary_by_account_by_event_nameSummarized wait events by account and event name.
events_waits_summary_by_host_by_event_nameSummarized wait events by host and event name.
events_waits_summary_by_instanceSummarized wait events by instance.
events_waits_summary_by_thread_by_event_nameSummarized wait events by thread and event name.
events_waits_summary_by_user_by_event_nameSummarized wait events by user and event name.
events_waits_summary_global_by_event_nameSummarized wait events by event name.
file_instancesSeen files.
file_summary_by_event_nameFile events summarized by event name.
file_summary_by_instanceFile events summarized by instance.
host_cacheHost and IP information.
hostsConnections by host.
mutex_instancesSeen mutexes.
objects_summary_global_by_typeObject wait events.
performance_timersAvailable event timers.
rwlock_instancesSeen read-write locks.
socket_instancesActive connections.
socket_summary_by_event_nameTimer and byte count statistics by socket instrument.
socket_summary_by_instanceTimer and byte count statistics by socket instance.
table_io_waits_summary_by_index_usageAggregate table I/O wait events by index.
table_io_waits_summary_by_tableAggregate table I/O wait events by table.
table_lock_waits_summary_by_tableAggregate table lock wait events by table.
threadsServer thread information.
usersConnection statistics by user.

 

Defining Proper Sizing for Data Collection

When it comes to optimizing data collection in MariaDB Performance Schema, defining proper sizing parameters is crucial. By default, or through auto-sizing mechanisms, users can ascertain the current settings by executing `show global variables like ‘performance_schema%’;` in the MariaDB console. However, it’s important to understand that these settings are interdependent on four key variables: `max_connections`, `table_definition_cache`, `table_open_cache`, and `open_files_limit`. Moreover, they are influenced by the «ulimit» set at the operating system level. While these performance variables can be customized, caution must be exercised to avoid overallocation of memory. Setting values too high may lead to excessive memory usage and potential server startup failures if insufficient memory is available. When determining the appropriate size for data collection, it’s essential to consider the required historical depth rather than defining it based on time intervals. The size is typically determined by the number of historical events needed for effective performance analysis.

By carefully configuring these parameters, users can strike a balance between resource utilization and the depth of performance data collected, ensuring optimal MariaDB server performance without overwhelming system resources. By using cloud storage to store the data, you can not only save costs but also gain flexibility, scalability, and enhanced data management capabilities for your organization.

Components of Performance Schema

Performance Schema comprises various components, each providing specific information about different aspects of server performance. Some key components include:

  1. Events Stages: Provides information about stages of query execution, including query parsing, optimization, and execution.
  2. Events Statements: Offers detailed statistics on SQL statements execution, including response time, latency, and resource consumption.
  3. Events Wait Classes: Focuses on waiting events, helping identify areas where the database might be experiencing delays.
  4. File I/O: Monitors file I/O operations, shedding light on potential disk-related performance issues.
  5. Table I/O: Similar to File I/O, but specific to table-level operations.
  6. Mutexes: Tracks mutexes, which are synchronization mechanisms critical for controlling access to shared resources.

Utilizing Performance Schema for Performance Tuning

Now that Performance Schema is enabled and its components are understood, let’s explore how to use it for performance tuning.

Identifying Slow Queries

Use the Events Statements component to identify slow-performing queries.

Analyze query execution time, examining the ‘TIMER_START’ and ‘TIMER_END’ events.

SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE `SUM_TIMER_WAIT` > 1000000
ORDER BY `SUM_TIMER_WAIT` DESC;

Monitoring Resource Utilization

Leverage the Events Stages and Wait Classes components to monitor resource consumption and waiting events.

Identify stages or classes with high wait times and optimize accordingly.

SELECT * FROM performance_schema.events_stages_summary_global_by_event_name;
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name;

Analyzing I/O Performance

Use File I/O and Table I/O components to analyze I/O operations.

Identify bottlenecks related to disk I/O and optimize queries or disk configuration.

SELECT * FROM performance_schema.file_summary_by_instance;
SELECT * FROM performance_schema.table_io_waits_summary_by_table;

Synchronization Analysis

Monitor mutexes using the Mutexes component to identify potential contention.

Optimize code or queries causing mutex contention.

SELECT * FROM performance_schema.mutex_instances;

Conclusion

MariaDB Performance Schema is a robust tool for monitoring and optimizing database performance. By enabling and utilizing its various components, database administrators can gain deep insights into query execution, resource utilization, and potential bottlenecks. This guide serves as a starting point for harnessing the power of Performance Schema to enhance the overall performance of your MariaDB server.

Try the Performance Schema tool by downloading MariaDB Server. MariaDB Community Server is free and available for anyone to download. For production workloads, customers may download MariaDB Enterprise Server that delivers additional availability, security and reliability benefits.