はてなブックマーク

MySQL is certainly a powerful open source database management system, but even the most robust engine struggles when queries take an eternity to execute. For DBAs and developers, improving MySQL query performance is an ongoing goal. Efficient query performance is crucial for ensuring the smooth operation and optimal user experience of applications powered by MySQL databases. When businesses rely h

Warning! Recently, Jean-François Gagné opened a bug on bug.mysql.com #115517; unfortunately, the bug is now private. However, the bug looks quite serious. We at Percona have performed several tests and opened the issue PS-9306 to investigate the problem. In short, what happens is that if you create a large number of tables, like 10000, the […]

All of Percona’s open-source software products, in one place, to download as much or as little as you need.

MySQL 8.4 has now been officially released, and this is a quick review of what is in the release notes. This is momentous as it is designated a Long-Term Support (LTS) release. Various 8.0 releases introduced material changes that impacted speed and stability, causing hair-pulling and swearing among those affected. Please note this is a first peek at the release notes, and comments in italics are

If you have enough experience with MySQL, it is very possible that you stumbled upon an unusually slow SELECT COUNT(*) FROM TABLE; query execution, at least occasionally. Recently, I had a chance to investigate some of these cases closer, and it stunned me what huge differences there can be depending on the circumstance given the very same table. As the problem turned out to be much more complex t

Oracle recently made their quarterly releases with MySQL 8.0.35 and MySQL 8.2. This blog post is a quick look at the release notes to see what these new versions bring to the community. You’ll want to keep an eye on the deprecations in particular because some long-accepted behavior, including wildcards, will change eventually. We get 83 and 50 bug fixes, respectively. We also get 17 and 33 depreca

Sometimes, there is a need to update the table and index statistics manually using the ANALYZE TABLE command. Without going further into the reasons for such a need, I wanted to refresh this subject in terms of overhead related to running the command on production systems. However, the overhead discussed here is unrelated to the usual cost of diving into table rows to gather statistics, which we c

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

All of Percona’s open-source software products, in one place, to download as much or as little as you need.

This blog is in reference to our previous ones for ‘Innodb Performance Optimizations Basics’ 2007 and 2013. Although there have been many blogs about adjusting MySQL variables for better performance since then, I think this topic deserves a blog update since the last update was a decade ago, and MySQL 5.7 and 8.0 have been released since then with some major changes. These guidelines work well for

With a special focus on Percona Operator for MySQL OverviewHAProxy, ProxySQL, MySQL Router (AKA MySQL Proxy); in the last few years, I had to answer multiple times on what proxy to use and in what scenario. When designing an architecture, many components need to be considered before deciding on the best solution. When deciding what to pick, there are many things to consider, like where the proxy n

All of Percona’s open-source software products, in one place, to download as much or as little as you need.

If you watched Finding Poorly Designed Schemas and How to Fix Them you witnessed Marcos Albe use some very interesting queries. These queries let you find tables without primary keys, tables with non-integer primary keys, tables that do not use InnoDB, tables and indexes with the most latency, indexes that are 50% larger than the table, find duplicate indexes, and find unused indexes. As promised,

A little bit ago, I released a blog post comparing the backup performance of different MySQL tools such as mysqldump, the MySQL Shell feature called Instance Dump, mysqlpump, mydumper, and Percona XtraBackup. You can find the first analysis here: Backup Performance Comparison: mysqldump vs. MySQL Shell Utilities vs. mydumper vs. mysqlpump vs. XtraBackup However, we know the backups are just the fi

Often users come to us with incidents of database crashes due to OOM Killer. The Out Of Memory killer terminates PostgreSQL processes and remains the top reason for most of the PostgreSQL database crashes reported to us. There could be multiple reasons why a host machine could run out of memory, and the most common problems are: Poorly tuned memory on the host machine. A high value of work_mem is

Recently, AWS presented its own CPU on ARM architecture for server solutions. It was Graviton. As a result, they update some lines of their EC2 instances with new postfix “g” (e.g. m6g.small, r5g.nano, etc.). In their review and presentation, AWS showed impressive results that it is faster in some benchmarks up to 20 percent. On the other hand, some reviewers said that Graviton does not show any s

Primary Key design is an important thing for InnoDB performance, and choosing a poor PK definition will have an impact on performance and also write propagation in databases. When this comes to Aurora, this impact is even worse than you may notice. In short, we consider a poor definition of a Primary Key in InnoDB as “anything but quasi sequential values”, which may cause very random access to dat

At some point, your needs for improving database performance may run into a threshold where optimization and tuning are no longer enough. If you cannot change the database engine and can no longer tune the parameters for your workload, you need to use scaling. As it is widely known, scaling may be either vertical or horizontal. Vertical scaling means adding more resources to a single node. Usually

Nobody likes change, especially when that change may be challenging.  When faced with a technical challenge, I try to remember this comment from Theodore Roosevelt: “Nothing in the world is worth having or worth doing unless it means effort, pain, difficulty.”  While this is a bit of an exaggeration, in this case, the main concept is still valid.  We shouldn’t shy away from an upgrade path because

The expected growth of ARM processors in data centers has been a hot topic for discussion for quite some time, and we were curious to see how it performs with PostgreSQL. The general availability of ARM-based servers for testing and evaluation was a major obstacle. The icebreaker was when AWS announced their ARM-based processors offering in their cloud in 2018. But we couldn’t see much excitement

I am very passionate about database observability, and I believe query performance observability is the most important insight you can get in your database.  Why? Because if you look from an application developer’s point of view, once a database is provisioned and you can connect to it, responding to your queries promptly and correctly is essentially all that you need from the database. This appli

All of Percona’s open-source software products, in one place, to download as much or as little as you need.

All of Percona’s open-source software products, in one place, to download as much or as little as you need.

When I speak about MySQL performance troubleshooting (or frankly any other database), I tend to speak about four primary resources which typically end up being a bottleneck and limiting system performance: CPU, Memory, Disk, and Network. It would be great if when seeing what resource is a bottleneck, we could also easily see what queries contribute the most to its usage and optimize or eliminate t

All of Percona’s open-source software products, in one place, to download as much or as little as you need.

All of Percona’s open-source software products, in one place, to download as much or as little as you need.

The RED Method (Rate, Errors, Duration) is one of the more popular performance monitoring approaches.  It is often applied to Monitoring Microservices though there is nothing that prevents it from being applied to databases like MySQL. In Percona Monitoring and Management (PMM) v2 we have all the required information stored in the ClickHouse database, and with the built-in ClickHouse datasource it

Due to the immense generation of data, scalability has become one of the hottest topics in the field of databases. Scalability can be achieved horizontally or vertically. Vertical scalability means adding more resources/hardware to existing nodes to enhance the capability of the database to store and process more data, for example, adding a new process, memory, or disk to an existing node. Every D