Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 34 Next 4 Older Entries

Displaying posts with tag: Storage Engines (reset)

How MariaDB makes Stored Procedures usable
+4 Vote Up -0Vote Down

I already wrote how MariaDB makes the debug of stored procedures much easier via the SQL Error Log. But I recently found out that MariaDB provides a usable workaround for some big limitations of their procedural SQL.

First, SELECT is not the only SQL statement which returns a resultset. Other examples are DELETE RETURNING, CALL, SHOW, EXPLAIN and administrative commands like ANALYZE TABLE or CHECK TABLE. But these commands cannot be used in place of SELECT in the following contexts:

  • Subqueries, derived tables, JOINs,

  [Read more...]
MariaDB/MySQL: Making ENGINE clause mandatory
+0 Vote Up -1Vote Down

I got this idea from a Valerii Kravchuk’s MySQL bug report:


In theory, I completely agree that MySQL and forks should not allow us to set a default storage engine which cannot be used to create a table. You can see the same with MariaDB’s SEQUENCE. The MySQL & forks philosophy seems to be: ignore your mistakes, so you can repeat them forever. Which can turn a mistype into a major data loss.

Unless you only use InnoDB and your magic powers tell you that this will never change, the ENGINE clause should be mandatory in your MySQL installation. Since there is no clean way to make it mandatory, setting a “weird” storage engine as default seems to be a decent workaround. I don’t like

  [Read more...]
MariaDB storage engines
+0 Vote Up -0Vote Down

This is a list of MariaDB storage engines that are not distributed with MySQL. I think that most of them will work with MySQL, but not all – at least CassandraSE doesn’t.

Engine Description Introduced XtraDB A fully-compatible fork of InnoDB, mantained by Percona Big Bang Aria A crash-safe MyISAM, also used for internal temptables Big Bang TokuDB A transactional engine with innovative buffers and high compression, by TokuTek 10.0   [Read more...]
Understanding Tokutek Fractal Tree Indexes
+1 Vote Up -0Vote Down

Download PDF Presentation

Thanks to Tim Callaghan for speaking Tuesday night at the Effective MySQL New York meetup on Fractal Tree Indexes : Theory and Practice (MySQL and MongoDB). There was a good turnout and a full room to learn how the TokuDB storage engine from Tokutek is changing how to handle big data in MySQL.

Also interesting is how the same technology has been applied for use in MongoDB including giving MongoDB transactions; a big change for NoSQL.

Related News: Tokutek Meets Big Data Demand With Open Source TokuDB

Plugins & Storage Engines Summit for MySQL/MariaDB
+1 Vote Up -0Vote Down

As is tradition after the O’Reilly MySQL Conference & Expo, there tends to be a storage engine summit right afterwards. This year it was expanded to also include plugins. I must graciously thank Facebook for hosting us at their campus, and giving us a rather healthy lunch, plus fueling us with all those drinks, caffeine and snacks that we needed to keep us going. While standing in the doorway, Mark (Callaghan) pointed to us that a certain other Mark (Zuckerberg) was walking into the campus, just like the rest of us.

The very raw notes are up on the Knowledgebase - Plugins & Storage Engines Summit for MySQL/MariaDB/Drizzle 2011. We definitely did not discuss anything Drizzle related,

  [Read more...]
MySQL 5.1.46 With InnoDB Plugin Kicks Butt
+2 Vote Up -1Vote Down

We were discussing the recommendations we issue each quarter around MySQL and the question of using InnoDB plugin came up. We usually follow Planet MySQL closely, so we read what the blogs had to say and it was all good, but we decided to provide our users some data of our own. We used our own sysbench tests on to get the information we needed.

A Word About Benchmarks

I don't trust most of the benchmarks that are published online because they really apply to the use case of whomever is writing the article. They are usually many factors that can influence them and I find it difficult to apply them as-is to our environment.

I do trust the benchmarks published online as a reference on how to create and run our own benchmarks. So this article

  [Read more...]
The Doom of XtraDB and Percona Server?
+7 Vote Up -5Vote Down

In The Doom of Multiple Storage Engines, Peter talks about how the storage engine concept of MySQL is usually spoken of in positive terms, but there are many negatives.

I have a hard time trying to figure out the deeper meaning behind Peter’s post, given that Percona writes a storage engine for MySQL, XtraDB. Does this mean that Percona will stop developing XtraDB? Does this mean that the Percona Server will diverge farther and farther away from MySQL so that they’re not compatible any more and migrating from MySQL to Percona Server is very difficult?

Or maybe it’s just that Peter is saying one thing and doing the opposite; which just seems wrong because that would be blatant hypocrisy on Percona’s part.

(This idea was a comment on

  [Read more...]
DBJ – Exotic Storage Engines for MySQL
+0 Vote Up -0Vote Down

In our March DBJ article we talked about some of the storage engines to choose from with MySQL.  With it’s plugin storage engine architecture, you have a range of options.  In our April article we continue to discuss a further selection of storage engines, and what features they offer to the DBA and database architect.

Database Journal – Exotic Storage Engines

Surveying MySQL’s Popular Storage Engines
+0 Vote Up -4Vote Down

In this month’s Database Journal piece we look at the spectrum of MySQL storage engines available, and examine what some of their strengths and weaknesses are.

View the article here: Survey of MySQL Storage Engines

Configuring the InnoDB Plugin (1.0.6) in MySQL 5.1.43
+2 Vote Up -0Vote Down
Configuring the InnoDB Plugin (1.0.6) is just as easy in the MySQL 5.1.43 release.  There are a few subtle changes in the new release.  Set the following parameters to configure the InnoDB plugin in 5.1.43.  A few notes: Set the PLUGIN_DIR parameter to the location of the plugin libraries. Verify all the libraries listed below are in the PLUGIN_DIR directory. The PLUGIN_LOAD parameter needs to
About CSV Tables
+1 Vote Up -0Vote Down
As most of MySQL users, I have often ignored what I'd like to call the minor storage engines. MYISAM, InnoDB and NDB (aka MySQL Cluster) are well covered in several articles; but what about engines like CSV or ARCHIVE? As part of some internal projects, we have been playing around with these 2 with some interesting results. On this article I'll concentrate on CSV.


Currently we have a few servers that are storing historical data that will eventually be migrated into Oracle. Two things need to happen until we can finally decommission them: 1) export the data to CSV so it can be imported in bulk into Oracle and 2) keep the data online so it can be queried as needed until the migration is finalized. I thought it would be interesting if we could solve both issues simultaneously and decided to try the CSV engine. Here's a description of the process.
  [Read more...]
What data types does your innovative storage engine NOT support?
+3 Vote Up -1Vote Down

I’ve been investigating a few different storage engines for MySQL lately, and something I’ve noticed is that they all list what they support, but they generally don’t say what they don’t support. For example, Infobright’s documentation shows a list of every data type supported. What’s missing? Hmm, I don’t see BLOB, BIT, ENUM, SET… it’s kind of hard to tell. The same thing is true of the list of functions that are optimized inside Infobright’s own code instead of at the server layer. I can see what’s optimized, but I can’t see whether FUNC_WHATEVER() is optimized without scanning the page — and there’s no list of un-optimized functions.

I don’t mean to pick on

  [Read more...]
Calculating your database size
+2 Vote Up -0Vote Down

I generally use the following MySQL INFORMATION_SCHEMA (I_S) query to Calculate Your MySQL Database Size. This query and most others that access the MySQL INFORMATION_SCHEMA can be very slow to execute because they are not real tables and are not governed by physical data, memory buffers and indexes for example but rather internal MySQL data structures.

Mark Leith indicates in his post on innodb_stats_on_metadata that Innodb performs 8 random(ish) dives in to the index, when anybody accesses any of SHOW TABLE STATUS, SHOW INDEX, INFORMATION_SCHEMA.TABLES,INFORMATION_SCHEMA.STATISTICS for InnoDB tables. This can have an effect on performance, especially with a large number of Innodb tables, and a poor ratio of innodb_buffer_pool_size to disk data+index

  [Read more...]
Understanding Innodb Transaction Isolation
+6 Vote Up -0Vote Down

The MySQL Innodb storage engine provides ACID compliance, the ‘I’ being isolation. There are four states defined in MySQL with the tx_isolation system variable, READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ and SERIALIZABLE.

Generally MySQL installations do not modify the default value of tx_isolation = REPEATABLE-READ, however I have seen with a number of clients the default value has been changed to READ-COMMITTED. When I attempt to identify the reason why, I have always received the same reason. Oracle uses a default transaction isolation of READ-COMMITTED. See

  [Read more...]
Engine agnostic MySQL test cases
+6 Vote Up -0Vote Down

Mark writes Now we all need the storage-engine independent test suite. I could not agree more. I have made comments about this probably as early as 4 years ago, and both before and while working for MySQL Inc.

There is however a way to do it with the current mysql-test syntax. While not ideal, it does actually work.
It took me like an hour to dig though old, old backup code, but I found it.

The Test Case:

$ cat t/engine_agnostic.test
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
--replace_result $ENGINE ENGINE

The Test Result:

cat r/engine_agnostic.result

  [Read more...]
How do I create a simple MySQL database
+1 Vote Up -0Vote Down

I was asked this question recently “I am wanting to create a simple MySQL database consisting of 5 tables”?

While it’s easy to tell people to RTFM, the question does warrant an answer for the MySQL beginner to provide a more specific guidance as to where to start, and what to do. As a expert in MySQL it’s easy to forget how you would describe what to do. Here are my tips to getting started.

Step 1. Download the MySQL 5.1 software for your platform (e.g. Linux, Windows, Mac etc) from MySQL 5.1 Downloads. There are many different versions of MySQL, MySQL 5.1 is the current production version.

Step 2. You will need to install the MySQL software. The MySQL reference manual is the place to go, Chapter 2

  [Read more...]
Configuring INFORMATION_SCHEMA Plugin Tables for InnoDB Plugin
+0 Vote Up -2Vote Down
There are seven new INFORMATION_SCHEMA tables (plugins) available with the InnoDB plugin.  They need to be loaded to be used. New InnoDB Plugins contain information on: Compressed InnoDB tables Compressed InnoDB buffer pool Current InnoDB transactions Transaction Locks Blocking transactions These INFORMATION_SCHEMA plugins are available by setting the plugin-load parmeter: plugin-load=
Configuring the InnoDB Plugin (1.0.4) in MySQL 5.1.38
+0 Vote Up -1Vote Down
The InnoDB Plugin (1.0.4) is  pretty straight forward configuration. These instructions are for MySQL 5.1.38.  There is a new release of InnoDB Plugin with the 5.1.41 release. With MySQL you can use the default version of InnoDB or the new Plugin but not both.  If using a non-default configuration make sure and set the plugin_dir variable to point to the ha_innodb_plugin library for your system.
MySQL 5.1.38 Releases with InnoDB Plugin
+2 Vote Up -3Vote Down
The MySQL 5.1.38 release is available today with the InnoDB Plugin (1.0.4) included in the software distribution.  The InnoDB Plugin offers some key features: Improved performance and scalability. Important management features that will be very helpful to MySQL DBAs.     I recommend you look into the new features available with the InnoDB Plugin. The InnoDB Plugin (1.0.4) is at end of beta
451 CAOS Links 2009.06.02
+0 Vote Up -0Vote Down

Cloudera lands funding. SourceForge acquires Ohloh. Novell reports Linux growth. And more.

Follow 451 CAOS Links live @caostheory

Cloudera shows signs of progress

GigaOM reported that Cloudera raised $6m Series B funding from Accel and Greylock and is now looking beyond web applications to wider enterprise adoption of Hadoop. Cloudera also announced its first certification program for Hadoop.

Open source goes mainstream in the UK
There have been signs of change recently with regards to open source adoption in the UK, which has traditionally lagged behind the rest of Europe and the US. CBR Magazine provided an analysis of open source in the UK

  [Read more...]
Tracking the Storage Engine Race
+0 Vote Up -0Vote Down
There are a number of new and enhanced storage engines that promise increased scalability, performance and important new online features. All this competition with storage engines is going to create a win for the MySQL community. This competition is what open source is all about. If you do not continue to innovate and improve you cannot expect to be a leader in the open source world. The nice
InnoDB Plugin Has Some Very Cool features
+0 Vote Up -0Vote Down
Reducing Administration Costs and Down TimeDBAs are always going to be challenged with growth, scalability and performance as long as databases get bigger and more users access them. Reducing administration and down time are always key issues in our 24 x 7 environments. The new InnoDB Plugin has some very important features that DBAs are going to want to take a serious look at. So I thought I
Understanding the various MySQL Products & Variants
+0 Vote Up -0Vote Down

The MySQL marketplace today is far more complex then simply choosing between a particular version of MySQL that Sun/MySQL (http://mysql.com) produces.
The MySQL server product in general is released under the GNU General Public License (GPL) v2, however you should carefully review the MySQL Legal Policies (http://www.mysql.com/about/legal/) as a number of exceptions and different license agreements operate for companion tools such as MySQL Cluster, MySQL client libraries and documentation for example.

Looking into the MySQL ecosystem for products, I’ve produced the following categories:

  • Sun/MySQL Official Products
    • MySQL Versions
  • MySQL Variants
    • Community
    • Enterprise
  • MySQL Plugins

  [Read more...]
Beginner CSV Engine issues
+0 Vote Up -0Vote Down

I’ve just started using the CSV engine for a practical application and I’ve come across a few subtle and unexpected issues/limitations.

First, you can’t create any columns in your CSV table nullable.

mysql> create table t1(i INT) ENGINE=CSV;
ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

RTFM shows this was introduced in 5.1.23. See CSV Limitations

The second and more annoying was creating a CSV table, inserting a sample row (just to check the syntax), then replacing the file ([datadir]/[schema]/[table].CSV) with the same format, but with additional generated rows. This was to no avail when attempting to SELECT from the table.

The solution was to do a REPAIR TABLE [table] in order to see the newly

  [Read more...]
Infobright Community Edition(ICE) - It’s Free
+0 Vote Up -0Vote Down

The March NY MySQL Meetup featured a presentation from Infobright, a data warehousing solution built on the MySQL Product.

With a pitch of “Simplicity, Scalability and low TCO” I became more impressed with the capability to delivery on these as the presentation proceeded. Here are some highlights.

  • The company and product has been around for a few years. Infobright started as a compression engine to sit beside Teradata, providing a significant cost saving to clients, and allowing a two way data transfer between Teradata.
  • In September 2008, a open source community edition was released, called ICE. (Which I didn’t know)
  • The technology is based on a Rough Set theory, a
  [Read more...]
FederatedX Moved to Launchpad
+0 Vote Up -0Vote Down
Just a quick post that I've moved The FederatedX Storage Engine for MySQL to Launchpad from my Mercurial repository. I'm trying like anything to get a spare moment to work on it-- writing a book takes every spare moment I have. I have made it group accessible and am working with Antony Curtis (who with Arjen encouraged me to get this on LP) on it. So, what I need to do is start a 5.1 compile (it has a problem with latest 5.1) and then give it a test run to see what the problem is. I want to keep this project moving!

The Launchpad page for it is:

innodb_file_per_table Revisited
+0 Vote Up -0Vote Down
In a previous post, I was trying to figure out the most optimal way to switch from two large innodb table space files to using innodb_file_per_table to take advantage of some of the benefits of this setting. I had one part of it solved, which was to stop MySQL, add innodb_file_per_table to the my.cnf, then restart, perform a "no-op" alter of "ALTER TABLE t1 ENGINE=InnoDB" which would cause the table to be re-created an it's own .ibd file. The remaining problem was how to be able to resize the huge table space files after converting all the tables to a smaller size (in my case from 10GB to 10MB).

Someone suggested a better way:

1. Alter all innodb tables to MyISAM
2. Stop the server
3. Add innodb_file_per_table to my.cnf
4. Change innodb_data_file_path to new settings (10MB tablespaces) in my.cnf
5. Move all innodb files (logs, data) to a backup directory

  [Read more...]
InnoDB file per table dilemma
+0 Vote Up -0Vote Down
I have recently decided I would like to take advantage of the benefits of InnoDB file-per-table. Namely, that OPTIMIZE TABLE benefits-- defragmentation (Grazr does a lot of writes, both INSERT and DELETE), .ibd files being able to shrink if optimized, etc.

So, the next question is how to convert a ton of data to this setup. What I have tried is this:

1. Add innodb_file_per_table to my.cnf
2. Restart MySQL
3. Alter all InnoDB tables engine=InnoDB

This works great... except, my two 10GB tablespace files remain. I would like to make them smaller, but there is no way of doing this.

Also, there is no trick where you back up the newly created .ibd tables after the alter, move the large table space files to a backup dir, restart and pray that the new smaller tablespace files magically work with your .ibd files. That does *not* work ;)

So, dump all

  [Read more...]
The new kid on the block - Drizzle
+0 Vote Up -0Vote Down

Before today, Drizzle was known as a light form of rain found in Seattle (among other places). Not any more. If you have not read the news already today, Drizzle, Clouds, “What If?” is the new kid on the RDBMS bock.

Faster, leaner and designed with the original goals of ease-of-use, reliability and performance, Drizzle will make an impact in those organizations that are seeking a viable database storage solution for large scalable applications. The key to Drizzle is several fold. First, the crud has been removed. The first part of Drizzle development is to remove bloat or non functioning software from the MySQL tree. In fact if you monitor the commits, it reads like, this has been removed, these files have been deleted, this code has been refactored, this new library has been introduced. Design

  [Read more...]
The pursuit of a synchronous world
+0 Vote Up -0Vote Down

Well at least your MySQL database world.

As Paul eluded to, PrimeBase Technologies has a project to provide synchronous replication for MySQL in a High Availability environment. It is more then an idea, there is a plan.

Is it possible?
What are the use cases?
How can you use it?
Would you use it?

Some input to date. We need these questions and more, and we seeking more input for discussion.

Unfortunately the opportunity to hear any input during a presentation is left to the last day of the conference, so we have created a BoF session on Tuesday night for a round table discussion if necessary. We encourage people to bring specific cases and situations for feedback, the reasons why MySQL Replication, MySQL Cluster, DRBD/HeatBeat or any other solution does not satisfy your needs, and

  [Read more...]
Showing entries 1 to 30 of 34 Next 4 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.