Mydumper supports streaming of backups right from version 0.11.3 and the latest version Mydumper 0.12.3 it enabled its support for compressed streaming backup. This was the most awaited feature added to Mydumper, making it a more powerful tool for data migration to RDS or Cloud.

If you are hearing mydumper for the first time, then let’s have a quick catch-up on what Mydumper is and what it does exactly.

Mydumper is a multithread logical backup and restores tool for MySQL and its forks. To know more you can refer to our previous blogs/presentations below.

• https://mydbops.wordpress.com/2016/01/31/faster-logical-backup-with-mydumper/
• …

As a Database Engineer, One of the biggest challenges in day-to-day activity is performing DDL on high-traffic and transaction-intensive tables. It will become overhead when handling a large number of servers/shards.

As a standard process, we will first deploy the changes in DEV and QA before deploying them in production.

In the sharded environment, It will become a heavy overhead to maintain the schema changes in DEV, QA, and PROD servers. Since we will have multiple servers in the sharding.

To overcome this deployment supervision, the Skeema tool will help to deploy the changes in QA, DEV, and PROD in a safe and parallel ( for Shards ) as well.

This blog focuses on the basic operation of Skeema, will have a series of blogs on Skeema

1. About the …

The following is a short HOWTO about deployment and use of Benchmark-kit (BMK-kit). The main idea of this kit is to simplify your life in running various MySQL benchmark workloads with less blood and minimal potential errors.

Generally as simple as the following :

$ bash /BMK/sb_exec/sb11-Prepare_50M_8tab-InnoDB.sh 32   # prepare data

$ for users in 1 2 4 8 16 32 64 128 256 512 1024
do   
  # run OLTP_RW for 5min each load level..
  bash /BMK/sb_exec/sb11-OLTP_RW_50M_8tab-uniform-ps-trx.sh $users 300
  sleep 15
done

Read more... (35 min remaining to read)

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 …

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 …

(based on a conversation with a colleague, and a bit of Twitter)

A Conundrum

A developer colleague paged me with this:

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 2 YEAR) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 2 YEAR) as delta\G
delta: 420

It is obviously wrong, and weirdly so. It only works for “2 year”, not with other values:

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-11 year_month) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-11 year_month) as delta\G
delta: 3600

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-12 year_month) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-12 year_month) as delta\G
delta: 3600

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-13 year_month) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL …

The tables in PERFORMANCE_SCHEMA (P_S) are not actually tables. You should not think of them as tables, even if your SQL works on them. You should not JOIN them, and you should not GROUP or ORDER BY them.

Unlocked memory buffers without indexes

The stuff in P_S has been created with “keep the impact on production small” in mind. That is, from a users point of view, you can think of them as unlocked memory buffers – the values in there change as you look at them, and there are precisely zero stability guarantees.

There are also no indexes.

Unstable comparisons

When sorting a table for a GROUP BY or ORDER BY, it may be necessary to compare the value of one row to other rows multiple times in order to determine where the row goes. The value compared to other rows can change while this happens, and will change more often the more load the server has. The end …

At work, I am in an ongoing discussion with a number of people on the Observability of Outliers. It started with the age-old question “How do I find slow queries in my application?” aka “What would I want from tooling to get that data and where should that tooling sit?”

As a developer, I just want to automatically identify and isolate slow queries!

Where I work, we do have SolarWinds Database Performance Monitor aka Vividcortex to find slow queries, so that helps. But that collects data at the database, which means you get to see slow queries, but maybe not application context.

There is also work done by a few developers which instead collects query strings, query execution times and query counts at the application. This has access to the call stack, so it can tell you which code generated the query that was slow.

It …

I am always surprised to learn something new whenever I talk to a member of the open-source community. No matter how much I think I have heard of every use case there is for Liquibase (and database change management in general), I always hear something that makes this space still feel new. There’s always something left to discover.

Today, that new something is the problem of how to perform large batches of changes with SQL ALTER TABLE statements. No problem you say? Okay, but this ALTER needs to happen in production. Still not worried? Well, let’s say you have millions of rows, and because you’re so successful, you have many transactions happening per minute (maybe even per second). Yeah…now we are talking. You can’t alter the table because you can’t afford to lock that table for the …