A database is showing replication delay, and so are all the other instances of the same replication hierarchy, all of which reside in Openstack.

Shortly before 21:30 the database begins to lag, until around 23:45, when it starts to catch up, slowly. After 00:30, we gain delay again, plateau and then around 01:45, we catch up.

The database is moving deep into replication delay sometimes. It does not do that on bare metal.

Ground Truth

The VM is a nice hardware blade simulation, 16C/32T, 128 GB of memory.

The data is on persistent volume backed by Ceph.

# df -Th /mysql/<hierarchyname>/
Filesystem                Type  Size  Used Avail Use% Mounted on
/dev/mapper/vg00-mysqlVol xfs   1.8T  1.1T  766G  58% /mysql/<hierarchyname>
# vgs
  VG    #PV #LV #SN Attr   VSize VFree
  sysvm   1   8   0 …

When asking for help in Libera Chat

, in the #mysql channel, people will ask you to use the mysql command line client. They will also point you to dbfiddle.uk

for asking questions. Specifically, when using phpMyAdmin, you will get hate.

Why is that?

When asking for help, it is almost impossible to help a GUI user, because they will need to paste screenshots in order to document what they did. The screenshots do not help us. They are hard to read, and do not contain the information about the problem you need help with in textual form.

Also, it is often that GUI users do not see all the details from a large result set cell. Specifically, things such as the output of SHOW ENGINE INNODB STATUS and similar usually fail to display in a GUI in a meaningful way. This is …

You have your database instance deployed with AWS and you are using AWS RDS for MySQL. All work smoothly in terms of satisfying queries for your application and delivering reliable uptime and performance. Now you need to take care of your backup strategy. Business is defined to have this retention policy:

• 7 daily full backups
• 4 weekly backups
• 12 monthly backups

Plus the ability to do point-in-time recovery (PITR) for the last 24 hours since the last full backup was taken.

The cloud vendor solution

This is a piece of cake. The daily backups: just set the backup retention period to six days. Done. This also has the plus that PITR is already covered since RDS uploads the transaction logs to S3 every five minutes and stores them in the parquet format, making it smaller than the regular text file. All amazing, right?

Now, the weekly and …

You know how much I praise and like MySQL Shell but if like me, for you too, old habits die hard, I advise you to create these different aliases in your ~/.bashrc (or ~/.bash_aliases) file to force yourself to use MySQL Shell even for a small statement check:

alias mysql="mysqlsh --sql mysql://localhost"
alias mysqlx="mysqlsh --js mysqlx://localhost"

Of course you can specify the user you want, by default it uses the same user as the system one.

For example, if this is your test machine and you want to always use the root account (even if not recommended), you can specify it like this by modifying the URI:

alias mysql="mysqlsh --sql mysql://root@localhost"

Example:

So now when using mysql MySQL Shell is launched and it connects directly to localhost in SQL mode using the classic protocol.

With …

Window functions in MySQL offer developers an efficient way to view and compare data across a result set. In this post we will talk about using aggregate functions as window functions and break down the different sections of a window frame.

Es gibt zwei Arten, wie man Tabellen in MySQL-Datenbanken erstellen kann: Ausführen einer Abfrage, die einen CREATE TABLE-Befehl enthält Nutzung der entsprechenden Funktionen von MySQL-bezogenen Tools und IDEs Der erste Ansatz ist hilfreich, wenn Sie Tabellen speziell über ein Skript erstellen müssen. Wenn Sie z.B. eine Tabelle zu einem ganz bestimmten Zeitpunkt erstellen möchten, es […]

The post Erstellen von Tabellen mit dem MySQL CREATE TABLE-Befehl in dbForge Studio for MySQL appeared first on Devart Blog.

New MySQL HeatWave capabilities released in 8.0.30-u1

To build the infrastructure you will both need an account and a tenancy within Oracle Cloud Infrastructure (OCI) account. If you do not have these, then please click here. Note the Oracle very often offers a free trial period which provides more than enough credits to complete the architecture described in this blog.

First introduced as part of the SQ:2003 Standard and available in MySQL 8.0, window functions in MySQL are compelling, but the syntax can also be a little intimidating when you first start using them. This post is the second in a series where we will discuss window functions – including breaking down the syntax and […]

A large table is a pain for many reasons as long as it is in a system. And as if that’s not enough, it is also a difficult task to get rid of it. In this post, we will understand why it is a pain to do this operation and what we can do about it. It will be like asking the table “Tell me what happened and I will ease up the eviction”.

So what happened? When a table is dropped (or truncated), InnoDB has to scan the pages throughout the buffer pool and remove all those belonging to that table. For a large buffer pool, this crawling in the buffer pool pages and eviction process will be slower. When we say “scan buffer pool”, it mainly looks for “LRU”, “FLUSH” (Dirty pages), and “AHI” entries.

LRU: Buffer pool pages are stored in a linked list of pages in order of usage. As the data reaches the end of the list, it is evicted to make space for new data. When the room is needed to add …