In this blog post, I’ll look for the bottleneck that prevented the performance in my previous post from achieving better results.
The powerful machine I used in the tests in my previous post has a comparatively slow disk, and therefore I expected my tests would hit a point when I couldn’t increase performance further due to the disk speed.
Hardware configuration:
Processors: physical = 4, cores = 72,
virtual = 144, hyperthreading = yes
Memory: 3.0T
Disk speed: about 3K …
In MySQL 5.6 InnoDB has a dedicated thread (page_cleaner) that’s
responsible for performing flushing operations. Page_cleaner
performs flushing of the dirty pages from the buffer pool based
on two factors:
- access pattern - the least recently used
pages will be flushed by LRU flusher from LRU_list when buffer
pool has no free pages anymore;
- age – the oldest modified non-flushed pages are part of
flush_list structure and will be flushed by flush_list flusher
based on several heuristics.
There is a good overview of the page_cleaner and also here you may find some details about flushing in MySQL 5.6. Below I describe several additional aspects of the flush_list flushing that was not really …
[Read more]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
…
[Read more]Not a big deal, but I just added a “Table of Contents” page to my blog to make finding older articles much easier.
I noticed most of my posts are quite lengthy, and it can take a bit of searching/clicking to find an older entry. So unless you happen to recall the ‘month/year’ it was published, which I don’t even remember that, then hopefully this will help.
Really simple, and looks just like this:
In the InnoDB plugin, a new variable was added named innodb_io_capacity, which controls the maximum
number of I/O operations per second that InnoDB will perform
(which includes the flushing rate of dirty pages as well as the
insert buffer (ibuf) batch size).
First off, let me just say this is a welcome addition (an addition provided by the
Google Team, fwiw).
However, before this was configurable, the internal hard-coded
value for this was 100. But when this became configurable, the
default was increased to 200.
For many systems, this is not an issue (i.e., the overall system
can perform 200 IOPS).
However, there are still many disks (which is often the
bottleneck) out there that are …
In my last post, I discussed how to troubleshoot InnoDB locks using the new InnoDB Plugin’s new Information Schema tables.
However, that got me to thinking about how many MySQL 5.1 users who have still not switched to use the plugin as opposed to the built-in version of InnoDB.
There are many advantages to using the plugin as opposed to the built-in version (aside from just the new I_S tables, and more importantly, numerous performance enhancements), and it’s breeze to set up, so I wanted to provide a quick start guide to using the new InnoDB plugin.
Note that the …
[Read more]Doing some performance testing on some modern hardware comparing Innodb plugin 1.0.4 with stock Innodb. I'm running a sysbench transactions test (reads and writes) with 200M rows in my table (table size is around 46G, RAM is 16G, buffer pool is set to 12G).
I was puzzled to see the innodb plugin to be decent, but not really as great as I expected, I was doing about ~6100 RW operations a second (individual statements within transactions). Then I compared it to the stock innodb and shockingly I got ~7K ops. I thought about what I tuned that was different in the plugin and came up with the innodb_io_capacity.
