Showing entries 1 to 10 of 18
8 Older Entries »
Displaying posts with tag: MySQL DBA Insights (reset)
Readable MultiAZ Cluster with AWS RDS MySQL under the hood.

Amazon Web Services (AWS) very recently(March 02, 2022) announced the GA of its new RDS feature “Readable standby with Multi-AZ deployments” for MySQL. Yes !! you heard it right you can now use the standby instances created with Multi-AZ deployments for failover as well as for Read-scaling starting with version 8.0.26 and later for MySQL in RDS

Launching a MultiAZ Cluster

Now let us see how to launch this readable-Multi AZ cluster?

Region Availability: As this is a new feature now it is currently limited to the regions US-EAST-1 (N.Virginia), US-WEST-1 (Oregon), and EU-WEST-1 (Ireland), this list would be extended progressively

VPC requirement:

Before launching the instance, you should have SUBNET created for 3 AZ(Availability Zone) within the VPC since the cluster instances would be spawn across 3AZ by default

Hereunder the “Engine Option” …

[Read more]
How to Estimate time for Rollback in a cancelled transaction MySQL ?

Rollback is an operation, which changes the current state of the transaction to the previous state. Undo logs are generally required if we want to roll back any of the uncommitted transactions and it plays a major role in Isolation.

For any changes made during a transaction, it must be stored priorly, because they are required if we choose to roll back the transaction.

Entries are made in undo logs when data modifications are done. If a transaction modifies data with SQL commands, It will create discrete undo logs for each operation. Once a transaction is committed MySQL is free to purge the undo logs created in that transaction. 

To know more about undo logs, you can check our previous blogs on overview to undo logs.

Usually, the Rollback process will take more time than the original operation. Because …

[Read more]
Faster Load data outfile in MySQL

While exporting the table with MySQL native utility, we don’t have any control on the process, and also there will be no progress update as well on the process completion. So when exporting the larger table will consume high resource utilization and also the disk space usage will also be high.

MySQL shell utility will make the process easier. It will export the table and we can import the data back with a parallel thread and also will provide the current progress status on export/import progress.

util.exportTable() utility was introduced in Shell – 8.0.22 version, will export the data in a controlled manner. We can store the data in either local or Cloud Infrastructure Object Storage bucket as well.

We will see about the compression ratio along with the time taken for native MySQL vs Shell utility

Feature :

  • Compression
  • Progress status
  • Supported output …
[Read more]
Troubleshooting an unique key addition during pt-online-schema-change

We all tried various alternative methods for modifying the table structure, but pt-online-schema-change (pt-osc) is the most convenient and preferred method for performing the alter online. It has more granular control too. But it may lead to data loss if proper precautionary steps are not taken care of.

In this blog, we are going to modify a column to a unique key using pt-osc, below I have shared the table structure.

mysql> show create table test\G
* 1. row *
Table: test
Create Table: CREATE TABLE test (
Personid int(11) NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255) DEFAULT NULL,
Age int(11) DEFAULT NULL,
PRIMARY KEY (Personid)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

I have inserted the data of 1000 rows using …

[Read more]
Introduction to MySQL Parallel query in AWS Aurora

Aurora has a salient feature “Parallel query“, Which will be more beneficial for analytical workload environments.

Before going to deep dive on this particular feature, let us understand the basis of Aurora.

Aurora Archiecture

Key feature

  • The key feature of Aurora is simple data synchronisation among the nodes. The sync latency will be too low when compared to RDS because the synchronisation is happening on storage volumes among the nodes. Also all the server will available in different zone, even when a zone goes down we can able to maintain will other server present in other zone with auto failure.
  • Auto healing volume, Each …
[Read more]
Fastest Parallel replication method in MySQL 8.

From MySQL 5.7, we had a Multi-threaded Slave (MTS) Applier mechanism called LOGICAL_CLOCK to overcome the problems of parallel replication within a database.

To further improve the parallelisation mechanism, from MySQL 8 (5.7.22) we have write-set replication, so before going further , lets look at the difference between Logical clock (LC) and Writeset.

LOGICAL_CLOCK

Transactions that are part of the same binary log group commit on a master are applied in parallel on a slave. The dependencies between transactions are tracked based on their timestamps to provide additional parallelisation where possible.

WRITESET

Write-set is a mechanism to track independent transactions that can be executed in parallel in the slave. Parallelising on write sets has potentially much more parallelism than logical_clock ,since it does not depend …

[Read more]
Install Specific Version of MySQL 8 using YUM

We have many ways to install MySQL on linux machines such as source, binary and so on. But most of the Engineers always prefer default package managers (yum for RPM-Based distributions and apt for DPKG-Based distributions) for its ease of use and it can resolve all dependencies on its own. And of course, it is not possible to use package managers in environments where the internet is not allowed, but this is a different case. 

At some point, we need to install exactly specific version of MySQL for the following cases

  • To create Production Replicas 
  • To simulate an Production Issue on similar kind of environment
  • To configure Disaster Recovery(DR)/UAT Setup
  • Compatibility with opensource tools ( Eg , …
[Read more]
Creating a Simple MySQL Binlog Server

In my previous Blog i have explained how we can integrate MySQL tools ( Percona Tool kit ) with systemd service, At Mydbops works with multiple clients ,we get various requirements from customers a few may be a complex ones ,one of the requirement was to setup a binlog server and copy the Production binlogs instantly .

To satisfy customer request i have made a simple shell script and integrated in systemd service which does a similar job as of Maxscale Binlog Server ( Pervious blog )

In this blog i will explain how to setup a simple binlog server.

Pre-requites for setting up the Binlog server.

  • A standalone remote server with required disk.
  • The mysqlclient to be …
[Read more]
An Overview to InnoDB Undo Log

As the name indicates, an undo log record contains information about how to undo the recent changes by a transaction. When a transaction writes data, it always makes writes on the tablespace files. InnoDB Undo log stores copy of data that is being modified by any current transaction. So, at this point in time if any other transaction queries for the original data (row) which is being modified,  the undo logs provide the same and serve the purpose. This is what provides a consistent read view ( based on isolation ) during any data modifications.

Here in the above representation, Transaction T1 modifies the data (Data-1). During the time of modification to ensure the reads are consistent, transactions T2  and T3 are given access only to the copy (previous row version) of Data -1 which is stored in the “UNDO” …

[Read more]
An Overview of DDL Algorithm’s in MySQL ( covers MySQL 8)

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 …

[Read more]
Showing entries 1 to 10 of 18
8 Older Entries »