Like I said, I never had much reason to use table compression, and only recently looked into the topic. MySQL Page Compression looks a lot easier at the database end of things, but relies on hole punching support in the file system. Let’s have a look at what that means.

Files, Inodes and Arrays of Blocks

The original Unix filesystem saw the disk as a sea of blocks, which were represented in a free map as an array of bits. Files have numbers, which are an index into an array of so-called inode structures. Inodes store the files metadata and contain an array of block numbers, which make up the actual file. The array is folded multiple times, to optimize for the more common case of small files: The first few block numbers were stored in the inode, followed by a pointer to a block containing file block numbers, then a pointer to a block containing pointers to blocks of file block numbers and so on.

The block list inside …

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

This is the seventh episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

In this episode, we’ll discover and use the OCI Bastion service to provide a restricted and time-limited access to administer our MySQL instance.

The post Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session first appeared on dasini.net - Diary of a MySQL expert.

Here’s a second post focusing on the performance of MySQL on ZFS in cloud environments. In the first post, MySQL/ZFS Performance Update, we compared the performances of ZFS and ext4. This time we’ll look at the benefits of using ephemeral storage devices. These devices, called ephemeral in AWS, local in Google cloud, and temporary in Azure, are provided directly by the virtualization host. They are not network-attached and are not IO throttled, at least compared to regular storage. Not only can they handle a high number of IOPs, but their IO latency is also very low. For simplicity, we’ll name these devices local ephemeral. They can be quite large: Azure lsv2, Google Cloud n2, and AWS i3 instance types offer TBs of fast NVMe local ephemeral storage.

The main drawback of local ephemeral …

in this tutorial, We’ll learn about how to add add, edit, delete functionality using Bootstrap 5, PHP and MySQL.I am extending previous tutorial Ajax Pagination with Search and Sort. We have already added functionality to listing, searching, and sorting into datatable, So Let’s add functionality to CRUD operation without page refresh. We have already added […]

The post Ajax Datatable CRUD Operation Using PHP and MySQL appeared first on Phpflow.com.

For the MySQL Million Challenge, I was going through the server syntax in order to understand what things can be created in the server. And now my OCD triggered. DDL is a mess.

Creation

As a database developer, I want to be able to create server objects using the CREATE thing syntax.

The server gives you that for the following things:

• DATABASE
• EVENT
• FUNCTION (and FUNCTION SONAME)
• INDEX
• LOGFILE GROUP (NDB only, not going to look at this)
• PROCEDURE
• RESOURCE GROUP
• ROLE
• SERVER
• SPATIAL REFERENCE SYSTEM
• TABLE
• TABLESPACE
• TRIGGER
• USER
• VIEW

…

For the MySQL Million Challenge, I was going through the server syntax in order to understand what things can be created in the server. And now my OCD triggered. DDL is a mess.

Creation

As a database developer, I want to be able to create server objects using the CREATE thing syntax.

The server gives you that for the following things:

• DATABASE
• EVENT
• FUNCTION (and FUNCTION SONAME)
• INDEX
• LOGFILE GROUP (NDB only, not going to look at this)
• PROCEDURE
• RESOURCE GROUP
• ROLE
• SERVER
• SPATIAL REFERENCE SYSTEM
• TABLE
• TABLESPACE
• TRIGGER
• USER
• VIEW

Safe creation

As a database developer I want to be able to script things safely, so I need IF NOT EXISTS clauses in my CREATE …

A long-standing idea that I have is to test the servers limits: How does it fail and break if there are very many of a thing? Previously that was too easy, because many structures were constructed in a way that it was obvious they would not scale. But with MySQL 8 many things were overhauled, so let’s see what we can make many of and see how the server fares.

The Million Challenge

Database servers are programs that are built to handle a lot of data. A million rows in a table are not a problem, and searching one in a million rows neither, because the server has structures that make this fast.

But is the database server using these structures internally in an efficient way and can it handle a lot of tables, schemas, views, users, grants, roles and so on?

What can we try?

• Make a million tables and see …

A long-standing idea that I have is to test the servers limits: How does it fail and break if there are very many of a thing? Previously that was too easy, because many structures were constructed in a way that it was obvious they would not scale. But with MySQL 8 many things were overhauled, so let’s see what we can make many of and see how the server fares.

The Million Challenge

Database servers are programs that are built to handle a lot of data. A million rows in a table are not a problem, and searching one in a million rows neither, because the server has structures that make this fast.

But is the database server using these structures internally in an efficient way and can it handle a lot of tables, schemas, views, users, grants, roles and so on?

What can we try?

• Make a million tables and see how performance degrades or not.
  • Will this work better if we use general …

     I frequently see requests on various web sites from MySQL novices looking for example databases with which to practice.  There are several that are highly recommended and we will take a look at them over the next few weeks.

1. World Data Base -- See https://dev.mysql.com/doc/world-setup/en/

This has been THE database used in MySQL's documentation, classes, examples, and just about anything else.  There are three tables -- city, country, and countrylanguage -- that are fantastic for teaching the basics such as JOINs, GROUP BY, HAVING, and other SQL commands.  

1x. World X Database -- See https://dev.mysql.com/doc/world-x-setup/en/

With the advent of the X DevAPI there needed to be a test database with JSON columns and the World Database was modified.  The countryinfo table was added to the inherited three so that you can practice with JSON columns and a JSON …

I never had much reason to use table compression, so I largely ignored the topic in MySQL. I knew that MySQL had table compression since 5.1, but I also knew the implementation was horribly complicated and double stored all data. There is also page compression, a feature introduced with 5.7, which replaces table compression and works much better.

Table Compression

Table Compression

is available in MySQL 5.1 and newer. It is used by setting an InnoDB table up with ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 or similar, for even smaller key block sizes. A lot of status tables in INFORMATION_SCHEMA.%CMP% are available to monitor it.

Table compression creates smaller pages (in the size you specify with …