September 19, 2014

Reference architecture for a write-intensive MySQL deployment

We designed Percona Cloud Tools (both hardware and software setup) to handle a very high-intensive MySQL write workload. For example, we already observe inserts of 1bln+ datapoints per day. So I wanted to share what kind of hardware we use to achieve this result. Let me describe what we use, and later I will explain […]

How to improve InnoDB performance by 55% for write-bound loads

During April’s Percona Live MySQL Conference and Expo 2014, I attended a talk on MySQL 5.7 performance an scalability given by Dimitri Kravtchuk, the Oracle MySQL benchmark specialist. He mentioned at some point that the InnoDB double write buffer was a real performance killer. For the ones that don’t know what the innodb double write […]

Looking for RAID Controller without Battery Learning problems ?

A lot have been written about Battery Learning Cycle problems and its impact to MySQL Performance. Here are couple of links (1,2). It is good to see though there are some controllers coming out which solve this problem, namely Adaptec 5Z series controllers (Z stands for Zero Maintenance). This is not quite new technology they […]

MLC SSD card lifetime and write amplification

As MLC-based SSD cards are raising popularity, there is also a raising concern how long it can survive. As we know, a MLC NAND module can handle 5,000-10,000 erasing cycles, after which it gets unusable. And obviously the SSD card based on MLC NAND has a limited lifetime. There is a lot of misconceptions and […]

Aligning IO on a hard disk RAID – the Benchmarks

In the first part of this article I have showed how I align IO, now I want to share results of the benchmark that I have been running to see how much benefit can we get from a proper IO alignment on a 4-disk RAID1+0 with 64k stripe element. I haven’t been running any benchmarks […]

The perils of uniform hardware and RAID auto-learn cycles

Last night a customer had an emergency in selected machines on a large cluster of quite uniform database servers. Some of the servers were slowing down in a very puzzling way over a short time span (a couple of hours). Queries were taking multiple seconds to execute instead of being practically instantaneous. But nothing seemed […]

RAID throughput on FusionIO

Along with maximal possible fsync/sec it is interesting how different software RAID modes affects throughput on FusionIO cards. In short conclusion, RAID10 modes really disappoint me, the detailed numbers to follow. To get numbers I run

test with 16KB page size, random read and writes, 1 and 16 threads, O_DIRECT mode. FusionIO cards are […]

RAID System performance surprises

Implementing MySQL database in 24/7 environments we typically hope for uniform component performance, or at least would like to be able to control it. Typically this is indeed the case, for example CPU will perform with same performance day and night (unless system management software decides to lower CPU frequency due to overheating). This is […]

MySQL 101: Monitor Disk I/O with pt-diskstats

Here on the Percona Support team we often ask customers to retrieve disk stats to monitor disk IO and to measure block devices iops and latency. There are a number of tools available to monitor IO on Linux. iostat is one of the popular tools and Percona Toolkit, which is free, contains the pt-diskstats tool […]

A closer look at the MySQL ibdata1 disk space issue and big tables

A recurring and very common customer issue seen here at the Percona Support team involves how to make the ibdata1 file “shrink” within MySQL. I can only imagine there’s a degree of regret by some of the InnoDB architects on their design decisions regarding disk-space management by the shared tablespace* because this has been a big […]