As with any new releases, MySQL 8.0.21 introduces many new improvements and updates, many of which deserve their own blog post for a deep dive into the new features. Among the notable changes are: Account Management Notes, JSON Notes, Authentication Notes and changes related to InnoDB, Optimizer, Gr...

Introduced in MySQL Shell 8.0.21, the new MySQL Shell Dump and Load utilities has as its main goal to minimize the time needed to create and restore logical dumps of large data sets.

Through heavy parallelization and other techniques, we were able to reduce the time needed for these tasks by an order of magnitude compared to previous dump utilities.…

Facebook Twitter LinkedIn

So this has turned into a small series, explaining how to work with MYSQL from a developers perspective. This post is intended as a directory for the individual articles. It will be amended and re-dated as necessary.

The code for the series is also available in isotopp/mysql-dev-examples on GitHub.

The Tag #mysqldev will reference all articles from this series.

• MySQL Transactions - the physical side. Looking at how MySQL InnoDB handles transactions on the physical media, enabling rollback and commit. Introduces a number of important concepts: The Undo Log, the Redo Log, the Doublewrite Buffer, and the corrosponding in memory …

Since we now know how to look at the state of locking in a live database, let’s look at what happens when we run a normal insert or update and an insert or update with foreign key relationships defined, and compare.

We will be using the tables and structures from our previous examples, a simple 1:n relationship between a and b:

CREATE TABLE a (
  a_id int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (a_id)
);

INSERT INTO a VALUES (10), (20), (30), (40);

CREATE TABLE b (
  b_id int NOT NULL AUTO_INCREMENT,
  a_id int NOT NULL,
  PRIMARY KEY (b_id),
  KEY `a_id` (a_id),
  CONSTRAINT a_id_exists FOREIGN KEY (a_id) REFERENCES a (a_id) ON DELETE RESTRICT ON UPDATE RESTRICT
);

INSERT INTO b VALUES (10,10), (40,40);

or the same definition for b without the constraint.

A normal INSERT and UPDATE

First, let’s look at an insert and update into b without any …

This second part about the new MySQL Shell Dump & Load utilities aims to demonstrate the performance while also comparing it with various other logical dump and load tools available: mysqldump, mysqlpump & mydumper.

To make the numbers more meaningful, I used several real world production datasets that are available online: stackoverflow.com…

Facebook Twitter LinkedIn

ARM introduced LSE (Large System Extensions) as part of its ARMv8.1 specs. This means if your processor is ARMv8.1 compatible it would support LSE. LSE are meant to optimize atomic instructions by replacing the old styled exclusive load-store using a single CAS (compare-and-swap) or SWP (for exchange), etc…. Said extensions are known to inherently increase performance of applications using atomics.

Understanding LSE

To better understand LSE let’s take a working example to see how the code is generated and possible optimization.

LSE turned off

As you can see there is a loop for doing CAS. Load the value, check with expected value and if different then store the value. Main loop is a 5 step process with 2 exclusive instructions with respective memory ordering. SWAP too has a loop for checking if the store is successful.

ARM has multiple variant of load/store instructions so before we …

MySQL Shell 8.0.21 includes some exciting new utilities to create a logical dump and do a logical restore, with a focus on ease of use, performance and integration. In MySQL Shell 8.0.17, we already introduced a multi-threaded CSV import utility util.importTable()…

Facebook Twitter LinkedIn

This tutorial help to import the csv data into MySQL and export data from MySQL to csv file.The CSV file is used to import and export data for moving/exchange data information between web application.You can use rest api or web application to import/export CSV data. The web application data is stored, accessed and exchanged between […]

The post How To Import/Export CSV Data Using MySQLi and PHP 7 appeared first on Phpflow.com.

A small thing that provides a huge help.

The other day I was writing some code to process a very large amount of items coming from a social media API. My items were ending in a queue in MySQL and then needed to be processed and eventually moved.

The task was not so strange,  but what I have to do is to develop a queue processor.  Now when you need to process a queue you have two types of queue: static and dynamic.

The static comes in a batch of N number of items in a given time interval and is normally easier to process given you have a defined number of items that you can split in chunks and process in parallel.

The dynamic is… well… more challenging. One option is to wait to have a predefined number of items, and then process them as if they were a static queue.

But this approach is not very good, given it is possible that it will delay a lot …

A small thing that brings huge help.

The other day I was writing some code to process a very large amount of items coming from a social media API. My items were ending in a queue in MySQL and then needed to be processed and eventually moved.

The task was not so strange,  but what I have to do is to develop a queue processor.  Now when you need to process a queue you have two types of queue: static and dynamic.

The static comes in a batch of N number of items in a given time interval and is normally easier to process given you have a defined number of items that you can split in chunks and process in parallel.

The dynamic is… well... more challenging. One option is to wait to have a predefined number of items, and then process them as if they were a static queue.

But this approach is not very good, given it is possible that it will delay a lot the …