Archive for the ‘MariaDB’ Category

Dynamic Columns Tutorial – Part 1: Introduction

Friday, October 3rd, 2014

Dynamic Columns Tutorial – Part 1: Introduction by Max Mether.

From the post:

For certain situations, the static structure of tables in a relational database can be very limited. Each column is statically defined, has a pre-defined type and you can only enter a value of that type into the column.You can be creative and list multiple values in one column, but then those values are not generally easily accessed and manipulated with other functions. You have to use an API or contortions of a function like SUBSTRING() to pull out a value you want. Even then, you have to know what is contained in the column to be able to manipulate it properly. These methods can require too much manual intervention to assess and access the data contained in the column.

If you want to add columns as the information stored in your tables grows and your needs change you need to do fairly expensive ALTER TABLE operations. These have traditionally been very expensive in MySQL and MariaDB although the performance is a bit better starting with MariaDB 10.0 and MySQL 5.6.

The other option for having a flexible structure is to use something like Anchor Modeling . This allows you to have a very flexible schema as adding an attribute basically just means adding a table. The problem with this approach is that you’ll end up with a lot of tables which means a lot of joins when looking for results which can easily become un-manageable, or at least hard to manage.

This is where dynamic columns steps into the picture. A good solution to the static structure problem is to use dynamic columns provided in MariaDB. It allows flexibility within a defined structure, within a column. A Dynamic Column is defined as a BLOB on the DDL level. However, within the BLOB column, you may set arbitrarily and dynamically defined attributes and values–for a maximum of 64k.

Dynamic columns are not in isolation: The usual functions will work fine with the values contained within them. And they can be used as join points for joining to other table as you would normally. This allows you to retain the power of Relational SQL while still mainting a flexibility with regards to your attributes for specific tables where it makes sense.

Probably channeling topic maps when I observe that dynamic columns are associating multiple properties with a subject. 😉

Very interested in seeing how joins are performed using dynamic columns, but that awaits in a future post.

I first saw this in a tweet by MariaDB

Announcing TokuDB v6.6: Performance Improvements

Wednesday, January 9th, 2013

Announcing TokuDB v6.6: Performance Improvements

From the post:

We are excited to announce TokuDB® v6.6, the latest version of Tokutek’s flagship storage engine for MySQL and MariaDB.

This version offers three types of performance improvements: in-memory, multi-client and fast updates.

Although TokuDB is optimized for large tables, which are larger than memory, many workloads consist of a mix of large and small tables. TokuDB v6.6 offers improvements on in-memory performance, with a more than 100% improvement on Sysbench at many concurrency levels and more than 200% improvement on TPC-C at many concurrency levels. Details to follow.

We have also made improvements in multi-threaded performance. For example, single threaded trickle loads have always been fast in TokuDB. But now multi-threaded trickle loads are even faster. An iibench run with four writers shows an increase from ~18K insertions/sec to ~28K insertions/sec. With a writer and reader running concurrently, we achieve ~13K insertions/sec.

Leif Walsh, one of our engineers, will be posting some details of how this particular improvement was achieved. So stay tuned for this and posts comparing our concurrent iibench performance with InnoDB’s.

A bit late for Christmas but performance improvements on top of already impressive performance are always welcome!

Looking forward to hearing more of the details!

Forbes: “Tokutek Makes Big Data Dance”

Saturday, October 6th, 2012

Forbes: “Tokutek Makes Big Data Dance” by Lawrence Schwartz.

From the post:

Recently, our CEO, John Partridge had a chance to talk about novel database technologies for “Big Data” with Peter Cohan of Forbes.

According to the article, “Fractal Tree indexing is helping organizations analyze big data more efficiently due to its ability to improve database efficiency thanks to faster ‘database insertion speed, quicker input/output performance, operational agility, and data compression.’” As a start-up based on “the first algorithm-based breakthrough in the database world in 40 years,” Toktuetek is following in the footsteps of firms such as Google and RSA, which also relied on novel algortithm advances as core to their technology.

To read the full article, and to see how Tokutek is helping companies tackle big data, see here.

I would ignore Peter Cohan’s mistakes about the nature of credit card processing. You don’t wait for the “ok” on your account balance.

Remember What if all transactions required strict global consistency? by Matthew Aslett of the 451 Group? Eventual consistency works right now.

I would have picked “hot schema” changes as a feature to highlight but that might not play as well with a business audience.

Webinar: Introduction to TokuDB v6.5 (Oct. 10, 2012)

Saturday, October 6th, 2012

Webinar: Introduction to TokuDB v6.5

From the post:

TokuDB® is a proven solution that scales MySQL® and MariaDB® from GBs to TBs with unmatched insert and query speed, compression, replication performance and online schema flexibility. Tokutek’s recently launched TokuDB v6.5 delivers all of these features and more, not just for HDDs, but also for flash memory.

Date: October 10th
Time: 2 PM EST / 11 AM PST

TokuDB v6.5:

  • Stores 10x More Data – TokuDB delivers 10x compression without any performance degradation. Users can therefore take advantage of much greater amounts of available space without paying more for additional storage.
  • Delivers High Insertion Speed – TokuDB Fractal Tree® indexes continue to change the game with huge insertion rates and greater scalability. Our latest release delivers an order of magnitude faster insertion performance than the competition, ideal for applications that must simultaneously query and update large volumes of rapidly arriving data (e.g., clickstream analytics).
  • Allows Hot Schema Changes — Hot column addition/deletion/rename/resize provides the ability to add/drop/change a column to a database without taking the database offline, enabling database administrators to redefine or add new fields with no downtime.
  • Extends Wear Life for Flash– TokuDB’s proprietary Fractal Tree indexing writes fewer, larger blocks which reduces overall wear, and more efficiently utilizes the FTL (Flash Translation Layer). This extends the life of flash memory by an order of magnitude for many applications.

This webinar covers TokuDB features, latest performance results, and typical use cases.

You have seen the posts about fractal indexing! Now see the demos!

Announcing TokuDB v6.5: Optimized for Flash [Disambiguation]

Tuesday, September 25th, 2012

Announcing TokuDB v6.5: Optimized for Flash

Semantic confusion follows me around. Like the harpies that tormented Phineus. Well, maybe not quite that bad. 😉

But I see in the news feed that TukoDB v6.5 has been optimized for Flash.

First thought: Why? Who would want a database optimized for Flash?

But they did not mean Flash, or one of the other seventy-five (75) meanings of Flash, but Flash.

I’m glad we had this conversation and cleared that up!

The “Flash” in this case refers to “flash memory.” And so this is an exciting announcement:

We are excited to announce TokuDB® v6.5, the latest version of Tokutek’s flagship storage engine for MySQL and MariaDB.

This version offers optimization for Flash as well as more hot schema change operations for improved agility.

We’ll be posting more details about the new features and performance, so here’s an overview of what’s in store.

TokuDB v6.5 continues the great Toku-tradition of fast insertions. On flash drives, we show an order-of-magnitude (9x) faster insertion rate than InnoDB. TokuDB’s standard compression works just as well on flash and helps you get the most out of your storage system. And TokuDB reduces wear on solid-state drives by more than an order of magnitude. The full technical details will be subject of a future blog post. In summary though, when TokuDB writes to disk, it updates many rows, whereas InnoDB may write a leaf to disk with a single modified row, in some circumstances. More changes per write means fewer writes, which makes the flash drive wear out much more slowly.

More Hot Schema Changes
TokuDB already has hot column addition, deletion and renaming. In this release we add hot column expansion, so you can change the size of the integers in a column or the number of characters in a field. These operations incurs no down time and the changes are immediately available on the table. In this release, we have also extended hot schema changes to partitioned tables.

Every disambiguation page at, in every language, is testimony to a small part of the need for semantic disambiguation.

Did you know that as of today, there are 218,765 disambiguation pages in Wikipedia? Disambiguation Pages.

How many disambiguations could you use for an index at work, that don’t appear in Wikipedia?

You can stop at ten (10). Point made.