tl;dr:
Make sure you flash an LSI-9211 to IT firmware rev#14 to get it to work 
with Linux and SSD trim.  You may have to downgrade from newer firmware
to older firmware to get the card to work.

Finding a SATA III controller with more than one PCI-e lane
After a recent hardware issue I decided to upgrade my computer to use new Intel 520 120MB SSD drives in RAID for improved performance.  The motherboard I use (an ASUS Rampage III extreme) has a Marvel SATA III controller with two ports, but I discovered that it is connected via only a single PCI-e lane (each lane can do at most 400MB/sec*).  This means that it can't effectively support even a single Intel 520 because one device can saturate the SATA III bus (An Intel 520 is rated at up to 550MB/sec sequential write).

So I went on a quest for a new SATA 3 controller.   To Frys! I exclaimed.  But unfortunately, all the PCI-e 2.x SATA III …

dm-cache is (albeit still classified “experimental”) is in the just released Linux 3.9 kernel. It deals with generic block devices and uses the device mapper framework. While there have been a few other similar tools flying around, since this one has been adopted into the kernel it looks like this will be the one that you’ll be seeing the most in to the future. It saves sysadmins the hassle of compiling extra stuff for a system.

A typical use is for an SSD to cache a HDD. Similar to a battery backed RAID controller, the objective is to insulate the application from latency caused by the mechanical device, the most laggy part of which is seek time (measured in milliseconds). Giventhe  relatively high storage capacity of an SSD (in the hundreds of GBs), this allows you to mostly disregard the mechanical latency for writes and that’s very useful for database systems such as MariaDB.

That covers writes (for the moment), but …

Sysbench OLTP, transactions per second

When dealing with high performance, low latency storage devices, such as SSD cards, one finds bottlenecks in new places. This is a story about such a bottle neck and how to work around it.

One unique feature of InnoDB is the double write buffer. This buffer was implemented to recover from half-written pages. This can happen in case of a power failure while InnoDB is writing a page (16KB = 32 sectors) to disk. On reading that page, InnoDB would be able to discover the corruption from the mismatch of the page checksum. However in order to recover, an intact copy of the page would be needed.

The double write buffer provides such a copy. Whenever InnoDB flushes a page to disk, it is first written to the double write buffer. Only when the buffer is safely flushed to disk, InnoDB writes the page to the final destination. When recovering, InnoDB scans the double write buffer and for each …

The Micron P320h SSD is an SLC-based PCIe solid-state storage device which claims to provide the highest read throughput of any server-grade SSD, and at Micron’s request, I recently took some time to put the card through its paces, and the numbers are indeed quite impressive.

For reference, the benchmarks for this device were performed primarily on a Dell R720 with 192GB of RAM and two Xeon E5-2660 processors that yield a total of 32 virtual cores. This is the same machine which was used in my previous benchmark run. A small handful of additional tests were also performed using the Cisco UCS C250. The operating system in use was CentOS 6.3, and for the sysbench fileIO tests, the EXT4 filesystem was used. The card itself is the 700GB model.

So let’s take a look at the data.

With the sysbench fileIO test in asynchronous …

Recently we have helped our customer to migrate their entire application stack from one data center to another. Before we were brought on-board, customer had already placed an order for a new set of servers with the new hosting provider. All of them were suppose to be high-end systems – many CPU cores, plenty of RAM and RAID array build on top of SSD drives. As the new machines started being available to us, we began setting up the new environment. At some point it turned out that the new machines were actually slower compared to the several year old systems and their load was much higher under comparable traffic.

We examined several of the new servers and each time the conclusion was that the problems were related poor I/O performance. In the benchmarks a RAID 10 array on Intel SSD 330 Series drives was barely able to achieve 200-300 IOPS in random writes and even that at the cost of …

A beautiful picture is sometimes better than words :

With standard HDDs (here in RAID 10), the innodb_io_capacity variable may have non expected effects.

You can see on this picture the result of a test on one of my server with only the replication thread activated. The value of the innodb_io_capacity variable was the single modification during the period.

So, don’t touch this parameter without a strong reason…
(Of course 30000 was a bit snooty)

[MAJ 2013-01-08] : I know 30k is a too high value for innodb_io_capacity, I used this value to make the graphe really eloquent.
Read this excellent post from Chris for more details : http://www.chriscalender.com/?p=201

…

Solid State Drive (SSD) have made it big and have made their way not only in desktop computing but also in mission-critical servers. SSDs have proved to be a break-through in IO performance and leave HDD far far behind in terms of Random IO performance. Random IO is what most of the database administrators would be concerned about as that is 90% of the IO pattern visible on database servers like MySQL. I have found Intel 520-series and Intel 910-series to be quite popular and they do give very good numbers in terms of Random IOPS. However, its not just performance that you should be concerned about, failure predictions and health gauges are also very important, as loss of data is a big NO-NO. There is a great deal of misconception about the endurance level of SSD, as its mostly compared to rotating disks even when measuring endurance levels, however, there is a big difference in how both SSD and HDD work, and that has a direct impact on the endurance …

Solid State Drive (SSD) have made it big and have made their way not only in desktop computing but also in mission-critical servers. SSDs have proved to be a break-through in IO performance and leave HDD far far behind in terms of Random IO performance. Random IO is what most of the database administrators would be concerned about as that is 90% of the IO pattern visible on database servers like MySQL. I have found Intel 520-series and Intel 910-series to be quite popular and they do give very good numbers in terms of Random IOPS. However, its not just performance that you should be concerned about, failure predictions and health gauges are also very important, as loss of data is a big NO-NO. There is a great deal of misconception about the endurance level of SSD, as its mostly compared to rotating disks even when measuring endurance levels, however, there is a big difference in how both SSD and HDD work, and that has a direct impact on the endurance …

TokuDB® is a proven solution that scales MySQL® and MariaDB® from GBs to TBs with unmatched insert and query speed, compression, replication performance and online schema flexibility. Tokutek’s recently launched TokuDB v6.5 delivers all of these features and more, not just for HDDs, but also for flash memory.

Originally Aired: October 10th
AVAILABLE ON DEMAND

TokuDB v6.5:

• Stores 10x More Data – TokuDB delivers 10x compression without any performance degradation. Users can therefore take advantage of much greater amounts of available space without paying more for additional storage.

• Delivers High Insertion Speed – TokuDB Fractal Tree® indexes …

A while ago I had a discussion with someone about the future of server infrastructure. Among other things, we were wondering whether companies will continue to run on dedicated servers or if eventually everyone just ends up in a Cloud environment. During the discussion I raised a point that in principle Cloud is a great idea that will keep attracting more and more people, but it is missing one important piece that stops many from using it – a high performance storage. Apparently, this has just changed.

Yesterday I received an e-mail announcing a new EC2 instance type – hi1.4xlarge. It features 16 logical CPUs (35 ECUs), 60GB of RAM, and… two 1TB SSD-based disk volumes! These are great specs that should work for nearly any database. Even assuming someone has a MySQL database larger than 2TB, not all tables will require fast storage, while more disk space can be easily added by attaching regular EBS volumes.

I decided …