{"id":51801,"date":"2025-08-23T06:56:24","date_gmt":"2025-08-23T06:56:24","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=51801"},"modified":"2025-08-23T07:11:37","modified_gmt":"2025-08-23T07:11:37","slug":"app-performance-optimization-complete-checklist","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/app-performance-optimization-complete-checklist\/","title":{"rendered":"App Performance Optimization \u2013 Complete Checklist"},"content":{"rendered":"\n<p><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Area<\/th><th>Sub\u2011Area<\/th><th>Action Items<\/th><\/tr><\/thead><tbody><tr><td><strong>0) Readiness &amp; Goals<\/strong><\/td><td>SLOs &amp; Metrics<\/td><td>Define business SLOs, Core Web Vitals, establish baselines, create dashboards, prepare test data<\/td><\/tr><tr><td><\/td><td>Profiling<\/td><td>Enable APM\/Tracing, endpoint heatmaps, profilers, DB slow query logs, Lighthouse runs<\/td><\/tr><tr><td><strong>1) Quick Wins<\/strong><\/td><td>Immediate Optimizations<\/td><td>Enable compression, cache static assets, use CDN, connection reuse, image optimization, lazy-load, fix N+1 queries<\/td><\/tr><tr><td><strong>A) Application Layer<\/strong><\/td><td>Framework Settings<\/td><td>Production mode, config\/route\/view cache, autoloader optimization, template caching, DTO optimization, pagination, compression<\/td><\/tr><tr><td><\/td><td>Code Quality<\/td><td>Remove N+1 queries, memoization, async external calls, circuit breakers\/timeouts, reduce payloads, early validation, idempotency<\/td><\/tr><tr><td><\/td><td>Language-Specific<\/td><td>PHP: OPcache, Octane, Horizon, Redis, Eloquent optimizations; Spring Boot: JVM heap, GC, HikariCP tuning, Actuator; Flask: gunicorn\/uwsgi tuning, async; Node.js: clustering, connection pools, monitor event loop<\/td><\/tr><tr><td><\/td><td>API Design<\/td><td>Idempotent GETs, PATCH for partial updates, ETags, rate limits, batch endpoints<\/td><\/tr><tr><td><strong>B) Backend Runtime<\/strong><\/td><td>General<\/td><td>LTS runtimes, JIT\/OPcache, minimal base images, container resource limits, warm-up strategies<\/td><\/tr><tr><td><strong>C) Frontend<\/strong><\/td><td>Web Assets<\/td><td>Lighthouse audits, bundle splitting, tree-shaking, minification, HTTP\/2 or 3, critical CSS inlined, preload\/preconnect, font optimization, Service Worker caching, limit 3rd-party scripts<\/td><\/tr><tr><td><strong>D) Database<\/strong><\/td><td>SQL Engine<\/td><td>Correct engine, buffer pool sizing, connection pools, indexing, query rewrites, replicas for reads, caching, analyze\/vacuum\/optimize, backup &amp; recovery<\/td><\/tr><tr><td><strong>E) Caches &amp; Messaging<\/strong><\/td><td>Redis\/Memcached, Queues<\/td><td>Namespaces &amp; TTLs, cache stampede protection, CDN caching, message queues for async tasks<\/td><\/tr><tr><td><strong>F) Web Server\/Proxy<\/strong><\/td><td>Nginx\/Apache\/Tomcat<\/td><td>Worker tuning, keep-alive, compression, serve static via CDN, health checks, TLS optimization<\/td><\/tr><tr><td><strong>G) Operating System<\/strong><\/td><td>Linux\/Windows<\/td><td>Increase ulimit, kernel TCP tuning, adjust swappiness, disable THP, NUMA tuning, time sync<\/td><\/tr><tr><td><strong>H) Network &amp; Dependencies<\/strong><\/td><td>Latency Reduction<\/td><td>DNS latency check, TLS optimization, connection pooling, audit third-party APIs<\/td><\/tr><tr><td><strong>I) Observability &amp; Guardrails<\/strong><\/td><td>Monitoring<\/td><td>Centralized logging, RED\/USE dashboards, SLO alerting, exception tracking, feature flags, canaries<\/td><\/tr><tr><td><strong>J) Load &amp; Resilience Testing<\/strong><\/td><td>Testing<\/td><td>Define load\/stress\/spike\/soak tests, tools (k6, JMeter, Locust), capture capacity curves, chaos experiments<\/td><\/tr><tr><td><strong>K) Cloud\/Infra<\/strong><\/td><td>Kubernetes\/VMs<\/td><td>Right-size instances, autoscaling, pod requests\/limits, node locality, service mesh overhead review<\/td><\/tr><tr><td><strong>L) Data Shape &amp; Storage<\/strong><\/td><td>Payload\/Data<\/td><td>Cap payloads, compress responses, use object storage, partition large tables, archival policies<\/td><\/tr><tr><td><strong>M) Security vs Performance<\/strong><\/td><td>Secure &amp; Fast<\/td><td>WAF\/CDN profiling, JWT size control, efficient crypto, TLS offload at edge<\/td><\/tr><tr><td><strong>N) CI\/CD &amp; Governance<\/strong><\/td><td>DevOps<\/td><td>Performance budgets in CI, regression tests, DB migration rehearsals, release changelogs with perf notes<\/td><\/tr><tr><td><strong>O) Playbooks &amp; Runbooks<\/strong><\/td><td>Incident Mgmt<\/td><td>High latency runbooks, DB emergency actions, cache outage mitigation<\/td><\/tr><tr><td><strong>P) Verification<\/strong><\/td><td>Post-Changes<\/td><td>Before\/after metrics comparison, user-visible improvements validated, update capacity plan<\/td><\/tr><tr><td><strong>Appendix: Laravel<\/strong><\/td><td>Optimization<\/td><td>Config\/route\/view cache, Octane with Swoole, Eloquent optimizations, Redis for cache\/session\/queue<\/td><\/tr><tr><td><strong>Appendix: Spring Boot<\/strong><\/td><td>Optimization<\/td><td>HikariCP tuning, G1\/ZGC, JFR, Undertow\/Netty, Micrometer + Prometheus\/Grafana<\/td><\/tr><tr><td><strong>Appendix: Flask<\/strong><\/td><td>Optimization<\/td><td>Gunicorn tuning, preload, async frameworks, template caching<\/td><\/tr><tr><td><strong>Appendix: MySQL\/MariaDB<\/strong><\/td><td>Optimization<\/td><td>Buffer pool\/log sizing, slow query log, indexing, duplicate index cleanup<\/td><\/tr><tr><td><strong>Appendix: Nginx\/Apache\/Tomcat<\/strong><\/td><td>Optimization<\/td><td>Worker tuning, Brotli, static serving via CDN, Tomcat thread tuning<\/td><\/tr><tr><td><strong>Appendix: Linux<\/strong><\/td><td>Sysctl &amp; Limits<\/td><td>Ulimit \u2265 65535, sysctl tuning, swappiness, disable THP<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>0) Readiness &amp; Goals<\/strong><\/h1>\n\n\n\n<p>since you\u2019re on <strong>XAMPP + Laravel + PHP + MySQL + Linux<\/strong>, and you want <strong>SLIs (latency, error rate, throughput) &amp; metrics<\/strong> just in your <strong>Dev environment<\/strong>, you don\u2019t need enterprise-grade APMs like New Relic yet. Laravel\/PHP has some <strong>native and lightweight options<\/strong> that can give you <strong>per-request response time, DB query timings, and code profiling<\/strong>.<\/p>\n\n\n\n<p>Here\u2019s a structured set of recommendations:<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udd39 Native \/ Laravel-Friendly Tooling<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1. <strong>Laravel Telescope (Official)<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>What it does:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Profiles each request (execution time, memory, status code).<\/li>\n\n\n\n<li>Shows all queries with timing, N+1 warnings, slow query detection.<\/li>\n\n\n\n<li>Displays jobs, cache hits\/misses, exceptions, mail, logs, etc.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Why good for Dev:<\/strong> Easy to install, beautiful dashboard, zero external infra.<\/li>\n\n\n\n<li><strong>Install:<\/strong> <code>composer require laravel\/telescope --dev php artisan telescope:install php artisan migrate php artisan serve<\/code><\/li>\n\n\n\n<li>Accessible at <code>\/telescope<\/code> in dev.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">2. <strong>Laravel Debugbar<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>What it does:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Adds a debug toolbar at the bottom of each page.<\/li>\n\n\n\n<li>Shows request duration, memory usage, DB queries with time.<\/li>\n\n\n\n<li>Highlights slow queries and duplicate queries.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Why good for Dev:<\/strong> Extremely lightweight, immediate visibility.<\/li>\n\n\n\n<li><strong>Install:<\/strong> <code>composer require barryvdh\/laravel-debugbar --dev<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">3. <strong>Clockwork<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>What it does:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Adds Chrome\/Firefox extension for profiling Laravel requests.<\/li>\n\n\n\n<li>Records request duration, queries, cache, events, logs, timeline.<\/li>\n\n\n\n<li>More \u201cdeveloper-friendly\u201d than Debugbar because it integrates with browser dev tools.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Install:<\/strong> <code>composer require itsgoingd\/clockwork --dev php artisan vendor:publish --tag=clockwork-config<\/code><\/li>\n\n\n\n<li>View in <strong>Clockwork browser extension<\/strong> or <code>\/__clockwork<\/code> endpoint.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">4. <strong>XHProf \/ Tideways \/ Blackfire (Low-Level Profilers)<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>What they do:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Provide <strong>per-function and per-line profiling<\/strong>.<\/li>\n\n\n\n<li>Show CPU time, memory usage, call counts.<\/li>\n\n\n\n<li>Help track bottlenecks beyond queries (e.g., loops, serialization).<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Why good for Dev:<\/strong> Deeper insight when Laravel Telescope\/Debugbar isn\u2019t enough.<\/li>\n\n\n\n<li><strong>Options:<\/strong>\n<ul class=\"wp-block-list\">\n<li><strong>XHProf<\/strong> (free, simple, supported by XHGUI web UI).<\/li>\n\n\n\n<li><strong>Tideways\/XHProf fork<\/strong> (better maintained).<\/li>\n\n\n\n<li><strong>Blackfire.io<\/strong> (commercial but free Dev tier).<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udd39 Metrics &amp; SLI Extraction<\/h2>\n\n\n\n<p>Since you want <strong>SLIs (latency, error rate, throughput)<\/strong>, you can:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Use Laravel Telescope \/ Debugbar Data<\/strong>\n<ul class=\"wp-block-list\">\n<li>Export query logs &amp; request timings \u2192 store in Prometheus\/Grafana (optional).<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Laravel Prometheus Exporter<\/strong>\n<ul class=\"wp-block-list\">\n<li>Package: <a href=\"https:\/\/github.com\/superbalist\/laravel-prometheus-exporter\" target=\"_blank\" rel=\"noopener\"><code>superbalist\/laravel-prometheus-exporter<\/code><\/a>.<\/li>\n\n\n\n<li>Exposes a <code>\/metrics<\/code> endpoint for Prometheus with:\n<ul class=\"wp-block-list\">\n<li>Request duration histogram<\/li>\n\n\n\n<li>Query count &amp; time<\/li>\n\n\n\n<li>Cache hits\/misses<\/li>\n\n\n\n<li>Jobs queued\/processed<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Self-hosted Grafana + Prometheus<\/strong> (optional for Dev)\n<ul class=\"wp-block-list\">\n<li>Pull <code>\/metrics<\/code> endpoint.<\/li>\n\n\n\n<li>Create SLI dashboards:\n<ul class=\"wp-block-list\">\n<li><strong>Latency:<\/strong> <code>http_request_duration_seconds<\/code> p95, p99.<\/li>\n\n\n\n<li><strong>Error rate:<\/strong> <code>http_requests_total{status=\"5xx\"} \/ http_requests_total<\/code>.<\/li>\n\n\n\n<li><strong>Traffic:<\/strong> requests\/sec.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 Suggested Dev Setup (Practical &amp; Lightweight)<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Start with <strong>Laravel Telescope<\/strong> \u2192 best native tool for queries + request profiling.<\/li>\n\n\n\n<li>Add <strong>Clockwork or Debugbar<\/strong> \u2192 for real-time in-browser visibility.<\/li>\n\n\n\n<li>If you want structured SLI metrics \u2192 add <strong>Laravel Prometheus Exporter<\/strong> and run Prometheus + Grafana in Docker.<\/li>\n\n\n\n<li>For deeper profiling \u2192 plug in <strong>XHProf\/XHGUI<\/strong> when you hit complex bottlenecks.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p>\ud83d\udc49 My recommendation:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use <strong>Telescope + Debugbar<\/strong> for day-to-day dev.<\/li>\n\n\n\n<li>Add <strong>Prometheus Exporter<\/strong> only if you want a <strong>Grafana SLI dashboard<\/strong>.<\/li>\n\n\n\n<li>Keep <strong>XHProf\/XHGUI<\/strong> in your toolkit for <strong>deep-dive debugging<\/strong>.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Database<\/strong><\/h1>\n\n\n\n<p>Great question. Here\u2019s a <strong>practical, safe set of MySQL\/MariaDB settings<\/strong> you can enable\/tune to process queries efficiently and avoid CPU\/memory blow-ups, tailored for a <strong>web app (Laravel\/PHP) on InnoDB<\/strong>. I\u2019ve grouped them by purpose and included <strong>suggested starting values<\/strong> plus a ready-to-drop <strong><code>my.cnf<\/code> template<\/strong>. (Use InnoDB everywhere; avoid MyISAM for OLTP.)<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Key principles (before you tune)<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Prefer <strong>InnoDB<\/strong>; set proper <strong>memory split<\/strong>: big \u201cglobal\u201d InnoDB cache, small \u201cper-connection\u201d buffers (to avoid RAM spikes).<\/li>\n\n\n\n<li>Keep <strong>max_connections<\/strong> realistic; too high \u2192 memory explosion under load.<\/li>\n\n\n\n<li>Turn on <strong>slow query log<\/strong> and fix queries\/indexes first\u2014it\u2019s the biggest win.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">A) Core InnoDB memory &amp; durability<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Setting<\/th><th>What it does<\/th><th>Starting point (single DB server)<\/th><th>Notes<\/th><\/tr><\/thead><tbody><tr><td><code>innodb_buffer_pool_size<\/code><\/td><td>Main data+index cache<\/td><td><strong>60\u201370% of RAM<\/strong> (DB-only host)<\/td><td>Largest lever for CPU (less I\/O).<\/td><\/tr><tr><td><code>innodb_buffer_pool_instances<\/code><\/td><td>Concurrency in buffer pool<\/td><td>1 per ~8\u201316GB (e.g., <strong>2<\/strong> for 16GB)<\/td><td>Don\u2019t overdo; 1\u20138 is typical.<\/td><\/tr><tr><td><code>innodb_log_file_size<\/code><\/td><td>Redo log size<\/td><td><strong>1\u20134GB<\/strong> total (e.g., 2\u00d71GB)<\/td><td>Bigger = fewer flushes; faster writes.<\/td><\/tr><tr><td><code>innodb_log_buffer_size<\/code><\/td><td>Buffer for redo before flush<\/td><td><strong>64\u2013256MB<\/strong><\/td><td>Helps heavy write bursts.<\/td><\/tr><tr><td><code>innodb_flush_log_at_trx_commit<\/code><\/td><td>Durability vs speed<\/td><td><strong>1<\/strong> (full ACID) or <strong>2<\/strong> (faster dev)<\/td><td><code>2<\/code> reduces fsyncs (OK in dev).<\/td><\/tr><tr><td><code>innodb_flush_method<\/code><\/td><td>Flush mode<\/td><td><strong>O_DIRECT<\/strong> (Linux)<\/td><td>Helps avoid double buffering.<\/td><\/tr><tr><td><code>innodb_file_per_table<\/code><\/td><td>Tablespaces<\/td><td><strong>ON<\/strong><\/td><td>Default; good for space and manageability.<\/td><\/tr><tr><td><code>innodb_flush_neighbors<\/code><\/td><td>SSD optimization<\/td><td><strong>0<\/strong> (on SSD\/NVMe)<\/td><td>Reduces extra flush work.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">B) Concurrency &amp; CPU<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Setting<\/th><th>What it does<\/th><th>Starting point<\/th><th>Notes<\/th><\/tr><\/thead><tbody><tr><td><strong>MariaDB<\/strong> <code>thread_pool=ON<\/code><\/td><td>Thread pool (caps active threads)<\/td><td>ON<\/td><td>Great at preventing CPU thrash (MariaDB only).<\/td><\/tr><tr><td><code>innodb_thread_concurrency<\/code><\/td><td>InnoDB internal concurrency<\/td><td><strong>0<\/strong> (auto)<\/td><td>Let InnoDB self-tune.<\/td><\/tr><tr><td><code>table_open_cache<\/code><\/td><td>Open tables cache<\/td><td><strong>2000\u20134000<\/strong><\/td><td>Raise if \u201cOpened_tables\u201d increases quickly.<\/td><\/tr><tr><td><code>table_definition_cache<\/code><\/td><td>Cached table defs<\/td><td><strong>2000<\/strong><\/td><td>Helps many tables\/schemas.<\/td><\/tr><tr><td><code>open_files_limit<\/code><\/td><td>Process file limit<\/td><td><strong>&gt;= 65535<\/strong><\/td><td>Aligns with table cache.<\/td><\/tr><tr><td><code>skip-name-resolve<\/code><\/td><td>Avoid DNS on connect<\/td><td>Enable<\/td><td>Prevents connect latency spikes.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">C) Per-connection memory (prevent RAM blow-ups)<\/h2>\n\n\n\n<p>These allocate <strong>per connection<\/strong>. Keep them modest and keep <code>max_connections<\/code> realistic.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Setting<\/th><th>Purpose<\/th><th>Starting point<\/th><th>Warning<\/th><\/tr><\/thead><tbody><tr><td><code>max_connections<\/code><\/td><td>Hard cap on sessions<\/td><td><strong>150\u2013300<\/strong><\/td><td>Each conn can allocate MBs \u2192 memory spikes.<\/td><\/tr><tr><td><code>tmp_table_size<\/code><\/td><td>Mem temp tables<\/td><td><strong>64\u2013128MB<\/strong><\/td><td>Pairs with <code>max_heap_table_size<\/code>.<\/td><\/tr><tr><td><code>max_heap_table_size<\/code><\/td><td>In-RAM temp tables<\/td><td><strong>64\u2013128MB<\/strong><\/td><td>Don\u2019t set wildly high.<\/td><\/tr><tr><td><code>join_buffer_size<\/code><\/td><td>No-index joins<\/td><td><strong>256KB\u20131MB<\/strong><\/td><td>Per-join, per-thread; keep small.<\/td><\/tr><tr><td><code>sort_buffer_size<\/code><\/td><td>ORDER BY sorts<\/td><td><strong>512KB\u20132MB<\/strong><\/td><td>Too big \u00d7 many connections = OOM.<\/td><\/tr><tr><td><code>read_buffer_size<\/code><\/td><td>Seq. scans<\/td><td><strong>512KB\u20131MB<\/strong><\/td><td>Keep conservative.<\/td><\/tr><tr><td><code>read_rnd_buffer_size<\/code><\/td><td>Random reads after sort<\/td><td><strong>256KB\u20131MB<\/strong><\/td><td>Keep conservative.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">D) Optimizer &amp; SQL safety<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Setting<\/th><th>What it does<\/th><th>Starting point<\/th><th>Notes<\/th><\/tr><\/thead><tbody><tr><td><code>sql_require_primary_key<\/code> (MySQL 8+)<\/td><td>Enforce PKs<\/td><td><strong>ON<\/strong><\/td><td>Vital for InnoDB performance &amp; replication.<\/td><\/tr><tr><td><code>optimizer_switch<\/code><\/td><td>Plan tweaks<\/td><td>Defaults are fine<\/td><td>Focus on indexes first.<\/td><\/tr><tr><td><code>innodb_autoinc_lock_mode<\/code><\/td><td>AUTO_INCREMENT contention<\/td><td><strong>2<\/strong><\/td><td>Better concurrency.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">E) I\/O &amp; background<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Setting<\/th><th>What it does<\/th><th>Starting point<\/th><th>Notes<\/th><\/tr><\/thead><tbody><tr><td><code>innodb_io_capacity<\/code><\/td><td>Flush\/IO pacing<\/td><td><strong>200\u2013800<\/strong> (SATA) \/ <strong>1000\u20134000<\/strong> (NVMe)<\/td><td>Match device capability.<\/td><\/tr><tr><td><code>innodb_read_io_threads<\/code><\/td><td>Parallel read threads<\/td><td><strong>4<\/strong><\/td><td>4\u20138 typical.<\/td><\/tr><tr><td><code>innodb_write_io_threads<\/code><\/td><td>Parallel write threads<\/td><td><strong>4<\/strong><\/td><td>4\u20138 typical.<\/td><\/tr><tr><td><code>innodb_adaptive_hash_index<\/code><\/td><td>Hot-range acceleration<\/td><td><strong>ON<\/strong> (default)<\/td><td>Consider OFF if contention (rare).<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">F) Logging &amp; instrumentation<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Setting<\/th><th>Purpose<\/th><th>Suggested<\/th><\/tr><\/thead><tbody><tr><td><code>slow_query_log=ON<\/code><\/td><td>Capture slow queries<\/td><td>Turn on in <strong>dev<\/strong> and <strong>prod<\/strong>.<\/td><\/tr><tr><td><code>long_query_time=0.2<\/code><\/td><td>Slow threshold<\/td><td>200ms (dev). 0.5\u20131s in prod.<\/td><\/tr><tr><td><code>log_queries_not_using_indexes=ON<\/code><\/td><td>Find table scans<\/td><td>Use in dev (noisy in prod).<\/td><\/tr><tr><td><code>performance_schema<\/code><\/td><td>Engine for metrics<\/td><td>Keep <strong>ON<\/strong>, but avoid enabling every consumer (memory!).<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">G) Binary logs &amp; timeouts (dev vs prod)<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Setting<\/th><th>Purpose<\/th><th>Dev<\/th><th>Prod<\/th><\/tr><\/thead><tbody><tr><td><code>skip-log-bin<\/code> or <code>log_bin<\/code><\/td><td>Binary logging<\/td><td><strong>Disable<\/strong> if no replicas<\/td><td>Enable (HA\/backups)<\/td><\/tr><tr><td><code>sync_binlog<\/code><\/td><td>Crash safety for binlog<\/td><td><strong>0<\/strong><\/td><td>1 (strong durability)<\/td><\/tr><tr><td><code>wait_timeout<\/code><\/td><td>Idle conn close<\/td><td><strong>60\u2013300s<\/strong><\/td><td>300\u2013600s<\/td><\/tr><tr><td><code>interactive_timeout<\/code><\/td><td>Idle interactive<\/td><td><strong>300\u2013600s<\/strong><\/td><td>600\u20131800s<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">H) MariaDB specifics (if you\u2019re on MariaDB)<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Setting<\/th><th>Why<\/th><th>Value<\/th><\/tr><\/thead><tbody><tr><td><code>thread_pool=ON<\/code><\/td><td>Avoid CPU storms under load<\/td><td>ON<\/td><\/tr><tr><td><strong>Query Cache<\/strong><\/td><td>Causes contention<\/td><td><strong>OFF<\/strong> (remove\/disable)<\/td><\/tr><tr><td><code>aria_pagecache_buffer_size<\/code><\/td><td>If using Aria tmp tables<\/td><td>Keep small; prefer InnoDB tmp tables<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Ready-to-use <code>my.cnf<\/code> template (Linux)<\/h2>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>Adjust memory-sized values to your RAM. Example below assumes ~16GB host <strong>primarily<\/strong> for MySQL.<\/p>\n<\/blockquote>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">&#91;mysqld]\n<span class=\"hljs-comment\"># General<\/span>\nuser = mysql\nbind-address = <span class=\"hljs-number\">0.0<\/span><span class=\"hljs-number\">.0<\/span><span class=\"hljs-number\">.0<\/span>\nskip-name-resolve\nsql_require_primary_key = ON\n\n<span class=\"hljs-comment\"># InnoDB core<\/span>\ndefault_storage_engine = InnoDB\ninnodb_buffer_pool_size = <span class=\"hljs-number\">10<\/span>G\ninnodb_buffer_pool_instances = <span class=\"hljs-number\">2<\/span>\ninnodb_log_file_size = <span class=\"hljs-number\">1<\/span>G\ninnodb_log_buffer_size = <span class=\"hljs-number\">256<\/span>M\ninnodb_flush_log_at_trx_commit = <span class=\"hljs-number\">2<\/span>      <span class=\"hljs-comment\"># 1 in prod for full durability<\/span>\ninnodb_flush_method = O_DIRECT\ninnodb_file_per_table = <span class=\"hljs-number\">1<\/span>\ninnodb_flush_neighbors = <span class=\"hljs-number\">0<\/span>\ninnodb_autoinc_lock_mode = <span class=\"hljs-number\">2<\/span>\ninnodb_io_capacity = <span class=\"hljs-number\">1000<\/span>\ninnodb_read_io_threads = <span class=\"hljs-number\">4<\/span>\ninnodb_write_io_threads = <span class=\"hljs-number\">4<\/span>\n\n<span class=\"hljs-comment\"># Concurrency &amp; caches<\/span>\ntable_open_cache = <span class=\"hljs-number\">4000<\/span>\ntable_definition_cache = <span class=\"hljs-number\">2000<\/span>\nopen_files_limit = <span class=\"hljs-number\">65535<\/span>\nmax_connections = <span class=\"hljs-number\">250<\/span>\nthread_cache_size = <span class=\"hljs-number\">64<\/span>\n\n<span class=\"hljs-comment\"># Per-connection memory (keep conservative)<\/span>\ntmp_table_size = <span class=\"hljs-number\">128<\/span>M\nmax_heap_table_size = <span class=\"hljs-number\">128<\/span>M\njoin_buffer_size = <span class=\"hljs-number\">1<\/span>M\nsort_buffer_size = <span class=\"hljs-number\">1<\/span>M\nread_buffer_size = <span class=\"hljs-number\">1<\/span>M\nread_rnd_buffer_size = <span class=\"hljs-number\">1<\/span>M\n\n<span class=\"hljs-comment\"># Logging &amp; instrumentation<\/span>\nslow_query_log = ON\nslow_query_log_file = \/<span class=\"hljs-keyword\">var<\/span>\/log\/mysql\/slow.log\nlong_query_time = <span class=\"hljs-number\">0.2<\/span>\nlog_queries_not_using_indexes = ON\nperformance_schema = ON\n\n<span class=\"hljs-comment\"># Binary log (dev)<\/span>\nskip-log-bin\n<span class=\"hljs-comment\"># For prod:<\/span>\n<span class=\"hljs-comment\"># log_bin = \/var\/lib\/mysql\/binlog<\/span>\n<span class=\"hljs-comment\"># sync_binlog = 1<\/span>\n\n<span class=\"hljs-comment\"># Timeouts<\/span>\nwait_timeout = <span class=\"hljs-number\">120<\/span>\ninteractive_timeout = <span class=\"hljs-number\">600<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>After changing redo log size (<code>innodb_log_file_size<\/code>), stop MySQL cleanly, rename old ib_logfiles if needed (MySQL 5.7), then start.<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">How to confirm impact (quick checks)<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Memory planning (rule of thumb)<\/strong><br><code>Total RAM \u2248 innodb_buffer_pool_size + global overhead + (max_connections \u00d7 per-connection buffers peak)<\/code><br>\u2192 Keep buffers modest; don\u2019t push <code>max_connections<\/code> beyond what RAM allows.<\/li>\n\n\n\n<li><strong>Watch these during load:<\/strong>\n<ul class=\"wp-block-list\">\n<li><code>SHOW GLOBAL STATUS LIKE 'Threads_connected';<\/code><\/li>\n\n\n\n<li><code>SHOW GLOBAL STATUS LIKE 'Opened_tables';<\/code> (shouldn\u2019t climb too fast)<\/li>\n\n\n\n<li><code>SHOW ENGINE INNODB STATUS;<\/code> (check buffer pool hit rate, waits)<\/li>\n\n\n\n<li><code>EXPLAIN ANALYZE &lt;query><\/code> (MySQL 8+\/MariaDB), fix missing indexes.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Tune iteratively:<\/strong> raise <code>table_open_cache<\/code> if <code>Opened_tables<\/code> grows; reduce per-connection buffers if memory spikes; if CPU high with many threads, <strong>MariaDB <code>thread_pool=ON<\/code><\/strong> (or reduce <code>max_connections<\/code>).<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Safe rollout order<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Turn on <strong>slow log<\/strong> and fix top queries\/indexes.<\/li>\n\n\n\n<li>Set <strong>buffer pool<\/strong> (and redo log) to match RAM.<\/li>\n\n\n\n<li>Cap <strong>max_connections<\/strong> and shrink per-connection buffers.<\/li>\n\n\n\n<li>Enable <strong>skip-name-resolve<\/strong>, tune <strong>table caches<\/strong>.<\/li>\n\n\n\n<li>Consider <strong>thread pool (MariaDB)<\/strong> and I\/O capacity.<\/li>\n\n\n\n<li>Re-test; iterate.<\/li>\n<\/ol>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Area Sub\u2011Area Action Items 0) Readiness &amp; Goals SLOs &amp; Metrics Define business SLOs, Core Web Vitals, establish baselines, create dashboards, prepare test data Profiling Enable APM\/Tracing, endpoint heatmaps, profilers,&#8230; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[2],"tags":[],"class_list":["post-51801","post","type-post","status-publish","format-standard","hentry","category-uncategorised"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/51801","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=51801"}],"version-history":[{"count":4,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/51801\/revisions"}],"predecessor-version":[{"id":51805,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/51801\/revisions\/51805"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=51801"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=51801"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=51801"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}