Posted on
• Artificial Intelligence

Artificial Intelligence MySQL Performance Tuning

Author
  • User
    linuxbash
    Posts by this author
    Posts by this author

Artificial Intelligence + MySQL: Practical Performance Tuning from the Command Line

Is your MySQL slowing down at the worst possible time? You grep through logs, tweak a variable, and pray your change helps. There’s a faster, safer path: use AI to turn telemetry into focused, testable tuning actions—right from Bash.

In this post you’ll learn a repeatable, shell-first workflow that blends proven MySQL instrumentation with AI-assisted analysis. You’ll collect the right data, summarize it, ask an LLM (local or cloud) for targeted advice, and validate changes with benchmarks. The result: faster queries, fewer blind guesses, and a documented trail of what worked.

Why AI for MySQL tuning is worth your time

  • MySQL performance problems are multi-dimensional: schema design, indexing, query plans, and server configuration interact. AI excels at synthesizing patterns across diverse inputs like slow logs, EXPLAIN plans, and table stats.

  • Most teams under-instrument. With a few toggles and CLI tools you can extract rich signals that models can transform into concrete hypotheses: rewrite this query, add this composite index, nudge these 3 variables—not a laundry list of random knobs.

  • The win isn’t “AI magic.” It’s speed-to-insight plus safe iteration. You still verify with benchmarks and roll back if needed.

Prerequisites: toolchain you can install with apt, dnf, or zypper

You’ll need the MySQL or MariaDB client, sysbench for benchmarking, and standard CLI utilities. Use the package manager for your distro:

  • On Debian/Ubuntu (apt):
sudo apt update
# MySQL or MariaDB client
sudo apt install -y mysql-client || sudo apt install -y mariadb-client
# Benchmarking + utilities
sudo apt install -y sysbench curl jq
  • On Fedora/RHEL/CentOS Stream (dnf):
# Client: usually MariaDB in default repos. If you use MySQL Community, replace with: sudo dnf install -y community-mysql
sudo dnf install -y mariadb
sudo dnf install -y sysbench curl jq
  • On openSUSE/SLES (zypper):
# Client: choose what's available in your repo; both names shown:
sudo zypper install -y mariadb-client || sudo zypper install -y mysql-client
sudo zypper install -y sysbench curl jq

Note:

  • mysql CLI includes mysqldumpslow for slow-log summarization.

  • curl and jq help you call an LLM API and parse responses. If you’ll use a local LLM, just skip the API step and adapt the prompt.

Step 1: Turn on the right instrumentation (low risk, high value)

Enable the slow query log and the Performance Schema. You can do this live, then persist later.

  • Live (SQL):
-- Slow log (adjust path as needed)
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 0.5; -- seconds
SET GLOBAL log_output = 'FILE';
-- Avoid noise unless you need it:
SET GLOBAL log_queries_not_using_indexes = OFF;

-- Performance Schema consumers for statement events
UPDATE performance_schema.setup_consumers
  SET ENABLED = 'YES'
  WHERE NAME IN ('events_statements_history', 'events_statements_current');

-- Sys schema views (MySQL 5.7+/8.0+ usually include it)
-- Example: quick top statements
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;
  • Persistent (/etc/mysql/my.cnf or /etc/my.cnf):
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_output = FILE
log_queries_not_using_indexes = OFF

Restart MySQL afterward if you changed the config file.

Step 2: Summarize and sanitize slow log + plans into an AI-ready bundle

First, condense the slow log into fingerprints so you don’t leak sensitive literals. mysqldumpslow does this out of the box:

# Show top 20 slowest query patterns by average time
sudo mysqldumpslow -t 20 -s at /var/log/mysql/slow.log > top20_slow.txt

# Show by count (most frequent)
sudo mysqldumpslow -t 20 -s c /var/log/mysql/slow.log > top20_freq.txt

Next, extract EXPLAIN plans and CREATE TABLE statements for the most important queries and tables. Here’s a Bash helper that:

  • Greps a candidate query from the slow log,

  • Runs EXPLAIN,

  • Captures SHOW CREATE TABLE for the referenced table(s),

  • Produces a compact JSON you can feed to an LLM.

Adjust connection variables and table names as needed.

#!/usr/bin/env bash
set -euo pipefail

MYSQL_HOST="${MYSQL_HOST:-127.0.0.1}"
MYSQL_PORT="${MYSQL_PORT:-3306}"
MYSQL_USER="${MYSQL_USER:-root}"
MYSQL_PWD="${MYSQL_PWD:-}"   # export MYSQL_PWD to avoid prompt
MYSQL_DB="${MYSQL_DB:-test}"

QUERY_SAMPLE_FILE="${1:-/var/log/mysql/slow.log}"
OUT_JSON="${2:-ai_bundle.json}"

# 1) Pick a candidate query (first non-blank slow query sample)
CANDIDATE_SQL="$(awk '/^# Time:/{block=1;next} block && NF{print} /^$/{exit}' "$QUERY_SAMPLE_FILE" \
  | sed -n '/^SELECT\|^UPDATE\|^INSERT\|^DELETE/p' \
  | head -n 20 | tr '\n' ' ')"

# Fallback if nothing parsed
if [[ -z "${CANDIDATE_SQL// }" ]]; then
  echo "Could not extract a candidate SQL from $QUERY_SAMPLE_FILE" >&2
  exit 1
fi

# 2) Get an EXPLAIN plan
EXPLAIN_JSON="$(
  mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" ${MYSQL_DB:+-D} "$MYSQL_DB" -s -N -e \
  "EXPLAIN FORMAT=JSON ${CANDIDATE_SQL}" 2>/dev/null || true
)"

# 3) Heuristically extract table names (simplistic; adjust as needed)
TABLES="$(echo "$CANDIDATE_SQL" | sed 's/`//g' | awk '
  { for(i=1;i<=NF;i++) {
      if(tolower($i)=="from" || tolower($i)=="join") { if((i+1)<=NF) print $(i+1) }
    }
  }' | sed 's/,//g' | sort -u
)"

# 4) Capture CREATE TABLE for each
SCHEMA_JSON="[]"
for t in $TABLES; do
  CT="$(mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" ${MYSQL_DB:+-D} "$MYSQL_DB" -s -N -e "SHOW CREATE TABLE \`$t\`\G" 2>/dev/null || true)"
  if [[ -n "$CT" ]]; then
    # Extract the CREATE line
    CREATE_STMT="$(echo "$CT" | sed -n 's/^Create Table: //p')"
    SCHEMA_JSON="$(jq -cn --arg tbl "$t" --arg ddl "$CREATE_STMT" \
      --argjson arr "$SCHEMA_JSON" '$arr + [{"table":$tbl,"ddl":$ddl}]')"
  fi
done

# 5) Bundle DB version and a few global variables
DB_VERSION="$(mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -s -N -e "SELECT VERSION();" || true)"
VARS="$(mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -s -N -e \
"SHOW VARIABLES WHERE Variable_name IN (
 'version','innodb_buffer_pool_size','innodb_flush_log_at_trx_commit',
 'max_connections','tmp_table_size','max_heap_table_size','sql_mode'
);" | awk '{print $1"="$2}')"

# 6) Emit JSON
jq -n --arg query "$CANDIDATE_SQL" \
      --arg explain "$EXPLAIN_JSON" \
      --arg version "$DB_VERSION" \
      --arg vars "$VARS" \
      --argjson schema "$SCHEMA_JSON" \
      '{query:$query, explain_json:$explain, mysql_version:$version, variables:$vars, schema:$schema}' \
      > "$OUT_JSON"

echo "Wrote $OUT_JSON"

Security tip: Prefer fingerprints (like mysqldumpslow output) and sanitized EXPLAINs when consulting external services. Avoid sending PII or full row data.

Step 3: Ask an LLM for hypotheses you can test

Use any OpenAI-compatible API or your preferred provider. The example below uses an environment variable for the API key; replace the URL/model as needed.

export OPENAI_API_KEY="your_api_key"

jq -r '.' ai_bundle.json | \
curl -s https://api.openai.com/v1/chat/completions \
  -H "Authorization: Bearer $OPENAI_API_KEY" \
  -H "Content-Type: application/json" \
  -d @- | jq -r '
    .choices[0].message.content
  ' <<'JSON'
{
  "model": "gpt-4o-mini",
  "messages": [
    {"role":"system","content":"You are a senior MySQL performance engineer. Be precise and safe."},
    {"role":"user","content":"Given this MySQL workload bundle, 1) suggest concrete index changes with CREATE INDEX statements and rationale, 2) propose safe query rewrites, 3) recommend at most 3 server variables to tune with expected trade-offs, 4) list validation steps with metrics to confirm improvement. Return concise, numbered steps."},
    {"role":"user","content": '"$(jq -c '.' ai_bundle.json | sed 's/"/\\"/g')"' }
  ],
  "temperature": 0.2
}
JSON

What to look for in the AI output:

  • Targeted index suggestions (e.g., composite index covering WHERE and ORDER BY).

  • Query rewrites with reduced function-on-column or improved join order.

  • 1–3 server knobs, not dozens, with reasons (e.g., larger tmp_table_size for on-disk temp table spill, or innodb_flush_log_at_trx_commit=2 for non-critical write paths).

  • A validation plan: exactly how to measure win vs. baseline.

Always review the advice—don’t blindly apply it.

Step 4: Apply safe changes incrementally

Start with index changes and query rewrites—they’re usually the biggest wins.

  • Create an index with minimal locking (InnoDB online DDL where possible):
-- MySQL 5.6+/8.0+ attempt online/inplace DDL
CREATE INDEX idx_user_status_created_at
  ON orders(user_id, status, created_at)
  ALGORITHM=INPLACE, LOCK=NONE;
  • Refresh optimizer stats if needed:
ANALYZE TABLE orders;
  • Try small, reversible server tweaks:
    • For MySQL 8.0+, SET PERSIST writes to mysqld-auto.cnf so it survives restarts. For older versions, use SET GLOBAL then add to my.cnf manually.
-- Reduce fsyncs for less critical writes (benchmark impact carefully)
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
-- or persist on 8.0+
-- SET PERSIST innodb_flush_log_at_trx_commit = 2;

-- Prevent temp table spills if you see "Created_tmp_disk_tables" climbing
SET GLOBAL tmp_table_size = 134217728;         -- 128 MiB
SET GLOBAL max_heap_table_size = 134217728;    -- match tmp_table_size
-- SET PERSIST tmp_table_size = 134217728;
-- SET PERSIST max_heap_table_size = 134217728;
  • Adjust InnoDB buffer pool size thoughtfully (aim for ~60–75% of RAM on dedicated DB hosts):
# Calculate ~70% of RAM and apply dynamically (supported in modern MySQL)
RAM_BYTES=$(awk '/MemTotal/ {print $2*1024}' /proc/meminfo)
BP_BYTES=$(python3 - <<PY
import os
print(int(int(os.environ["RAM_BYTES"])*0.70))
PY
)
mysql -u root -e "SET GLOBAL innodb_buffer_pool_size=${BP_BYTES};"
# To persist (MySQL 8.0+): SET PERSIST innodb_buffer_pool_size=${BP_BYTES};

Document every change with the reason and an expected outcome (e.g., “Reduce UPDATE latency p95 by 30%”).

Step 5: Benchmark before/after with sysbench

Use a workload close to your real pattern. Here’s a quick harness for read-only and read-write tests:

DBHOST="${DBHOST:-127.0.0.1}"
DBPORT="${DBPORT:-3306}"
DBUSER="${DBUSER:-root}"
DBPASS="${DBPASS:-}"

# Prepare schema
sysbench oltp_common \
  --db-driver=mysql \
  --mysql-host="$DBHOST" --mysql-port="$DBPORT" \
  --mysql-user="$DBUSER" --mysql-password="$DBPASS" \
  --tables=10 --table-size=500000 prepare

# Read-only baseline (60s)
sysbench oltp_read_only \
  --db-driver=mysql \
  --mysql-host="$DBHOST" --mysql-port="$DBPORT" \
  --mysql-user="$DBUSER" --mysql-password="$DBPASS" \
  --tables=10 --table-size=500000 \
  --threads=32 --time=60 --report-interval=10 run | tee baseline_ro.txt

# Read-write baseline (60s)
sysbench oltp_read_write \
  --db-driver=mysql \
  --mysql-host="$DBHOST" --mysql-port="$DBPORT" \
  --mysql-user="$DBUSER" --mysql-password="$DBPASS" \
  --tables=10 --table-size=500000 \
  --threads=32 --time=60 --report-interval=10 run | tee baseline_rw.txt

# Apply AI-suggested changes (indexes, rewrites, variables), then re-run the same tests:
# ... apply changes ...

# Compare
sysbench oltp_read_only ... | tee tuned_ro.txt
sysbench oltp_read_write ... | tee tuned_rw.txt

# Extract throughput/latency deltas
grep -E "transactions:|Latency" baseline_ro.txt tuned_ro.txt
grep -E "transactions:|Latency" baseline_rw.txt tuned_rw.txt

# Cleanup when done
sysbench oltp_common --db-driver=mysql \
  --mysql-host="$DBHOST" --mysql-port="$DBPORT" \
  --mysql-user="$DBUSER" --mysql-password="$DBPASS" cleanup

What “good” looks like:

  • Significant drop in avg/p95 latency for targeted queries.

  • Higher transactions/sec with similar or lower CPU/IO waits.

  • Reduced “Created_tmp_disk_tables”, fewer full table scans in EXPLAIN, and improved index usage.

Real-world style example

  • Symptom: 1.2 s SELECT with WHERE user_id=? AND status=? ORDER BY created_at DESC LIMIT 50 on a 50M-row orders table.

  • AI suggestion: Add composite index (user_id, status, created_at DESC). Rationale: filters on user_id/status; sort on created_at; index supports order + limit; covering if SELECT picks columns in index.

  • Action:

CREATE INDEX idx_orders_u_s_ca ON orders(user_id, status, created_at DESC)
  ALGORITHM=INPLACE, LOCK=NONE;
ANALYZE TABLE orders;
  • Result: Query drops from ~1.2 s to ~45 ms p95; buffer pool hit ratio improves; temp disk tables disappear for this query.

Common pitfalls and guardrails

  • Don’t send raw production data to external services. Prefer fingerprints, schemas, and EXPLAINs without literals.

  • Favor schema/query fixes over server-wide knob twiddling. Big global changes can hide root causes.

  • Change one thing at a time, benchmark, and keep a rollback plan.

  • Watch for transaction durability trade-offs when adjusting innodb_flush_log_at_trx_commit.

Conclusion and next steps

AI won’t replace your judgment, but it can massively accelerate how you find and validate MySQL performance fixes. The winning recipe is simple:

  • Instrument with slow logs and Performance Schema,

  • Summarize and sanitize,

  • Ask targeted AI questions,

  • Apply the smallest safe change,

  • Benchmark and iterate.

Your next step: 1) Enable your slow log and generate a bundle with the script above. 2) Ask an LLM for 1–3 concrete actions. 3) Validate with sysbench and keep a running “tuning notebook” of what worked.

If you want a follow-up post with a fully automated Bash pipeline (nightly bundles, AI suggestions, and Markdown reports), let me know which distro you’re on and I’ll tailor the scripts with apt/dnf/zypper paths.

Related posts