Tune SQL, InnoDB, Memory, I/O, Threading, and Galera/Replication from Evidence

Record before each run:  server: SELECT VERSION(), @@version_comment  connector/runtime: node --version; npm ls mariadb  dataset: row counts + database/index bytes  cache state: warm or intentionally cold  topology: single primary / async replica / Galera; binlog on/off; wsrep state  container/VM CPU + memory limits  storage type and free space  workload seed, mix, concurrency, duration  schema migration versions  changed variable: exactly one planned differenceDo not compare runs if any uncontrolled item materially changed.

SELECT VERSION(), @@version_comment;SELECT COUNT(*) AS tickets FROM servicehub22.ticket;SELECT COUNT(*) AS comments FROM servicehub22.ticket_comment;SELECT ROUND(SUM(data_length+index_length)/1024/1024,1) AS servicehub_mbFROM information_schema.TABLES WHERE table_schema='servicehub22';SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Threads_connected','Threads_running','Created_tmp_tables','Created_tmp_disk_tables', 'Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_pages_dirty','Innodb_data_reads','Innodb_data_writes', 'Innodb_os_log_written','Bytes_received','Bytes_sent');

USE servicehub22;DROP TABLE IF EXISTS bench_seq;CREATE TABLE bench_seq (n INT PRIMARY KEY) ENGINE=InnoDB;INSERT INTO bench_seq VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);-- 10,000 deterministic rows from a four-digit Cartesian product.INSERT INTO ticket(tenant_id,ticket_id,customer_id,status,priority,subject,created_at,updated_at)SELECT 1,       100000 + (a.n*1000+b.n*100+c.n*10+d.n),       CASE WHEN MOD(a.n+b.n+c.n+d.n,2)=0 THEN 1001 ELSE 1002 END,       CASE MOD(a.n+b.n+c.n+d.n,4)         WHEN 0 THEN 'OPEN' WHEN 1 THEN 'PENDING'         WHEN 2 THEN 'RESOLVED' ELSE 'CLOSED' END,       1 + MOD(a.n+b.n+c.n+d.n,5),       CONCAT('benchmark ticket ',a.n,b.n,c.n,d.n),       TIMESTAMP('2026-01-01 00:00:00') + INTERVAL (a.n*1000+b.n*100+c.n*10+d.n) SECOND,       TIMESTAMP('2026-01-01 00:00:00') + INTERVAL (a.n*1000+b.n*100+c.n*10+d.n) SECONDFROM bench_seq a CROSS JOIN bench_seq b CROSS JOIN bench_seq c CROSS JOIN bench_seq d;SELECT COUNT(*) FROM ticket WHERE tenant_id=1;

ALTER TABLE servicehub22.ticket DROP INDEX ix_ticket_queue;EXPLAINSELECT tenant_id,ticket_id,status,priority,subject,updated_atFROM servicehub22.ticketWHERE tenant_id=1 AND status='OPEN'ORDER BY updated_at DESC, ticket_id DESCLIMIT 50;-- On supported/current versions also capture observed execution:ANALYZE FORMAT=JSONSELECT tenant_id,ticket_id,status,priority,subject,updated_atFROM servicehub22.ticketWHERE tenant_id=1 AND status='OPEN'ORDER BY updated_at DESC, ticket_id DESCLIMIT 50;

mkdir capstone22-bench && cd capstone22-benchnpm init -ynpm install mariadbnode --versionnpm ls mariadb# Set DB_HOST/DB_USER/DB_PASSWORD in your shell or secret manager.# Do not commit credentials.

import mariadb from 'mariadb';import { performance } from 'node:perf_hooks';const concurrency = Number(process.env.CONCURRENCY ?? 8);const iterations = Number(process.env.ITERATIONS ?? 100);const pool = mariadb.createPool({  host: process.env.DB_HOST ?? '127.0.0.1',  port: Number(process.env.DB_PORT ?? 3306),  database: 'servicehub22',  user: process.env.DB_USER,  password: process.env.DB_PASSWORD,  connectionLimit: concurrency,  acquireTimeout: 5000,  connectTimeout: 5000,  resetAfterUse: true});const samples=[];let errors=0;async function one(worker, i) {  let conn;  const t0=performance.now();  try {    conn=await pool.getConnection();    if ((i + worker) % 5 === 0) {      await conn.query(        `UPDATE ticket SET priority=CASE WHEN priority=5 THEN 1 ELSE priority+1 END         WHERE tenant_id=? AND ticket_id=?`,        [1, 100000 + ((i*37 + worker*101) % 10000)]);    } else {      await conn.query(        `SELECT ticket_id,status,priority,subject,updated_at         FROM ticket WHERE tenant_id=? AND status='OPEN'         ORDER BY updated_at DESC,ticket_id DESC LIMIT 50`, [1]);    }  } catch (e) { errors++; }  finally {    samples.push(performance.now()-t0);    if (conn) conn.release();  }}await Promise.all(Array.from({length: concurrency}, (_,w) =>  (async()=>{ for(let i=0;i<iterations;i++) await one(w,i); })()));await pool.end();samples.sort((a,b)=>a-b);const pct=p=>samples[Math.min(samples.length-1, Math.floor((samples.length-1)*p))];console.log(JSON.stringify({  concurrency, samples:samples.length, errors,  p50_ms:pct(.50), p95_ms:pct(.95), p99_ms:pct(.99), max_ms:samples.at(-1)}, null, 2));

ALTER TABLE servicehub22.ticket  ADD KEY ix_ticket_queue (tenant_id,status,updated_at,ticket_id);EXPLAINSELECT tenant_id,ticket_id,status,priority,subject,updated_atFROM servicehub22.ticketWHERE tenant_id=1 AND status='OPEN'ORDER BY updated_at DESC,ticket_id DESCLIMIT 50;

experiment_id: 22-L3-index-queuebaseline:  schema_version: record the exact applied migration version  plan: save the complete EXPLAIN/ANALYZE output  p50/p95/p99/errors: save the raw benchmark JSON  server counters before/after: save both snapshotschange:  add ix_ticket_queue(tenant_id,status,updated_at,ticket_id)after:  same dataset/cache/concurrency/mix/duration  plan: save the complete post-change plan  p50/p95/p99/errors: save the post-change raw JSONresult:  accepted / rejected based on predeclared criteriaresidual_risks:  write/index maintenance cost, storage growth, different tenant cardinalities

-- Async replication: run only on a configured replica.SHOW REPLICA STATUS\G-- Galera: run only when wsrep/Galera is actually enabled.SHOW GLOBAL STATUS LIKE 'wsrep_%';-- Always record effective thread/memory configuration.SHOW VARIABLES WHERE Variable_name IN ('max_connections','thread_handling','innodb_buffer_pool_size',  'tmp_table_size','max_heap_table_size','sort_buffer_size','join_buffer_size');

-- Confirm the critical index was restored.SELECT index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) columns_in_orderFROM information_schema.STATISTICSWHERE table_schema='servicehub22' AND table_name='ticket'  AND index_name='ix_ticket_queue'GROUP BY index_name;-- Keep benchmark rows for Lesson 4 unless you need a small reset.DROP TABLE IF EXISTS servicehub22.bench_seq;