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 ...
Post a Comment

HA Provisioning For Backend Instances For Maxwell




In my previous post I have explain how we are running maxwell as service in Ubuntu. Now in this post I will be explaining how we achieved HA for Backend Instances (MySQL Hosts), Maxwell was using to read binlog.

So the stack we have introduced to achieve high availability contains HAProxy (http://www.haproxy.org/) and GTID enabled slaves. HAProxy is a free, very fast and reliable solution offering high availability, load balancing, and proxying for TCP and HTTP-based applications.

We have installed HAProxy on the local machine where we are running Maxwell and configured maxwell to connect to localhost where we have used HAProxy primary-secondary backup strategy using below config (/etc/haproxy/haproxy.cfg):

defaults
       mode tcp
       timeout connect 5000ms
       timeout client 86400s
       timeout server 86400s

listen  MySQL
        bind 127.0.0.1:3306
        mode tcp
        option mysql-check user haproxy  # mysql-check enables a database server check, to determine whether the node is currently operational
server Primary host1:3306 check
server Secondary host2:3306 check backup

Now stack becomes:


This stack has solved our problem incase MySQL slave goes down, Our whole pipeline will not be impacted.

Next Post: We will be publishing on How we achieved HA for Maxwell service. 

Comments

  1. PalashFebruary 13, 2019 at 8:33 PM

    can you please elaborate why were GTID slaves needed in the first place?

    ReplyDelete
  2. Ankit KumarFebruary 14, 2019 at 11:08 AM

    Hey Thanks for going through the blog. Maxwell read binlogs using either coordinates (binlog name and position) or GTID. So there might be data mismatch or coordinate not found issue if we do not use GTID enabled slaves and do switchover to secondary slave.

    ReplyDelete

Post a Comment

Popular posts from this blog

Azure VM Application Consistent MySQL DB Disk Snapshots

Image
Backup of Database is the pillar of our system which is necessary and mandatory to provide us data incase of crash, new machine provisioning and many other scenarios listed here .  As part of the backup process, a snapshot is taken, and the data is transferred to the Recovery Services vault with no impact on production workloads. The snapshot provides different levels of consistency, as described below: 1. Application-consistent: App-consistent backups capture memory content and pending I/O operations. App-consistent snapshots use a VSS writer (or pre/post scripts for Linux) to ensure the consistency of the app data before a backup occurs. When you're recovering a VM with an app-consistent snapshot, the VM boots up. There's no data corruption or loss. The apps start in a consistent state. 2. File-system consistent: File-system consistent backups provide consistency by taking a snapshot of all files at the same time. When you're recovering a VM with a file-system cons...
Read more

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 ...
Read more

MongoDB InPlace Version Upgrade 3.4x to 3.6x

Image
Today I am putting simple and straight forward steps to in-place upgrade / downgrade mongoDB single as well as replica set cluster. Note: Steps are for CentOS kindly modify for other OS. Warning:  IMPORTANT Always backup all of your data before upgrading MongoDB and test steps in nonprod env first. 1. Checking compatibility version db.system.version.find(); 2. setting compatiblity version db.adminCommand( { setFeatureCompatibilityVersion: "3.4" } ) 3. change dir cd /tmp/ 4. download pkgs ( link ) wget https://repo.mongodb.org/yum/redhat/7/mongodb-org/3.6/x86_64/RPMS/mongodb-org-shell-3.6.14-1.el7.x86_64.rpm && wget https://repo.mongodb.org/yum/redhat/7/mongodb-org/3.6/x86_64/RPMS/mongodb-org-server-3.6.14-1.el7.x86_64.rpm 5. cd home cd 6. stopping mongo systemctl stop mongod 7. backing up conf cp /etc/mongod.conf /tmp/ 8. removing current Binary yum remove mongod* 9. cd download dir cd /tmp/ 10. installing n...
Read more