Skip to content

MariaDB / MySQL ​

Database and User Setup ​

sql
-- create a database named newdb
CREATE DATABASE newdb;

-- create a user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'strong_password';

-- grant all permissions on one database
GRANT ALL PRIVILEGES ON newdb.* TO 'newuser'@'localhost';

-- grant specific permissions on one table
GRANT SELECT, INSERT, UPDATE, DELETE ON newdb.table_name TO 'newuser'@'localhost';

-- allow the user to execute a stored procedure
GRANT EXECUTE ON PROCEDURE newdb.procedure_name TO 'newuser'@'localhost';

FLUSH PRIVILEGES; is usually unnecessary after CREATE USER or GRANT on modern MariaDB versions, but it remains safe if you need to force a reload.

Common Commands ​

sql
-- list all databases
SHOW DATABASES;

-- switch database
USE newdb;

-- list all tables
SHOW TABLES;

-- set the server time zone
SET GLOBAL time_zone = 'Asia/Kuala_Lumpur';

-- change the user password
ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'new_password';

Performance Investigation ​

Start with the server version, current activity, and lock waits; then investigate the SQL responsible for the load. Examples use app_db.orders as a placeholder table and 123 as a placeholder connection ID. Replace them with actual values. \G is vertical output in the mysql / mariadb command-line client; use ; in SQL tools that do not support it.

sql
SELECT VERSION(), @@version_comment;
SHOW GRANTS FOR CURRENT_USER;

Viewing other users' sessions generally requires PROCESS. Performance Schema tables need suitable read privileges; changing global settings or terminating other users' sessions requires additional administrative privileges.

Check the Process List ​

sql
-- FULL avoids truncating the SQL text.
SHOW FULL PROCESSLIST;

-- MariaDB and older MySQL: show active sessions, longest state duration first.
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM information_schema.PROCESSLIST
WHERE COMMAND <> 'Sleep'
  AND ID <> CONNECTION_ID()
ORDER BY TIME DESC;

MySQL deprecates the Information Schema process list. On MySQL 8.0.22+, prefer this Performance Schema query when available:

sql
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM performance_schema.processlist
WHERE COMMAND <> 'Sleep'
  AND ID <> CONNECTION_ID()
ORDER BY TIME DESC;
Field / stateWhat to investigate
IdConnection identifier for inspecting or cancelling a statement.
TimeSeconds in the current state; this is not a CPU-time measurement.
InfoCurrent statement; NULL can mean no statement is executing.
SleepIdle connection; check pooling and open transactions before treating it as a problem.
Waiting for table metadata lockOften DDL waiting for another transaction to release a table's metadata lock.
Sending dataBroad execution state; investigate the plan and rows examined, not just network throughput.

Repeat snapshots during the incident. Short, frequent statements may never appear in a snapshot; use statement digests or the slow query log below. See MariaDB SHOW PROCESSLIST and MySQL Performance Schema processlist.

Describe Tables and Explain SQL ​

DESCRIBE table_name shows column definitions. Use EXPLAIN SELECT ... to inspect how SQL will access tables and indexes.

sql
DESCRIBE app_db.orders;
SHOW CREATE TABLE app_db.orders\G
SHOW INDEX FROM app_db.orders;

EXPLAIN
SELECT id, created_at
FROM app_db.orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

-- More detail; JSON output differs between MariaDB and MySQL.
EXPLAIN FORMAT=JSON
SELECT id, created_at
FROM app_db.orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
Plan fieldHow to read it
typeALL means a full table scan; index means a full index scan. ref, range, and const often examine fewer rows.
possible_keys / keyCandidate indexes versus the index actually chosen. key = NULL means no index was selected.
rows / filteredEstimated rows examined and percentage remaining after filtering. Compare with actual execution statistics.
Using filesortExtra sorting step; does not necessarily mean disk I/O.
Using temporaryAn internal temporary table is needed; it may be in memory or on disk.
Using indexA covering index can supply the selected columns.

Scans and sorting can be reasonable for small tables or queries returning much of a table. For the example, evaluate a composite index starting with (customer_id, created_at) against existing indexes and write costs. See MySQL EXPLAIN and MariaDB EXPLAIN.

To inspect an already-running statement without submitting it again:

sql
-- MySQL: use an active connection ID from the process list.
EXPLAIN FOR CONNECTION 123;

-- MariaDB:
SHOW EXPLAIN FOR 123;

The connection must be executing a supported statement with an available plan. See MariaDB SHOW EXPLAIN.

Measure Actual Query Execution ​

These commands execute the query and add load. Use a representative, bounded SELECT on a test system or during a controlled production investigation. Statements involving functions or locks may have effects even when written as SELECT.

sql
-- MySQL 8.0.18+: actual timing, rows, and loops in TREE output.
EXPLAIN ANALYZE
SELECT id FROM app_db.orders WHERE customer_id = 42 LIMIT 20;

-- MariaDB 10.1+: actual statistics in JSON; syntax differs from MySQL.
ANALYZE FORMAT=JSON
SELECT id FROM app_db.orders WHERE customer_id = 42 LIMIT 20;

Compare estimates with actual rows and repeated loops to find expensive plan steps. MariaDB's ANALYZE SELECT ... differs from ANALYZE TABLE ..., which updates optimizer statistics. See MySQL EXPLAIN ANALYZE and MariaDB ANALYZE FORMAT=JSON.

Find Lock Waits and Long Transactions ​

sql
-- Both: engine activity, latest detected deadlock, and transaction details.
SHOW ENGINE INNODB STATUS\G

SELECT trx_id, trx_state, trx_started, trx_wait_started,
       trx_mysql_thread_id, trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

-- MySQL 8.0+: convenient view when the sys schema is installed.
SELECT waiting_pid, blocking_pid, wait_age, locked_table,
       waiting_query, blocking_query
FROM sys.innodb_lock_waits;

-- MySQL 8.0+: lower-level row-lock dependencies.
SELECT * FROM performance_schema.data_lock_waits;

MariaDB (also applicable to MySQL 5.7) uses different row-lock wait tables:

sql
SELECT r.trx_mysql_thread_id AS waiting_pid,
       b.trx_mysql_thread_id AS blocking_pid,
       r.trx_started AS waiting_trx_started,
       r.trx_query AS waiting_query,
       b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS AS w
JOIN information_schema.INNODB_TRX AS r
  ON r.trx_id = w.requesting_trx_id
JOIN information_schema.INNODB_TRX AS b
  ON b.trx_id = w.blocking_trx_id;

A sleeping session can hold an open transaction and block others; its current SQL may be NULL. Row-lock wait tables do not cover metadata locks. If the process list shows metadata waits, inspect performance_schema.metadata_locks where supported and instrumented (MariaDB 10.5.2+ / MySQL 5.7+), including PENDING requests and locks already GRANTED on the same object.

Have the owning application commit or roll back an abandoned transaction. If cancellation is necessary, verify the connection ID and owner first:

sql
-- Cancel only the current statement; the connection remains open.
KILL QUERY 123;

-- Disconnect the session and roll back its active transaction.
KILL CONNECTION 123;

KILL QUERY can leave transaction locks held. Disconnecting a large transaction can trigger a lengthy rollback. See MySQL lock investigation and MariaDB INNODB_LOCK_WAITS.

Find Expensive Query Patterns ​

With Performance Schema enabled and statement digest collection active, rank normalized SQL by cumulative elapsed time:

sql
SHOW VARIABLES LIKE 'performance_schema';

SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR AS executions,
       ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_seconds,
       ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Timers are in picoseconds. Compare total cost, frequency, average latency, and rows examined versus returned. Summaries accumulate until restart, reset, or eviction; compare snapshots over the incident window. An empty table may mean instrumentation is disabled, not that there is no workload. MariaDB installations may have Performance Schema disabled; enabling it requires startup configuration and a restart. See MySQL statement digests.

Capture Slow Queries ​

First record the existing values so they can be restored:

sql
SHOW GLOBAL VARIABLES
WHERE Variable_name IN (
  'slow_query_log', 'slow_query_log_file', 'long_query_time',
  'log_output', 'min_examined_row_limit'
);

-- Example temporary capture; requires administrative privileges.
SET GLOBAL log_output = 'FILE';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log = ON;

Global long_query_time applies to new sessions; existing pooled connections keep their session value. Reconnect them through the application's normal lifecycle, or use SET SESSION long_query_time = 1 in the session being tested. MariaDB 10.11+ uses log_slow_query and log_slow_query_time as newer names, retaining the older names as aliases.

Inspect the configured file on the database host. MySQL's summary utility can group similar statements:

bash
mysqldumpslow -s t -t 10 /path/from/slow_query_log_file
# MariaDB's corresponding utility:
mariadb-dumpslow -s t -t 10 /path/from/slow_query_log_file

Keep the capture window bounded, account for disk usage and SQL containing sensitive literals, and restore the recorded settings afterward. Changing log_output also affects general-log destinations. Runtime global changes do not necessarily survive restart; managed databases may require a parameter group or provider log viewer. See MySQL slow query log and MariaDB slow query log.

Check Server and Host Pressure ​

sql
SHOW GLOBAL STATUS
WHERE Variable_name IN (
  'Uptime', 'Threads_connected', 'Threads_running', 'Max_used_connections',
  'Connections', 'Aborted_connects', 'Slow_queries',
  'Created_tmp_tables', 'Created_tmp_disk_tables',
  'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',
  'Innodb_row_lock_waits', 'Innodb_row_lock_time'
);

SHOW GLOBAL VARIABLES
WHERE Variable_name IN ('max_connections', 'innodb_buffer_pool_size');
SignalNext step
Connections near max_connectionsCheck pool sizes, leaks, idle sessions, and connection errors.
Sustained high Threads_runningCorrelate with CPU, query plans, and lock waits.
Rising disk temporary tablesInvestigate grouping, sorting, and rows processed before increasing memory limits.
Rising buffer-pool readsCompare physical reads with logical read requests and host storage latency.
Rising row-lock waits / timeFind blocking transactions and shorten transaction scope.

Sample cumulative counters twice, for example 30 seconds apart, and use their differences to calculate rates. Gauges such as Threads_running are current values; Max_used_connections is a high-water mark. A lifetime cache-hit ratio can hide a recent I/O spike. See MySQL status variables.

On a Linux database host, correlate the same time window with host metrics (install sysstat if iostat is unavailable):

bash
top
vmstat 1 10
iostat -xz 1 10
df -h

Check CPU saturation, swap activity, storage latency, and free disk space. In containers or managed services, also check CPU/memory limits and provider storage metrics.

Investigation Workflow ​

  1. Record the incident time, server version, affected endpoint, and exact SQL with representative parameters.
  2. Capture process lists, open transactions, and lock dependencies while the problem occurs.
  3. Rank query digests or capture slow queries to find both slow statements and frequent expensive patterns.
  4. Inspect table definitions, indexes, and estimated plans; measure actual execution when appropriate.
  5. Check for missing composite indexes, functions or implicit conversions on indexed columns, large result sets, deep pagination, and repeated application queries (N+1).
  6. Change one thing at a time and compare latency, rows examined, and server load under comparable traffic. Include write overhead when evaluating new indexes.

Stored Procedure Example ​

sql
DELIMITER //

CREATE PROCEDURE sp_next_no(IN seq_type VARCHAR(10), IN prefix VARCHAR(10))
BEGIN
  DECLARE next_no DECIMAL(15, 0);

  UPDATE seq
  SET curr_no = curr_no + 1
  WHERE `type` = seq_type;

  SELECT curr_no INTO next_no
  FROM seq
  WHERE `type` = seq_type;

  IF next_no IS NULL THEN
    INSERT INTO seq (`type`, curr_no, prefix)
    VALUES (seq_type, 1, prefix);
    SET next_no = 1;
  END IF;

  SELECT next_no;
END//

DELIMITER ;

Production-ready recommendations ​

Create a dedicated application user instead of using root:

sql
CREATE USER 'app_user'@'10.%' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON app_db.* TO 'app_user'@'10.%';

Basic server settings:

ini
# /etc/mysql/mariadb.conf.d/99-custom.cnf
[mysqld]
bind-address = 127.0.0.1
max_connections = 200
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-name-resolve

Enable binary logging if backups or replication matter:

ini
[mysqld]
server_id = 1
log_bin = mariadb-bin
binlog_format = ROW
expire_logs_days = 7

Operational checklist:

  • Keep MariaDB bound to private IPs or localhost unless public access is required.
  • Back up with mysqldump or physical snapshots before schema changes.
  • Monitor slow queries and enable the slow query log on busy systems.