The following is a quick reference with commands and queries useful for managing and administering your MariaDB Xpand database.

Gathering and examining Logs

Gather Logs for the last 24 hours

The following will tar up the last 24 hours of logs

clx -a -s 24 logdump all

Sort an entire log file by query time (to compile the a useful indicatory of slow queries)

clx cmd 'grep "2021-09-19" /data/clustrix/log/query.log' >> ~/query_sort.log cat query_sort.log | sed 's/^.*time \([0-9]*\).*$/\1 \0/' | sort -rn | less

Measure Frequency of a log line (by hour)

grep "foo" <log> | cut -b1-13|uniq -c

Example:

grep "Duplicate key in container" /data/clustrix/log/query.log | grep "qpc_current" | cut -b1-13|uniq -c
    11 2021-09-19 22
     1 2021-09-19 23
     4 2021-09-20 00

Queries

Longest Running Query and info from the Transactions Table

Queries for the longest-running transaction and system data about that transaction

SELECT   *,
         @xid:=xid
FROM     sessions
WHERE    statement_state='executing'
AND      time_in_state_s > 1
ORDER BY time_in_state_s DESC
LIMIT    1\g
SELECT *
FROM   transactions
WHERE  [email protected];

Check CPU load per node

select * from cpu_load order by 1,2;

Long-running queries per node:

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

Top Queries for the last day

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;

On this page: