Performance & Indexing
Slow queries, missing indexes, locks and deadlocks.
12 articles
Find the Query Killing Your ERPNext: A MariaDB Slow Query Log Playbook
Your ERP is slow and three queries are responsible. Configure the MariaDB slow query log properly, run pt-query-digest, and read the numbers that tell you whether it is an index problem or a hardware problem.
Indexes That Actually Work: A Field Guide to tabGL Entry and tabStock Ledger Entry
ERPNext ships mostly single-column indexes, which are the wrong shape for the reports killing your server. Real EXPLAIN output, the leftmost prefix rule, key_len maths, and the composite indexes that fix ERP reports.
MySQL error 1213 deadlock found: retry it, you cannot prevent it
Error 1213 is InnoDB breaking a lock cycle on purpose, not a bug you can configure away. How to read the real deadlock, fix lock ordering, and retry the transaction correctly.
MySQL 'Lock wait timeout exceeded': find the blocker, not a retry loop
Error 1205 names the wrong transaction. Here is how to find the one actually holding the lock, why the default rollback behaviour is dangerous, and the real fixes.
Slow SELECT * on InnoDB While Disk Read Speed Looks Low
A full table scan crawls at 8 MB/s on a disk that does hundreds. The answer is request size, not disk speed, and here is how to measure it in four steps.
MySQL 'Waiting for table metadata lock': why 'online' ALTER TABLE still isn't free
An ALTER on a busy MySQL table hangs on 'Waiting for table metadata lock' and queues every query behind it. Here is what actually holds the lock, and why online DDL still stalls at cutover.
Postgres Table Bloat: Why the Default Autovacuum Settings Are Too Passive
Postgres waits until 20% of a busy table is dead tuples before autovacuum fires, so bloat piles up silently. Here is how to tune it per table and confirm vacuum is keeping up.
The ORM N+1 Query Problem: Why It Ships to Production Undetected
A page that should issue 2 queries issues 201, and no tool catches it before production. Here is how N+1 hides, how to spot it, and the one-line fix per ORM.
'The Server Is Slow' With Zero Budget and No APM: A Command-Line Triage
No Datadog, no New Relic, no dashboards. Here is a free, repeatable command-line order to find why a production server is slow, using tools already on the box.
Frappe Custom Fields Hit MariaDB's 'max 64 keys allowed': Finding the Excess Indexes
A heavily customised Item or Customer DocType can silently pile up indexes until MariaDB refuses at 64. Here is how to list them, find the wasteful ones, and remove them safely.
Idle in Transaction: The Postgres Sessions Quietly Bloating Your Database
A Postgres session sitting idle in transaction holds its snapshot and locks, blocks autovacuum, and grows table bloat for hours. Here is how to find them, kill them, and stop them coming back.
Reading Postgres EXPLAIN ANALYZE Correctly: the 'loops' Multiplier Everyone Misses
The 'actual time' on a plan node is per loop, not total. Miss the loops=N multiplier and you will underestimate a node's real cost by orders of magnitude and tune the wrong query.