Monitoring active InnoDB threads?

How can I track the number of active InnoDB threads?

To track the number of active InnoDB threads, you can use the following SQL query:

SELECT COUNT(*) AS active_innodb_threads 
FROM performance_schema.threads 
WHERE NAME LIKE 'thread/innodb/%'   
AND PROCESSLIST_ID IS NOT NULL;

This query provides a count of active InnoDB threads by:

  • Querying the performance_schema.threads table, which contains information about server threads
  • Filtering for threads with names starting with ‘thread/innodb/’, which identifies InnoDB-specific threads
  • Ensuring the PROCESSLIST_ID is not null, indicating an active thread

For more detailed information about each InnoDB thread, you can use:

SELECT thread_id, name, processlist_id, processlist_user, processlist_host 
FROM performance_schema.threads 
WHERE NAME LIKE 'thread/innodb/%'   
AND PROCESSLIST_ID IS NOT NULL;

This query will show you the thread ID, name, process list ID, user, and host for each active InnoDB thread.

Additionally, you can use the InnoDB Standard Monitor to get comprehensive information about InnoDB threads and their activities. To view the Standard Monitor output, run:

SHOW ENGINE INNODB STATUS;

In the output, look for the “BACKGROUND THREAD” section, which provides details about InnoDB background threads and their operations.

Remember that the number of InnoDB threads can be influenced by the innodb_thread_concurrency setting, which limits the number of concurrent threads. You may want to monitor this value and adjust it based on your system’s performance requirements.

Troubleshooting PostgreSQL Performance: Analyzing Active Queries for Optimization

How to check which queries are active or blocked in PostgreSQL?

Analyzing Active Queries and Transactions in MySQL