Database schema change is becoming more frequent than before, Four out of five application updates(Releases) requires a corresponding database change, For a DBA schema change is a more often a repetitive task, it might be a request from the application team for adding or modifying columns in a table and many more cases.

MySQL supports online DDL from 5.6 and the latest MySQL 8.0 supports instant columns addition.

This blog post will look at the online DDL algorithms inbuilt which can be used to perform schema changes in MySQL.

DDL Algorithms supported by InnoDB is,

• COPY
• INPLACE
• INSTANT ( from 8.0 versions)

INPLACE Algorithm:

INPLACE algorithm performs operations in-place to the original table and avoids the table copy and rebuild, whenever possible.

If the INPLACE algorithm is specified with the …

This blog is about one of the issues encountered by our Remote DBA Team in one of the production servers. We have a setup of MySQL 5.7 Single Primary (Writer) GR with cluster size of  3 . Due to OOM, the MySQL process in the primary node got killed, this repeated over the course of the time.

We all know about the OOM (out of memory), theoretically, it is a process that the Linux kernel employs when the system is critically low on memory.

In a dedicated DB server, when the OOM triggers the direct impact will be on mysqld process since it will be the most memory consuming one.

Going forward will look into the detailed analysis made to tackle the issue of OOM.

DB Environment:-

• Service – Group Replication Cluster
• Cluster Nodes – 3
• GR mode – Single Primary …

Your application should retry the update or roll back. InnoDB is not losing atomicity. You commit a transaction with changes and that os just a bug in your app. Note that InnoDB always rolls back whole transactions on deadlock. Your application has to handle failed statements.

LikeLiked by 1 person

The blog title seems something crazy ?

Yes, by default your InnoDB transactions don’t have the Atomicity . But, you can control the atomicity of your InnoDB transaction with the variable innodb_rollback_on_timeout. We had a weird data inconsistency issue within the transaction . In this blog , I am going to explain “How it will happen?” and “How it can be fixed ?” .

Whats is Atomicity ?

From Wiki ,

Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single “unit”, which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged.

What is …

In this blog I will go through a number of popular applications that use
NDB Cluster 8.0 and also how these applications have developed over the
years.

There is a presentation at slideshare.net accompanying this blog.

The first major NDB development project was to build a prototype of a
number portability application together with a swedish telecom provider.
The aim of this prototype was to show how one could build advanced
telecom applications and manage it through standardised interfaces.
The challenge here was that the telecom applications have stringent
requirement, both on uptime and on latency. If the database access
took too long time the telecom applications would suffer from
abandoned calls and other problems. Obviously the uptime of the …

In this post I’ll expand on the subject of my MySQL pre-FOSDEM talk: what dbadmins need to know and do, when upgrading from MySQL 5.7 to 8.0.

I’ve already published two posts on two specific issues; in this article, I’ll give the complete picture.

As usual, I’ll use this post to introduce tooling concepts that may be useful in generic system administration.

The presentation code is hosted on a GitHub repository (including the …

Recently one of our client approach Mydbops with Query slowness on a MySQL environment . They deployed the new code for generate the huge reports for the year end analytics data . After the deployment the queries were extremely slow and they struggled lot , then they approached us for the solution. After the analysis, their OLAP database as expected it was IO bound with 100% disk IOPS utilised during the report generation. So, the queries were starving for the Disk IO slows the process .

Problem statement :

• Reports are majorly focused on two larger log tables ( emp_Report_model , emp_details ) .
• The report generator (procedure) is using the count(*) statement to stimulate the aggregated data on each call. It is required for their business purpose .
• Count(*) is terribly slow in MySQL ( Using MySQL 5.7 ) as it …

Galera is the best solution for High Availability, It is being used by many peoples world wide . Galera is doing synchronous replication ( really it is Certification based replication ) to keep update the data on group nodes . In this blog I have explained about “How the Galera replication works?” . For the better understanding, I have made an architecture diagram to describe the replication flow . I have also provided the explanation for the key words which has used in the architecture diagram .

Architecture flow Diagram :

What is writeset ?

Writeset contains all changes made to the database by the transaction and append_key of the changed rows .

What is append_key ?

Append_key registers the key of the changed data by the transaction. The key for rows can be represented in three parts as DATABASE NAME, TABLE NAME, PRIMARY KEY . …

Overview The Skinny

In this blog post we explore various options for tuning MySQL traffic routing in the Tungsten Connector for better control of the distribution.

A Tungsten Cluster relies upon the Tungsten Connector to route client requests to the master node or optionally to the slaves. The Connector makes decisions about where to route requests based on a number of factors.

This blog post will focus on the Load Balancer algorithms available via configuration that allow you to adjust the routing behavior of the Connector, along with ways to debug the Connector Load Balancer’s routing decisions.

The Question Recently, a customer asked us:

How do I know which load balancer algorithm is in use by the Connector? And how do we enable debug logging for the Connector load balancer?

How to Deploy MySQL InnoDB Replica Set in Production?

Before i talk about Deployment process of MySQL InnoDB Replica Set , it is more important to know below details:-

• What is MySQL InnoDB Replica Set?
• What is prerequisite and limitation of using MySQL Replica Set?
•  In what kind of scenarios MySQL Replica Set is not recommended.
•  How to configure and deploy MySQL Replica Set- (step by step guide )
• How to use InnoDB Replica Set?
• What if Primary goes down? Does select query re-routed to another server?
• What if Secondary goes down while executing select queries?

§      I will answer these all question in this blog.

   What is Replica Set ?

MySQL InnoDB ReplicaSet a quick and easy way to get MySQL replication(Master-Slave), making it well suited …