I use Aspersa tools a lot and I find myself going to the website just to download one of the tools all the time. I love I can download maatkit with a simple wget maatkit.org/get/tool command so I made bit.ly shortcuts for all of the current aspersa tools. Here’s the full list with my favorite on the top and least favorite (but none the less very useful) on the bottom:
Over last few years I’ve been suggesting more people to disable Query Cache than to enable it. It can cause contention problems as well as stalls and due to coarse invalidation is not as efficient as it could be. These are however mostly due to neglect Query Cache received over almost 10 years, with very little changes done to the core of original implementation which appeared in MySQL 4.0 Query Cache was designed to work with single core systems and relatively small memory amounts, both of which are things of the past.
However, if you think about Core idea of the MySQL Query Cache – it is great. The transparent cache which does not require any extra handling from an application side and which just makes things faster without adding complexity to the application (as memcached does for example). Query Cache is also one of the few cache implementations which I’d call an …
[Read more]I wrote about MySQL Error Messages before and as you might guess I’m not very happy with quality for error messages it produces. Now I’m revisiting this subject with couple of more annoying examples I ran into during last couple of days.
mysql> drop database test; ERROR 1010 (HY000): Error dropping database (can't rmdir './test/', errno: 17)
First question you should ask is what is error 17 ? Do to this you can run external utility perror:
[pz@test ~]$ perror 17 OS error code 17: File exists
It would be already a lot handy if one would walk through all error messages where error code is used and add the text description right into the code. ERROR 1010 (HY000): Error dropping database (can’t rmdir ‘./test/’, errno: 17 “File exists”) would already be a lot more helpful.
…
[Read more]Combating “data drift”
In my first post in this series, I described materialized views (MVs). An MV is essentially a cached result set at one point in time. The contents of the MV will become incorrect (out of sync) when the underlying data changes. This loss of synchronization is sometimes called drift. This is conceptually similar to a replication slave that is behind. Until it catches up, the view of the data on the slave is “behind” the changes on the master. An important difference is that each MV could have drifted by a different length of time.
A view which has drifted out of sync must be refreshed. Since an MV drifts over time from the “base tables” (those tables on which the view was built) there must be a process to bring them up-to-date. …
[Read more]I mentioned problems with InnoDB flushing in a previous post. Before getting to ideas on a solution, let’s define some terms and take a look into theory.
The two most important parameters for InnoDB performance are innodb_buffer_pool_size and innodb_log_file_size. InnoDB works with data in memory, and all changes to data are performed in memory. In order to survive a crash or system failure, InnoDB is logging changes into InnoDB transaction logs. The size of the InnoDB transaction log defines how many changed blocks we can have in memory for a given period of time. The obvious question is: Why can’t we simply have a gigantic InnoDB transaction log? The answer is that the size of the transaction log affects recovery time after a crash. The bigger the log, the longer the recovery time. …
[Read more]Sometimes the question is put like are you looking for Performance OR Stability, which I believe is a strange way to put it. In real life systems you care both about Performance AND Stability. I would even say Stability is a not the best world here, I would day you care about your minimal performance in most cases.
If system can handle 5000 q/sec for 1 minute and when 20.000
s/sec for the next one, how much I can count on in terms of
capacity planning ? In case this is typical OLTP system I will
have to use 5000 q/sec number as I need my system to always be
able to reach performance requirements. If the system though is
doing batch processing may be I can count on the average which is
12.5K in this case.
The difference between stability and minimal performance is important as I can be quite OK with “unstable” performance if it is performance bursts rather than stalls, for example if my system performs 7000 q/sec and …
[Read more]You may have seen in the last couple of weekly news posts that Baron mentioned we are working on a new adaptive flushing algorithm in InnoDB. In fact, we already have three such algorithms in Percona Server (reflex, estimate, keep_average). Why do we need one more? Okay, first let me start by showing the current problems, and then we will go to solutions.
The basic problem is that, unfortunately, none of the existing flushing implementations (including both MySQL native adaptive flushing and that in Percona Server) can handle it properly. Our last invention, “keep_average”, is doing a very good job on systems based on SSD/Flash storage, but it is not so good for regular slow hard drives.
Let me state the following: If you have a lot of memory (and this is not rare nowadays, for example Cisco UCS C250), your database fits into …
[Read more]In my previous post I introduced materialized view concepts. This post begins with an introduction to change data capture technology and describes some of the ways in which it can be leveraged for your benefit. This is followed by a description of FlexCDC, the change data capture tool included with Flexviews. It continues with an overview of how to install and run FlexCDC, and concludes with a demonstration of the utility.
As a reminder, the first post covered the following topics:
If you know me, then you probably have heard of Flexviews. If not, then it might not be familiar to you. I’m giving a talk on it at the MySQL 2011 CE, and I figured I should blog about it before then. For those unfamiliar, Flexviews enables you to create and maintain incrementally refreshable materialized views.
You might be asking yourself “what is an incrementally refreshable materialized view?”. If so, then keep reading. This is the first in a multi-part series describing Flexviews.
edit:
You can find part 2 of the series here:
http://www.mysqlperformanceblog.com/2011/03/25/using-flexviews-part-two-change-data-capture/
The output of …
It has taken a years to get a proper integration between
operating system kernel, device driver and hardware to get
behavior with caches and IO modes correctly. I remember us having
a lot of troubles with fsync() not flushing hard drive write
cache and so potential hard drives can be lost on power failure.
Happily most of these are resolved now with “real hardware” and
I’m pretty confident running Innodb with both default (fsync
based) or O_DIRECT innodb_flush_method.
Virtualization however adds yet another layer and we need to
question again whenever IO really durable in virtualized
environments. My simple testing shows this may not always be the
case
I’m comparing O_DIRECT and fsync() single page writes to 1MB file using SysBench on Ubuntu, ext4 running on VirtualBox 4.0.4 running on Windows 7 on my desktop computer with pair of 7200 RPM hard drives in RAID1. Because there is no write cache I expect it to …
[Read more]