Slow Query Capture, PROFILE Evidence, Lock/Transaction Diagnostics, and Workload Correlation

An AtlasMart API call takes 4 seconds. The application log only says /recommendations timeout. One query engineer sees a scan in an old plan; another sees a lock wait from a different request. The useful investigation must correlate the same request window across driver timing, live transaction state, and a representative plan.

Learning outcomes

Reproducible lab baseline

1. Community and Enterprise slow-query capture differ

2. Baseline a deliberately broad query

The bad query forms a broad two-stream match and filters by a non-indexed shared segment. Its final aggregation looks compact, but the work before that aggregation can be much larger than the four returned region rows. This is the same anti-pattern Chapter 5 warned about: a small result is not proof of a small intermediate cardinality.

CYPHER 25
PROFILE
MATCH (c:ObsCustomer), (i:ObsItem)
WHERE i.segment = c.segment
RETURN c.region AS region, count(*) AS pairs
ORDER BY pairs DESC;

3. Change query/model dimension, not random settings

For the chapter fixture, the observed relationship already represents the relevant event. Traversing it avoids comparing every customer/item pair and asks the graph the question it was modeled to answer.

CYPHER 25
PROFILE
MATCH (c:ObsCustomer)-[:OBS_VIEWED]->(i:ObsItem)
WHERE i.active
RETURN c.region AS region,
       count(*) AS views,
       round(avg(i.price), 2) AS avgPrice
ORDER BY views DESC;

4. Reproduce a lock wait safely

The following Python exercise opens separate sessions/transactions. Writer A updates the single hot inventory node and intentionally holds the transaction for five seconds; Writer B then requests the same write lock. While A sleeps, use a third session with SHOW TRANSACTIONS. This is isolated, short, and fully resettable.

from neo4j import GraphDatabase
from threading import Thread, Event
import time

URI = "bolt://127.0.0.1:7687"
AUTH = ("neo4j", "atlasmart-course-2026")
driver = GraphDatabase.driver(URI, auth=AUTH)
first_has_lock = Event()

def writer_a():
    with driver.session(database="neo4j") as session:
        tx = session.begin_transaction()
        tx.run("MATCH (x:ObsInventory {obsId:$id}) "
               "SET x.reserved = x.reserved + 1, x.version = x.version + 1",
               id="INV-HOT-18").consume()
        first_has_lock.set()
        time.sleep(5)  # intentionally hold the write lock in this disposable lab
        tx.commit()

def writer_b():
    first_has_lock.wait()
    with driver.session(database="neo4j") as session:
        tx = session.begin_transaction()
        tx.run("MATCH (x:ObsInventory {obsId:$id}) "
               "SET x.reserved = x.reserved + 1, x.version = x.version + 1",
               id="INV-HOT-18").consume()
        tx.commit()

a=Thread(target=writer_a); b=Thread(target=writer_b)
a.start(); b.start()
# While A sleeps, use a third cypher-shell/session with SHOW TRANSACTIONS.
a.join(); b.join(); driver.close()

CYPHER 25
SHOW TRANSACTIONS
YIELD transactionId, username, database, currentQuery, currentQueryStatus,
      elapsedTime, waitTime, currentQueryElapsedTime, currentQueryWaitTime,
      activeLockCount, resourceInformation,
      currentQueryAllocatedBytes, allocatedDirectBytes, estimatedUsedHeapMemory,
      pageHits, pageFaults, currentQueryPageHits, currentQueryPageFaults,
      indexes, runtime, statusDetails
RETURN *
ORDER BY currentQueryElapsedTime DESC;

5. Do not “fix” contention with more connections

Increasing driver pool/concurrency can make a hot-node queue longer. If every reservation updates one global counter node, topology and connection count do not remove serialization. Repair options include narrower transaction duration, sharded/partitioned counters when semantics allow, relationship/event modeling, per-SKU inventory nodes, or an application workflow that reduces contention. The correct change depends on the invariant.

6. Separate server time from fetch/result/client time

Neo4j can finish producing records while the application still spends time fetching, decoding, serializing JSON, compressing, or sending a huge payload. Record at least request start, connection acquisition, query submission, first record, result consumption/summary, serialization end and response end. If the database segment is 50 ms and JSON/network is 900 ms, an index will not fix the user-visible bottleneck.

7. Parameter skew changes the incident

A customer with two relationships and a customer with 200,000 relationships are the same Cypher text but not the same workload shape. Preserve sparse, median, dense/high-degree, empty and worst-business-case parameters in performance tests. Correlate actual rows/DB hits/result size and transaction memory for each.

8. Acceptance test

A remediation is accepted only if the business result remains correct and the same evidence improves under representative load. Keep the before/after plan, request-ID timing, error rate, p50/p95/p99, transaction wait/memory fields and OS snapshot together.

Production judgment

Summary and next step

Slow-query and lock diagnosis now have observable mechanisms. Lesson 4 shifts from one incident to the longer horizon: store, index, degree and transaction-log growth and the capacity headroom required to survive it.

Authoritative references

• Current Neo4j versions — Current Neo4j 2026.07.1 and 5.26.30 LTS release snapshot.
• Monitoring overview — Current monitoring surfaces: logs, metrics, query/transaction management, connections, jobs, and reports.
• Metrics — Enterprise metrics architecture and supported export surfaces.
• Essential metrics — Server, Neo4j, cluster, and workload signals that require correlation rather than single-metric tuning.
• Metrics reference — Current page-cache, JVM, CPU, file-descriptor, transaction and store metric names.
• Logging — Current neo4j/debug/http/gc/query/security log behavior and edition boundaries.
• Show and terminate transactions — Current SHOW TRANSACTIONS fields for locks, wait time, memory, page hits/faults, runtime, indexes, and status.
• Manage queries — Current query visibility through transaction management rather than legacy dbms.listQueries().
• Memory configuration — Heap, page cache, native memory, transaction limits, and memory-recommendation guidance.
• Disks, RAM and other tips — Page-cache warmup, disk behavior, and operating-system considerations.
• Performance — Performance topics spanning memory, disks, statistics, execution plans, Bolt threads, and filesystem tuning.
• Show configuration settings — Current SHOW SETTINGS syntax and self-managed server scope.
• Transaction logs — Transaction-log location, rotation, retention, pruning, and capacity implications.
• Query tuning and PROFILE — Execution-plan evidence for rows, database hits, memory, access paths, and operator shape.
• Python driver performance — Driver-side timing, database selection, result consumption, and application/database boundary considerations.