Proxysql stats_mysql_processlist

Proxysql is a high-performance TCP proxy for MySQL servers. It provides features like load balancing, query caching, and query routing. One of the important features of Proxysql is the ability to monitor and analyze the MySQL process list using the stats_mysql_processlist table.

What is stats_mysql_processlist?

stats_mysql_processlist is a virtual table in Proxysql that provides detailed information about the active connections and queries running on the MySQL servers being proxied. It allows you to monitor the current state of the MySQL servers and analyze the performance of your database.

How to access stats_mysql_processlist?

To access the stats_mysql_processlist table, you need to connect to the Proxysql admin interface using a MySQL client. The default port for the admin interface is 6032. Once connected, you can query the stats_mysql_processlist table like any other table in MySQL.

Here is an example code for connecting to the Proxysql admin interface and querying the stats_mysql_processlist table:

mysql -h 127.0.0.1 -P 6032 -u admin -padmin

SELECT * FROM stats_mysql_processlist;

Understanding stats_mysql_processlist

The stats_mysql_processlist table provides various columns that give insights into the current state of the MySQL servers. Here are some of the important columns:

  • srv_host: The hostname or IP address of the MySQL server.
  • srv_port: The port number of the MySQL server.
  • hostname: The hostname of the client machine.
  • username: The MySQL username used to establish the connection.
  • client: The IP address of the client machine.
  • state: The current state of the connection or query (e.g., "running", "waiting for table lock").
  • command: The type of the command being executed (e.g., "Query", "Sleep").
  • query_time: The execution time of the query in seconds.
  • bytes_received: The number of bytes received from the client.
  • bytes_sent: The number of bytes sent to the client.

You can use these columns to identify long-running queries, analyze the load on the servers, and troubleshoot performance issues.

Analyzing stats_mysql_processlist

Using the stats_mysql_processlist table, you can perform various analytical queries to gain insights into the performance of your MySQL servers. Here are some examples:

  • Identify long-running queries:
SELECT * FROM stats_mysql_processlist WHERE command = 'Query' AND query_time > 5;
  • Analyze the load on the servers:
SELECT srv_host, COUNT(*) AS connections, SUM(query_time) AS total_query_time 
FROM stats_mysql_processlist 
GROUP BY srv_host;
  • Find the top queries by execution time:
SELECT LEFT(query, 100) AS query, query_time 
FROM stats_mysql_processlist 
WHERE command = 'Query' 
ORDER BY query_time DESC 
LIMIT 10;

These queries can help you identify performance bottlenecks, optimize your queries, and improve the overall performance of your MySQL servers.

Conclusion

In this article, we explored the stats_mysql_processlist table in Proxysql and how it can be used to monitor and analyze the MySQL process list. We learned how to access the table, understand its columns, and perform analytical queries. By utilizing this feature, you can gain deep insights into the performance of your MySQL servers and optimize your database for better efficiency.

"Proxysql's stats_mysql_processlist table is a powerful tool for monitoring and analyzing the MySQL process list. With its rich set of columns and analytical capabilities, it provides valuable insights into the performance of your database. By using this feature effectively, you can identify and resolve performance bottlenecks, optimize your queries, and ensure the smooth operation of your MySQL servers."