This post continues Getting rid of huge ibdata file, no dump required, part I, where I describe way of converting your single-tablespace InnoDB database into a file-per-table one, without the pain of exporting and importing everything at once.

In previous part we put aside the issue of foreign keys. We address this issue now.

What if my InnoDB tables have foreign keys?

MyISAM does not support them, so you can't just ALTER an InnoDB table to MyISAM and back into InnoDB, and expect everything to work.

Alas, this calls for additional steps (i.e. additional ALTER commands). However, these still fall well under the concept of "do it one table at a time, then take time to recover your breath

  [Read more...]

As I mentioned here, there is a slight change for enabling the [original] InnoDB Plugin in MariaDB 5.5 (as compared to how you would enable it in 5.1).

Remember, in MariaDB 5.5, if you do not “enable” (i.e., add anything to the config file to do so) the InnoDB Plugin in MariaDB 5.5, you’ll end up with XtraDB+ for your InnoDb plugin. However, if you do “enable” the InnoDB plugin, then you end up with the original InnoDB plugin provided by Oracle/InnoDB.

The change is that the plugin file (.dll for Windows, .so file for Linux) which was previously named “ha_innodb_plugin.dll” is now just “ha_innodb.dll”.

Thus, if you previously enabled the plugin with (would have been in a 5.1 instance):

[mysqld]
ignore_builtin_innodb

  [Read more...]

I built MariaDB 5.5.24 on Windows yesterday from source, so I just wanted to share my steps.

Here is the short version:

bzr branch lp:maria/5.5 maria-5.5
cd maria-5.5
mkdir bld
cd bld
cmake .. -G "Visual Studio 10"
cmake --build . --config relwithdebinfo --target package

Done! Nice, neat zip file is created right there.

Here is the longer version with outputs for those interested:

C:\mariadb-5.5>bzr branch lp:maria/5.5 maria-5.5
Connected (version 2.0, client Twisted)
Authentication (publickey) successful!
Secsh channel 1 opened.
Branched 3418 revision(s).

C:\mariadb-5.5>cd maria-5.5

C:\mariadb-5.5\maria-5.5>mkdir bld

C:\mariadb-5.5\maria-5.5>cd bld

C:\mariadb-5.5\maria-5.5\bld>cmake .. -G "Visual Studio 10"
-- Check for working C compiler using: Visual Studio 10
-- Check for working C compiler using: Visual Studio 10 -- works
-- Detecting C compiler

  [Read more...]

You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump.

To quickly reiterate, you can only delete the ibdata1 file when no InnoDB tables exist. Delete this file with an existing InnoDB table, even a table in its own tablespace, and nothing ever works anymore.

The problem with the dump-based solution

The impact of doing a logical dump is often overwhelming. Well, the dump may be tolerable, but the restore is

  [Read more...]

 

You could say: what could be the reason for having really big number of tables? Just design the application properly! It’s not always that easy. And this post isn’t really about arguing whether having many tables is good or not, it’s about what happens in terms of memory usage if you already reached that point.

Btw what do I mean by *many*? From my experience it’s tens of thousends or even millions rather than hundreds.

The inspiration for me to write this post was strong desire to try out the latest declared improvements in that area announced to be done in MySQL 5.6. _1

What I did was a very simple

  [Read more...]

Being a data recovery specialist and having recovered countless GBs of corrupted, and/or stranded, InnoDB data in my days, I am very happy to hear about the new InnoDB Transportable Tablespaces coming in MySQL 5.6!

Back in the day, if you had a stranded .ibd file (the individual InnoDB data file with –innodb-file-per-table option), you basically had nothing (even though that file contained all of the data). This was because unless you had the original instance that that particular .ibd file (table) originated from, there was no way to load it, import, or dump from it. So it was not of much use, though all the data was *right* there.

Thus I created the method of Recovering an InnoDB table from only an .ibd file (I should note that this was before

  [Read more...]

I’d had some difficulty manually creating my own windows service for MariaDB (worked fine from the installer), but it was due to the way I was creating it, so I wanted to share the proper approach:

Old Way:

sc create "maria55" binpath= "\"C:/Program Files/MySQL/MariaDB 5.5/bin/mysqld\"
\"--defaults-file=C:/Program Files/MySQL/MariaDB 5.5/data/my.ini\""
DisplayName= "Maria55" start= "auto"

New Way:

sc create "maria55" binpath= "\"C:/Program Files/MySQL/MariaDB 5.5/bin/mysqld\"
\"--defaults-file=C:/Program Files/MySQL/MariaDB 5.5/data/my.ini\" maria55"
DisplayName= "Maria55" start= "auto"

The key is adding the name, maria55, after the –defaults-file=.. option, but still within the “” that belong to “binpath”.

This extra parameter exists so that mysqld knows whether or not it was started as a service or

  [Read more...]

I just wanted to take a moment and say congratulations to all of the 2012 Community Contributor Award Winners!

The usual suspects, Baron, Sarah, Gerardo, and Sheeri, each won, and deservedly so. All 4 contribute a great deal to the MySQL Community and ecosystem, and it is very much appreciated.

However, I was especially happy to hear that James Day was also a winner (a name everyone might not be so familiar with). But, having worked with James for 5 years, I really couldn’t say it better than Henrik Ingo already did (in his initial post of this year’s winners), so let me quote him:

“James was nominated for ‘MySQL advocacy and swimming upstream to keep people informed

  [Read more...]

I am very pleased to say that earlier today, SkySQL announced it has raised $4 Million in Series A Round Funding.

Let me post the main part of the press release here:

SAN JOSE – April 18, 2012 – SkySQL, the first choice in affordable database solutions for the MySQL® and MariaDB® databases in the enterprise and the cloud, today announces that the company has raised $4 million in Series A funding from a number of investors, including OnCorps, an elite peer-based community of veteran technology investors and advisors committed to bringing better, cost-disruptive technologies into the mainstream. Also funding the round are European investors including Finnish Industry Investment Ltd., Spintop Ventures and Open Ocean Capital.

SkySQL will primarily use the investment to fund growth in its new product development, including adding

  [Read more...]

Last week was full of exciting news for all things MySQL.

However, SkySQL also had some great individual news in that it announced 2 new partners:

• hastexo
• PalominoDB

I just wanted to take a moment and officially welcome both to the SkySQL fold!

PalominoDB and hastexo, it’s great to be partnered with you!

And we’re looking forward to all the exciting things the future has to hold!

For those interested, you can read more about each partnership here:

  [Read more...]

Well, as you or may not have heard, MariaDB 5.5 (5.5.23) was declared GA last week!

It was only about 6-ish weeks ago that MariaDB 5.5 had been released as alpha, so the fact it’s already GA is excellent news for all MariaDB users (and MySQL 5.5 users looking to migrate).

Some of the 5.5 enhancements include:

• Significantly more efficient thread pool, comparable in functionality to the closed source feature in MySQL Enterprise.
• Non-blocking client API Library (MWL#192)
• @@skip_replication option (MWL#234)
• SphinxSE updated to version 2.0.4.
• “extended keys” support for XtraDB and InnoDB
• New INSTALL SONAME statement
• New LIMIT ROWS EXAMINED optimization.
• mysql_real_connect() Changes
  In MySQL, and in MariaDB versions before 5.5.21, mysql_real_connect() removes from the MYSQL object
  

  [Read more...]

Day 0 of the MySQL Conference is a day unlike any other day. It is, in fact, tutorial day. While regular days of the Percona Live MySQL Conference feature 50 minute sessions, usually split into 40 minute talk and a 5-10 minute question period, tutorials are 3 hour long sessions (with a generous 10 minute break in the middle for those that wish to go to the WC) that provide an in-depth dive into some aspect of MySQL. Due to the length of the tutorials, they are more in-depth and technical than individual sessions can provide, but at the same time we are limited to 2 tutorials slots per day instead of the 5 session slots per day. The tutorial schedule for the conference is located here and with so many good ones, it was hard to choose which one(s) to go to. For the morning session, I  [Read more...]

Some time ago, I wrote a rather popular post The MySQL “swap insanity” problem and the effects of the NUMA architecture (if you haven’t read it, stop now and do that!), which described using numactl --interleave=all to balance memory allocation across nodes in a NUMA system.

I should’ve titled it differently

In reality, the problem posed by uneven allocation across nodes under NUMA is not entirely a swapping problem. I titled the previous post as it was and explained it in the way it was explained largely to address a specific problem seen in the MySQL community. However, the problem described actually has very little to do with swap itself. The problem is really related to Linux’s behavior under memory pressure, and specifically the

  [Read more...]

This is just an update to let you know that the new SkySQL Training Schedule is now available online.

The schedule extends as far as September 2012.

There are courses in the Belgium, Germany, France, the United Kingdom, Ireland, the Netherlands, Sweden, and and the United States.

And some of the course titles include:

• Administering a MySQL® Database
• Developing Applications with the MySQL® Database
• Performance Tuning for the MySQL® Database
• High Availability for the MySQL® Database
• Administering MySQL® Cluster

You can read the full schedule here:

http://www.skysql.com/services/training/schedule?vdt=training_course_list|page_3

 

Have you ever wanted to get a list of indexes and their columns for all tables in a MySQL database without having to iterate over SHOW INDEXES FROM ‘[table]‘? Here are a couple ways…

The following query using the INFORMATION_SCHEMA STATISTICS table will work prior to MySQL GA 5.6 and Percona Server 5.5.

SELECT table_name AS `Table`,
       index_name AS `Index`,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE table_schema = 'sakila'
GROUP BY 1,2;

This query uses the

  [Read more...]

I have an internal project that is generating a materialized view of some pretty important data, using 96 application server cores against a 12 core database with 192g of memory and a buttload of SSD, good for about 250 MB/sec peak write rate in synthetic file creation.

The project was underperforming, even with MySQL 5.5-current. We never had more than 15mb/s sustained write rate, and never more than 24G of dirty pages in innodb. Looking at the appropriate (self-made) merlin graphs showed redo log contention and excessive checkpointing.

Install MySQL 5.6.4-m7, which can do large redo logs. A quick back-of-the-napkin calculation shows that around 32GB of redo log are just fine for our worklog, most likely. So I configure 2 ib_logfile of 16000M each, and we turn on the load.

The result is a write rate of 100mb/sec sustained, until





  [Read more...]

This is another, in-case-you-missed-it, but it is definitely worth mentioning again:

MariaDB 5.3 is now GA

So, if you’re using MariaDB 5.1 or 5.2, I’d definitely recommend upgrading to the new MariaDB 5.3.

Also, if you’re a Windows user using MySQL 5.1, I’d highly consider making the switch to MariaDB 5.3 due to all of the Windows-specific improvements in it.

MariaDB 5.3 Enhancements:

• Subquery optimizations
• Semi-join subquery optimizations
• Non-semi-join optimizations
• Subquery Cache
• Subquery Optimizations Map
• Optimizations for derived tables and views
• Disk access optimization
• Join optimizations
• Index Merge improvements
• Optimizer control
• NoSQL-style interfaces
• Group commit for the binary log
• Replication and binary logging enhancements
• Microsecond support for NOW() and timestamp, time, and

  [Read more...]

I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5

I was again reminded why setting SQL_MODE is so important in any new MySQL environment. While performing benchmark tests on parallel backup features with a common InnoDB tablespace and per file tablespace, I inadvertently missed an important step in the data migration. The result was the subsequent test that performed data population worked without any issues however there was no data in any InnoDB tables.

These are the steps used in the migration of InnoDB tables from a common tablespace model to a per-table tablespace model.

Dump all InnoDB tables

Drop all InnoDB tables

Shutdown MySQL

Change the my.cnf to include innodb-file-per-table

Remove the InnoDB ibdata1 tablespace file

Remove the InnoDB transactional log files

Start MySQL

Verify the error log

Create and load new InnoDB

  [Read more...]

Our latest changes have been pushed to public mysql@facebook branch, allowing this post to happen \o/

Recently we started rolling out InnoDB compression to our main database tier, and that has been a huge undertaking for multiple teams and a major test for MySQL. Nizam was sure the hero of all this work, and make sure you don’t miss his talk about it at MySQL conference.

Though MySQL manuals have quite some introduction about benefits of compression, we agree that benefits are good – in theory we can do less reads from disk, keep more data in buffer pool or

  [Read more...]