Advanced Event and Query Logging

Version Introduced: ProxySQL 2.7.2

Overview

The Advanced Event and Query Logging feature provides a powerful mechanism to capture, store, and analyze events in ProxySQL. It is designed for all ProxySQL users but is especially beneficial for high-performance setups. The feature is invaluable for troubleshooting, auditing, performance analysis, and real-time traffic insights, enabling statistical and pattern analysis, such as failure patterns and Service Level Objectives (SLO) monitoring.

High-Level Architecture and Data Flow

  1. Circular Buffer:

    • A configurable, fixed-size circular buffer temporarily stores recent events.
    • Events are overwritten when the buffer becomes full unless transferred elsewhere.

  2. Storage Options:

    • In-Memory SQLite Table: Provides fast, real-time access for analysis.
    • On-Disk SQLite Table: Offers persistent storage for long-term retention.

  3. Event Transfer:

    • Events are transferred asynchronously to the SQLite tables using commands or at defined intervals.
    • Supported transfer commands:

      • DUMP EVENTSLOG FROM BUFFER TO MEMORY
      • DUMP EVENTSLOG FROM BUFFER TO DISK
      • DUMP EVENTSLOG FROM BUFFER TO BOTH

  4. Performance Optimization:

    • A dedicated thread manages event logging to prevent performance impact on real-time traffic.
    • Writes are batched to minimize disk I/O.

  5. Dynamic Configuration:

    • Configuration variables can be adjusted at runtime, ensuring flexibility and adaptability to different workloads.

Configuration

The following configuration variables control the behavior of the logging system:

Variable Description Default Value Notes
mysql-eventslog_buffer_history_size Defines the size of the circular buffer in number of events. 0 Setting to 0 disables the feature.
mysql-eventslog_table_memory_size Maximum number of events in the in-memory table. 10000 Automatic cleanup is triggered if the limit is reached.
mysql-eventslog_sync_buffer_to_disk Time interval (in seconds) for auto-dumping events from the buffer to disk. 0 0 disables auto-dumping.
mysql-eventslog_buffer_max_query_length Maximum allowed length for tracked SQL queries. Unlimited Longer queries are truncated.

Commands

The following commands control the event logging system:

  1. Dump Events:

    • DUMP EVENTSLOG FROM BUFFER TO MEMORY
    • DUMP EVENTSLOG FROM BUFFER TO DISK
    • DUMP EVENTSLOG FROM BUFFER TO BOTH

  2. Applying Settings:

   SET mysql-eventslog_buffer_history_size = 1000000;
   SET mysql-eventslog_default_log = 1; -- Enables query logging
   LOAD MYSQL VARIABLES TO RUNTIME; -- Applies the settings

Usage Examples

Basic Setup

Enable query logging and set a circular buffer of 1,000,000 events:

SET mysql-eventslog_buffer_history_size = 1000000;
SET mysql-eventslog_default_log = 1;
LOAD MYSQL VARIABLES TO RUNTIME;

Dump and Analyze Events

Dump events from the buffer to the in-memory SQLite table and analyze them:

DUMP EVENTSLOG FROM BUFFER TO MEMORY;
SELECT * FROM stats_mysql_query_events;

Example query for statistical analysis:

SELECT query, COUNT(*) AS count
FROM stats_mysql_query_events
GROUP BY query
ORDER BY count DESC
LIMIT 10;

Best Practices

  • Always monitor memory and disk usage when enabling event logging.
  • Use mysql-eventslog_buffer_history_size to control memory usage and avoid overflows.
  • Disable the feature when not in use to conserve resources.
  • Set appropriate retention policies based on available resources and requirements for real-time or long-term analysis.

Performance Metrics

The following metrics are available for monitoring the event logging system. Please note that as usual, metrics naming and unit can differ between stats table and Prometheus due to convention rules.

From stats_mysql_global in stats database:

Metric Name Description Exposure
MySQL_Logger-totalEventsCopiedToMemory Number of times events were copied to the in-memory database Prometheus/ProxySQL stats
MySQL_Logger-totalEventsCopiedToDisk Number of times events were copied to the on-memory database Prometheus/ProxySQL stats
MySQL_Logger-getAllEventsCallsCount Number of times the get_all_events method was called Prometheus/ProxySQL stats
MySQL_Logger-getAllEventsEventsCount Total number of events retrieved by the get_all_events method Prometheus/ProxySQL stats
MySQL_Logger-totalMemoryCopyTimeMicros Total time spent copying events to the in-memory database (microseconds) Prometheus/ProxySQL stats
MySQL_Logger-totalDiskCopyTimeMicros Total time spent copying events to the on-disk database (microseconds) Prometheus/ProxySQL stats
MySQL_Logger-totalGetAllEventsTimeMicros Total time spent in get_all_events (microseconds) Prometheus/ProxySQL stats
MySQL_Logger-diskCopyCount Total number of events copied to the in-memory database Prometheus/ProxySQL stats
MySQL_Logger-memoryCopyCount Total number of events copied to the on-disk database Prometheus/ProxySQL stats
MySQL_Logger-circularBufferEventsAddedCount The total number of events added to the circular buffer Prometheus/ProxySQL stats
MySQL_Logger-circularBufferEventsDroppedCount The total number of events dropped from the circular buffer Prometheus/ProxySQL stats
MySQL_Logger-circularBufferEventsSize Number of events currently present in the circular buffer Prometheus/ProxySQL stats

From Promethus exporter:

Metric Name Tags Description
proxysql_mysql_logger_copy_total target={memory/disk} Number of times events were copied to the in-memory/on-disk databases.
proxysql_mysql_logger_get_all_events_calls_total Number of times the ‘get_all_events’ method was called.
proxysql_mysql_logger_get_all_events_events_total Number of events retrieved by the get_all_events method.
proxysql_mysql_logger_get_all_events_seconds_total Total time spent in get_all_events method.
proxysql_mysql_logger_copy_seconds_total target={memory/disk} Total time spent copying events to the in-memory/on-disk databases.
proxysql_mysql_logger_events_copied_total target={memory/disk} Total number of events copied to the in-memory/on-disk databases.
proxysql_mysql_logger_circular_buffer_events_total type={added/dropped} The total number of events added/dropped to/from the circular buffer.
proxysql_mysql_logger_circular_buffer_events Number of events currently present in the circular buffer.

Troubleshooting

Common Issues

  1. Memory Overuse:

    • Ensure mysql-eventslog_buffer_history_size is correctly tuned to avoid exhausting available memory.
    • Use the circular buffer’s automatic overwriting to maintain stability.

  2. Disk Space:

    • Monitor disk usage if mysql-eventslog_sync_buffer_to_disk is enabled.

Monitoring Tools

Use the Prometheus exporter or ProxySQL stats tables to track performance metrics.

Disabling the Feature

To disable the feature:

SET mysql-eventslog_buffer_history_size = 0;
LOAD MYSQL VARIABLES TO RUNTIME;

This will clear the circular buffer and stop logging.