The latest from the MySQL community

As the usage of your app grows, performance can steadily decline.

There’s nothing necessarily surprising by that statement, but what is surprising is the number of bottlenecks that can surface and the options available to you to fix them. One such bottleneck can be directly related to the time it takes to read from and write to your database. After all, behind the complexities of a relational database, you’re still working with storage systems that have inherent IO latency.

This is where a well-architected caching system can help.

A good caching system can reduce the load on your database and increase the general performance of your application. A faster application results in happier users and potentially more revenue, which is always a good thing! However, caching systems have their own setup complexities, along with a number of gotchas that might creep up unexpectedly.

In this article, we’ll explore …

This blog post discusses roles in MySQL 8.0, which are named collections of privileges. Like user accounts, roles can have privileges granted and revoked as required.

Typically, we have multiple users with the same set of privileges. Previously, the only way to grant and revoke privileges to multiple users was to change the privileges of each user individually, which was time-consuming. To make it easier, MySQL provided a new object called role. A role is a named collection of privileges.

Here are the primary SQL commands that we will be discussing in relation to managing MySQL roles:

CREATE ROLE and DROP ROLE create and remove roles.
GRANT and REVOKE assign privileges to revoke privileges from user accounts and roles.
SHOW GRANTS displays privilege and role assignments for user accounts and roles.
SET DEFAULT ROLE specifies which account roles are active by default.
SET ROLE changes the active roles within the current session.
The …

Performing an operation is always challenging when dealing with K8s.

When on-prem or DBaaS like RDS or Cloud SQL, it is relatively straightforward to apply a change. You can perform a DIRECT ALTER, use a tool such as pt-osc, or even, for certain cases where async replication is in use, perform changes on replicas and failover.

In this blog post, I’ll provide some thoughts on how schema changes can be performed when running MySQL on Kubernetes

I won’t focus on DIRECT ALTERs as it is pretty straightforward to apply them. You can just connect to the MySQL service POD and perform the ALTER.

But how can we apply changes in more complex scenarios where we may want to benefit from pt-osc, gain better control over the operation, or take advantage of the K8s features?

One convenient way that I’ve found …

Transparent Huge Pages (THP) is a memory management feature in Linux operating systems that aims to enhance system performance. While THP can be beneficial for many applications, enabling it on a database server could have unintended consequences. In this post, we will explore THP, its impact on database servers, and how to disable it for optimal performance and stability.

What are Transparent Huge Pages?

In order to understand THP, we should first start with a brief description of Linux HugePages. The concept of HugePages in Linux has existed for many years, first introduced in 2007. By default, the majority of widely used Linux distributions employ a virtual memory page size of 4KB. However, the inclusion of the HugePages feature allows the Linux kernel to efficiently handle substantial memory pages alongside the standard 4KB size.

In order for an application to utilize HugePages, however, it must explicitly include an …

As a DBA, one of the very frequent tasks is to stop/start MySQL service for batching or some other activities. Before stopping MySQL, we may need to check if there are any active connections; if there are, we may need to kill all those. Generally, we use pt-kill to kill the application connections or prepare kill statements using the select queries.

Example commands:

pt-kill --host=192.168.11.11 --user=percona -p --sentinel /tmp/pt-kill.sentinel2 --pid /tmp/pt-kill.pid  --victims all --match-command 'Query' --ignore-user 'pmm|rdsadmin|system_user|percona' --busy-time 10 --verbose --print --kill 

select concat('kill ',id,';') from information_schema.processlist where user='app_user';

MySQL has a variable called offline_mode to set the server into maintenance mode. When you set this, it immediately disconnects all the client connections that don’t …

Whenever you install your favorite MySQL server on a freshly created Ubuntu instance, you start by updating the configuration for MySQL, such as configuring buffer pool, changing the default datadir director, and disabling one of the most outstanding features – query cache. It’s a nice thing to do, but first things first. Let’s review the best practices we usually follow in Managed Services before using your MySQL server in production and stage env, even for home play purposes.

Memory

Our usual recommendation is to use specific memory parameters, which we suggest to ensure optimal performance.

• To prevent out-of-memory (OOM) episodes, the OOM Score has to be set to -800.
• vm.swappiness = 1
• Disable Transparent Huge Pages
• Install and enable jemalloc. Let’s briefly go through each setting to understand why adjustments are needed. Afterward, we will see how to configure these …

As MySQL database administrators, we are well aware of the significance of implementing a primary key within a table. Throughout our careers, most of us have encountered situations where the absence of a primary key has led to operational challenges. Primary keys play an indispensable role in sound database design by uniquely identifying individual rows and significantly enhancing data retrieval, manipulation, and overall system performance.

From the MySQL documentation:

The PRIMARY KEY clause is a critical factor affecting the performance of MySQL queries and the space usage for tables and indexes. The primary key uniquely identifies a row in a table. Every row in the table should have a primary key value, and no two rows can have the same primary key value.

It is common for tables to be inadvertently created without a primary key, often leading to regrettable consequences that we only recognize when issues …

ProxySQL is a high-performance SQL proxy, which runs as a daemon watched by a monitoring process. The process monitors the daemon and restarts it in case of a crash to minimize downtime.

The daemon accepts incoming traffic from MySQL clients and forwards it to backend MySQL servers.

The proxy is designed to run continuously without needing to be restarted. Most configurations can be done at runtime using queries similar to SQL statements in the ProxySQL admin interface. These include runtime parameters, server grouping, and traffic-related settings.

Here, we will consider ProxySQL configured for async replication. Even when a replica is broken/stopped, ProxySQL still routes connections to replicas. It can be overcome by setting the appropriate value for mysql-monitor_slave_lag_when_null

…

Some fun is coming

While I was writing the internal guidelines on how to migrate from MariaDB to Percona Server, I had to export the users accounts in a portable way. This given MariaDB uses  some non standard syntax brings me to first test some external tools such as Fred https://github.com/lefred/mysqlshell-plugins/wiki/user#getusersgrants and our PT-SHOW-GRANTS tool. 

Useless to say this had open a can worms, given first I had to fix/convert the specifics for MariaDB (not in the scope of this blog), then while testing I discover another nasty issue, that currently prevent us to easily export the new …

If you are deploying MySQL on containers, one of the first tasks is to find the right image.

There’s a certain amount of confusion, especially when we’re trying to help someone who’s having problems with their deployment.

For example, when people say I’m using the official docker image… what does that really mean?

Docker Hub, provides their official image (https://hub.docker.com/_/mysql), but this is not the official MySQL image that we, the MySQL Team at Oracle, support.

Before the mess with Docker Hub ([1], [2], [3]), the real official images for MySQL …