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

How to remove/deregister an instance from PMM?

We all need a monitoring system like pmm to monitor our database. Let me give a brief about how pmm works.


So PMM uses prometheus to store metrics/graphs and grafana to display them. As seen in the architecture diagram prometheus uses consul to get the list of host to scrap metrics. More information on architecture and installation are available in docs.
We were facing issues related to down hosts and if setup alerts in grafana for down host and host is terminated it continuously throws alerts. So problem was how can we remove/deregister instances from pmm.
We can access consul UI using below link:
http://PMM-SERVER/consul/#/dc1/
Previously there was option available on this ui to deregister host. while in the new releases of consul it has been removed see link.
But now we can use below procedure from command line to deregister hosts from consul.
Get list of nodes:
curl -s 'http://USER:PASSWORD@PMM-SERVER-IP/v1/internal/ui/nodes?dc=dc1' | python -mjson.tool | less
Remove node from consul:
curl -s -X PUT -d '{"Datacenter":"dc1","Node":"NODE-NAME"}' 'http://USER:PASSWORD@PMM-SERVER-IP/v1/catalog/deregister?dc=dc1'
After that, it is needed to remove data from Prometheus:
curl -X DELETE 'http://USER:PASSWORD@PMM-SERVER-IP/prometheus/api/v1/series?match\[\]=\{instance="NODE-NAME"\}'
Kindly make a note node names in consul and in prometheus might be different for the one host
Percona is also planning to add Deregister button on interface in PMM-544.

Comments

  1. UnknownMarch 23, 2022 at 11:25 PM

    Do you need to restart Prometheus or Consul after the removal of hosts? I tried your approach but still see Prometheus scraping metrics for the host.

    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

Shell/Bash Commands Execution from MySQL Client or Stored Procedure/function

Image
Today I am going to explain ways to execute shell or bash commands from mysql clients or stored procedure and function. There are basically 2 method to do so: Method 1: Use MySQL Client inbuilt feature  To run single command: \! command or system command. eg \! uptime or system command  To get terminal \! bash or \! sh Method 2: Deploy external plugin ( lib_mysqludf_sys ) Step 1: Download lib_mysqludf_sys from github: git clone https://github.com/mysqludf/lib_mysqludf_sys.git Step 2: Install libmysqlclient15-dev, for Ubuntu you can use: apt-get install libmysqlclient15-dev Step 3: Note down o/p of: mysql -uroot -pxxxx -e "select @@plugin_dir;" Step 4: Change directory to git clone dir. cd lib_mysqludf_sys/ Step 5: Compile and put plugin in plugin dir gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o {value from step 3}lib_mysqludf_sys.so -fPIC Step 6: Create necessary functions for lib_mysqludf_sys: mysql -uroo...
Read more