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:
- The server performs acceptably for a few days.
- After a certain amount of time, DML performance suddenly drops and remains low until the server is rebooted.
- Reboot restores original performance, which again only lasts for a few days.
The situation was resulting in DML latency degradation during normal OLTP …
[Read more]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. …
[Read more]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.…
I worked on an issue last recently where a query was too slow when executed in MySQL Cluster. The issue was that Cluster has some restrictions when it comes to push down conditions.
As an example of this, consider the following query using the employees sample database. The query takes a look at the average salary based on how many years the employee has been with the company. As the latest hire date in the database is in January 2000, the query uses 1 February 2000 as the reference date.
Initially the query performs like (performance is with two data
nodes and all nodes in the same virtual machine on a laptop, so
the timings are not necessarily representative of a production
system, though the improvements should be repeatable):
mysql> SELECT FLOOR(DATEDIFF('2000-02-01', hire_date)/365) AS LengthOfService, COUNT(DISTINCT employees.emp_no) AS NoEmployees, AVG(salary) AS AvgSalary FROM salaries …[Read more]