Undo Tablespaces can be truncated either implicitly or explicitly in MySQL 8.0. Both methods use the same mechanism. This mechanism could cause periodic stalls on very busy systems while an undo tablespace truncate completes. This problem has been fixed in MySQL 8.0.21.…
Facebook Twitter LinkedIn
So you have waited two weeks (cause the crash isn’t easily repeatable) and finally you get the crash again. You check your non-datadir core file directory with loads of free space and discover nothing was written. When MySQL crashes, you … Continue reading →
We all know as SQL professionals that a common use of the
ALTER TABLE command is that we can change a tables’
structure in a myriad number of ways. And, that’s a good thing
too because chances are, you won’t always nail down the
initial structure. Due to changing business or application
requirements, you may even have to add additional columns that
were not considered during the schema design phase. Suppose you
have many tables that are structured similarly and they all need
a specific column added to their already-existing design. Under
certain circumstances, using the MySQL Shell in Python mode
(\py), can reduce the number of manual
ALTER TABLE statements you have to type. Continue
reading to see examples in the MySQL Shell…
Photo by elCarito …
[Read more]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
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
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
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 …
[Read more]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 …
[Read more]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 …
[Read more]
As a data architect I always ensure that for any database schema
change there a fully recoverable execution path.
I have generally advised to create a patch/revert process for
every change. For example, if a change adds a new column or
index to a table, a revert script would remove the respective
column or index.
The goal is to always have a defensive position for any changes.
The concept is that simple, it is not complex.
In its simplest form I use the following directory and file structure.
/schema
schema.sql
/patch
YYYYMMDDXX.sql where XX,ZZ are sequential 2 digit numbers, e.g. 01,02
YYYYMMDDZZ.sql
/revert
YYYYMMDDXX.sql This is the same file name in the revert sub-directory.
YYYYMMDDZZ.sql
At any commit or tag in configuration management it is possible
to create a current copy of the schema, i.e. use
schema.sql.
It is also possible to take the first …