If, in Percona Server, you are observing tail latencies on queries that should be fast, this might be a side effect of Percona's improved InnoDB Empty Free List Algorithm.  When using this algorithm (the default in 5.6 and 5.7 and optional configuration in 8.0), a query needing a free page while none are available waits until the LRU Manager Thread refills the free list.  Because this thread is

If you are using MyDumper as your Logical Backup solution and you store your backups on S3, you need to take a local backup and then upload it to S3. But what if there is not enough space to hold the backup on the server where we are taking the backup? Even if we have enough disk space, we will need to wait until the end to start to upload the files, making the whole process longer.

MyDumper implemented stream backup in v0.11.3 and we have been polishing the code since then. We also implemented two ways of executing external commands:

--exec-per-thread: The worker that is getting the data from the database will write and redirect to the STDIN of the external command. It will be similar to execute cat FILE | command per every written and closed file.

--exec: In this case, the worker writes in the local storage and when the file is closed, the filename is enqueued. The exec threads are …

While the LAMP stack – Linux/Apache/MySQL/PHP – is not the dominant platform it was a decade ago, there are still many websites that depend on it. One of the pillars of this quartet had a milestone last week when PHP 8.0 passed into End Of Life status. While it will still have limited security issue support for another year, 8.0 should be phased out of your environments.

By the way, 8.1 has another year until it reaches End of Life status.

So please update your PHP 8.0 as soon as you can, double check the connector you are using to access your database is updated too, and make sure you are ready for 8.1’s future.

And make sure that if you are currently running MySQL 5.7 that you upgrade by October 2023 when it too reaches End of Life.

In my honest opinion, MS Access is one garbage of a database. More likely, database is too strong of a word. While I won’t be using Access in this article, there is a story there (but I’m not here to have an Access bash party). Continue reading for an example of how you may use a correlated subquery and simulate the LEAD() Window Function.

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.

Image by  …

The Primary key is like the hero of a row, which has more beneficial features in the table while performing any task on the table.

The DBA knows the importance of the primary key in the table and how to handle it.

1. Notable features of having a primary key:
2. Requirements:
3. Enabling GIPK:
4. Handling GIPK:
…

We all know that data is important, and some businesses need historical data to be available all the time. The problem is that queries on large tables perform poorly if they are not properly optimized. We get many customer requests in Managed Services to purge/archive large tables, and to achieve it, we use pt-archiver.

Recently, we received a request to archive a large table, and the customer was worried about the downtime and performance issues during the archival.

We proposed a solution to the customer to archive the table using pt-archive. The idea is to archive old data to other tables and keep the latest data on the current table with minimal performance issues. All of the data will remain available and can be queried anytime.

In the blog, I will …

Earlier in the year, I was working on an issue where one of my clients had reported a massive influx in connection on their hosts after enabling Percona Monitoring and Management (PMM). This was something I had not seen before and after researching for a couple of days I discovered that if you monitor a MySQL instance with PMM configured to collect table statistics, and if the tables that it’s gathering statistics from are Federated, it will generate a connection on the remote host for the Federated tables, one for each Federated table in the instance. Let’s go over the details and provide some examples so we can understand this a bit better.

First, I’ll offer a reminder that a Federated table is simply a table that you can put in your MySQL instance that is empty locally and uses a network connection to get the data from …

In this article you will see how you can replace the user's password by an SSL X509 certificate to connect to MySQL.

More and more people are requesting how they could connect to MySQL without using a password but using a SSL certificate. Known as X509.

CA Certificate

A CA certificate is a digital certificate issued by a certificate authority (CA). It’s used by clients to verify the SSL certificates sign by this CA.

Such certificates is usually paid and needs to be manually installed with MySQL Server. But by default, MySQL generates a self-signed certificate and provides its own CA.

For obvious reason, I will use the certificates that have been auto-generated by MySQL on my system. However, for production, I encourage you to have a real CA signed certificate.

The CA certificate is called ca.pem and is located in MySQL’s datadir (/var/lib/mysql/ca.pem on Oracle Linux, RHEL, Fedora, CentOS, …).

In case you don’t know where your ca.pem is located, you can …

MySQL HeatWave is the only MySQL based service on AWS that combines transaction processing, real-time analytics, and machine learning within one single database. It eliminates the need for complex, time-consuming ETL operations between separate databases and tools for OLTP, analytics, and machine learning use cases. MySQL HeatWave also includes MySQL Autopilot, providing workload-aware, machine learning-powered automation of various aspects of the database system lifecycle.