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 …

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 KEY_BLOCK_SIZE), and loads and stores compressed pages of this smaller size from and to the …

We observe a large number of messages of the kind

The table '../tmp/#sql…' is full

Before MySQL 8

In older Versions of MySQL, implied temporary tables are being created whenever your EXPLAIN contained the phrase using temporary.

In this case, MySQL would create an in-memory temporary table to materialize an intermediate query result, and then continue to process the data from there. If that temporary table was larger than some configurable limit, the temporary table would instead be converted to a MyISAM table on disk, streamed out, and then work would continue with this.

This has a large number of disadvantages:

• A table in the MEMORY storage engine can have no variable length columns. That means, any VARCHAR column gets converted to …

We observe a large number of messages of the kind

The table '../tmp/#sql…' is full

Before MySQL 8

In older Versions of MySQL, implied temporary tables are being created, whenever your EXPLAIN contained the phrase using temporary.

In this case, MySQL would create an in-memory temporary table to materialize an intermediate query result, and then continue to process the data from there. If that temporary table was larger than some configurable limit, the temporary table would instead be converted to a MyISAM table on disk, streamed out, and then work would continue with this.

This has a large number of disadvantages:

• A table in the MEMORY storage engine can have no variable length columns. That means, any VARCHAR column gets converted to a CHAR column in the MEMORY version of that table.
  …

Setup for SSL MySQL Enterprise Database Version 8.0.22

Purpose of the Document

Procedure for MySQL Database Installation ( 8.0.22 – Enterprise) and setup for SSL on RHEL 8

=====================================================================

MySQL Database Installation –RHEL 8.x – DB Version 8.0.22 – Enterprise Edition

=====================================================================

The below procedure is related to MySQL Database Installation Version 8.0.22 on RHEL 8.4. ( Enterprise Edition)

MySQL Server 172.31.17.141

MySQL Client 172.31.25.170

(1) Check the OS version of RHEL by using below command.

[root@mysql-server ~]# cat /etc/redhat-release

Red Hat Enterprise Linux release 8.4 (Ootpa)

(2) Create local group for dba

[root@mysql-server ~]# groupadd -g 1020 dba

(3) Create local user for mysql

…

This blog post is inspired by Jesper's recent blog post on how the automatic update of InnoDB persistent statistics may never trigger if servers are restarted frequently. However, the matter is even worse! In this blog post I will show that even when the automatic recalculation is performed, there are no guarantees as to when the server will see the changes.

Myths about persistent statistics

I must admit that I, for a long time, believed in the myths I was told about when updates to InnoDB's index statistics become visible to the query optimizer. There were basically two variants:

• The updated statistics will only be visible to new connections (i.e., sessions that are started after the statistics was updated)
• When a new connection accesses the table, the updated statistics …