I can’t thank you all enough for making OpenLampTech a successful endeavor for me. We are continuing to publish great newsletters that get read and draw in new subscribers. This week’s issue is loaded with great content so enjoy those articles you like most. Please share with others…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

In OpenLampTech issue #43 we have content on:

• The PHP Constructor
• WordPress needs writing
• MySQL Window Functions
• Managing 1,300 WordPress sites
• A list of great free …

Update June 12, 2023: This component has been deprecated in v17 and will be removed in v18! We recommend that you instead use VTOrc with the semi_sync durability policy. Introduction # MySQL group replication is a new replication mechanism that was released in 2016. Group replication involves establishing a group of nodes that are coordinated automatically via Group Communication System (GCS) protocols, an implementation of Paxos. For a transaction to commit, a majority of the group has to agree on the order of a given transaction in the global sequence of transactions.

For anything but the most trivial software projects source control is highly recommended. Where multiple developers contribute to a project then it is essential to have proper source control so that changes to code can be properly managed/merged.

Another day, another company announces it changed its license away from open source while claiming “We Believe In A Model Of Sustainable Open Source.”

I’m not sure about you, but this rings to me as similar to an alleged quote about the Vietnam War: “We had to destroy the village in order to save it.”

What Lightbend’s CEO and Founder, Jonas Boner, really means is that they have decided that they do not believe an open source license (Apache 2.0) suits their business goals best any longer, and a proprietary, source available license (BSL) will be a better fit.

As an entrepreneur and business founder myself, I understand business is hard. Often, your choices are limited, especially if you’ve taken other …

More and more people are using UUID’s to identify records in their database.

As you already know, for MySQL’s storage engine (InnoDB) the primary key is very important ! (for performance, memory and disk space).

See the following links:

• https://lefred.be/content/mysql-invisible-column-part-ii/
• https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html (GIPK mode in MySQL 8.0.30 !)

Problems

There are 2 major problems having a UUID as Primary Key in InnoDB:

1. generally they are random and cause clustered index to be rebalanced
2. they are included in each secondary indexes (consuming disk and memory)

Let’s have a look at …

Whilst not part of the LAMP stack per-se, you will need a MySQL client in order to be able to upload and make changes to your database schema. MySQL clients that could be used include: the traditional MySQL client; MySQL shell; MySQL Workbench as well as phpMyAdmin. At the stage the most efficient of these clients will be MySQL Shell.

In my previous blog post, ProcFS UDF: A Different Approach to Agentless Operating System Observability in Your Database, I wrote about the ProcFS UDF MySQL plugin, which allows you to get operating systems stats, through the MySQL database, without having shell access to the server and any local agent installation.

Some of you wondered whether there is a way to use this goodness in Percona Monitoring and Management (PMM), and this blog post will show you exactly how to do that.

Unfortunately, at this point, Percona Monitoring and Management does not support the ProcFS UDF MySQL plugin out of the box. It is in the backlog, along with many other cool things. However, …

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 …