For the week of April 18th, 2012: Oracle Announces It’s First Sponsored MySQL Conference A New Development Milestone Release of MySQL 5.6 is now available IOUG’s Real World Performance Tour Returns to California this Month IOUG’s Plug-In to Vegas! adds … Continue reading →
There are probably thousands of articles on the Internet about disk statistics in Linux, what various columns mean, how accurate the information is, and so on. I decided to attack the problem from a little bit more practical side. Hopefully this will be just the first of many future posts on identifying various I/O related performance problems on a MySQL server.
Linux exposes disk statistics through /proc/diskstats. However the contents of this file isn’t something anyone can understand quickly. It needs a tool to transform the information into something human readable. A tool that is available for any Linux distribution is called iostat and comes with sysstat package.
How to access and read I/O statistics
Usually you want to call iostat one way:
iostat -xkd <interval> <block device>
The interval should typically be one second as it is the …
[Read more]
Last summer my colleague Marko Mäkelä committed this seemingly
innocent performance fix for InnoDB in MySQL 5.6:
3581 Marko Makela 2011-08-10
Bug#12835650 VARCHAR maximum length performance impact
row_sel_field_store_in_mysql_format(): Do not pad the unused part
of
the buffer reserved for a True VARCHAR column (introduced in
5.0.3).
Add Valgrind instrumentation ensuring that the unused part will
be
flagged uninitialized.
Before this, buffers which were used to send VARCHARs from InnoDB
to the MySQL server were padded with 0s if the string was shorter
than specified by the column. If, e.g., the string "foo" was
stored in a VARCHAR(8), InnoDB used to write "3foo00000" to the
buffer (the first character - 3 - determines the actual length of
the string). However, even though these trailing bytes are not
used anywhere, writing 0s to the buffer certainly has a cost.
Hence …
The performance of flush_list flushing of InnoDB decides the basic performance for modifying workloads. So, it is important to optimize the flush behavior. In this post we’ll consider how to optimize the neighbor-flushing behavior.
Factor 1: Characteristics of storage
Depending on the characteristics of your storage’s throughput for write IO, you can term your storage as either “write amount bound” or “write times bound”. The minimum unit of the InnoDB datafile is page size (16KB or less). And InnoDB attempts to combines them in a single IO up to 1 extent (1MB) maximum, if they are contiguous.
<one HDD>: Almost “write times bound”. Because head-seek time is the most effective factor for access time of HDD. And around 1MB size can be treated by the 1 head-seek.
…
[Read more]In MySQL labs release April 2012 we have reworked the flushing heuristics in InnoDB. In this post I’ll give an overview of what we have changed and the various configuration variables we have introduced to fine tune the flushing algorithm. If you are interested in finding out how our new flushing algorithm fares in benchmarks you can get these details in Dimitri’s well-explained blog here.
Flushing means writing dirty pages to disk. I have explained in some detail about adaptive_flushing and types of flushing in my previous notes. Please go through these notes if you want to …
[Read more]Note: this article was originally published on http://blogs.innodb.com on April 11, 2012 by Sunny Bains.
The problem
After making several performance fixes, notable among them being
the kernel mutex split and the new handling of read-only
transaction and in particular non-locking auto-commit read-only
transactions, we weren’t seeing any increase in transaction per
second (TPS) on our high-end hardware. On this one particular
host, a 24 core with 2 threads per core host. The TPS using
Sysbench was a tepid 5.6K at 16 threads and more or less
plateaued till 1K user threads. No matter what config setting we
used, we would more or less end up with the same result.
We ended up getting together for a meeting at Paris to discuss this issue and during the brain storming, one of the potential issues that cropped up was the effect of …
[Read more]
The problem
After making several performance fixes, notable among them being
the kernel mutex split and the new handling of read-only
transaction and in particular non-locking auto-commit read-only
transactions, we weren’t seeing any increase in transaction per
second (TPS) on our high-end hardware. On this one particular
host, a 24 core with 2 threads per core host. The TPS using
Sysbench was a tepid 5.6K at 16 threads and more or less
plateaued till 1K user threads. No matter what config setting we
used, we would more or less end up with the same result.
We ended up getting together for a meeting at Paris to discuss this issue and during the brain storming, one of the potential issues that cropped up was the effect of cache coherence and/or false sharing. After using …
[Read more]
This feature is a continuation of the “Fast Index Creation”
feature introduced in Fast Index Creation in the InnoDB
Storage Engine. Now you can perform other kinds of DDL operations on InnoDB tables online: that is, with minimal delay for
operations on that table, and without rebuilding the entire
table. This enhancement improves responsiveness and availability
in busy production environments, where making a table unavailable
for seconds or minutes whenever its column definitions change is
not practical.
The DDL operations enhanced by this feature are these variations on the …
[Read more]Wow! A lot has changed since the last MySQL conference I blogged about in 2007.
MySQL has been acquired twice: once as MySQL by Sun and the second time around bundled with Sun when Oracle bought Sun. The conference is no longer organized by O’Reilly but by Percona. And the MySQL database itself has changed — we were talking about new features in MySQL 5.1, which wasn’t released yet, along with Falcon (where did it go?). 5.1 has long since been released as has 5.5, and we’re now talking about 5.6 instead of 6.0. There was no “Cloud” on the horizon, nor was there MariaDB, XtraDB, Drizzle, Schooner, or any of the other offshoots of MySQL, all of which are creating a new buzz around the product.
Yet, one thing remains constant: the vibrant community around MySQL. In spite of all the changes in technology, ownership, versions, …
[Read more]
A lot has happened in MySQL 5.6 for queries joining many tables.
For the most common use cases we have drastically reduced the
cost of finding the execution plan. We have also improved the
heuristics and removed bugs so that the final plan is often
better than it used to be. Read on if you are one of those people
who do 15 way joins!
Finding a query execution plan
First some background. You can skip this part if you know how
MySQL picks the table join order in 5.5.
When presented with a query, MySQL will try to find the best
order to join tables by employing a greedy search algorithm. The
outcome is what we call a query execution plan, QEP. When you
join just a few tables, there's no problem calculating the cost
of all join order combinations and then pick the best plan.
However, since there are (#tables)! possible combinations, the
cost of calculating them all soon becomes too high: for five
tables, e.g., …