As a best practice before dropping a large table in MySQL, pt-archiver can be used to delete all the records in batches. This helps to avoid a situation where your server may get stalled under certain circumstances.

I recently read a comment from a user saying “The pt-archiver is not working as expected! It is skipping the last record, which seems like a bug.”. Let’s examine pt-archiver’s default behavior and understand why the author of this comment believes that pt-archiver is bugged (Spoiler: It’s not!).

But wait, before continuing on busting the blame, let me clarify why to use pt-archiver before dropping large tables.

When we drop a table in MySQL:

• Table data/index (ibd) and definition (frm) files are removed.
• Triggers are removed.
• Table definition cache is updated by removing the table being dropped.
• InnoDB buffer pool is scanned for associated pages to …

Recently we saw how we can catch audit information using invisible column, JSON data type and triggers in MySQL 8.0:

• Poorman’s MySQL table audit information
• Poorman’s MySQL table audit information – part 2

Of course, the creation of these triggers can quickly become a tedious operation. Once again, MySQL has all what we need to make it simple.

As you may know, it’s impossible to create triggers from store procedure, so using a sys schema function would not be something possible. However, MySQL Shell is again the answer ! It’s very easy to use a python plugin to perform the necessary operations.

I’ve written such example, available on …

Ingress is a resource that is commonly used to expose HTTP(s) services outside of Kubernetes. To have ingress support, you will need an Ingress Controller, which in a nutshell is a proxy. SREs and DevOps love ingress as it provides developers with a self-service to expose their applications. Developers love it as it is simple to use, but at the same time quite flexible.

High-level ingress design looks like this: 

1. Users connect through a single Load Balancer or other Kubernetes service
2. Traffic is routed through Ingress Pod (or Pods for high availability)
   • There are multiple flavors of Ingress Controllers. Some use nginx, some envoy, or other proxies. See a curated list of Ingress Controllers here.
3. Based on HTTP headers traffic is routed …

Where can you find weekly content on all the things PHP, MySQL, the LAMP stack and related frameworks? If you guessed the OpenLampTech newsletter then you’re correct! Let’s dive into this week’s issue…

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.

I’m including so much original and curated content in these weekly newsletters, it’s a wonder my email service provider can even deliver them lol.

Let’s see what is in store in this week’s OpenLampTech issue #22:

…

Access patterns intrigue me because it seems that everyone knows what they are and talks about them, but there’s also very little written about them—in MySQL literature, at least. That’s why I set out to enumerate a list of access patterns (specific to MySQL). Since there’s no apparent standard for access patterns, I cannot say how my list measures up, but after spending most my career with MySQL, I know this: it is necessary to consider these access patterns when evaluating and improving MySQL performance. Simply put: you cannot ignore how the application accesses MySQL.

Access patterns intrigue me because it seems that everyone knows what they are and talks about them, but there’s also very little written about them—in MySQL literature, at least. That’s why I set out to enumerate a list of access patterns (specific to MySQL). Since there’s no apparent standard for access patterns, I cannot say how my list measures up, but after spending most my career with MySQL, I know this: it is necessary to consider these access patterns when evaluating and improving MySQL performance. Simply put: you cannot ignore how the application accesses MySQL.

Access patterns intrigue me because it seems that everyone knows what they are and talks about them, but there’s also very little written about them—in MySQL literature, at least. That’s why I set out to enumerate a list of access patterns (specific to MySQL). Since there’s no apparent standard for access patterns, I cannot say how my list measures up, but after spending most my career with MySQL, I know this: it is necessary to consider these access patterns when evaluating and improving MySQL performance. Simply put: you cannot ignore how the application accesses MySQL.

Codership is pleased to announce a new release of Galera Manager. This is version 1.6.2 of Galera Manager GUI. Users will notice many usability improvements, and multiple fixes for issues filed at the galera-manager-support issue tracker.

The biggest user facing feature is now being able to deploy Galera Manager managed nodes on DigitalOcean, in addition to Amazon Web Services (AWS) Elastic Compute Cloud (EC2). Now with an access token, one can fully deploy a managed cluster from within Galera Manager. 3 node fully managed Galera Clusters on Digital Ocean in under 10 minutes? This is the promise and delivery of the latest Galera Manager.

From a security standpoint, SSL …

I really enjoyed to dig into the solution I described yesterday in this post, to generate table audit information using invisible columns and triggers.

In this post, I will focus only on the solution using a JSON column to store the audit information.

Yesterday, I wrote that it’s also possible to track all changes an not only the last one but also keep information about what changed.

I wanted to illustrate that with an example, let’s start with the output:

This is exactly what I was looking for !

And of course we can search in that audit information. For example let’s search for all records that have been modified and where the old or new name was/is frederic:

We can verify that indeed that record had frederic as initial value.

Triggers

Compare …

Abstract:

By diving into the details of our case study, we will explain how incorrect table statistics may lead the optimizer to choose a suboptimal execution plan. We will also go into how MySQL calculates the table statistics and the ways to correct the table statistics to prevent it from happening again.

Case study: Incorrect table statistics lead the optimizer to choose a poor execution plan.

A customer reported a drastic performance degradation of a query while there were no code changes and no configuration changes made. The data in this article has been edited for brevity and modified to mitigate the exposure of confidential information. The case has also been approved for publication by the customer.

We obtained the query execution plan, and got the results as shown below (execution plan #1):

mysql> explain 
-> SELECT count(con.id)          ,
->        MAX(DAYNAME(con.date)) ,
-> …