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

MySQL: How To Sync Specific Table(s) from Master to Slave





Most of us used to get errors like (Row not found, Duplicate row etc) on slave in Master slave replication and sometimes it is very difficult to find unsynced data and fix it while we know table name(s).

There are few recommended tools from percona to check replication integrity and fixed unsync data:

1. pt-table-checksum: performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master.

2. pt-table-sync: synchronizes data efficiently between MySQL tables.

Recommended tools work well with limited data with less time, But what if table size is more and we don't have time to analyze each row of the table with recommended tools which is time consuming?

We can achieve this with very simple step though:

Let's assume a simple cluster(MySQL 5.7.XX) with master m1 and slave s1 with GTID based replication replicating a db(db_ankit) with 5 tables(a,b,c,d,e). Now due to some issue data of table c in db_ankit on slave s1 got corrupted and table if huge in size like > 500GB.

  1. On Slave s1: Start slave with replication filter to ignore table c in replication.
  2. From Master m1: Dump table c with replication co-ordinates or GTID. mydumper can be used with multiple threads for faster dump according to the server capacity.
  3. On Slave s1: Rename current table c and restore(myloader with multiple threads will save your time if dump has been taken with mydumper) dumped table.
  4. On Slave s1: Once restore finishes stop the slave and note down the master exec position and GTID executed sets.
  5. On Slave s1: Reset slave & Change replication Filter to replicate only c.
  6. On Slave s1: Run Change master to start replication of table c from coordinates of dump taken in step 2.
  7. On Slave s1: start slave until the coordinates noted in step 4.
  8. On Slave s1: when replication lag becomes 0 and sql_thread_status 0 remove replication filter and start slave without any restriction.


In this way with the help of multithreaded dump and restore one can sync specific table(s) from master.

Comments

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