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 …

Percona Live Online Agenda Slot: Wed 21 Oct • New York 7:30 a.m. • London 12:30 p.m. • New Delhi 5:00 p.m. • Singapore 7:30 p.m.

Abstract

ProxySQL is a high performance, high available, protocol aware proxy for MySQL. 2.0 has been GA for some time now, and there have been a lot of changes that come in point releases as well, such that you can benefit from them.

Listen to René, the founder of ProxySQL, take you through some of the new features in 2.0, and how you can effectively utilize them. Some topics that are covered include:

– LDAP authentication
– SSL for client connections
– AWS Aurora usage
– Native clustering support for Percona XtraDB Cluster (PXC) / Galera Cluster / group replication
– Kubernetes deployments

This is the talk to take you from intermediate ProxySQL user to …

Would you play a multiplayer game if you discovered other people are cheating? According to a survey by Irdeto, 60% of online games were negatively impacted by cheaters, and 77% of players said they would stop playing a multiplayer game if they think opponents are cheating. Player churn grows as cheating grows.

Stopping this is therefore essential if you want to build and develop your community, which is essential to success for today’s gaming companies. This session at Percona Live ONLINE was presented by Yahav Biran, specialist solutions architect, gaming technologies at Amazon Web Services, and Yoav Eilat, Senior Product Manager at Amazon Web Services, presented a talk and demonstration about anti-cheating tools in gaming based on using automation and machine learning (ML).

Yoav notes that while people might think of ML in terms of text or images, but: …

We are proud to announce that Percona was selected as a participating organization for the Google Summer of Code (GSoC) 2020 program, this is our second year as a participating org with the GSoC program.

GSoC is a great program to involve young student developers in open source projects. We participated in the program in 2019 for the first time and we were really happy and satisfied with the results.
Percona Platform Engineering team decided to participate again for the 2020 program and we are glad and really happy to inform you that we were selected and welcome the student to work with our team during the summer of 2020 on their GSoC Project.

Preparations

We started planning for GSoC around November-December 2019, with the help from our Product Management team, we were able to shortlist a few ideas which we thought were really the right fit for …