TL;DR — If you are hosting your database server in GCE, then you have to be very cautious with capacity planning. The number of CPU cores has an impact on IOPS you get. Even if your workload is not CPU intensive, you might want to provision enough depending on the kind of IOPS you are going to need. For more details, read through one of the problems we faced and the RCA.

Problem statement:

We had a master-slave setup for one of the MySQL database servers. The MySQL server’s performance was good, we didn’t notice any performance related issues initially. But we recently noticed replication lag on the slave. and we figured out that the slave thread was very slow.

Server’s Capacity:

Both our master-slave servers are of the same size.

• 4 Core CPU
• 16GB Memory
• 2 databases are hosted
• 20GB is the database size.
…

MySQL Adventures: Reduce MySQL Memory Utilization With ProxySQL Multiplexing

In our previous post, we explained about how max_prepared_statement_count can bring production down . This blog is the continuity of that post. If you can read that blog from the below link.

How max_prepared_stmt_count bring down the production MySQL system

We had set the max_prepared_stmt_count to 20000. But after that, we were facing the below error continuously.

Can't create more than max_prepared_stmt_count statements (current value: 20000)

We tried to increase it to 25000, 30000 and finally 50000. But unfortunately, we can’t fix it and …

MySQL Adventures: How max_prepared_stmt_count can bring down production

We recently moved an On-Prem environment to GCP for better scalability and availability. The customer’s main database is MySQL. Due to the nature of customer’s business, it’s a highly transactional workload (one of the hot startups in APAC). To deal with the scale and meet availability requirements, we have deployed MySQL behind ProxySQL — which takes care of routing some of the resource intensive SELECTs to chosen replicas. The setup consists of:

• One Master
• Two slaves
• One Archive database server

Post migration to GCP, everything was nice and calm for a couple of weeks, until MySQL decided to start misbehaving and leading to an outage. We were able to quickly resolve and bring the system back online and what follows are lessons from this experience.

The configuration of the …

From my past 2 posts, I have explained how to configure replication between CloudSQL to VM and VM to CloudSQL. Now we have implemented one more solution in CloudSQL is Configure replication between CloudSQL to another CloudSQL. Its almost similar to setting up the external Master. Lets see how to do this.

Where this will help you?

• Migrating from one project to different projects.
• Migrate the existing CloudSQL to Another Region.
• There is no straightforward ways shrink the MySQL volume(Some worst cases the auto increment added more space). So we can replicate it to another CloudSQL with less downtime.
…

In our previous blog, we have explained that to configure external replica for a CloudSQL instance. Here we are going to see how to configure external master for CloudSQL. Unlike native replication or the method which we explained in our previous blog, setting up external master for CloudSQL is pretty different way. Lets see how we can achive this.

Process Overview:

• Launch VM and configure MySQL 5.7(it should be 5.5+).
• Initiate the dump along with the binlog filename and its position.
• Upload the Backup file to Google Storage.
• Enable the CloudSQL Admin API.
• In the CloudSQL console, use migrate data.
• Select the CloudSQL instance type and its network.
• Provide the Master server’s IP …

CloudSQL is fully managed MySQL / PostgreSQL database system. CloudSQL reduces the workload for DBAs and anyone can easily manage even without a DBA. In many cases, people wants to have a replica of their production database for Testing purpose or even some other purpose. CloudSQL provides the flexibility to have external replicas on VM or On-prem. In this blog we are going to configure external replica for CloudSQL.

MySQL with GTID:

CloudSQL 2nd generation support GTID based replication and binlog file based replication. But GTID is more consistent. But 1'st generation only support binlog replication.

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between …

As part of building an enterprise DW for one of our customers we had to sync a bunch of tables from a MYSQL slave to BigQuery at 30 min intervals. Considering the range of other non-relational data sources which will be part of the this load, we chose Matillion as ETL tool. Matillion is easy to setup (just provision the VM and start authoring jobs) and long list of integrations so it made sense.

This post explains building a Matillion job that does the following:

1. Full Load
2. Incremental load for tables with larger row count and an ID that can be looked up for new rows since last load.

MYSQL Drivers

If you came from a Google search looking for Matillion — I am assuming you are done with provisioning the instance, setting up default project etc are done, so I am skipping those. While Matillion ships with PostgreSQL drivers, for some reason it doesn’t have MYSQL …