C C++ CMake CSS Cypress date-fns Django Django REST Framework ESLint Git GNU Make HTML htmx HTTP JavaScript MariaDB NumPy pandas PHP PostgreSQL Python PyTorch Qt SQLite Tailwind CSS

Diagnosing Performance Bottlenecks in MariaDB using events_waits_summary_by_host_by_event_name


  • Wait Time Details
    The table stores details about the total, minimum, average, and maximum wait times for each summarized event. This helps identify potential bottlenecks or areas where wait times are impacting performance.

  • Summary by Host and Event Name
    This table summarizes wait events based on two key factors:

    • Host
      This refers to the hostname or IP address from where the connection to MariaDB originated.
    • Event Name
      This identifies the specific type of wait event that occurred (e.g., waiting for a table lock, waiting for I/O).

  • Wait Events
    These events represent situations where a thread within MariaDB is paused or waiting for a specific resource. The events_waits_summary_by_host_by_event_name table provides insights into these wait events.

  • Performance Monitoring
    This table belongs to the Performance Schema, a built-in feature for monitoring MariaDB server performance. It doesn't store data related to SQL statements but rather focuses on wait events.

By analyzing this table, you can gain valuable insights into how efficiently MariaDB is handling wait events for different clients (based on host) and different wait event types. This information can help you optimize your database server's performance.


SELECT
  hostname,
  event_name,
  COUNT(*) AS wait_events_total,
  SUM(wait_time) AS total_wait_time_ms,
  AVG(wait_time) AS average_wait_time_ms,
  MAX(wait_time) AS max_wait_time_ms
FROM events_waits_summary_by_host_by_event_name
GROUP BY hostname, event_name
ORDER BY total_wait_time_ms DESC;

This query retrieves data from the events_waits_summary_by_host_by_event_name table and groups the results by hostname and event name. It then calculates various metrics for each combination:

  • max_wait_time_ms: Maximum wait time recorded in milliseconds for a specific event type by host.
  • average_wait_time_ms: Average wait time in milliseconds for each event type by host.
  • total_wait_time_ms: Total time spent waiting in milliseconds for a specific event type by a host.
  • wait_events_total: Total number of wait events of a specific type for a particular host.

Other Performance Schema Tables

  • events_waits_current
    This table provides a snapshot of currently active wait events, helping to diagnose immediate bottlenecks.
  • events_waits_history
    This table tracks historical wait events, allowing you to analyze trends and identify performance changes over time.
  • events_waits_global
    This table provides a global summary of wait events across all connections, similar to events_waits_summary_by_host_by_event_name but without host differentiation.

User-Defined Variables

MariaDB allows defining user-defined variables to track specific wait events. You can write custom code to increment these variables whenever a particular wait event occurs, providing detailed information beyond the pre-defined tables.

External Monitoring Tools

Several third-party tools can monitor MariaDB performance, including wait events. These tools often provide visualizations, historical data analysis, and alerting capabilities beyond what the Performance Schema offers directly. Some popular options include:

  • Percona Monitoring and Management (PMM) (open-source)
    Offers advanced features for monitoring MariaDB and other databases.
  • phpMyAdmin (open-source)
    Provides basic performance monitoring capabilities.
  • MySQL Enterprise Monitor (paid)
    Offers comprehensive monitoring functionalities.