Debugging MySQL Memory Alerts: When innodb_buffer_pool_size Isn't the Whole Story

Image
Posted on MySQL Ninjas | August 2026 Every 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 ...

Orchestrator RAFT Leader Check with Proxy pass with Basic Auth Using Nginx



Recently we have setup Orchestrator in High Availability mode using RAFT. We are running a 3 node setup in which there used to be a leader and rest 2 are Healthy raft member.


So To access orchestrator service we may only speak to the leader node using /api/leader-check as HTTP health check for our proxy. This url returns http 200 on leader and 404 on members. So using below code in open nginx we have setup http health check with basic auth.
Prerequisite: Lua support should be enabled in nginx.
Below code is to define upstreams with healthcheck:
upstream orchestrator { server 10.xx.xx.35:3000 max_fails=2; server 10.xx.xx.37:3000 max_fails=2; server 10.xx.xx.40:3000 max_fails=2; } lua_shared_dict myhealthcheck 1m; lua_socket_log_errors off; include /etc/nginx/lua/active_health_checks.lua;
Lua Script for health check: 
Before creating script we will need a hash with base64 encoding below is the command to create it:
echo -n 'user:password' | base64
Lua script defined in HealthCheck: /etc/nginx/lua/active_health_checks.lua
init_worker_by_lua_block { local hc = require "resty.upstream.healthcheck" local ok, err = hc.spawn_checker{ shm = "myhealthcheck", -- defined by "lua_shared_dict" upstream = "orchestrator", -- defined by "upstream" type = "http", http_req = "GET /api/leader-check HTTP/1.0\r\nHost: orchestrator.domain.com\r\nAuthorization: Basic {output of command}\r\n\r\n", interval = 3000, -- run the check cycle every 3 sec timeout = 3000, -- 3 sec is the timeout for network operations fall = 3, -- # of successive failures before turning a peer down rise = 2, -- # of successive successes before turning a peer up valid_statuses = {200, 302, 301}, -- a list valid HTTP status code concurrency = 10, -- concurrency level for test requests } if not ok then ngx.log(ngx.ERR, "failed to spawn health checker: ", err) return end }
Note: I am quite new to Nginx So I would love to see comments in improving it.

Comments

  1. Looks good! (Though I'm not very knowledgeable about Nginx and Lua).
    It's worth noting that in a Raft setup, even if you do happen to make a request to a non-leader node, the node you communicate with will forward the request to the leader. This works when the node you communicate with can _see_ the leader. It's best to communicate to the leader directly.

    ReplyDelete
    Replies
    1. Thanks much for the clarification! Please keep guiding us.

      Delete

Post a Comment

Popular posts from this blog

Azure VM Application Consistent MySQL DB Disk Snapshots

Debugging MySQL Memory Alerts: When innodb_buffer_pool_size Isn't the Whole Story

MongoDB InPlace Version Upgrade 3.4x to 3.6x