This is the third blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is targeted at the join related optimizations introduced in the optimizer. These optimizations are available in both MySQL 5.6 and MariaDB 5.5, and MariaDB 5.5

The post Join Optimizations in MySQL 5.6 and MariaDB 5.5 appeared first on ovais.tariq.

Do the operating systems kill your MySQL instances from time to time? Are some database servers swapping constantly? These are relatively common problems. Why? How to prevent them?

Memory allocation

When a running program needs some additional memory, it can typically allocate it dynamically with malloc() function. It finds an unused continuous block that is at least as large as the requested size, reserves as much as it needs, and returns a pointer to that space. No initialization of the memory contents is performed at the time. When malloc() returns NULL instead of a valid address, it is an information to the calling program that there wasn’t enough memory available and the call has failed to allocate anything. In such cases applications typically take appropriate actions to notify users about the problem and terminate some of their activity or completely shut down.

In Linux it can be a little bit …

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 …

Lately I’ve received a number of SHOW ENGINE INNODB STATUS outputs with embedded “\n” characters appearing as text, but not true line breaks.

Of course this makes reading a long, INNODB STATUS nearly impossible.

With Linux, you can more easily fix this using sed or awk (or if you have those on Windows w/ gnu or cygwin or the like).

I didn’t have either handy, nor a fancy editor (just using WordPad), but I did have a hex editor handy (necessary for such cases as this).

In the hex editor (I use Freeware Hex Editor XVI32 for Windows), fixing this took about 2 seconds.

I just replaced:

5C6E <-- which is the text "\n"

with:

0D0A <-- which is the true carriage return/newline

I realize …

The South East Linux Fest is next week in Charlotte, North Carolina and will feature a MySQL day on Friday with two FULL tracks.

MySQL Tracks for SELF

This will be followed on Sunday by the Open Database Unconference. And did I mention this is all free? Plus there will be other tracks on Linux, FOSS, and many vendors present.  This is an amazing show and you really need to be there.

This is a great opportunity to hear from some of the best and the brightest presenters in the MySQL Community. We managed to get a few MySQL Engineers to head East after MySQL Innovation Day to present.

And if you know anyone who needs a real …

SSH tunnels provide a very effective means to access remote services and applications. Not only does it provide encryption of data between hosts, but it allows you to route connections between a sequence of servers, thus chaining connections. A common use of this method is to provide encrypted connections to MySQL servers so that user accounts can be limited to only “localhost” privileges, yet accessed from remote workstations without having to run MySQL+SSL.

The concept is simple, for example let’s say you have three servers: localhost (your workstation in America), a server in Europe, and a server in Japan. You want to access Apache running on port 80 on the Japan server but because of firewall restrictions you cannot access port 80 remotely, and to make things more difficult the Japan server only allows SSH connections from the Europe server’s IP. We can solve this by creating a SSH tunnel that forwards localhost port 8080 …

Today, I think we witnessed a small sign for a big revolution...

http://www.pcworld.com/businesscenter/article/256383/dell_reaches_for_the_cloud_with_new_prototype_arm_server.html
"Dell announced a prototype low-power server with ARM processors, following a growing demand by Web companies for custom-built servers that can scale performance while reducing financial overhead on data centers"In short, ARM (see Wikipedia definition here) is an architecture standard for processors. ARM processors are slower compared to good old x86 processors from Intel and AMD, but have power-efficiency, density and price attributes that intrigue customers, especially in our days of green data centers where carbon emissions is …

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` < …

TokuDB® is a proven solution that scales MySQL® and MariaDB® from GBs to TBs with unmatched insert and query speed, compression, and online schema flexibility.

Tokutek’s recently launched TokuDB v6 delivers all of these features and more, with the introduction of high performance replication for MySQL and MariaDB. TokuDB v6 eliminates the common and persistent problem of “slave lag” in which a replication server is unable to keep up with the query load borne by the master server. TokuDB v6 solves this by offering high ingestion rates at the slave.

Time: 2PM EDT / 11AM PDT

REGISTER TODAY

TokuDB v6 delivers

• Replication Performance — High ingestion rates at the slave deliver read scalability and improved disaster …

I have to think that the “Client does not support authentication protocol” error message may be the single most common error ever encountered for MySQL. While it’s not exactly coming back in 5.6, those users who have implemented workarounds in support of older client libraries will find they need to add an additional step if they upgrade to 5.6. This is because in 5.6.5, a change was made to default the secure_auth option to ON. Here’s what the manual has to say about this:

This option causes the server to block connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format. Use it to prevent all use of passwords employing the old format (and hence insecure communication over the network). Before MySQL 5.6.5, this option is disabled by default. As of MySQL 5.6.5, it is enabled by …