Understanding Memory Usage in MariaDB with Performance Schema

Purpose

Identify potential memory bottlenecks within specific threads or events.
Monitor memory usage of different events (like parsing queries, sorting data) occurring on individual database connections (threads).

Relation to SQL Statements & Structure

By identifying events that consume excessive memory on a particular thread, you can potentially pinpoint problematic SQL queries that might require optimization.
While not directly about SQL code, it helps analyze how efficiently memory is used during various stages of processing an SQL statement.

Table Content

CURRENT_COUNT_USED
Current number of allocated memory blocks that haven't been freed for this event.
HIGH_NUMBER_OF_BYTES_USED
Highest number of bytes used by this event at any given time.
LOW_NUMBER_OF_BYTES_USED
Lowest number of bytes used by this event at any given time.
SUM_NUMBER_OF_BYTES_ALLOC
Total number of bytes allocated for this event across all allocations.
SUM_NUM_FREE
Total number of attempts to free previously allocated memory for this event.
SUM_NUM_ALLOC
Total number of memory allocation attempts for this event on the thread.
EVENT_NAME
Name of the memory allocation event (e.g., sql/statement/compile, sort/merge).
THREAD_ID
Unique identifier for the database connection (thread).

By analyzing these metrics, you can identify events or threads with abnormally high memory usage, potentially indicating inefficient queries or processes.

SELECT THREAD_ID, EVENT_NAME, SUM_NUMBER_OF_BYTES_ALLOC / SUM(SUM_NUMBER_OF_BYTES_ALLOC) AS ALLOC_PERCENT
FROM performance_schema.memory_summary_by_thread_by_event_name
GROUP BY THREAD_ID, EVENT_NAME
ORDER BY ALLOC_PERCENT DESC
LIMIT 10;

This query retrieves data from the memory_summary_by_thread_by_event_name table and calculates the percentage of total memory allocated for each event on each thread. It then sorts the results by allocation percentage in descending order and limits the output to the top 10 entries.

By analyzing this data, you can see which events and threads are consuming the most memory. This might point you towards specific queries or operations that require further investigation for potential memory optimization.

This is a basic example. You can modify the query to filter and analyze memory usage based on specific criteria like thread IDs or event names.
Remember that the Performance Schema tables are volatile and reflect real-time information. Running the query multiple times might show different results depending on current database activity.

Other Performance Schema Tables

events_statements_summary
Tracks information about executed SQL statements, including the number of executions, total execution time, and rows returned. By analyzing slow or frequently executed queries, you might identify areas for optimization that can indirectly reduce memory usage.
memory_summary_by_user_by_event_name
Provides similar information but grouped by user instead of thread. This might be helpful if you're more concerned about overall memory usage per user than individual threads.

Operating System Tools

OS-specific tools
Linux offers tools like procfs or ps to access detailed information about processes, including their memory usage. These require more technical expertise to interpret but can provide valuable insights.
top or htop commands
These provide a high-level overview of overall memory usage on the server, including processes and memory allocation. While not as granular as memory_summary_by_thread_by_event_name, they can help identify trends and potential memory bottlenecks.

Third-Party Monitoring Tools

Several commercial and open-source monitoring tools integrate with MariaDB and provide detailed performance insights, including memory usage breakdowns. These tools might offer user-friendly interfaces and additional features like anomaly detection or historical data analysis.