MariaDB 5.3/5.5 has introduced a new join type “Hash Joins” which is an implementation of a Classic Block-based Hash Join Algorithm. In this post we will see what the Hash Join is, how it works and for what types of queries would it be the right choice. I will show the results of executing benchmarks for different queries and explain the results so that you have a better understanding of when using the Hash Join will be best and when not. Although Hash Joins are available since MariaDB 5.3, but I will be running my benchmarks on the newer MariaDB 5.5.

Overview

Hash Join is a new algorithm introduced in MariaDB 5.3/5.5 that can be used for joining tables that have a equijoin conditions of the form tbl1.col1 = tbl2.col1, etc. As I mentioned above that what is actually implemented is the Classic Hash Join. But its known as Block Nested Loop Hash (BNLH) Join in MariaDB.
The Classic Hash Join Algorithm …

Have you wanted to compress only certain types of columns in a table while leaving other columns uncompressed? While working on a customer case this week I saw an interesting problem where a table had many heavily utilized TEXT fields with some read queries exceeding 500MB (!!), and stored in a 100GB table. In this case we were not allowed to make any query or application logic changes so we chose to implement the Barracuda file format and utilize compressed rows as this appealed to me for this mostly-read application. One quick way you can see if your rows will benefit from compression would be to read Peter Zaitsev’s blog post and execute:

SELECT AVG(LENGTH((`colTextField`)) FROM `t1` WHERE `id` < 1000

compare this to:

SELECT AVG(LENGTH(COMPRESS(`colTextField`))) FROM `t1` WHERE `id` < …

If you follow the general advices to create secure password the following ones seem to be secure, right?

The answer to the question is, “it depends on how you use them”

Notice that these passwords all contain multiple exclamation points and ampersands which are normally special characters for your shell. The people tend to copy and paste them directly to the terminal but that can lead to some non-predictable behavior and therefore cause big problems depending on the character combination.

Let’s execute the previous examples:

Login to mysql:

root@debian:~# mysql -uroot -ps11P$||!sh&2
mysql -uroot -ps11P$||shutdown -r now&2
[1] 1758
-bash: 2: command not found
root@debian:~# ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using …

Whenever I learn about a new technology, I typically want to know the major administrative touch points more than I want to know exhaustive detail about every configuration option.  Give me the gist, show me enough to get started, and give me a link to the manual.

XtraDB cluster (and Galera, the technology on which it is based) has been attracting a lot of interest in the community and we want to start presenting information about both what we know is essential, but also what we suspect will become essential as more and more production experience is logged with these important new technologies.

As such, I put together a baseline talk for getting you into a working knowledge of XtraDB …

I used to think one should never look at max_binlog_size, however last year I had a couple of interesting cases which showed that sometimes it may be very important variable to tune properly. I meant to write about it earlier but never really had a chance to do it. I have it now!

One of our customers was complaining that the database would lock up at random times and then it would go back to normal in just a few seconds. This was MySQL 5.0 running MyISAM/InnoDB mix, not heavily loaded. We used pt-stalk (at that time it was aspersa stalk) trying to figure out what is happening, however all we found was a spike in writes, many queries piled up and looking at the system process list it was quite obvious that page flush daemon was acting out. I/O Pattern was rather awkward – here is an output from …

I have been working for a customer benchmarking insert performance on Amazon EC2, and I have some interesting results that I wanted to share. I used a nice and effective tool iiBench which has been developed by Tokutek. Though the “1 billion row insert challenge” for which this tool was originally built is long over, but still the tool serves well for benchmark purposes.

OK, let’s start off with the configuration details.

Configuration

First of all let me describe the EC2 instance type that I used.

EC2 Configuration

I chose m2.4xlarge instance as that’s the instance type with highest memory available, and memory is what really really matters.

High-Memory Quadruple Extra Large Instance
68.4 GB of memory
26 EC2 Compute Units (8 virtual cores with 3.25 EC2 Compute …

The recording and slides for my webinar on zero-downtime schema changes with MySQL are available now. Don’t miss Vadim’s webinar tomorrow!

One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times. Investigating them we find out what the cause is some batch jobs, reports and other non response time critical activities are overloading the system causing user experience to degrade.

The first thing you need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though fixing these does help in many cases. Whatever powerful and well tuned system you have if you put too heavy of concurrent load on it the response times will increase and user experience will suffer.

So what you can do to prevent this problem from happening ? The answer is easy. Throttle the side load so it does not consume too much system resources. Here are some specific techniques to use.

Do push concurrency too high Many developers will test script with …

The recording of yesterday’s webinar, as well as a PDF of my slide deck, is now available. You can watch the recording and get the slides here.

I got a lot of questions, and did not have enough time to answer all of them, so let me try to answer some of the remaining questions here.

Q: Isn’t it possible to take all the locks required in the beginning to prevent dead locks? So you would get a lock wait instead of a dead lock. No, that would cause blocking that would defeat the purpose of the tool.

Q: When you say bail out, what does that mean? Does the tool clean up (i.e., remove table / triggers?) About cleanup.. What about manual aborting a run. will that cleanup ? Q: dose the tool exit gracefully while interupted? The tool doesn’t clean up the temporary table or triggers. It is designed …

Working on customer case today I ran into interesting problem – query joining about 20 tables (thank you ORM by joining all tables connected with foreign keys just in case) which would take 5 seconds even though in the read less than 1000 rows and doing it completely in memory. The plan optimizer picked was very good one, yet you could notice EXPLAIN itself was taking same 5 seconds, which points to problem with optimizer performance. Note though if you have subqueries these might need to be executed during EXPLAIN phase yet making it unusable to check the optimizer performance.

Solution for this problem was to use set optimizer_search_depth=0, rarely used option which as per manual will chose best value automatically. Making this change I could bring optimization, and full query execution time to less than 50ms. …