Recently, somebody requested some help as he was trying to modify the structure of a large table. The requested modification was related in changing a fixed length string to a variable length string column.

If you don’t know it yet, MySQL supports multiple algorithms when altering a table (DDL):

• COPY: operations are performed on a copy
• INPLACE: operations may rebuild the table inplace (no copy)
• INSTANT: operations only change the metadata in the data dictionnary

The operation that interests us today needs to copy the full table. And this was the problem for the user as the MySQL Datadir could not store twice the amount of that large table.

In this blog post I will explain a method that can be used to solve this problem. There are also others method like a logical dump …

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 …

We have planned for archiving the data to improve the DB performance and to reclaim the space. We were evaluating Compression in InnoDB and TokuDB. To find out the best compression method. We started benchmarking the compression ratio between InnoDB and TokuDB.

Everything goes well for some time, but after a few hours got an error message that can’t able to insert the data into the TokuDB table due to storage being full. It is so strange and the host has enough free space.

Table structure:-

mysql> show create table mydbops.tokudb\G
*************************** 1. row ***************************
       Table: tokudb
Create Table: CREATE TABLE `tokudb` (
  `ID` int DEFAULT NULL,
  `Name` longtext,
  `Image` blob
) ENGINE=TokuDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (2.18 sec)

mysql> show create table mydbops.innodb\G
*************************** 1. row …

To copy the data of the particular column of the table to another table/server, We have an option to export the data as CSV and import the data back to a different table. But when the table size is large and we need to copy the data only for the required data to the target table will cause the load in the server since the table scanning is huge.

To overcome this, we have the pt-archiver copy the data from the source table to the destination as a whole or only for required columns. And also we can do this in a controlled manner as well. So there will be no performance impact even on the production time.

Source table structure :

mysql> show create table source\G
*************************** 1. row ***************************
       Table: source
Create Table: CREATE TABLE `source` (
  `id` int unsigned NOT NULL …

If you are looking to improve the performance of MySQL databases in your software, you may need to know all the differences between InnoDB and MyISAM, two well-known types of MySQL storage engines. And if you are about to choose one of them, we believe you would like to find out what each of them […]

The post InnoDB vs MyISAM: A Detailed Comparison of Two MySQL Storage Engines appeared first on Devart Blog.

MySQL 8.0.27 introduced a new variable to control the maximum of parallel threads InnoDB can use for creating (sorting and building) secondary indexes: innodb_ddl_threads.

This new variable is coupled with another new variable: innodb_ddl_buffer_size.

If you have fast storage and multiple CPU cores, tuning these variables can speed up secondary index creation.

For this example, I used the airportdb database, and I added a secondary index to the largest table, booking.

Let’s start with the default settings:

 SQL  alter table booking …

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 …

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 …

Well, let me say if that happens it’s because there is a logic mistake in your application. But you need to know and understand what happens in MySQL to be able to avoid the problem. 

In short, the WHY of this article is to inform you about possible pitfalls and how to prevent them from causing you damage.

Let us start by having a short introduction to what Repeatable reads are about. Given I am extremely lazy, I am going to use (a lot) existing documentation from the MySQL documentation.

Transaction isolation is one of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing …

Recently, I saw many interest in understanding and getting information about database locking.

MySQL InnoDB’s locking can be complex and having an overview not always simple.

For more information about how InnoDB locking is working, I can only recommend this excellent series of articles by Kuba:

• https://dev.mysql.com/blog-archive/innodb-data-locking-part-1-introduction/
• https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-locks/
• https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-5-locks-deeper-dive/
• …