MySQL Adventures: MetaBase Sync May Eat Your InnoDB Buffer Pool
MySQL Adventures: Metabase Sync May Eat Your InnoDB Buffer Pool
To continue our adventures on MySQL, today we investigated one more issue about MySQL’s memory consumptions. It's not a very big deal but looks something interesting/warning if you are using Metabase.
You may like these articles:
How max_prepared_stmt_count can bring down production
Reduce MySQL Memory Utilization With ProxySQL Multiplexing
Metabase is a great lightweight tool for analytics or we can use it for query MySQL database(it’s not the primary purpose, but still we can use it as a GUI for run your report queries). It supports many databases including Google BigQuery which I like most in Metabase.
We received an alert from our monitoring system that one of our read replicas consuming more memory than usual. If MySQL uses more memory then absolutely its fine since More In-Memory More Performance. But I didn’t convince with that because from past 6 months it never crossed 60% but suddenly it reached 80%.
The reason for writing this blog is, many times DBAs are SREs wherein the situation to find out the sudden memory spikes in MySQL. Also, we need to run Metabase Sync very carefully. I have done my investigation step by step to find the cause. (If you are a MySQL DBA you might know these steps).
I’m using Percona Monitoring Tool to monitor all of my MySQL servers. These investigations are also done with it.
The below graph shows that the system memory usage was gradually increasing.
2. We need to make sure MySQL is the one who ate this memory.
3. And this utilization happened because of the Buffer pool. It was storing some huge amount the data in it. More than 1 million data pages were pushing into the buffer pool.
4. The reason why suddenly buffer pool start capturing the data is, some queries were running but the data pages for those queries not available in the buffer pool. So those queries actually read the data from the SSD disk and push it to Buffer pool.
The below graph is showing there were some huge transactions happened.
5. To dig deeper, what kind of Operation caused this much transaction is actually READ. The below graph is confirming this.
6. Now we should take a look at Disk IO to verify those data are actually taken from disk and loaded to the Buffer pool.
The below graph confirms that during this period, the InnoDB got the data from DISK, not from memory.
7. Finally, we verified the data read from DISK. DISK monitoring metrics confirmed this. More than 10K read OPS happened on the disk.
8. And those queries actually did the read on the disk and almost 80% IO utilized.
9. Its time to identify what are those queries made this much disk read. The Query Analysis will tell you.
10. If you the query runtimes is actually pretty high. If you expand the queries these all are starts with --metabase
But one good thing about this is, It did limit 5000
Finally, we identified the exact queries while piled up the memory. Let’s see why metabase wants this sync?
From MetaBase’s Doc:
By default, Metabase performs a lightweight hourly sync of your database, and a nightly deeper analysis of the fields in your tables to power some of Metabase’s features, like filter widgets.
Metabase maintains its own information about the various tables and fields in each database that is added to aid in querying. By default, Metabase performs this lightweight sync hourly to look for changes to the database such as new tables or fields. Metabase does not copy any data from your database. It only maintains lists of the tables and columns.
How to Disable metabase sync and run whenever we need?
Go to Admin panel → Databases → Click the MySQL database you want to disable sync.
Turn on This is a large database, so let me choose when Metabase syncs and scans
I recollect one more case like this, I was using Metabase with SQL Server to generate some report and send it to the Business team at every 12/24hr interval. After setting up Metabase, the Report Server’s performance was dramatically slow. And Metabase ran the queries more than 2 hours.
Caution - MetaBase for BigQuery:
By default, metabase will run the sync at some particular interval. If you configure metabase to point to large BigQuery or Athena datasets, make sure you disable sync — because you will end up paying for scanned data
So if you are using metabase, do sync at non-production hours or fewer traffic hours. In the documentation they mentioned without sync, metabase won’t work. But still, Im using metabase to run Ad-Hoc queries and working fine without the sync on other MySQL servers.
MySQL Adventures: MetaBase Sync May Eat Your InnoDB Buffer Pool was originally published in Searce Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.
Percona XtraDB Cluster Operator 0.2.0 Early Access Release Is Now Available
Percona announces the release of Percona XtraDB Cluster Operator 0.2.0 early access.
The Percona XtraDB Cluster Operator simplifies the deployment and management of Percona XtraDB Cluster in a Kubernetes or OpenShift environment. It extends the Kubernetes API with a new custom resource for deploying, configuring and managing the application through the whole life cycle.
Note: PerconaLabs and Percona-QA are open source GitHub repositories for unofficial scripts and tools created by Percona staff. These handy utilities can help save your time and effort.
Percona software builds located in the Percona-Lab and Percona-QA repositories are not officially released software, and also aren’t covered by Percona support or services agreements.
You can install the Percona XtraDB Cluster Operator on Kubernetes or OpenShift. While the operator does not support all the Percona XtraDB Cluster features in this early access release, instructions on how to install and configure it are already available along with the operator source code, hosted in our Github repository.
The Percona XtraDB Cluster Operator on Percona-Lab is an early access release. Percona doesn’t recommend it for production environments.
Advanced nodes assignment implemented in this version allows to run containers with Percona XtraDB Cluster nodes on different hosts, availability zones, etc. to achieve higher availability and fault tolerance.
Cluster backups are now supported, and can be performed on a schedule or on demand.
Percona XtraDB Cluster Operator now supports private container registries like those in OpenShift so that Internet access is not required to deploy the operator.
CLOUD-69: Annotations and labels are now passed from the deploy/cr.yaml configuration file to a StatefulSet for both Percona XtraDB Cluster and ProxySQL Pods
CLOUD-55: Now setting a password for the ProxySQL admin user is supported.
CLOUD-48: Migration to operator SDK 0.2.1
CLOUD-82: Pods were stopped in random order while the cluster removal, which could cause problems when recreating the cluster with the same name.
CLOUD-79: Setting long cluster name in the deploy/cr.yaml file made Percona XtraDB Cluster unable to start.
CLOUD-54: The clustercheck tool used monitor user instead of its own clustercheck one for liveness and readiness probes.
Percona XtraDB Cluster is an open source, cost-effective and robust clustering solution for businesses. It integrates Percona Server for MySQL with the Galera replication library to produce a highly-available and scalable MySQL® cluster complete with synchronous multi-master replication, zero data loss and automatic node provisioning using Percona XtraBackup.
Help us improve our software quality by reporting any bugs you encounter using our bug tracking system.
Percona XtraBackup 2.4.13 Is Now Available
Percona is glad to announce the release of Percona XtraBackup 2.4.13 on January 18, 2018. You can download it from our download site and apt and yum repositories.
Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, it drives down backup costs while providing unique features for MySQL backups.
New features and improvements:
PXB-1548: Percona XtraBackup enables updating the ib_buffer_pool file with the latest pages present in the buffer pool using the --dump-innodb-buffer-pool option. Thanks to Marcelo Altmann for contribution.
xtrabackup did not delete missing tables from the partial backup which led to error messages logged by the server on startup. Bug fixed PXB-1536.
The --history option did not work when autocommit was disabled. Bug fixed PXB-1569.
xtrabackup could fail to backup encrypted tablespace when it was recently created or altered. Bug fixed PXB-1648.
When the --throttle option was used, the applied value was different from the one specified by the user (off by one error). Bug fixed PXB-1668.
It was not allowed for MTS (multi-threaded slaves) without GTID to be backed up with --safe-slave-backup. Bug fixed PXB-1672.
Percona Xtrabackup could crash when the ALTER TABLE … TRUNCATE PARTITION command was run during a backup without locking DDL. Bug fixed PXB-1679.
xbcrypt could display an assertion failure and generated core if the required parameters are missing. Bug fixed PXB-1683.
Using --lock-ddl-per-table caused the server to scan all records of partitioned tables which could lead to the “out of memory error”. Bugs fixed PXB-1691 and PXB-1698.
xtrabackup --prepare could hang while performing insert buffer merge. Bug fixed PXB-1704.
Incremental backups did not update xtrabackup_binlog_info with --binlog-info=lockless. Bug fixed PXB-1711
Other bugs fixed: PXB-1570, PXB-1609, PXB-1632
Release notes with all the improvements for version 2.4.13 are available in our online documentation. Please report any bugs to the issue tracker.
Replication Manager Works with MariaDB
Some time ago I wrote a script to manage asynchronous replication links between Percona XtraDB clusters. The original post can be found here. The script worked well with Percona XtraDB Cluster but it wasn’t working well with MariaDB®. Finally, the replication manager works with MariaDB.
First, let’s review the purpose of the script. Managing replication links between Galera based clusters is a tedious task. There are many potential slaves and many potential masters. Furthermore, each replication link must have only a single slave. Just try to imagine how you would maintain the following replication topology:
A complex replication topology
The above topology consists of five clusters and four master-to-master links. The replication manager can easily handle this topology. Of course, it is not a fix to the limitations of asynchronous replication. You must make sure your writes are replication safe. You could want, for example, a global user list or to centralize some access logs. Just to refresh memories, here are some of the script highlights:
Uses the Galera cluster for Quorum
Configurable, arbitrarily complex topologies
The script stores the topology in database tables
Elects slaves automatically
Monitors replication links
Slaves can connect to a list of potential masters
As you probably know, MariaDB has a different GTID implementation and syntax for the multi-source replication commands. I took some time to investigate why the script was failing and fixed it. Now, provided you are using MariaDB 10.1.4+ with GTIDs, the replication manager works fine.
You can found the script here. Be aware that although I work for Percona, the script is not officially supported by Percona.
MySQL 8.0.13 : InnoDB Transparent Tablespace Encryption for General Tablespaces
In my previous post (here) I talked about “InnoDB Transparent tablespace encryption” which was introduced in MySQL 5.7.11. That encryption was limited to file-per-table tablespaces. In MySQL 8.0.13, encryption for general tablespace is introduced. This blog post aims to give a peek into this new feature.…