MariaDB / MySQL
Database and User Setup
-- 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
-- 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.
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
-- 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:
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 / state | What to investigate |
|---|---|
Id | Connection identifier for inspecting or cancelling a statement. |
Time | Seconds in the current state; this is not a CPU-time measurement. |
Info | Current statement; NULL can mean no statement is executing. |
Sleep | Idle connection; check pooling and open transactions before treating it as a problem. |
Waiting for table metadata lock | Often DDL waiting for another transaction to release a table's metadata lock. |
Sending data | Broad 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.
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 field | How to read it |
|---|---|
type | ALL means a full table scan; index means a full index scan. ref, range, and const often examine fewer rows. |
possible_keys / key | Candidate indexes versus the index actually chosen. key = NULL means no index was selected. |
rows / filtered | Estimated rows examined and percentage remaining after filtering. Compare with actual execution statistics. |
Using filesort | Extra sorting step; does not necessarily mean disk I/O. |
Using temporary | An internal temporary table is needed; it may be in memory or on disk. |
Using index | A 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:
-- 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.
-- 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
-- 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:
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:
-- 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:
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:
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:
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_fileKeep 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
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');| Signal | Next step |
|---|---|
Connections near max_connections | Check pool sizes, leaks, idle sessions, and connection errors. |
Sustained high Threads_running | Correlate with CPU, query plans, and lock waits. |
| Rising disk temporary tables | Investigate grouping, sorting, and rows processed before increasing memory limits. |
| Rising buffer-pool reads | Compare physical reads with logical read requests and host storage latency. |
| Rising row-lock waits / time | Find 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):
top
vmstat 1 10
iostat -xz 1 10
df -hCheck 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
- Record the incident time, server version, affected endpoint, and exact SQL with representative parameters.
- Capture process lists, open transactions, and lock dependencies while the problem occurs.
- Rank query digests or capture slow queries to find both slow statements and frequent expensive patterns.
- Inspect table definitions, indexes, and estimated plans; measure actual execution when appropriate.
- Check for missing composite indexes, functions or implicit conversions on indexed columns, large result sets, deep pagination, and repeated application queries (N+1).
- 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
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:
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:
# /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-resolveEnable binary logging if backups or replication matter:
[mysqld]
server_id = 1
log_bin = mariadb-bin
binlog_format = ROW
expire_logs_days = 7Operational checklist:
- Keep MariaDB bound to private IPs or localhost unless public access is required.
- Back up with
mysqldumpor physical snapshots before schema changes. - Monitor slow queries and enable the slow query log on busy systems.