Beyond TIME_MS: Alternative Methods for Query Performance Analysis in MariaDB

What is TIME_MS?

Unlike TIME (another column in PROCESSLIST), TIME_MS displays the time in milliseconds with microsecond precision.
TIME_MS is a column that shows the elapsed time a thread has been in its current state.

How is it programmed?

This difference is then converted to milliseconds and stored in the TIME_MS column.
The exact implementation details are internal to MariaDB, but it likely involves tracking timestamps when the thread enters its current state and calculating the difference with the current time.

Relation to SQL Statements & Structure

By using TIME_MS, you can analyze how long a particular SQL statement (represented by a thread) has been running in its current stage (e.g., waiting to acquire a lock, executing the query). This information helps in identifying potential bottlenecks or slow queries in your MariaDB database.
PROCESSLIST is a table within INFORMATION_SCHEMA that specifically shows details about currently executing threads. These threads are typically processing SQL statements.
INFORMATION_SCHEMA is a special database in MariaDB that provides information about the server itself, including things like tables, columns, and running threads.

Understanding TIME_MS is beneficial for performance optimization in MariaDB.
It helps analyze the execution time of SQL statements within threads.
TIME_MS offers more precise measurement (milliseconds and microseconds) compared to TIME (seconds).

Checking Thread Execution Time

SELECT user, TIME_MS, command
FROM information_schema.processlist
WHERE user = 'your_username'
ORDER BY TIME_MS DESC;

This code retrieves information about threads for the specified user (your_username). It sorts them by TIME_MS in descending order, allowing you to see which threads have been running the longest. This can help identify potentially slow queries.

Filtering by State and Time

SELECT user, TIME_MS, command
FROM information_schema.processlist
WHERE user = 'your_username'
  AND command = 'Sleep'  -- Filter by specific command (e.g., Sleep)
  AND TIME_MS > 1000;  -- Filter by execution time (more than 1 second)

This code refines the previous example. It filters threads for a specific user, looking only at those executing the Sleep command and running for more than 1 second (as indicated by TIME_MS). This helps narrow down the focus to potentially long-running idle threads.

Customizing Output

SELECT user, TIME_MS / 1000 AS execution_seconds, command
FROM information_schema.processlist
WHERE user = 'your_username';

This code showcases customizing the output. It calculates the execution time in seconds by dividing TIME_MS by 1000 and renames the column using an alias (execution_seconds).

Combining with SHOW PROCESSLIST

You can combine it with SHOW PROCESSLIST. This built-in statement provides information about current threads, including the Time column which shows the total elapsed time since the thread started.
While INFORMATION_SCHEMA.PROCESSLIST offers TIME_MS, it doesn't directly represent the total execution time of a query.

SELECT user, TIME_MS, command
FROM information_schema.processlist
WHERE id IN (SELECT id FROM information_schema.processlist WHERE command = 'Query');

-- Further processing and analysis based on TIME_MS and Time

Performance Schema

Tables like events_statements_summary and events_statements_history track query execution statistics, including total execution time.
MariaDB offers a performance_schema database containing detailed performance information.

SELECT first_seen, avg_timer_wait, count_executions, sql_text
FROM performance_schema.events_statements_summary
ORDER BY avg_timer_wait DESC;

This retrieves information about recently executed statements, including average wait time (avg_timer_wait), execution count (count_executions), and the actual SQL statement (sql_text).

Monitoring Tools

These tools often leverage INFORMATION_SCHEMA or performance_schema data and offer additional features like historical trends and alerting for slow queries.
Dedicated monitoring tools like phpMyAdmin, MySQL Workbench, or third-party solutions can provide a visual representation of query execution times.

Monitoring tools provide a user-friendly interface and additional features for comprehensive performance management.
For in-depth performance analysis and historical data, performance_schema offers detailed information.
For quick checks and basic analysis, combining INFORMATION_SCHEMA.PROCESSLIST with SHOW PROCESSLIST might suffice.