Useful Queries

Top Queries

To show the top queries for the last day, query the clustrix_statd.qpc_history table:

SELECT query_key,
       LEFT(statement, 60),
       RIGHT(statement, 60),
       Sum(exec_count)                  AS sum_exec_count,
       Avg(avg_exec_ms)                 AS avg_avg_exec_ms,
       Sum(rows_read)                   AS sum_rows_read,
       Sum(rows_read) / Sum(exec_count) AS avg_rr,
       Avg(rank)                        AS avg_rank
FROM   clustrix_statd.qpc_history
WHERE  timestamp > Now() - INTERVAL 1 day
GROUP  BY query_key
ORDER  BY sum_rows_read DESC
LIMIT  20;

For additional information about the clustrix_statd.qpc_history table, see "Optimize Performance Using Query Plan Cache (QPC) with MariaDB Xpand".

Long Running Queries per Node

To show the long-running queries for each cluster node, query the system.sessions system table:

SELECT nodeid,
  time_in_state_s,
  last_statement
FROM system.sessions
WHERE statement_state = 'executing'
  AND time_in_state_s > 1
ORDER BY time_in_state_s;

Additional details about long-running queries can be obtained using the Query Plan Cache (QPC) system tables. For additional information about the Query Plan Cache (QPC), see "Optimize Performance Using Query Plan Cache (QPC) with MariaDB Xpand".

Longest-running Transaction

To identify the longest-running transaction and return metadata about that transaction, use the system.sessions and system.transactions system tables:

1. The system.sessions system table can provide the transaction ID for the session that has been in the executing state for longest:

   SELECT   *,
     @xid:=xid
   FROM system.sessions
   WHERE statement_state='executing'
     AND time_in_state_s > 1
   ORDER BY time_in_state_s DESC
   LIMIT 1\g
   

2. The system.transactions system table can provide the transaction metadata for the specific transaction ID:

   SELECT *
   FROM system.transactions
   WHERE xid=@xid;
   

CPU Loads per Node

To show the CPU load for each cluster node, query the system.cpu_load system table:

SELECT *
FROM system.cpu_load
ORDER BY 1,2;

Identify the Source IP or Hostname of a Query

There are multiple ways to identify the source IP or hostname of a query, depending on which source table you want to use and which information you want to use as a filter.

When MaxScale is used, the queries in this section will only show the true source IP or hostname when the proxy protocol is enabled. When MaxScale is used without the proxy protocol, the maxctrl list sessions command can be used to obtain the source IP or hostname of a session.

The system.sessions system table can provide the source IP:

• To use the query string as a filter, specify the last_statement column in the WHERE clause:

  SELECT source_ip,
    last_statement
  FROM system.sessions
  WHERE last_statement LIKE 'select * from test.tab1';
  

• To use the session ID as a filter, specify the session_id column in the WHERE clause:

  SELECT source_ip,
    last_statement
  FROM system.sessions
  WHERE session_id=10198018;
  

The information_schema.processlist table can provide the source hostname:

• To use the query string as a filter, specify the info column in the WHERE clause:

  SELECT host,
    info
  FROM information_schema.processlist
  WHERE info LIKE 'select * from test.tab1';
  

• To use the session ID as a filter, specify the session_id column in the WHERE clause:

  SELECT host,
    info
  FROM information_schema.processlist
  WHERE session_id=10198018;
  

If you are using a load balancer, the system.sessions and information_schema.processlist tables will contain the source IP or hostname of the load balancer. In those cases, one way to track the source IP or hostname of a query it to include the IP address for each query as a SQL comment:

SELECT * FROM test.tab1
/*client-IP:192.168.1.1*/;

Adding the source IP or hostname as a SQL comment can help to identify the source of a query in the process list and/or query log. It can also be helpful to add the line number in the application code that a particular query is run from. Both of these can help in identifying and resolving problem queries.

View Table Sizes

To view table sizes, query the system.table_sizes system table.

For example, consider the following tables:

CREATE TABLE test.tab1 (id int, name varchar);

CREATE TABLE test.tab2 (id int not null primary key, name varchar);

CREATE TABLE test.tab3 (id int not null primary key, name varchar, key indexkey (name));

Query the system.table_sizes system table to view the sizes of each table:

SELECT *
FROM system.table_sizes
WHERE `database` = 'test';

+----------+-------+--------+
| Database | Table | Bytes  |
+----------+-------+--------+
| test     | tab1  | 327680 |
| test     | tab2  | 327680 |
| test     | tab3  | 655360 |
+----------+-------+--------+