General query logs and slow query logs remain one of the most popular sources of auditing and diagnostic information in MySQL databases. Customers often ask about the cost of general and slow query logging so I went googling for existing research I could point them to. The data I found was not quite what I hoped for, which is why I decided to do some more testing.

Introduction Logging overhead is an obvious thing and it has been the topic of many blog posts before this one. Sure enough, my Google search returned multiple articles, however I wasn't fully satisfied the findings.
Perhaps the topic is so obvious that DBAs don't revisit it very often. In any case, most of the articles I found were several years old and that presents a few issues:

1. Most of the tests were run on MySQL 5.0 or 5.1.
2. Database hardware isn't what it used to be, which changes the perspective on database performance bottlenecks. …

If there is something that I love about information technology is the incredible amount of unpredictable ways that bugs can bite you where and when you less expect it.

This one I'm about to describe is a serious one, yet there has been a very quiet response from Percona bug team at Launchpad, where I first submitted it (because we use Percona server here). If you have read my other posts you know by now that I am a laz... err, busy guy so bear with me if only today I have verified that this is, in fact, an upstream bug and have raised a bug to Oracle too.
It affects 5.6.33, latest version at the time of this article.

Anyways, the bug started to manifest itself in the form of complete stalls of our main cluster, which was happening at different times and apparently in a way that was unrelated to the amount of traffic hitting the master. When stalling, system CPU time was topping 100% of total available …

FRM-less, transactional data dictionary is arguably the most significant feature change announced MySQL 8.0 development release. The server still has two separate dictionaries (MySQL, InnoDB) but the infamous FRM files are finally being replaced with transactional, InnoDB-based storage.

While this is interesting for various reasons, this particular post will focus on the impact this change has on data dictionary performance.
Test configuration Server configuration:

• Hardware: Amazon EC2 m4.4xlarge, 500GB, 15K IOPS
• OS: Debian Jessie
• MySQL versions tested: 5.7.15, 8.0.0-dmr
• Notable customer MySQL configuration values:
  • innodb_buffer_pool_size = 4GB
  • innodb_log_file_size = 256MB
  • innodb_buffer_pool_dump_at_shutdown = 0
  • innodb_buffer_pool_load_at_startup = 0
  • …

In this blog post we will present a first look at the performance of Group Replication (GR), now that the RC is out. The goal is to provide some insight on the throughput, latency and scalability one can expect in a modern computing infrastructure, using GR in single- and multi-master configurations, but mostly focused on single-master.…

I'm not a huge fan of the InnoDB FULLTEXT feature and I admit I wasn't too keen to play with it in the past. Apparently, the feeling is mutual and so FULLTEXT issues haven't popped up in too many projects I worked on... until last week.

This post describes the troubleshooting process of a FULLTEXT cache performance issue. Quite inconspicuous at the beginning, it proved to be a lot of fun in the end.

Ready, Set, GDB!
Background It all started with a generic performance issue report unrelated to FULLTEXT indexing. The issue, as described in the report, was:

1. The server performs acceptably for a few days.
2. After a certain amount of time, DML performance suddenly drops and remains low until the server is rebooted.
3. Reboot restores original performance, which again only lasts for a few days.

The situation was resulting in DML latency degradation during normal OLTP …

One of the things we have been working on for MySQL 8, is speeding up scans that read multiple records from a table or an index. Such scans benefit from reading the records in batches, so that they don’t get the overhead of latching data pages and navigating in the B-tree for every single record that is read.…

Thank you for attending my 22nd July 2016 webinar titled “Top Most Overlooked MySQL Performance Optimizations“. In this blog, I will provide answers to the Q & A for that webinar.

For hardware, which disk raid level do you suggest? Is raid5 suggested performance-wise and data-integrity-wise?
RAID 5 comes with high overhead, as each write turns into a sequence of four physical I/O operations, two reads and two writes. We know that RAID 5s have some write penalty, and it could affect the performance on spindle disks. In most cases, we advise using alternative RAID levels. Use RAID 5 when disk capacity is more important than performance (e.g., archive databases that …

In this blog post, we’ll discuss how we’ve improved TokuDB and PerconaFT fragmented data file performance.

Through our internal benchmarking and some user reports, we have found that with long term heavy write use TokuDB/PerconaFT performance can degrade significantly on large data files. Using smaller node sizes makes the problem worse (which is one of our performance tuning recommendations when you have faster storage). The problem manifests as low CPU utilization, a drop in overall TPS and high client response times during prolonged checkpointing.

This post explains a little about how PerconaFT structures dictionary files and where the current implementation breaks down. Hopefully, it explains the nature of the issue, and how our solution helps addresses it. It also provides some contrived benchmarks that prove the solution.

PerconaFT map file disk format

NOTE. …

In this blog post, we’ll discuss how a small innodb_page_size can create a performance boost for SSD.

In my previous post Testing Samsung storage in tpcc-mysql benchmark of Percona Server I compared different Samsung devices. Most solid state drives (SSDs) use 4KiB as an internal page size, and the InnoDB default page size is 16KiB. I wondered how using a different innodb_page_size might affect the overall performance.

Fortunately, MySQL 5.7 comes with the option innodb_page_size, so you can set different InnoDB page sizes than the standard 16KiB. This option is still quite inconvenient to use, however. You can’t change innodb_page_size for the existing database. Instead, you need to create a brand new database with a …

Group Replication introduces a new way to do replication in MySQL. With great features such as multi-master replication it brings a range of exciting deployment scenarios where some difficult problems become much easier to solve. Group Replication also brings a new set of options that may need to be configured to extract the highest performance from the underlying computing resources.…