A special extended edition of Tech Messages for 2011-09-21 through 2012-05-11:
WordPress on S3: run a beautiful website on Amazon S3 cloud storage
OblakSoft is proud to introduce the 1st ever dynamic WordPress site running on top of Amazon S3: Yapixx. Now you too can launch your own beautiful website on Amazon S3.
While Yapixx stands for Yet Another Picture Sharing Site, it is actually one of a kind. Yapixx is WordPress that was moved to run on top of Amazon S3 storage without changing a line of code in the WordPress core engine.
[Read more]
A short while after I fixed the legacy bug that prevented
temporary MyISAM tables from using the dynamic record format, I
got an email from Davi Arnaut @ Twitter. It turned out that
Twitter needed to fix the very same problem, but for the case
when INFORMATION_SCHEMA temporary tables use MyISAM.
In short, INFORMATION_SCHEMA tables provide access to database
metadata. Despite their name, they are more like views than
tables: when you query them, relevant data is gathered from the
dictionary and other server internals, not from tables. The
gathered data is stored in a temporary table (memory or MyISAM
depending on size) and then returned to the user.
The reason Davi emailed me was to let me know that he had further
improved the fix for temporary MyISAM tables to also enable the
use of dynamic record format for …
I had to benchmark an EC2 instance to see whether a database could be safely moved to it. It is a good practice, which helps avoiding surprises when an instance or its storage are allocated in a noisy neighborhood, where the neighbors use so much resources that it affects the performance of our MySQL database. It is understandable that one can never get very reliable results on EC2, this is a shared environment after all, and that some fluctuations should be expected, however it is still good to know the numbers. I started my benchmarks and everything seemed fine at first, but then sometimes statistics I was getting started looking quite odd.
I was running the benchmarks on a High-CPU Extra Large Instance and couldn’t see any reliability in the results at all. I mean, in one moment I was getting poor throughput and horrible response times only to see it improve a lot a few minutes later. I ruled out a possibility that it could be …
[Read more]Security-Enhanced Linux or SELinux is a Linux kernel feature that provides a mechanism for supporting access control security policies. It enables a system administrator to create an extra set of rules that define allowed operations for programs even after the standard controls are checked. In other words, SELinux can help improving system security by restricting access of an application to only a few resources it actually needs, which makes it more difficult for an attacker to gain access to the entire system through exploiting any possible vulnerabilities in the application.
However as rarely anything in life is free, is there any price we have to pay to use SELinux on a MySQL server?
I ran a simple MySQL benchmark first with database working in a system with SELinux enabled (SELINUX=enforcing), and then also with the extra security layer entirely disabled (SELINUX=disabled).
The …
[Read more]In the latest episode of our “Meet The MySQL Experts” podcast, Mikael Ronstrom, senior MySQL Architect, explains us how the MySQL Thread Pool improves MySQL Scalability.
You can try out the MySQL Thread Pool via our MySQL Enterprise Edition Trial.
And…MySQL being of Nordic origin, Hyvää Vappua/Glada Vappen to all the Finns and Swedes among us!
Enjoy the podcast!
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]