The latest from the MySQL community

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 the blog post but seems to be trapped in the spam filter, so I’m …

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

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) 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

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.
ScenarioCurrently 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.
Understanding CSV EngineThe CSV engine, as far as I can tell, was and example …

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 Infobright. I’ve recently looked at another third-party storage engine and they did exactly the same thing. It’s just that the docs I saw weren’t public as …

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 footprint.

What is even more …

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=

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.

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