-
100 SQL MCQ with Answers (SQL Test 2026)
These 100 SQL MCQs with answers cover the concepts that appear repeatedly in beginner assessments, from SELECT and filtering through joins, grouping, constraints, and set operations. Answer each question before opening its explanation, then use every miss to name the SQL concept you need to practise in a database. How to use these SQL MCQ […]
-
Java and MySQL Connectivity Using JDBC
Java Database Connectivity (JDBC) gives a Java program a standard API for opening a MySQL connection, sending SQL, and reading rows. The work starts with a compatible MySQL driver, a complete JDBC URL, and an account that can reach only the database your program needs. I compiled the example below with Maven resolving MySQL Connector/J […]
-
Debugging MySQL Memory Alerts: When innodb_buffer_pool_size Isn't the Whole Story
Posted on MySQL Ninjas | August 2026Every DBA has been there. An alert fires. You SSH into the box, run free -h, and see MySQL consuming far more RAM than you configured. You double-check innodb_buffer_pool_size. It's set correctly. So where is the memory going?
This is the story of debugging exactly that — on a Google Cloud c4d-standard-4 instance with 14.7 GB RAM, MySQL configured with a 7168 MB buffer pool, and mysqld RSS sitting at 10.4 GB. That's a 3.2 GB gap nobody could explain.
The Setup
We run a large MySQL fleet on GCP — over 200 DR replica nodes across three datacenters. As part of a buffer pool tuning project (fitting the right pool size to the right machine class), we set innodb_buffer_pool_size = 7168 MB on our c4d-standard-4 nodes (16 GB RAM, 14.7 GB usable).
Shortly after, memory alerts started firing. When I looked at what was actually happening:
$ ps aux | grep mysqld | awk '{print $6/1024 " MB"}'
10490 MB
mysqld RSS: ~10,490 MB.
Configured buffer pool: 7,168 MB.
Unexplained overhead: ~3,300 MB.
That's not rounding error. Something was eating memory we hadn't accounted for.
Layer 1: The Expected Overhead
The first thing to understand is that MySQL's RSS is never equal to innodb_buffer_pool_size. There are well-known, legitimate sources of overhead:
Component
Approximate Size
InnoDB internal structures (AHI, change buffer, log buffer)
200–400 MB
Performance Schema
100–300 MB (depends on config)
Per-thread buffers (sort, join, read)
Varies
Connection overhead
~1 MB × max_connections
On our node, max_connections = 3000. That's potentially 3 GB of THD memory root alone — a massive risk. We confirmed via sys.memory_by_thread_by_current_bytes that THD::main_mem_root had peaked at 7.7 GB under load. This alone was an OOM risk, not just a memory mystery.
Fix #1: Reduce max_connections from 3000 → 500 for DR replicas. They don't serve application traffic; they don't need 3000 slots.
After accounting for all of this, we could explain roughly 1,500–1,800 MB of the gap. We still had ~1,500 MB unaccounted for.
Layer 2: InnoDB Buffer Pool Chunk Overhead (~9% mmap Padding)
InnoDB allocates the buffer pool in chunks (innodb_buffer_pool_chunk_size, default 128 MB). When MySQL calls mmap() to allocate these chunks, the kernel doesn't give you exactly what you asked for — there's alignment overhead, guard pages, and internal bookkeeping in the virtual memory subsystem.
In practice, InnoDB's actual RSS from the buffer pool is roughly 9% higher than the configured size:
7168 MB × 1.09 ≈ 7813 MB
This is documented nowhere prominently, but you can verify it empirically by inspecting /proc/<pid>/smaps and looking for the large anonymous mappings:
$ grep -A2 "7[0-9][0-9][0-9][0-9] kB" /proc/$(pgrep mysqld)/smaps | head -40
You'll see several large anon regions just over 128 MB each — these are your buffer pool chunks, with mmap overhead baked in.
So after accounting for chunk overhead: 7813 MB + ~1500 MB other = ~9313 MB. We were at 10,490 MB. Still ~1,177 MB unexplained.
Layer 3: jemalloc — The Hidden Memory Manager
Here's where it gets interesting.
Our MySQL is started with:
LD_PRELOAD=/usr/lib64/libjemalloc.so.2
jemalloc is a high-performance memory allocator that replaces glibc's malloc. It's widely used in MySQL deployments to reduce fragmentation. But jemalloc has a behavior that surprises people: it holds onto freed memory as "dirty" pages, ready to hand back to threads quickly without going back to the OS.
Check if you're running jemalloc:
$ strings /proc/$(pgrep mysqld)/environ | grep LD_PRELOAD
On a busy primary, dirty pages are constantly churned and released. On an idle replica — like a DR slave not actively serving reads — jemalloc dirty pages accumulate and are never returned to the OS because there's no memory pressure to trigger eviction.
We confirmed this by inspecting /proc/<pid>/smaps for dirty anonymous pages not accounted for by InnoDB mappings. The amount held by jemalloc dirty pages on our idle DR replica: approximately 125 MB.
Layer 4: The gdb Arena Purge (Emergency Release)
To prove the jemalloc dirty-page theory and release the memory without restarting MySQL, we used a technique that feels like surgery: calling mallctl via gdb while MySQL was live.
First, find how many arenas exist:
$ gdb -p $(pgrep mysqld) --batch \
-ex 'call (int)mallctl("opt.narenas", 0, 0, 0, 0)'
Then purge all arenas:
$ gdb -p $(pgrep mysqld) --batch \
-ex 'call mallctl("arena.0.purge", 0, 0, 0, 0)' \
-ex 'call mallctl("arena.1.purge", 0, 0, 0, 0)' \
-ex 'call mallctl("arena.2.purge", 0, 0, 0, 0)' \
-ex 'detach'
After purging all arenas, RSS dropped by approximately 125 MB — confirming jemalloc dirty pages were the source. But it also confirmed they were only ~125 MB, not the ~1,500 MB we initially suspected.
Warning: Running gdb against a live MySQL instance briefly pauses the process. Use this only on non-critical instances (DR replicas, test nodes). Never on a primary under active traffic.
The Root Cause: MALLOC_CONF Not Set
The deeper issue wasn't the dirty pages themselves — it was that jemalloc's background decay was completely disabled.
jemalloc 5.x has a background_thread feature: a dedicated thread that periodically purges dirty pages back to the OS on a decay schedule. Without it, dirty pages only get released when a new allocation request needs the memory.
Check your jemalloc config:
$ strings /proc/$(pgrep mysqld)/environ | grep MALLOC_CONF
# if nothing returns, MALLOC_CONF is not set
On our node: MALLOC_CONF was not set at all. That means:
background_thread = false (default)
dirty_decay_ms = 10000 ms — but only triggered by allocation activity
muzzy_decay_ms = 10000 ms (same)
On an idle DR replica, there's minimal allocation activity. Decay never triggers. Dirty pages accumulate indefinitely.
The Fixes
Fix 1: Tune innodb_buffer_pool_size down slightly
Instead of 7168 MB, use 6144 MB on c4d-standard-4 nodes. This is a clean multiple of the 128 MB chunk size (48 chunks), saves ~1.1 GB RSS vs. 7168 MB accounting for mmap overhead, and leaves more headroom for thread buffers.
# Puppet/Hiera
percona::server::config::innodb_pool_size: 6144
Fix 2: Reduce max_connections
DR replicas don't serve application reads. 3,000 connections is both wasteful and an OOM risk.
max_connections = 500
Fix 3: Enable jemalloc background_thread with decay
Add to /etc/sysconfig/mysql (requires mysqld restart):
MALLOC_CONF="background_thread:true,dirty_decay_ms:5000,muzzy_decay_ms:5000"
This tells jemalloc to run a dedicated background thread to decay dirty pages within 5 seconds, even on an idle process. After this change, jemalloc RSS overhead on idle replicas dropped to near zero.
Fix 4: Add Swap
Our DR nodes had zero swap. When the OOM killer fires with no swap, it's immediate and brutal — no warning, no time to respond. Even 4 GB buys you time to react.
fallocate -l 4G /swapfile
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile
echo '/swapfile none swap sw 0 0' >> /etc/fstab
The Full Memory Accounting (Before vs. After)
Component
Before
After
InnoDB buffer pool (configured)
7,168 MB
6,144 MB
InnoDB mmap chunk overhead (~9%)
~645 MB
~553 MB
InnoDB internals (AHI, log buffer, etc.)
~300 MB
~300 MB
Performance Schema
~150 MB
~150 MB
Per-thread buffers
~2,000 MB (3000 conns)
~500 MB (500 conns)
jemalloc dirty pages
~125 MB (accumulating)
~50 MB (background decay)
OS + binary + misc
~200 MB
~200 MB
Total mysqld RSS
~10,490 MB
~7,897 MB
Key Takeaways
mysqld RSS ≈ buffer_pool × 1.09 + everything else. The 9% InnoDB mmap overhead is real and underdocumented.
max_connections is memory, not just a connection limit. 3,000 connections × ~1 MB THD overhead = 3 GB potential. Size it for actual use.
jemalloc on idle replicas accumulates dirty pages. If MALLOC_CONF isn't set, background_thread is off and decay only happens during allocation pressure. Fix it with background_thread:true.
gdb arena purge is a valid diagnostic tool on non-critical instances — but it's a band-aid. Fix MALLOC_CONF properly.
No swap = no warning before OOM. Even 4 GB buys you response time before the OOM killer fires.
Check /proc/<pid>/smaps when RSS is mysterious. It shows every memory region with sizes and dirty-page counts.
Quick Diagnostic Checklist
# 1. Current mysqld RSS
ps -o rss= -p $(pgrep mysqld) | awk '{print $1/1024 " MB"}'
# 2. Buffer pool size
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"
# 3. Are you running jemalloc?
strings /proc/$(pgrep mysqld)/environ | grep LD_PRELOAD
# 4. Is MALLOC_CONF set?
strings /proc/$(pgrep mysqld)/environ | grep MALLOC_CONF
# 5. Max connections configured vs. peak usage
mysql -e "SHOW VARIABLES LIKE 'max_connections'; SHOW STATUS LIKE 'Max_used_connections'"
# 6. Swap availability
free -h
# 7. smaps summary (requires root)
awk '/^Rss:/{r+=$2} /^Dirty:/{d+=$2} END{print "RSS:", r/1024, "MB | Dirty:", d/1024, "MB"}' \
/proc/$(pgrep mysqld)/smaps
If this helped you track down a memory mystery, drop a comment below. MySQL memory accounting is genuinely complex — the more we share real debugging stories, the better the community gets at it.
-
Install MySQL on macOS with Homebrew
Homebrew gives you the shortest path to a local MySQL server on macOS, but an install command alone does not prove that the server is running or that the client can connect. I checked the Homebrew mysql formula and built this sequence around the package, service, security, and connection checks it documents. Use this route […]
-
Connect Deno to MySQL with mysql2
Deno can use the mysql2 driver through its npm compatibility layer, so a Deno app can open a MySQL connection without reviving an older URL import. The working path is small: give the app a limited database account, read its connection values from environment variables, execute a parameterized query, then close the pool. I ran […]
|
