InnoDB, Replication, Galera, Connections, Temp Tables, and OS Metrics

Learning outcomes

A graph labeled “database CPU 90%” cannot tell you whether the cause is a scan-heavy deployment, a buffer-pool miss storm, replica catch-up, Galera flow control, connection churn, or an unrelated host process. Production diagnosis requires correlation: line up MariaDB counters and state with operating-system CPU, memory, disk, filesystem, and network evidence over the same time window.

1. Start with metric semantics, not dashboards

Two snapshots ten seconds apart can be more informative than one giant global-status dump because they establish change. Record Uptime so you know whether the server restarted between comparisons.

DROP DATABASE IF EXISTS servicehub17;CREATE DATABASE servicehub17 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub17;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status ENUM('open','waiting','closed') NOT NULL,  priority TINYINT NOT NULL,  opened_at DATETIME(6) NOT NULL,  closed_at DATETIME(6) NULL,  INDEX ix_status_opened(status, opened_at),  INDEX ix_customer(customer_id)) ENGINE=InnoDB;CREATE TABLE ticket_events (  event_id BIGINT PRIMARY KEY AUTO_INCREMENT,  ticket_id BIGINT NOT NULL,  event_type VARCHAR(40) NOT NULL,  event_at DATETIME(6) NOT NULL,  payload VARCHAR(500) NULL,  INDEX ix_ticket_time(ticket_id, event_at)) ENGINE=InnoDB;INSERT INTO tickets(customer_id,status,priority,opened_at,closed_at)WITH RECURSIVE seq AS (  SELECT 1 AS n  UNION ALL SELECT n+1 FROM seq WHERE n < 500)SELECT 1000 + (n % 80),       CASE WHEN n % 7 = 0 THEN 'closed' WHEN n % 3 = 0 THEN 'waiting' ELSE 'open' END,       1 + (n % 5),       NOW(6) - INTERVAL n MINUTE,       CASE WHEN n % 7 = 0 THEN NOW(6) - INTERVAL (n-2) MINUTE ELSE NULL ENDFROM seq;INSERT INTO ticket_events(ticket_id,event_type,event_at,payload)SELECT ticket_id,       CASE WHEN ticket_id % 4=0 THEN 'comment' ELSE 'status_change' END,       opened_at + INTERVAL 30 SECOND,       RPAD('x', 120, 'x')FROM tickets;

2. InnoDB and temp-table pressure: correlate mechanisms

SHOW GLOBAL STATUS WHERE Variable_name IN (  'Uptime',  'Innodb_buffer_pool_reads',  'Innodb_buffer_pool_read_requests',  'Innodb_buffer_pool_pages_dirty',  'Innodb_data_reads',  'Innodb_data_writes',  'Innodb_os_log_written',  'Created_tmp_tables',  'Created_tmp_disk_tables',  'Threads_connected',  'Threads_running');

Innodb_buffer_pool_reads is physical reads requested from storage, while Innodb_buffer_pool_read_requests reflects logical requests. A ratio can be useful within a stable window, but a high historical counter is not a current incident. Dirty pages, redo volume, checkpoint behavior, and storage latency must be correlated before blaming “buffer pool size.”

USE servicehub17;SELECT customer_id, status, COUNT(*) AS c, MAX(opened_at)FROM ticketsGROUP BY customer_id, statusORDER BY c DESC, customer_id;SHOW SESSION STATUS LIKE 'Created_tmp%';

Session status is useful for isolating one connection. Global temp-table counters mix every workload since the reset/start point. The fact that a query created an internal temp table does not prove it is slow; size, disk spill, frequency, and concurrency determine impact.

3. Connections and concurrency: count is not CPU demand

SHOW GLOBAL STATUS LIKE 'Threads_%';SHOW GLOBAL STATUS LIKE 'Connections';SHOW GLOBAL STATUS LIKE 'Aborted_connects';SHOW FULL PROCESSLIST;

Threads_connected counts open sessions, many of which may be idle. Threads_running is closer to active server work but still needs CPU and wait context. A pool with 500 idle connections is a different problem from 80 simultaneously running CPU-heavy statements.

4. Replication and Galera: status fields are topology-specific

On an asynchronous replica, inspect transport and apply independently. Depending on version/terminology, SHOW REPLICA STATUS exposes I/O and SQL thread state, relay positions, errors, and lag-related fields. A zero/NULL lag number is not sufficient if a thread has stopped or the source is disconnected.

SHOW REPLICA STATUS\G-- Verify transport thread, apply thread/workers, last I/O/SQL errors,-- relay/source positions, GTID state, and whether lag is meaningful.

On Galera, use wsrep status such as component state, readiness, local node state, flow control, certification conflicts, and receive/send queues. A standalone non-Galera server may expose no useful wsrep cluster state; that absence is not a failure of the mandatory lab.

SHOW GLOBAL STATUS LIKE 'wsrep_cluster_status';SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';SHOW GLOBAL STATUS LIKE 'wsrep_ready';SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';SHOW GLOBAL STATUS LIKE 'wsrep_flow_control%';SHOW GLOBAL STATUS LIKE 'wsrep_local_cert_failures';

Replication lag and Galera flow control are different mechanisms. Do not put them on one “replication health” light without preserving what each topology actually guarantees.

5. OS correlation: the database cannot explain the whole host

Use free platform tools to sample CPU, memory, disk latency/throughput, filesystem capacity, and network. The exact commands differ by OS; the operational model does not.

# CPU/load/memoryuptimefree -hvmstat 1 5# Filesystem capacitydf -h# If sysstat is installed:iostat -xz 1 5

Get-Counter '\Processor(_Total)\% Processor Time',            '\Memory\Available MBytes',            '\PhysicalDisk(_Total)\Avg. Disk sec/Read',            '\PhysicalDisk(_Total)\Avg. Disk sec/Write' -SampleInterval 1 -MaxSamples 5Get-Volume | Select DriveLetter, FileSystemLabel, Size, SizeRemaining

If MariaDB wait time rises while OS disk latency also rises and the workload begins reading far more pages, the evidence is mutually reinforcing. If MariaDB waits rise while storage is idle, investigate locks, metadata locks, CPU scheduling, network, or application-side delay instead.

6. Wrong approach: “the counter is huge, therefore the server is sick”

Suppose Created_tmp_disk_tables equals 8 million. Without uptime or a previous sample, you do not know whether those occurred in ten minutes or ten months.

SELECT NOW(6) AS sampled_at;SHOW GLOBAL STATUS WHERE Variable_name IN (  'Uptime','Questions','Created_tmp_tables','Created_tmp_disk_tables');-- Wait a known interval under a known workload, then sample again.SELECT NOW(6) AS sampled_at;SHOW GLOBAL STATUS WHERE Variable_name IN (  'Uptime','Questions','Created_tmp_tables','Created_tmp_disk_tables');

Convert the difference to per-second/per-query rates, then correlate with query digests and user latency. This repair changes an alarming number into an interpretable measurement.

7. Production judgment and cleanup

Build dashboards from a small number of causal questions: demand, saturation, errors, latency, durability/recovery risk, and topology health. Preserve both the database and host dimensions. A metric that cannot be tied to an action or hypothesis often becomes decorative noise.

DROP DATABASE IF EXISTS servicehub17;